Excel Evaluate() with INDEX(range, ROW(), 1) uses wrong row-CodePudding

Trying to write a My_Eval function (Performing calculations where I look up functions in a table).

This is my VBA

Function MY_EVAL(ref as String) As Variant
    MY_EVAL = evaluate(ref)
End Function

Which should work, here is where it fails.

	A	B	C
1	TOP	Cell 2	`=MY_EVAL(B3)`
2	BOTTOM	Cell 4	`=MY_EVAL(B3)`
3	Formula	INDEX(A:B,ROW(),1)

C1 Shows TOP, as expected

C2 Shows TOP as well instead of BOTTOM, Why?

Performing My_Eval on cell containing Row() also works as expected.

I expected for cell C to evaluate to BOTTOM, instead of evaluating to "TOP"

CodePudding user response：

Your function may produce unexpected results if the sheet with your lookup table is not the ActiveSheet - the default Application.Evaluate uses the ActiveSheet for context: if you want to use a specific sheet then use the Worksheet.Evaluate form.

For example:

Function MY_EVAL(ref As String) As Variant
    MY_EVAL = Application.ThisCell.Worksheet.Evaluate(ref)
End Function

(Using ThisCell to identify the cell with the formula calling this function)

EDIT: this still doesn't work - info from here https://www.mrexcel.com/board/threads/vba-using-column-or-row-within-evaluate.802692/

"The COLUMN function returns an array that apparently Evaluate can't dereference."

Same applies to ROW() - if you put in your UDF:

Debug.Print TypeName(Application.ThisCell.Worksheet.Evaluate("ROW()"))

it will give you Variant() which explains the problem.

So using @scottcraner's suggestion below:

Function MY_EVAL(ref As String) As Variant
    With Application.ThisCell
        MY_EVAL = .Worksheet.Evaluate(Replace(ref, "ROW()", .Row))
    End With
End Function

...assuming ref doesn't use forms like (eg) ROW(A1:A3) etc.