Evaluate functions on named ranges, themselves defined via EVALUATE

Question

In Excel 2010.

Given a named range's name, I am trying to evaluate formulae on this named range, when it is itself already defined through evaluate. It is maybe better to give an example:

Using this vba function :

Function eval(str As String) As Variant
    eval = ActiveSheet.Evaluate(str)
End Function

And defining the named range _xyz (Formula tab > Name Manager, or Alt, I, N, D) (the underscore is here to avoid conflict with cell names later) :

Name : _xyz

Scope : Sheet1

Refers to : =ROW(Sheet1!$A$1:$A$10)

If one creates an array-formula somewhere in the Sheet1 (as a vertical 1-column row) with the formula = _xyz (then hit Ctrl-Shift-Enter), we get the values 1, 2, 3... 10 in a column.

Then formulae such as =AVERAGE(_xyz) or =MAX(_xyz) work (with respective results of 5.5 and 10). So do the equivalent eval functions, given the name of the named range (here "_xyz") and the appropriate function (average or max): =eval("AVERAGE(_xyz)") returns 5.5 and =eval("MAX(_xyz)") returns 10. So far so good.

Now I define a new named range, _xyz2 as :

Name : _xyz2

Scope : Sheet1

Refers to : =EVALUATE("_xyz*_xyz")

If one creates an array-formula somewhere in the Sheet1 (as a vertical 1-column row) with the formula = _xyz2 (then hit Ctrl-Shift-Enter), we get the values 1, 4, 9... 100 in a column (that is, the squares of the values of _xyz).

Formulae such as =AVERAGE(_xyz2) or =MAX(_xyz2) both work well (with respective results of 38.5 and 100), however the eval function does not work anymore : formulae such as =eval("AVERAGE(_xyz2)") and =eval("MAX(_xyz)") now return #VALUE!.

I have tried other sorts of eval functions. Eg :

Function eval2(str As String) As Variant
    eval2 = Application.WorksheetFunction.Average(ActiveSheet.Evaluate(str))
End Function

... hoping that =eval2("_xyz2") would work, but it doesn't (=eval2("_xyz") works well though).

Is there a function, Excel or VBA, which takes the string "_xyz2" as an input and returns the appropriate results (38.5 and 100 for the average and max functions in our example)?

Charles Williams Charles Williams · Accepted Answer · 2017-03-20T15:41:03

Works OK for me if _xyz2 is defined as =_xyz*_xyz but not if it is defined as =EVALUATE("_xyz*_xyz").
I suspect that this hits the problem of Evaluate not being able to call itself

Evaluate functions on named ranges, themselves defined via EVALUATE

1 Answers