Alan Beban wrote...
....
A few years back you mused that perhaps VLOOKUP and INDEX return some
other sort of collection object than an array. But this certainly
doesn't seem to be the case if these worksheet functions are invoked
through VBA. E.g.:
Function IndexVBA(iRef, iRow, iColumn)
IndexVBA = Application.Index(iRef, iRow, iColumn)
End Function
....
Your test is flawed. Just before the End function statement IndexVBA
contains a variant containing an array of variants. The array formula
=TYPE(IndexVBA({1,4,7;2,5,8;3,6,9},2,{1,2}))
returns 64, while the array formula
=TYPE(INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}))
returns 1. Gosh, Alan, what could be the difference?
For that matter, try the nonarray formulas
=ISREF(IndexVBA($A$1:$C$3,2,{1,2}))
which returns FALSE and
=ISREF(INDEX($A$1:$C$3,2,{1,2}))
which returns TRUE.
This is due to your udf implicitly converting the VBA .Index call's
range result to its .Value property. You could try guarding against
that by using
Function IndexVBA1(iRef, iRow, iCol)
If IsObject(iRef) Then
Set IndexVBA1 = Application.Index(iRef, iRow, iCol)
Else
IndexVBA1 = Application.Index(iRef, iRow, iCol)
End If
End Function
But both the nonarray and array formulas
=ISREF(IndexVBA($A$1:$C$3,2,{1,2}))
still return FALSE because the formula
=TYPE(indexvba1($A$1:$C$3,2,{1,2}))
returns 16, error. Note that the nonarray formula
=CELL("Address",INDEX($A$1:$C$3,2,{1,2}))
returns $A$2, and the same formula entered as an array formula in a
2-column by 1-row range returns {"$A$2","$B$2"}. So the worksheet INDEX
function called with a range reference as first argument and an array
as second or third argument returns something that (1) isn't an error
but (2) behaves in part like a range reference.
I await your revised udf providing *EXACTLY* these semantics.
