Roller Coaster - bizarre chart in Excel
Channel: EngineeringFun
Date: 2011-06-24

A 3D animated roller coaster in Excel... 
 DOWNLOAD A RECENTLY UPDATED VERSION here: excelunusual.com/a-3d-animated-excel-roller-coaster-video-preview/  

My approach is targeted to the practical person who enjoys tinkering and does not want to learn standard syntax, but use Google and common sense to create cool work in a "step by step, little by little" approach. If you got stuck, it generally means the step was too large, or you didn't play enough at the previous step. This is also targeted to the person who thinks life is short, wants to get a general programming sense, but would rather play than study hard to get a promotion or a grade.

Some basic geometry and very basic math/physics might be required here and there, but as minimal as possible. No need to go back to school, but refresh things when needed by using Google.

Download: https://excelunusual.com/a-3d-animated-excel-roller-coaster-video-preview/

Original tags :  idiots at work,  NRA, foot locker, Champs, Eastbay, End of the World, Suzy Favor Hamilton, December 21 2012, Animation, Sensational, incredible, Excel animation


With the risk of boring you to death :) here is a more in-depth outline:

If there is a point in the 3D space with coordinates (x,y,z), there are 2 simple formulas that convert (x,y,z) in (u,v) which is a 2D representation of the 3D space. A camera or the eye do that conversion (the film or the retina are flat, almost).

Your eye can only see in 2D but interprets that image as 3D. That's what I did here, I am using a 2D scatter chart to draw a complex line that mimics a 3D landscape. Think of having a 3D object outside the window and you shoot with a gun at the critical points (perimeter for instance, or corners, etc). The bullet holes would form that 2D representation of the 3D object on the glass window screen (computer monitor). Essentially the 3D object outside, produces the same image on the retina as the bullet holes on the window glass. The two formulas to do this are extremely simple. https://excelunusual.com/3d-2d-perspective-mapping-in-excel-part-1/

Two numbers in adjacent Excel cells (2 excel cells) can define (if selected properly) the (x, y) coordinates of a point on an Excel 2D scatter chart. Two points (4 Excel cells) like this determine a line segment between the points. If you have a series of points in a large table, you define a complex line. If you delete the data somewhere in the middle, the complex line is still there but "broken" at the point where the data is missing. 

This particular model is essentially a line plot on a 2D scatter chart. The vertices are from a table which is being re-calculated in a loop perhaps 30 times a second (as soon as a calculation is finished and refreshed/displayed a new calculation starts). I plot 3 lines (based on 3 tables) on the chart only and I select “broken line” rather than smooth in the chart settings. Why 3? Because there are 3 colors in the model, white, green and brown.

I made the chart with black background and deleted any axes or grid lines from the settings. At that time I knew very little VBA and most of the geometrical perspective is calculated in cell formulas within tables. Today I would do it differently, all VBA. Plus I wouldn’t use a chart, just have VBA build this real time out of free segments, which are destroyed and rebuilt during each frame. It would be faster especially with the sluggish charting in new versions.

George from excelunusual.com