Skip to main content

Featured Post

Why I Wrote The Sheet Mechanic (And Why Calculations Aren’t Enough)

For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...
Post a Comment
NEW RELEASE: Stop trying to be a Hero. Start being a Mechanic. Get "The Sheet Mechanic" on Amazon »
Disclosure: As an Amazon Associate, I earn from qualifying purchases.

DIY Motion Simulation: Visualize Machine Timing with Excel VBA

Motion Simulation with Microsoft Excel

Mechanical design usually deals with the movement of several parts. There are a lot of software packages that help simulate the movement of machine parts, such as NX (UG), Solid Edge, and SolidWorks.

Advertisement

But how do you make a simulation without those expensive software packages?

I use Microsoft Excel to simulate my timing diagrams. As you can see in the video above, it provides a clear visual representation of the machine's cycle.

How it Works

Actually, it is nothing but motion simulation using the graphing method in Microsoft Excel. While it cannot simulate complex 3D collisions or physics like high-end CAD, it helps you get a feel for how your machine will behave according to your timing diagram.

This technique uses VBA (Visual Basic for Applications) to control the movements. Here is the general workflow:

  1. Prepare the Spreadsheet: Create a table defining the geometry (coordinates) of your machine parts.
  2. Set Formulas: Use Excel formulas to manipulate location (x, y) and rotation (angle) based on a time variable.
  3. Link the Chart: Create an X-Y Scatter chart linked to these coordinate tables.
  4. Automate with VBA: Write a simple VBA loop that incrementally changes the "Time" value. As the value changes, the formulas update the coordinates, and the chart updates instantly, creating an animation.
Advertisement

For example, in the video, I used an indexing box on my transport conveyor which has 4 stops. The cam profile used is MS (Modified Sine). By putting the MS mathematical function into the VBA code, I can visualize exactly how the conveyor moves relative to other mechanisms.

VBA Code Example: Modified Sine (MS) Function

Below is the specific VBA code for the Modified Sine cam curve used in the simulation. You can use this function within your own Excel macros to calculate displacement based on time.

' Modified Sine function
' T is dimensionless time (0 to 1)
' hm is stroke of movement

Function MS(T As Double, hm As Double)
    Const PI = 3.14159265358979
    Dim T5 As Double, T6 As Double
    Dim V1 As Double, V2 As Double, V3 As Double, V4 As Double, V5 As Double, V6 As Double
    Dim S1 As Double, S2 As Double, S3 As Double, S4 As Double, S5 As Double, S6 As Double
    Dim Am As Double
    Dim s As Double

    Const T1 = 0.125
    Const T2 = 0.125
    Const T3 = 0.5
    Const T4 = 0.5

    T5 = 1 - T1
    T6 = T5
    Am = 1 / (2 * T1 / PI + (2 - 8 * T1) / PI ^ 2)

    V1 = 2 / PI * T1 * Am
    V2 = V1
    V5 = V1
    V6 = V1

    V3 = 2 / PI * (T3 - T2) * Am + V2
    V4 = V3

    S1 = 2 * T1 ^ 2 / PI * Am - (2 * T1 / PI) ^ 2 * Am
    S2 = S1

    S3 = (2 / PI * (T3 - T2)) ^ 2 * Am + V2 * (T3 - T2) + S2
    S4 = S3

    S5 = 1 - S1
    S6 = S5

    Select Case T
        Case 0 To T1
            s = 2 * T1 / PI * Am * (T - 2 * T1 / PI * Sin(PI * T / (2 * T1)))
        Case T2 To T3
            s = (2 / PI * (T3 - T2)) ^ 2 * Am * (1 - Cos(PI * (T - T2) / (2 * (T3 - T2)))) + V2 * (T - T2) + S2
        Case T4 To T5
            s = 2 / PI * (T5 - T4) * Am * (2 / PI * (T5 - T4) * Sin(PI * (T - T4) / (2 * (T5 - T4))) - (T - T4)) + V4 * (T - T4) + S4
        Case T6 To 1
            s = (2 / PI * (1 - T6)) ^ 2 * Am * (Cos(PI * (T - T6) / (2 * (1 - T6))) - 1) + V6 * (T - T6) + S6
        Case Else
            s = 1
    End Select

    MS = s * hm

End Function

By implementing functions like this, you can create powerful engineering simulations directly on your desktop.

Labels:

Comments

Post a Comment

Popular posts from this blog

Ball Detent Torque Limiter – Working Principle & Selection

Figure 1: The ball detent mechanism provides precise overload protection by disengaging instantly when the torque limit is exceeded. The First Line of Defense: Overload Clutches In high-speed automation and heavy industrial machinery, a "jam" is not a matter of if , but when . Whether it is a cardboard box getting stuck in a packaging machine or a tool crashing in a CNC lathe, the resulting torque spike can destroy gearboxes, twist shafts, and burn out expensive servo motors in milliseconds. A torque limiter (or overload clutch) is the mechanical fuse of the drive system. While electronic monitoring (current limiting) is common, it is often too slow to prevent physical damage from the massive kinetic energy stored in the system inertia. A mechanical torque limiter provides a physical disconnect that operates in a fraction of a second. Search for Torque Limiters & Safety Couplings Advertisement Why Choose ...
Post a Comment
Read more about this Mechanical Design Article »

Taper-Lock Bushing Failures: The Physics of Wedge Angles

The Failure Scenario: A technician is installing a heavy 20-inch cast-iron V-belt sheave onto a motor shaft using a standard Taper-Lock® bushing. To make future removal easier, they apply a generous coat of anti-seize lubricant to the tapered surface. They tighten the set screws with an impact wrench. Instantly, a loud "CRACK" echoes through the shop. The massive cast-iron hub has split perfectly in half. The Cause: The technician misunderstood the physics of mechanical wedges and friction. By lubricating the taper and using unregulated torque, they generated a radial expansion force that exceeded the Ultimate Tensile Strength of the cast iron, causing a catastrophic hoop stress failure. Tapered bushings (including Taper-Lock and QD styles) are the industrial standard for mounting sprockets, gears, and pulleys to shafts. They rely on the mechanical advantage of a wedge to create massive clamping friction. This guide explains the physics of wedge mechanics, the de...
Post a Comment
Read more about this Mechanical Design Article »

Polynomial Cams: Analysis & Design Pitfalls (Part 3)

Figure 1: Mathematical coefficients determine the physical shape. Poor math leads to physical defects like the "dip" shown on the right. In [ Polynomial Cam Function (Derivation of Fifth-degree function) - Part 2 ], we derived the equations for the Fifth-Degree (3-4-5) Polynomial . Advertisement Now, we apply this math to the real world of Mechanical Cam Design . The shape of the physical cam is determined by plotting these functions. Unlike a standard Cycloid curve, the polynomial allows us to manipulate the Start Velocity (v 0 ) and End Velocity (v 1 ) of the follower. However, this flexibility requires careful design. If the coefficients are not balanced, the physical cam profile can develop "dips" or negative slopes, causing the mechanical linkage to behave unpredictably. Case 1: Standard Dwell-to-Dwell (Zero Velocity) Figure 2: The standard profile (v 0 =0, v 1 =0). Safe, smooth, and ide...
Post a Comment
Read more about this Mechanical Design Article »