In this article Dependence, Dirty Cells, and Recalculated Cells Asynchronous User Defined Functions (UDFs) Volatile and Non-Volatile Functions Calculation Modes, Commands, Selective Recalculation, and Data Tables The user can trigger recalculation in Microsoft Excel in several ways, for example: This topic does not distinguish between the user directly pressing a key or clicking the mouse, and those tasks being done by a command or macro.
The user runs the command, or does something to cause the command to run so that it is still considered a user action.
Then I resized the windows so that I could see both the Excel window and the VBE windows at the same time and triggered a calculation: I could see the VBE title bar flashing. Then I closed the VBE window and triggered another calculation. Then I saved the workbook with the VBE window closed, closed Excel, reopened Excel and the workbook and triggered another calculation: This time it took 1.1 seconds. And it still takes 0.058 seconds using Application.
Then I initiated the calculation from VBA using Application. Calculate even with the VBE window open and visible.
Excel depends on analysis of the input arguments of a Function to determine when a Function needs to be evaluated by a recalculation.
What this means is that the function will only be flagged as needing evaluation when one or more of the input arguments change (unless the function is volatile).
A few years back I was having trouble working out why VBA UDFs ran so much faster on my PC than on someone else’s system (I think it was Jan Karel Pieterse’s).
Eventually I tracked it down to the fact that I had Fast Excel installed.
For example, you need to reference to a Range when you use SUM Function.
Excel uses a special mechanism to minimize the number of calculation to speed up the process.
Excel builds “Dependency Trees”, which keeps track on the following changes Navigate to Excel Options to turn Workbook Calculation to Automatic, then your formula Cell will recalculate automatically if the relationship is in the Dependency Trees.
In Part 1 and Part 2 of Writing Efficient VBA UDFs I looked at some simple ways of changing the VBA code you write to make it run massively faster.
In this post I look at a bug in Excel that slows down your UDFs and show you how to avoid it.