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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Where is the code located, is it in a standard module?
 

Don Gulledge

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
As that code is in a sheet module "Calculate" on it's own will only calculate that one sheet.
 

Don Gulledge

New Member
Joined
Mar 25, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
That's one way, the other is to just use Application.Calculate as you have done.
 

Don Gulledge

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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
Top