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

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

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

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

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

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

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

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

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



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

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