Motion Simulation with Microsoft Excel

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.

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.

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.