Time - Accurate Vehicle Animation using Excel VBA

[mlonderg]

I am a novice VBA/Excel programmer in need of help. I work for a small firm, Brach Engineering, that develops accident reconstruction software. <o></o>
<o> </o>
To give you an idea of what we're looking for, Jon Peltier's “RollingWheel” example (http://peltiertech.com/WordPress/rolling-wheel-animation/) inspired us to look into animating positional data that our vehicle dynamics simulation software produces. From the start, we needed the animation to represent the real-time motion of the vehicle, so we used the windows API timer (http://www.cpearson.com/excel/OnTime.aspx) to set the pace. We’re trying to produce results like the “RollingWheel” example, including tracing points on the vehicle, etc. To accomplish this, we are currently relying on an excel chart for the graphical representation of the vehicle.<o></o>
<o> </o>
We are looking for a performance of 15 to 30 frames per second, and have not been able to achieve it yet. <o></o>
<o> </o>
Our code below creates a new chart series and updates the X and Y values for each iteration of the API timer. <o></o>
<!--[if !supportLists]-->-<!--[endif]-->RmtnX and RmtnY are the positional arrays for the vehicle.<o></o>
<!--[if !supportLists]-->-<!--[endif]-->Actr is updated with each iteration, thus pointing to correct row of positional data for each timestep.<o></o>
<!--[if !supportLists]-->-<!--[endif]-->The process continues until all the data has been displayed.<o></o>
<o> </o>
We notice a large lag with our current compilation. An animation that should take 10 seconds takes up to 25 to complete when we try to animate at 15 or 30 fps. We achieve acceptable results at less than 10fps, but this too low of a frame rate for our purposes.<o></o>
<o> </o>
We have tried various methods, including the “SERIES” function with a counter like that used in the “RollingWheel” example. All of our efforts have focused on minimizing read/write to the spreadsheet (using arrays, etc). Regardless, all of our attempts show an evident delay, even though the API timer is ticking right on time. <o></o>
<o> </o>
Is there a better way of doing this in VBA that doesn’t create such a delay?

Should we look outside of VBA to solve this problem?

Might there be some sort of graphics engine that we could use that would allow us to get through this?<o></o>
<o> </o>

Any input is much appreciated.

Regards,
Matthew Londergan
Brach Engineering
<o></o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o></o>
<o> </o>

Sub Fwd()
   
  Actr = 0
   
  Set VehicleSRS = ActiveChart.SeriesCollection.NewSeries
   
  With VehicleSRS
          .Name = "Vehicle"
          .Values = Array(RmtnY(Actr, 0), RmtnY(Actr, 1), RmtnY(Actr, 2), RmtnY(Actr, 3), RmtnY(Actr, 4))
          .XValues = Array(RmtnX(Actr, 0), RmtnX(Actr, 1), RmtnX(Actr, 2), RmtnX(Actr, 3), RmtnX(Actr, 4))
  End With
   
  StartTimer
   
  End Sub
   
   
  Sub TimerProc(………..)
   
     With VehicleSRS
          .Values = Array(RmtnY(Actr, 0), RmtnY(Actr, 1), RmtnY(Actr, 2), RmtnY(Actr, 3), RmtnY(Actr, 4))
          .XValues = Array(RmtnX(Actr, 0), RmtnX(Actr, 1), RmtnX(Actr, 2), RmtnX(Actr, 3), RmtnX(Actr, 4))
      End With
          
      Actr = Actr + 1 
      
      If Actr >= Trow Then
              EndTimer
      End If
  End Sub