Wednesday, March 19, 2008

Motion Simulation with Microsoft Excel


video
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