Solution - Experienced Challenge 4: Hmmm, So Maybe This is Rocket Science After All
This is the story of The Challenge That Almost Wasn’t. When we first thought of adding a missile trajectory challenge to OfficePalooza (because, after all, could you really call yourself a Palooza if you didn’t have missiles in there somewhere?) we immediately turned to the Internet for help on calculating such a trajectory.
Note. Yes, you’d think that we’d know how to calculate missile trajectory off the top of our heads, wouldn’t you? Sadly, we must confess that we don’t. However, if you ever need to know how to calculate a pitcher’s earned run average in baseball, well, feel free to give us a call. That’s something we do know how to calculate.
At any rate, the first algorithm we found was about 1,000 pages long, and included such considerations as air resistance, the effects of gravity at various altitudes, and the rotation of the earth. That would have been challenging, but we were afraid that it might be a little much to ask of people. Therefore, we decided to skip the missile trajectory challenge.
Of course, a few days later – when we had to submit the final list of challenges – we realized we hadn’t come up with a replacement event. That meant missile trajectory was back. And, fortunately (for you, if not for us) we found a much simpler algorithm for calculating that trajectory.
Kind of a heart-warming story, if we do say so ourselves.
But we don’t have time for stories, do we? Instead, we have code to show, and code to explain:
Sub PlotTrajectory()
accelerationX = 0
accelerationY = -9.8
originalVelocity = 40
projectileAngle = 35 * (WorksheetFunction.Pi() / 180)
velocityX = originalVelocity * Cos(projectileAngle)
velocityY = originalVelocity * Sin(projectileAngle)
apexTime = (accelerationX - velocityY) / accelerationY
maximumHeight = (velocityY * apexTime) + _
(0.5 * accelerationY) * (apexTime * apexTime)
totalTime = apexTime * 2
totalDistance = velocityX * totalTime
Application.Cells(8, 3) = velocityX
Application.Cells(9, 3) = velocityY
Application.Cells(11, 3) = apexTime
Application.Cells(12, 3) = maximumHeight
Application.Cells(14, 3) = totalTime
Application.Cells(16, 3) = totalDistance
Application.Cells(18, 3) = 0
Application.Cells(18, 4) = 0
Application.Cells(19, 3) = totalDistance / 6
dblDistance = Application.Cells(19, 3)
Application.Cells(19, 4) = (dblDistance * (velocityY / velocityX)) - _
(0.5 * accelerationY * -1 * dblDistance * dblDistance) / (velocityX * velocityX)
Application.Cells(20, 3) = totalDistance / 3
dblDistance = Application.Cells(20, 3)
Application.Cells(20, 4) = (dblDistance * (velocityY / velocityX)) - _
(0.5 * accelerationY * -1 * dblDistance * dblDistance) / (velocityX * velocityX)
Application.Cells(21, 3) = totalDistance / 2
Application.Cells(21, 4) = maximumHeight
Application.Cells(22, 3) = totalDistance * (2 / 3)
Application.Cells(22, 4) = Application.Cells(20, 4)
Application.Cells(23, 3) = totalDistance * (5 / 6)
Application.Cells(23, 4) = Application.Cells(19, 4)
Application.Cells(24, 3) = totalDistance
Application.Cells(24, 4) = 0
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Worksheets("Sheet1").Range("C18:C24")
ActiveChart.SeriesCollection(1).Values = Worksheets("Sheet1").Range("D18:D24")
Worksheets("Sheet1").ChartObjects(1).Left = Worksheets("Sheet1").Columns("E").Left
Worksheets("Sheet1").ChartObjects(1).Top = Worksheets("Sheet1").Rows("3").Top
End Sub
We start off by assigning values to four different variables:
accelerationX = 0
accelerationY = -9.8
originalVelocity = 40
projectileAngle = 35 * (WorksheetFunction.Pi() / 180)
These are the values needed in order to make our calculations, and these are the values we included on the Experienced_challenge_4.xlsm spreadsheet. You might have noticed we hard-coded these values into our script. That’s fine; the instructions didn’t require you to read the values off the spreadsheet. A few of you went ahead and did that anyway, using code similar to this:
Val(Application.Cells(4, 3))
This is actually a clever solution to a problem we inadvertently created. We listed our values by mixing numbers and text; for example, 40.0 m/s. That’s fine, except it makes it tricky to pull out just the number (needless to say, you can only use the value 40 in an equation, not the value 40 m/s). The Val function strips away the text and returns just the number, which is exactly what we were hoping it would do.
Like we said, very clever.
After we assign values to our variables we then use those variables in a series of mathematical equations, equations that determine everything from the velocity in the X (horizontal) and Y (vertical) directions; the time it takes before the missile reaches its maximum height; the maximum height itself; the total flight time; and the total distance covered. (Whew!) Here are the calculations, which we won’t bother explaining in any detail (in part because we don’t fully understand the details):
velocityX = originalVelocity * Cos(projectileAngle)
velocityY = originalVelocity * Sin(projectileAngle)
apexTime = (accelerationX - velocityY) / accelerationY
maximumHeight = (velocityY * apexTime) + _
(0.5 * accelerationY) * (apexTime * apexTime)
totalTime = apexTime * 2
totalDistance = velocityX * totalTime
As soon as the calculations are complete we then assign those values to the appropriate boxes on the spreadsheet (for example, the total flight time is assigned to cell C14, the cell labeled Total flight time):
Application.Cells(8, 3) = velocityX
Application.Cells(9, 3) = velocityY
Application.Cells(11, 3) = apexTime
Application.Cells(12, 3) = maximumHeight
Application.Cells(14, 3) = totalTime
Application.Cells(16, 3) = totalDistance
That resulted in a series of spreadsheet cells that looked like this:
Velocity in the X direction |
32.7660818 |
Velocity in the Y direction |
22.9430575 |
Time until projectile reaches its apex |
2.34112831 |
Maximum projectile height |
26.8563207 |
Total flight time |
4.68225662 |
Distance traveled |
153.419203 |
And now the fun begins. (Yes, we know: you have been having fun up to this point, haven’t you? But now the fun gets even funner.)
Our next step is to plot the distance and height of the missile at various points in the trajectory. We know the height and distance at takeoff: the missile is 0 meters off the ground and has traveled 0 meters. We also now know the height and distance at touchdown: the missile will, again, be 0 meters off the ground and will have traveled 153.419203 meters. (How did we know that? Because we calculated the total distance traveled.) We also know the height and distance at the midpoint of the flight: the height is the maximum height and the distance is the distance traveled divided by 2.
Man, that was easy; maybe we’re in the wrong line of work. (Something our manager keeps telling us.)
At this point we told you that you could just estimate the height and distance traveled for the remaining plot points. Just for the heck of it, we decided to go ahead and calculate the height and distance traveled. That’s what our blocks of code that look like this are for:
Application.Cells(19, 3) = totalDistance / 6
dblDistance = Application.Cells(19, 3)
Application.Cells(19, 4) = (dblDistance * (velocityY / velocityX)) - _
(0.5 * accelerationY * -1 * dblDistance * dblDistance) / (velocityX * velocityX)
By the time we finished with these calculations we ended up with the following set of plot points:
Plot point 1 |
0 |
0 |
Plot point 2 |
25.5698672 |
14.9201782 |
Plot point 3 |
51.1397345 |
23.872285 |
Plot point 4 |
76.7096017 |
26.8563207 |
Plot point 5 |
102.279469 |
23.872285 |
Plot point 6 |
127.849336 |
14.9201782 |
Plot point 7 |
153.419203 |
0 |
Admittedly, your plot points might be different from ours; that depends on how (or if) you estimated plot points 2, 3, 5, and 6. But that’s OK. Our only concern was that you followed the basic instructions and came up with something that looked something like a missile trajectory. The details didn’t matter much to us.
And no, we don’t think that’s the case with people who calculate missile or rocket trajectories for real.
Well, except maybe the people who did the calculations for the Mars Surveyor.
All that’s left now is to add a chart that graphs our trajectory. There are a number of different ways to add a chart to an Excel spreadsheet; we decided (for no reason other than the fact that we already knew how to do this) to start the process by calling the AddChart method to add a chart to Excel’s Shapes collection:
ActiveSheet.Shapes.AddChart.Select
After the chart has been added we then configure it using these four lines of code:
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Worksheets("Sheet1").Range("C18:C24")
ActiveChart.SeriesCollection(1).Values = Worksheets("Sheet1").Range("D18:D24")
In this first line, we set the ChartType to xlXYScatterSmoothNoMarkers; that’s an Excel constant that gives us the desired scatter plot chart. In the next three lines we define the data series for the charts; as you can see we’re using cells C18 through C24 for the X values and D18 to D24 for the Y values.
Those lines of code will draw us a chart; as a matter of fact, it will draw us this chart:
There’s only one problem: by default, the chart gets plopped down smack dab in the middle of our spreadsheet. (Not very aesthetically-pleasing, in no small part because it covers up a good portion of our data.) Therefore, the very last thing we do is use these two lines of code to move the upper left corner of the chart to cell E3:
Worksheets("Sheet1").ChartObjects(1).Left = Worksheets("Sheet1").Columns("E").Left
Worksheets("Sheet1").ChartObjects(1).Top = Worksheets("Sheet1").Rows("3").Top
And that, as they say, is that. But hey, you must have known that The Challenge That Almost Wasn’t would have a happy ending, right?