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

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


"John Bundy" wrote:

This seems to answer everything, note the Open event

http://www.decisionmodels.com/calcsecretse.htm
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"J. Caplan" wrote:

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