My VBA macro displays an animation based on a row-by-row time step simulation in the worksheet. The user enters the desired inputs and the worksheet calculates the evolution of the simulation in a 1000 row table. The user then runs the macro to display the results in an animation. The macro loops through the rows in the simulation. For each row, the macro extracts four values and applies them as positions to four shapes in the worksheet.

The macro runs fast when I open the workbook and run the macro without doing else first. If I do an edit to the worksheet that causes it to recalculate the simulation, then the macro runs about 1/3 as fast.

The Task Manager shows some interesting things about the memory usage. When the macro is run without an edit, the usage jumps 66,000 K every run. When the macro is run after an edit, the usage jumps only 2,000 K every run. I've checked the values of ScreenUpdating, EnableEvents, Calculation and DisplayPageBreaks, and they don't change if the worksheet is edited.

Is there something I can do to prevent this other than saving and reopening the workbook after I make an edit?

Sub playfast()
Dim arrRangeValues() As Variant
Dim j As Long
Dim vpos As Double
Dim hpos As Double
Dim calcflag As Boolean
Dim chartflag As Boolean
Dim eventflag As Boolean

If iVal = 0 Then iVal = 1
If iVal = 1000 Then iVal = 1
If iVal = 1001 Then iVal = 1
isCancelled = False
calcflag = ActiveSheet.Cells(8, 12).Value
chartflag = ActiveSheet.Cells(9, 12).Value
eventflag = ActiveSheet.Cells(10, 12).Value

ActiveSheet.Cells(7, 15).Value = "PLAYING"
vpos = ActiveSheet.Cells(6, 12).Value
hpos = ActiveSheet.Cells(7, 12).Value

arrRangeValues = Range("a41:q1041").Value

For j = iVal To 1001 Step ActiveSheet.Cells(24, 12).Value
    iVal = j
    show hpos, vpos, vpos - arrRangeValues(j, 4) * 2, vpos - arrRangeValues(j, 17) * 2, vpos - arrRangeValues(j, 5) * 2
    ActiveSheet.Cells(17, 10).Value = arrRangeValues(j, 1)  'Write time into worksheet
    If eventflag Then DoEvents    'Yield to operating system.
    If calcflag Then ActiveSheet.Calculate   'May be necessary for animation to move after worksheet is changed

    If chartflag Then
    End If

    If isCancelled Then GoTo EarlyExit

Next j
isCancelled = False
ActiveSheet.Cells(7, 15).Value = "STOPPED"

End Sub`
Sub show(leftedge As Double, groundtop As Double, bodytop As Double, wheelstop As Double, passengerstop As Double)

ActiveSheet.Shapes("ground").Left = leftedge
ActiveSheet.Shapes("ground").Top = groundtop

ActiveSheet.Shapes("body").Left = leftedge
ActiveSheet.Shapes("body").Top = bodytop

ActiveSheet.Shapes("wheels").Left = leftedge
ActiveSheet.Shapes("wheels").Top = wheelstop

ActiveSheet.Shapes("passengers").Left = leftedge
ActiveSheet.Shapes("passengers").Top = passengerstop

End Sub
  • \$\begingroup\$ A few questions: Where is iVal coming from? What are the type of calculations performed in the worksheet (through the formulas), especially those that are necessary each time your code loops? You're bringing your 1000 row range into an array prior to the loop, but if the worksheet recalculates values in that area, the updated values are not re-copied into the array (so the calculations don't matter). Do you have sample data that we can use to replicate the behavior you're seeing? \$\endgroup\$ – PeterT Nov 30 '18 at 13:18
  • \$\begingroup\$ iVal is used to save the current place in the loop if the user wants to stop the animation. The loop stops via EarlyExit if the user sets isCancelled to True while the loop is running. When the user runs playfast() again, the loop resumes where it stopped. \$\endgroup\$ – Paul N Nov 30 '18 at 22:30
  • \$\begingroup\$ The range actually does get re-copied if the user runs playfast() again, and the loop starts with j set to the value stored in iVal. Ordinarily the user will reset iVal via another macro called reset() before playing the animation again if he edits the worksheet. I can provide sample data if that would be helpful. \$\endgroup\$ – Paul N Nov 30 '18 at 23:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.