### Motion Simulation with Microsoft Excel

**Motion Simulation with Microsoft Excel**

Mechanical design usually deals with movement of several parts. There are a lot of softwares that help simulate the movement of concerned parts of machines such as UG, Solid Edge, Solid Work, etc.

But

**how do you make simulation without those softwares?**

I use

**Microsoft Excel**to do the simulation of my timing diagram. Watch the video.

Actually it is nothing but motion simulation using graphing method in Microsoft Excel. It cannot simulate all of your complex movements, but it helps you get the feeling of how your machine will behave according to your timing diagram.

This technique uses VBA (Visual Basic for Application) to control the movements. What you have to do is to prepare the excel spreadsheet with the table defining the sketch of your machine parts to be used in simulation. You have to set the formula in excel spreadsheet to manipulate the location (x,y) and rotation (angle). After proper settings, just let VBA code, which is embedded in Microsoft Excel, changes the control values in the manipulation table (the x-y chart is linked to this table). Then you can control the movement of parts that move in translation or rotation. For example, I use an indexing box at my transport conveyor which has 4 stops and the cam profile is MS (Modified Sine). So I put MS function into my VBA code and I can see how my conveyor is moving at the same as others.

Example of Modified Sine Movement Function.

=================================================================

' Modified Sine function

' T is dimensionless

' 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

=================================================================

For those who are interested in downloading the example file, please click the link below

DOWNLOAD EXAMPLE FILE FOR FREE

Extract the zip file with password: mechanical-design-handbook.blogspot.com

## Comments