Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Application.Volatile False doesn't work with Application.Run?
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Application.Volatile False doesn't work with Application.Run?
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 "Hank Scorpio" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
application.volatile | Excel Programming | |||
Application.Volatile Question, HelP!!! | Excel Programming | |||
Application.Volatile | Excel Programming | |||
What can cause application.screenupdating=false to not work? | Excel Programming | |||
Need for Application.Volatile? | Excel Programming |