This is the next in a series of projectile motion tutorials for creating 2D trajectory models using numerical analysis of projectile dynamics (including aerodynamic drag).
The trajectory formulas were derived in the previous tutorial.
This post describes the Excel implementation (spreadsheet formulas, VBA code, buttons and charts).
[sociallocker]
[/sociallocker]
Projectile Motion Tutorial #5 – a 2D projectile motion model using numerical analysis of projectile dynamics (including aerodynamic drag)
by George Lungu <excelunusual.com>
An outline of the formulas derived in the previous part:
As a conclusion, to run this model for as long as we want, all we need is the above four formulas plus the above ten constants.
We choose t so as to get a good compromise speed-precision (a smaller Dt results in a better precision but a slower model).
Let’s implement this in Excel:
-Copy the “Tutorial_3” worksheet and name the new worksheet “Tutorial_4-5”
-Cut C25:G2200 and paste it one column to the right (to D25:H2200)
-Replace G36 with H36 in the macro “Fire”
-Hit the “Fire” button and verify that the macro and the animation are still working properly
Sub Fire()
[B20] = 0
Do While [G36] > 0 And [B20] < 2000
DoEvents
[B20] = [B20] + 1LoopSleep 1000[B20] = 0
Exit Sub
End Sub
<excelunusual.com> 2
Add the following entries to the input data area:
-Air Density (cell B1), Projectile Frontal Area (cell B3), Drag Coefficient
CX (cell B5) and Projectile Mass (cell B7)
– The buttons for Projectile Frontal Area and Projectile Mass can go
between Min=0 and Max=18
Private Sub Frontal_Area_Change()
arrScale = Array(1, 2, 5)
[B3] = arrScale(Frontal_Area.Value Mod 3) * 10 ^ Int(Frontal_Area.Value / 3) / 1000000
End Sub
——————————————————————————————————————
Private Sub Mass_Change()
arrScale = Array(1, 2, 5)
[B7] = arrScale(Mass.Value Mod 3) * 10 ^ Int(Mass.Value / 3) / 100000
End Sub
-The fist macro changes the Projectile Frontal Area by the following series (oscilloscope style):
0.000001, 0.000002, 0.000005. 0.00001, 0.00002…….0.02, 0.05, 0.1, 0.2, 0.5, 1
– The second macro changes the Projectile Mass by the following series (oscilloscope style):
0.00001, 0.00002, 0.00005. 0.0001, 0.0002… 0.2, 0.5, 1, 2, 5, 10
– This type of macro uses two VBA functions, “Mod” and “Int” to crate an “oscilloscope style” almost
exponential variable setting and it will be explained in a future post
<excelunusual.com> 3
The initial conditions:
– The cells you can see to the right with text in the highlighted
areas are just labels
– After filling in for labels let’s input the initial conditions:
– Cell B26: “=B10*COS(RADIANS(B12))”
– Cell C26: “=B10*SIN(RADIANS(B12))”
– Cell D26: “=0”, Cell E26: “=B14”
Here are the formulas (from page 2 of the current presentation):
previous x previous_ y
-Cell B27: “=B26*(1-B$1*B$3*B$5
current x previous_ x
*SQRT(B26^2+C26^2)*B$16/(2*B$7))”
previous x previous_ y
Cell C27: “=C26*(1-B$1*B$3*B$5*
current_ y previous_ y
SQRT(B26^2+ C26^2)*B$16/(2*B$7))-
9.81*B$16”
current previous current_ y
– Cell E27: “=E26+C27*$B$16”
– After entering the above formulas copy down the range B27:E27 to row 2100
<excelunusual.com>4
We need to add a column with the total speed data:
– As opposed to the previous model, in
this one, the total speed drops in flight
due to the air friction. It would be nice to
plot the total projectile speed along the
trajectory
– We can find the total speed from the X
and Y speed components using
Pythagoras theorem
– The data for the total speed will be
placed in the range J26:J2100
– To the right there is a snapshot of the
finalized calculation area of the
spreadsheet
– We will add a new scatter chart (green)
displaying the total speed on top of the
existing trajectory chart. The X data for
this chart is taken from range D26:D2026
and the Y data is taken from J26:J2026
<excelunusual.com> 5
Chart renaming macros:
-We use these macros one by one to set the chart names to “Chart_1” and “Chart_2” respectively
-Select the lower chart and run “Rename1” from the VBA editor, Repeat with the upper chart and “Rename2”
Sub Rename1()
ActiveChart.Parent.Name = “Chart_1”
End Sub
Sub Rename2()
ActiveChart.Parent.Name = “Chart_2”
End Sub
Chart scale adjustment macros:
-The upper macro is used to adjust the X axes of both charts to a value between 1 and 1 million (same value using an “oscilloscope style” pseudo exponential rule: 1, 2, 5, 10, 20, 50, …)
-The lower macro is used to adjust the Y axis of the trajectory chart from 1 to 1 million (the upper chart Y scale is set on “Auto”)
-On the sheet there are two but-tons associated to these macros
Private Sub Scale_X_Change()
Dim aX As LongarrScale = Array(1, 2, 5)
aX = arrScale(Scale_X.Value Mod 3) * 10 ^ Int(Scale_X.Value / 3)
ActiveSheet.ChartObjects(“Chart_1”).Chart.Axes(xlCategory).MaximumScale = aX
ActiveSheet.ChartObjects(“Chart_2”).Chart.Axes(xlCategory).MaximumScale = aX
End Sub
Private Sub Scale_Y_Change()
Dim aX As LongarrScale = Array(1, 2, 5)
aY = arrScale(Scale_Y.Value Mod 3) * 10 ^ Int(Scale_Y.Value / 3)
With ActiveSheet.ChartObjects(“Chart_1”).Chart
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = aY
End With
End Sub
To be continued…
by George Lungu <excelunusual.com>
