How do I run code on startup before Excel re-calculates formulas?

[J. Caplan]

I have an XLA that is to be distributed to multiple users. Each user will
use the UDFs in the Add-In in different spreadsheets. I have added the
following code to the "ThisWorkbook" object

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
' Do some stuff here
End Sub

For each XLS file I open in Excel, my App_WorkbookOpen code is called as
expected. The question I have is about when it fires compared to Excel
recalculating all cells referencing my UDFs.

Each time I open an Excel spreadsheet using my UDFs, Excel recalculates the
formulas and THEN raises the App_WorkbookOpen event. I want the cells to
automatically calculate on open of the file, however, I want to run some code
before it does this recalculation (namely cache some data so that the recalc
is faster).
I have even added: Private Sub App_SheetCalculate(ByVal Sh As Object)
but this fires after the auto recalculation on startup and after the
Workbook_open.

Does anyone have any suggestions?

Thanks,
Jeff

 

[Barb Reinhardt]

Have you tried setting the calculation to manual when the workbook is opened?

application.calculation = XLCalculationManual (I think)

 

[J. Caplan]

Thanks.

I have played with this some, but I think it is more encomapssing than I
need. Which method would I add this to?
If I add this to App_WorkbookOpen, then it is too late since the workbook
seems to recalculate before this event occurs.

If I add this to Workbook_Open() then it will be called every time Excel is
opened and my Add-In is loaded (which should be all of the time). Any
workbook opened that may not contain my formulas will be set to manual
calculation mode, correct?

 

[Barb Reinhardt]

True, it affects everything in Excel. I use it when I'm executing something
and I don't want to recalculate every time something is changed in a cell.
When I'm done with all of my changes, I calculate the sheet (or workbook) at
that time and then turn the calculation back on programmatically.

 

[Charles Williams]

as far as I know there is no general way of avoiding this calculation except
by using Manual Mode.

The only suggestion I have is to trigger the cacheing from the UDF if the
cache does not exist.

regards
Charles Williams
Decision Models

 

[J. Caplan]

I am actually doing just that. The first time that my UDF is called, I build
my cache and as each subsequent call to the UDF is made by Excel during
startup, it gets its value from the cache.

I am using a variable to let me know that it is the first time the UDF is
being run. The problem is if I open another workbook while the same session
of Excel is open. This does NOT know to build the cache since the variable
was already tripped by the last workbook opened in this session of Excel. I
was trying to use the Application_WorkbookOpen to reset this variable, but it
fires AFTER the recalc...thus my dilema.

Any suggestions on a better way to handle this?

 

[Charles Williams]

Sounds like you need a separate variable for each workbook: maybe use a
collection which is indexed on workbook name?

regards
Charles Williams
Decision Models

 

[J. Caplan]

Awesome thought.

I now use the WorkbookOpen and WorkbookBeforeClose to add and remove the
workbook name from a cache. When my UDF is run, it checks to see if
Application.ActiveWorkbook.Name exists in the collection. If it does not, it
fills in the cache (as it knows it is the first time this has been open in
this session of Excel)

Thanks for the suggestion!!

 

[Charles Williams]

Glad it works for you,

probably better to use Application.Caller.Parent.Parent.Name rather than
Application.ActiveWorkbook.Name
- does not depend on the book calling the udf being the active workbook.
- allows for variations where the cache needs to be refreshed based on
external events/timers etc.

Charles Williams
Decision Models