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