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:

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?