VBA Code for manual calculations including spills

Don Gulledge

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi & welcome to MrExcel
Where is the code located, is it in a standard module?
 
Upvote 0
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".
 
Upvote 0
As that code is in a sheet module "Calculate" on it's own will only calculate that one sheet.
 
Upvote 0
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?
 
Upvote 0
That's one way, the other is to just use Application.Calculate as you have done.
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top