User Defined Forumla and persistence

[Eros Pedrini]

Hi,
I have a new problem with Excel... and it appear to me in the world of the
"User Defined Formulas".

If I write a new formula like this (into a VBA module):

Function TEST(RngName As String, Optional FncName As String = "SUM") As
Variant
Application.Volatile

Dim TheCaller As Range
Set TheCaller = Application.Caller

Dim R As Range
Set R = ActiveWorkbook.Names(RngName).RefersToRange

TEST = Application.Evaluate(FncName & "(" & R.Address & ")")
End Function

and I use it in a sheet (e.g. "Sheet 1") it works fine... but when I go to
another sheet (e.g. "Sheet 3") and I forse a recalculus (F9 shortcut) when I
return to "Sheet 1" the function show the 0 (ZERO) value

Why? Something idea?

Thanks in adavance



Eros Pedrini

 

[Charles Williams]

Hi Eros,

When Application.Evaluate evaluates a string containing a range reference
that is not qualified by a worksheet, it assumes it is on the active sheet.
So when you change sheet you get a different answer.

So you either need to use Worksheet.evaluate (which assumes the unqualified
reference is on Worksheet) or use .address(External:=True) which gives you a
qualified reference.

For more limitations and advice on EVALUATE see
http://www.DecisionModels.com/calcsecretsh.htm


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com