VBA Code for manual calculations including spills

[Don Gulledge]

I have a complex and large workbook that includes power Query connections to multiple database tables. In order to prevent constant calculation delays for thread calculations I have placed the workbook in manual calculation, this way instead of updating everytime I move cells, it only updates when I execute a VBA routine, or when I execute another Power Query Refresh of the data tables. I use the following code to execute the Refresh with a command to recalculate the workbook once the refresh is done:

ActiveWorkbook.RefreshAll
Calculate
MsgBox "ViewPoint Data Update Complete.", vbOKOnly, "Data Update"

The first line above updates all the Power Query data tables from my external database, the second line is suppose to then cause all worksheets in the workbook to recalculate on the new data.

I was under the impression that executing the "Calculate" command was identical to hitting the F9 key manual calculation, but noted that my code for "Calculate" and hitting the F9 key took a very different amount of run time, the F9 key took almost a minute to run while the "Calculate" executed in code was complete in just a few seconds. Hitting F9 cause the message at the bottom right of the screen to cycle through the "Calculating 8 threads - xx%" done which lasted about a minute, and then showed "Resizing Threads" for about 30 seconds and everything looked right when done. I then noted that the "Calculate" VBA code showed the "Calculating 8 threads - xx%" for only about 4 seconds and that was it - even though there is a spill on that and other worksheets, the resize message did not appear. It was also clear that the calculations performed on the data tables were not fully updated across all worksheets despite the database tables being updated (changed). After running the code, pressing F9 correctly updates all the calculations after it's 1.5 minute processing time. I assumed this meant that the "Calculate" command was not running on the entire workbook, so I experimented until I tried replacing "Calculate" with "Applicaiton.Calculate" which seemed to solve the problem.

My understanding is that "Calculate" is supposed to work to perform a manual calculation on all open workbooks - all worksheets, and an alternative syntax is "Application.Calculate" but they are supposed to function the same. That Worksheet(1).Calculate specifies it to a single worksheet. I changed my code to "Application.Calculate" and it performed correctly without having to hit the F9 after the code completed. Has the "Calculate" command in VBA changed with Excel recently or is the documentation on how to use it incorrect? It appears that Application.Calculate works on the entire workbook (all sheets), but "Calculate" does not.

 

[Fluff]

Hi & welcome to MrExcel
Where is the code located, is it in a standard module?

 

[Don Gulledge]

It is executed with the click of an ActiveX command button on one of the worksheet pages through this subroutine:

Private Sub CommandButton2_Click()
ActiveWorkbook.RefreshAll
Application.Calculate
MsgBox "ViewPoint Data Update Complete.", vbOKOnly, "Data Update"

End Sub

It now has "Application.Calculate" but originally I thought "Calculate" was supposed to be sufficient as the literature says in VBA "Calculate" is equivalent to pressing "F9".

 

[Fluff]

As that code is in a sheet module "Calculate" on it's own will only calculate that one sheet.

 

[Don Gulledge]

So, I'm assuming then that for the "Calculate" command to be in the code, the answer would be to place the subroutine in the standard module and in the command button code call that subroutine?

 

[Fluff]

That's one way, the other is to just use Application.Calculate as you have done.

 

[Don Gulledge]

Got, understood. Thanks for the explanation. It's one thing to figure out a way to make it work, it is another thing to understand why it had to be done that way. I tested it out in the module1 with a call from the button and sure enough, "Calculate" works out there just like you said. While I see not advantage in this application of calling a sub instead of just using the short code on the button and using the longer version of the command - it is good to know the when and why for next time.

 

[Fluff]

You're welcome & thanks for the feedback.