Evaluating a String argument as if it were in an Excel cell

Question

I have looked through the similar questions and cannot find one that quite matches my issue: I have a UDF that I pass arguments to to retrieve data. The arguments can be simple strings (e.g. "vapour_mass_flow"), simple cell references (e.g. "C4"), or complex cell formulae (e.g. "CHOOSE($B$8, "overall", "vapour", "liquid") & _mass_flow"). I have a 'helper' function that needs to be able to resolve the argument value from parsing the arguments. I have a working parser that can extract the relevant arguments as a String() array, but I am struggling with how to get VBA to 'resolve' the complex arguments to a string value. The 'helper' function is essentially a navigation button, that is looking to find a call to the UDF somewhere in a worksheet, and detect which call is a match on one of the arguments.

I have tried using Worksheet.Evaluate(argument) using the Worksheet object that the UDF is being called from (in any given instance). For clarity, I have:

Private Function resolveCellRef(CellRef As String, cell As Range) As String
    ' CellRef is the argument to the UDF as a String, cell is the calling cell in the sheet
    Dim ws As Worksheet
    On Error GoTo errhandler    ' Use error handling to handle two known issues
    Set ws = cell.Parent
    resolveCellRef = ws.Evaluate(CellRef)

    If error_raised Then resolveCellRef = ActiveWorkbook.Names(CellRef).RefersToRange.Value2
    Exit Function

errhandler:
    If Err.Number = 1004 And Not error_raised Then      ' Set the error_raised flag and resume next to try a Named Range
        Err.Clear: error_raised = True
        Resume Next
    ElseIf Err.Number = 1004 And error_raised Then      ' If we get here, then the cellRef isn't a cell reference or Named Range, so just return it as is
        Err.Clear
        resolveCellRef = CellRef
        Resume Next
    End If
End Function

This code successfully resolves the argument CellRef to whatever value it contains if:

1. It is a simple string already (obviously)
2. It simply points to a cell on the same or another sheet
3. It is a Named Range (the error handling for this may be unnecessary now, but I originally was not using the Evaluate function and needed to check for Named Ranges in this way).

The code above does not work for my example where CellRef is equal to:

"CHOOSE($B$8, "overall", "vapour", "liquid") & _mass_flow"

Instead, the function resolves to a null string. Any thoughts on what I might be doing wrong, or whether there is a fundamental limitation here I am unaware of?

Thanks.

Answer 1

Turns out I had 'cleaned' my complex argument strings previously - I have left the original message unedited so you can see the text I had that was failing, but the correctly parsed argument should have read (note the " mark before _mass_flow that was missing in my original!):

"CHOOSE($B$8, "overall", "vapour", "liquid") & "_mass_flow"

So my approach was right all along, and the error handler is all not needed as well, it turns out. The Worksheet.Evaluate function handles it all for you.