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

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

application.calculation = XLCalculationManual (I think)

--
HTH,
Barb Reinhardt



"J. Caplan" wrote:

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

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" wrote:

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

application.calculation = XLCalculationManual (I think)

--
HTH,
Barb Reinhardt



"J. Caplan" wrote:

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

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

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" wrote:

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

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

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

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