Cfrtjryk

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

        ActiveSheet.ChartObjects("uprchart").Activate

        ActiveSheet.ChartObjects("uprchart").Chart.Refresh

        ActiveSheet.ChartObjects("lwrchart").Activate

        ActiveSheet.ChartObjects("lwrchart").Chart.Refresh

    End If



    If isCancelled Then GoTo EarlyExit



Next j

EarlyExit:

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