Search code examples
excelvba

Excel Evaluate Function Returns #REF When Using Indirect

I have a lookup which returns a formula as text, depending on the value of a particular cell.

The formula (as text) returned looks like this:

SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()))

I then use the EVALUATE() function to evaluate the formula, but receive a #REF error because of the use of INDIRECT().

This Microsoft Support Page was the only reference that I could find to this particular problem, and it doesn't seem to offer an appropriate workaround.

How can I either: a) restructure the formula to avoid using INDIRECT, or b) get EVALUATE() to play nice?

EDIT

The lookup table looks something like this:

Type A                    SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()))
Type B                    SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()), INDIRECT("AH"&Row()))

On another sheet, the contents of cells B1:200 are either Type A or Type B. A lookup returns the formula string based on the value of the cell and puts it into Bx. I then Evaluate the string to give me the result.

Solution

  • OK 3rd attempt at an answer:

    thanks for the example: it fails because you are trying to use INDIRECT to evaluate a named formula and INDIRECT only handles references not formulas.

    You need to use EVALUATE instead, but there is no built-in EVALUATE worksheet function (the EVALUATE you are using in the defined name is an ancient XLM Macro function).
    I suggest you use my EVAL VBA UDF instead 

    Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

    and then use defined names with relative references like typeA=SUM(Sheet3!RC1,Sheet3RC2).

    There are some "quirks" of EVALUATE that you should be aware of: see
    http://www.decisionmodels.com/calcsecretsh.htm