Application.Volatile False doesn't work with Application.Run?

[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.

 

[Peter T]

Without knowing what ElParent does I put a dummy function in my Personal an
used app.run to call that in one of the UDFs. I couldn't reproduce what you
describe, Volatile false/true works same way in both functions. I tested
like this

Dim a&, b&

Public Function Foo1(arg) As String
Dim x
a = a + 1
Application.Volatile False
x = Application.Run("Personal.xls!Foo3")
Foo1 = a
End Function

Public Function Foo2(arg)

Application.Volatile False
b = b + 1
Foo2 = b
End Function

' in another workbook
function Foo3()
Foo3 = 123
end function


Regards,
Peter T

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.