Why are my Add-In formulas recalculating everytime I open a workbo

[J. Caplan]

I know that Excel has smart logic built in to recalculate formulas when it
needs to and that you can manually have it calculate even if nothing has
changed, but is it supposed to recalc unchanged cells on open of an XLS file?

As a test, I created a VBA module (see test code below) and saved the
project as an XLA. I added the Add-In to Excel, opened a workbook, added the
RepeatValue function (see below) to a cell, saved the XLS file, closed Excel
and opened it again. When I open the XLS file, it reruns the macro.

I noticed that the the field says#NAME?. Not sure why it needs to resolve
the name.

Public Function RepeatValue(ByVal cellValue As String, ByVal timesToRepeat
As Integer) As String
Dim ii As Integer
Dim returnString As String

returnString = ""
For ii = 1 To timesToRepeat
returnString = returnString & cellValue
Next ii

MsgBox (returnString) 'Added Msgbox so I could see that this was running
RepeatValue = returnString

End Function

 

[John Bundy]

This seems to answer everything, note the Open event

http://www.decisionmodels.com/calcsecretse.htm

 

[J. Caplan]

Yes, but it states: "Excel will automatically recalculate all open workbooks
at each and every change, and whenever you open a workbook. Usually when you
open a workbook in Automatic mode and Excel recalculates you will not see the
recalculation because nothing will have changed since the workbook was saved."

Nothing has changed. To prove this, I had one of my users give me a
spreadsheet that they have that calls UDFs in a 3rd party Add-In that I do
not have. When I open the workbook, it opens fine even though I don't have
the Add-In. When I do something to a cell used in the formula, I get errors
in the cell, as expected. If I add calls to my UDF (written in VBA
code-behind Excel) as well on this workbook, it DOES recalculate them when I
open the workbook, even thought nothing has changed.

 

[Charles Williams]

You are correct: Excel calculates all references to XLA UDFs at workbook
open time if calculation is set to automatic.
The only reason I can imagine for this behaviour is that Excel does not know
if the UDF has changed since the workbook was saved, and does not track what
happens during an Excel session.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[J. Caplan]

But I don't get any errors for the cells that refer to the Add-In that I do
not have. So it apparently does not see those cells as changed. The problem
here is that my UDF calls to a database. Many calls can be time consuming.
When I open up a workbook that contains calls to my UDF, you have to wait
while dozens of cells are updated. These dozens of hits to the database is
costly.

 

[Charles Williams]

Cells that refer to a non-open addin UDF are treated more like external
references.

For the situation that you describe there is often a way of getting multiple
results from the DB in one hit into a local cache and then having the UDF
query the cache.
One useful technology for this is disconnected ADO recordsets.

Charles
________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[J. Caplan]

Actually, I have a routine that will go through the worksheet, gather up all
of the information from the cells that contain my formula and then make one
database hit. I store these results in a cache. I then recalc the cells and
they get the data from the cache. It works great. On startup though, it
does not call this routine as it is trying to re-calc each of my cells
individually first.

 

[Charles Williams]

So you must be nearly there, you just need a way of making the UDF do
nothing if the cache is not populated (except maybe notify the DB-Hit
Routine that this UDF needs data), or make the first call to the UDF trigger
getting the cache if its not populated.

Maybe a Global switch that the UDF looks at?

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com