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" wrote in message
...
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" wrote:

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" wrote in message
...
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