1

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)?

Community
  • 1
  • 1
Janthelme
  • 989
  • 10
  • 23

1 Answers1

1

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

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I agree that if _xyz2 is defined as =_xyz*_xyz, it does work. But I need somehow to link this formula to a cell value, so that _xyz2's definition is updated automatically as one changes this cell's value. This is why I was using EVALUATE in the first place, and I do not know how to produce a =_xyz*_xyz like definition for _xyz2 without it. Yes, I could go the vba route and trap the Worksheet_Change event and change _xyz2 definition each time that a given cell changes, but I was trying to avoid this approach. – Janthelme Mar 23 '17 at 13:31
  • Not sure I understand: if _xyz points to a range then any reference to _xyz2 (defined as =_xyx*_xyz) will update whenever a cell in the _xyz range changes. Are you sure you need to use Evaluate anyway? – Charles Williams Mar 23 '17 at 14:32
  • Sorry for the confusion, what I want to update dynamically is the definition of _xyz2. Ideally I'd like to enter "_xyx*_xyz" in a cell, linked somehow to the named range _xyz2. Then change this cell value arbitrarily, say to "ln(_xyx) + sin(_xyz)", and I'd like _xyz2 definition to be updated immediately with this new cell value. EVALUATE allows me to do that, but comes with other limitations down the road, in particular it might not be able to call itself, as you mentioned, and I do not know how to define yet a 3rd named ranged referencing _xyz2 as, say, = xyz2 * 5. This does not seem to work. – Janthelme Mar 23 '17 at 14:44
  • I think what you want to do hits the fundamental limitation that a single Excel cell can only contain a scalar value and not an array/vector (without a lot of trickery). If _xyz and its descendants are always a scalar then I think its doable. – Charles Williams Mar 23 '17 at 15:08