H
Hank Scorpio
I'm wondering whether anyone has come across this before, and whether
they're aware of any workarounds. I can't find anything in the
knowledgebase about it nor in a Google Groups search, though with the
state of the latter that's not surprising.
It appears that if you set Application.Volatile to False in a user
defined function (UDF), but the UDF does an Application.Run command to
call a function in another add-in, then the Application.Volatile
statement is ignored and the function will calculate anyway.
I initially noticed this in a rather more complex function, but
isolated it to the bare bones via the following two. (ElParent is the
name of a function in another .xla add-in. It's a commercially
supplied one, not one of mine so I don't know what its code is):
Public Function ParentElement(Dimension As String, Element As String)
As String
Application.Volatile False
ParentElement = Application.Run("ElParent", Dimension, Element, 1)
Debug.Print "ParentElement" & " " & Now()
End Function
'-----------------------
Public Function CombinedElement(Dimension As String, Element As
String) As String
Application.Volatile False
CombinedElement = Dimension & Element
Debug.Print "CombinedElement" & " " & Now()
End Function
'------------------------
I put these functions into two cells in a worksheet, with the relevant
arguments pointing to the same input cells. Both functions return the
correct values.
On the first calculation the Immediate window shows, as expected:
CombinedElement 30/08/2009 1:29:53 PM
ParentElement 30/08/2009 1:29:53 PM
However thereafter if I punched [F9] or [Shift]+[F9], without changing
the inputs and with those being the only two functions in the Excel
session (that is, no other functions were on the sheet, no other
workbooks were open) I got, for example:
ParentElement 30/08/2009 1:30:01 PM
ParentElement 30/08/2009 1:30:12 PM
In other words the ParentElement function keps calculating whenever I
press [F9] regardess of whether there have been input changes. The
CombinedElement function won't; it will only calculate if its inputs
have changed (or if I do a [Ctrl]+[Alt]+[F9]). The difference between
the two is the Application.Run statement.
Thoughts, anyone?
T.I.A.
they're aware of any workarounds. I can't find anything in the
knowledgebase about it nor in a Google Groups search, though with the
state of the latter that's not surprising.
It appears that if you set Application.Volatile to False in a user
defined function (UDF), but the UDF does an Application.Run command to
call a function in another add-in, then the Application.Volatile
statement is ignored and the function will calculate anyway.
I initially noticed this in a rather more complex function, but
isolated it to the bare bones via the following two. (ElParent is the
name of a function in another .xla add-in. It's a commercially
supplied one, not one of mine so I don't know what its code is):
Public Function ParentElement(Dimension As String, Element As String)
As String
Application.Volatile False
ParentElement = Application.Run("ElParent", Dimension, Element, 1)
Debug.Print "ParentElement" & " " & Now()
End Function
'-----------------------
Public Function CombinedElement(Dimension As String, Element As
String) As String
Application.Volatile False
CombinedElement = Dimension & Element
Debug.Print "CombinedElement" & " " & Now()
End Function
'------------------------
I put these functions into two cells in a worksheet, with the relevant
arguments pointing to the same input cells. Both functions return the
correct values.
On the first calculation the Immediate window shows, as expected:
CombinedElement 30/08/2009 1:29:53 PM
ParentElement 30/08/2009 1:29:53 PM
However thereafter if I punched [F9] or [Shift]+[F9], without changing
the inputs and with those being the only two functions in the Excel
session (that is, no other functions were on the sheet, no other
workbooks were open) I got, for example:
ParentElement 30/08/2009 1:30:01 PM
ParentElement 30/08/2009 1:30:12 PM
In other words the ParentElement function keps calculating whenever I
press [F9] regardess of whether there have been input changes. The
CombinedElement function won't; it will only calculate if its inputs
have changed (or if I do a [Ctrl]+[Alt]+[F9]). The difference between
the two is the Application.Run statement.
Thoughts, anyone?
T.I.A.