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