Why does activating specific sheets affect run time of my macro

Question

Edited for brevity/correctness: Scroll to the end for a TL/DR

I run a fairly lengthy macro which opens a bunch of files from Workspace/Sharepoint, copies things over to my excel sheet, loads data into arrays, compares array entries, writes data to sheets etc. It uses PowerPivot and Power Query and reads/writes things in several sheets in several workbooks.

During a hunt for code optimization options, I discovered that activating a sheet with the following line made my code run 3x faster. I would like to figure out why and how, in order to avoid such slow downs in the future.

ThisWorkbook.Sheets("Dashboard").Activate

I also changed the "Dashboard" to another, freshly added, completely empty sheet (sheets(15)) and that had no effect on the run time, meaning its not 3x faster. I thought, working on an active sheet has some effect. But no, activating the new, empty and unused sheet didnt make the code faster. I use the following macro enhancements:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

I do not use .activate anywhere else in the code. (I cntrl+f'ed that specifically). The .activate is right before a lengthy For-Loop which calls 3 other subs. In the following picture you can see the run times of the For-Loop.

See the image for some run times in seconds

In the picture start refers to the time taken to assign some variables etc.. Tools, serial numbers and other are the subs ran during the loop. Those numbers are the sum of all steps during the loop, the loop mostly has about 300ish steps which may take from 0s to 0.3s.

The following is part of the code. I will post the For-Loop and the "check_others" sub as well as the subs it calls. That is the shortest sub but as you can see in the picture above, even that sub is effected, so what ever is going on does not seem to only effect one sub. For ease of reading I also got rid of the code that timed the loop.

'Go from first row of the Prisma report to the last row. (Reference WITHIN the pivot table. e.g: 1 is absolut row 6)
RowCount = ThisWorkbook.Sheets("Prisma").PivotTables("OperationData").DataBodyRange.Rows.Count
PrismaArr = ThisWorkbook.Sheets("Prisma").Range("H6:U" & RowCount + 5)

'It is unclear why and how but without this line, the code takes 3x longer to execute.
ThisWorkbook.Sheets("Dashboard").Activate

For Prismarow = 1 To RowCount

    'Set some of the most used variables and progres bar. (Indicator, Comment, Instruction)
    progress 13 + (Prismarow / RowCount) * 82, "Analizing"
    IndicatorSkipFurtherAnalyses = 0
    CommentToBeAnalysed = PrismaArr(Prismarow, 5)
    InspectionInstruction = PrismaArr(Prismarow, 6)


'#7     G - Check Tools
        Call Check_Tools(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction)

'#8     H - Check serial numbers
        If Not IndicatorSkipFurtherAnalyses Like "1" Then _
        Call Check_Serial_Numbers(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction)

'#9     I - Check for others (everything else)
        If Not IndicatorSkipFurtherAnalyses Like "1" Then _
        Call Check_Others(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction)

SkipThisEntry:
Next

Call Check_Others(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction):

Sub Check_Others(ByVal PrismaArr, ByVal Prismarow As Long, ByVal CommentToBeAnalysed As String, ByVal InspectionInstruction As String)

'Check if the entry was already deleted, ignore it otherwise
If Not PrismaArr(Prismarow, 7) Like "E D" Then


    'Check that all entries on the PDF which are not tools or serial numbers are empty
    If PrismaArr(Prismarow, 13) Like "2" And PrismaArr(Prismarow, 10) Like "FALSCH" And PrismaArr(Prismarow, 11) Like "FALSCH" Then
        If Not CommentToBeAnalysed Like "" Then
        Call Fill_Out_Others_Analysis(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction, 5)
        End If
    End If

    'Check if there should have been a serial number here
    If PrismaArr(Prismarow, 10) Like "WAHR" And CommentToBeAnalysed Like "" Then
        Call Fill_Out_Others_Analysis(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction, 19)
    End If

    'Check if there should have been a tool number here
    If PrismaArr(Prismarow, 11) Like "WAHR" And CommentToBeAnalysed Like "" Then
        Call Fill_Out_Others_Analysis(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction, 18)
    End If

End If
End Sub

Call Fill_Out_Others_Analysis(PrismaArr, Prismarow, CommentToBeAnalysed, InspectionInstruction, 5):

Sub Fill_Out_Others_Analysis(ByVal PrismaArr, _
    ByVal Prismarow As Long, _
    ByVal CommentToBeAnalysed As String, _
    ByVal InspectionInstruction As String, _
    ByVal Error_Code As String)

'Write the given information into the analysis sheet
    With ThisWorkbook.Sheets("Analysis").ListObjects("Analysis_Others").ListRows.Add
        .Range.ClearFormats
        .Range(1, 1) = PrismaArr(Prismarow, 1)
        .Range(1, 2) = Prismarow + 5 & " (" & PrismaArr(Prismarow, 2) & ")"
        .Range(1, 3) = CommentToBeAnalysed
        .Range(1, 4) = InspectionInstruction
        .Range(1, 6) = "----"
        .Range(1, 7) = "----"
        .Range(1, 8) = "----"
        .Range(1, 9) = "----"
        .Range(1, 10) = "----"
        .Range(1, 11) = "----"

        'Assign errors, including coloring and statistical error assignment
        Call AssignError(Error_Code, .Range)
    End With

End Sub

At this point it calls "AssignError" which is just too long to really post. But even if I comment out that Call, the macro is still sped up by using .activate.

Also I can speed up the macro similarly if I start the macro and then, while it is opening and closing excel sheets to gather data, but before the For-Loop starts, click out of excel. I simply click on the windows button and leave the start menue open while I watch my progress bar fill up 3x faster than usual. In fact, this has an even better performance, speeding the program up even when I use .activate by an addional ~20% (16s->13s). This does not surprise me all that much tho. But for the .activate behaviour I simply have no explanation.

Can someone make sense of this?

TL/DR: Does anybody have an idea how worksheet.activate in the middle of a macro could possibly speed up the execution by a factor of 3. I.e. adding worksheet.activate makes my code finish running in 1/3 the time it otherwise takes.

Thanks Denis

Answer 1

I have several ideas, however, I'm also fishing.

1) Graphic updating still happening. Even though you have set Application.ScreenUpdating to False, could it be that it is still updating/keeping track of graphics when updating the cells in the sub Fill_Out_Others_Analysis on the sheet Analysis? More speed on the active start menu indicates this as well. A way to check this is by comparing the times (with the .activate line enabled versus disabled) when the VBA runs without writing the values (disable the block With ThisWorkbook.Sheets("Analysis").ListObjects("Analysis_Others").ListRows.Add) as values on the sheets (and thus in graphic memory) arent changed all the time. I'm assuming here that the Analysis sheet is the active sheet when you have the .Activate line disabled - however you mentioned the same difference was there when you had an empty sheet active so I dont think it is likely to make a difference.

2) Shorter data references. Are there any references running via the Dashboard sheet? As in formulas or tables that point to other sheets, or the pivot table getting data from the Dashboard sheet: fetching data during the VBA via [thisworkbook --> current active sheet] might be faster than [thisworkbook --> find worksheet --> data from dashboard sheet]. At first, I thought your pivot table was on Dashboard sheet, but reading again it was on another. So also unlikely.

3) Its not the Dashboard sheet itself, but the current sheet index of the Dashboard sheet that causes the difference. I dont know why, but I remember reading once or twice that workbooks are faster if sheets with lots of cells with formulas/when calculation chains are the back of the workbook (on the last sheets of the workbook). This indicates that excel-speed is related to sheetindexes, and so that the speed difference isn't because of the Dashboard sheet itself, but because the Dashboard sheet is now (for example) the first sheet of the workbook. Try moving the Dashboard sheet to an opposite end where it is now (if it is sheet(1) now, move it to sheet(15)), activate sheet 1 and run speed tests again. If the VBA is now faster with another sheet being active it still doesn't answer your question of why, but at least there is a lead.

Besides, in chase of a faster VBA: Likely as the name states, the Dashboard sheet shows stuff from the Analysis tab. With the table in the Analysis tab being updated repeatedly during the VBA, so are the references to that table (so also the Dashboard tab). It is probably faster to write the analysis to the memory first (to an array) and then at the end of the VBA write it to the table: this way the table size and all its references only have to change once instead of every time when 1 row is added.