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