Limits reached in INDEX(MATCH()) when taking array as argument to look in

Question

I use this formula:

=IFERROR(IF(MATCH(transf(E7);transf(Sheet2!$C$2:$C$66648);0)>0;"YES");"no")

transf is a UDF which simply converts/transforms the actual text value in cell to lowercase and does some other stuff (not the subject for this question).

Normally, if the value of transf(E7) is found in the formed array transf(Sheet2!$C$2:$C$66648), the formula returns YES, if no - no.

The UDF itself works well, it is tested many times. The problem is that this time it does not work. The returned answer is no, and it's not correct. Is this formula failure related to the big array of 66k+ items? What are the limitations of UDFs used as array formulas?

EDIT 1

This is the simplified version of my UDF:

Public Function transf(ByVal vText As Variant) As Variant
Dim aText() As Variant
Dim j As Long
    On Error GoTo ErrH

    If TypeName(vText) = "String" Then
        '...some code...
    ElseIf TypeName(vText) = "Variant()" Then
        '...some code...
    ElseIf TypeName(vText) = "Range" Then   ' <<< both instances of the UDF fall here
        ReDim aText(1 To vText.Count)
        For j = 1 To vText.Count
            aText(j) = Trim(LCase(vText(j)))
        Next
        transf = Application.Transpose(aText)   ' <<< this line causes an error 13
    Else
        transf = CVErr(xlErrValue)
    End If
ErrH:
    'This was created not for the current case, but the error the UDF gets has the same #
    If Err.Number = 13 Then
        Err.Clear
        Resume Next
    End If

End Function

If you notice any other imperfections in case of performance, please, let me know.

EDIT 2

I'm using Excel 2010. No compatibility mode, the file itself - .xlsm, the UDF is in .xlam add-in file.

Answer 1

The Transpose function has a limit depending on the version of Excel you are using. In most, it is equal to the number of possible columns in the worksheet -- although it might be greater in the latest versions (I've not checked that out).

I suggest you populate aText so you don't have to do the Transpose. Although, for just finding a value in the array, you don't have to Transpose it at all. Of course, you might be doing something else with the results.

e.g: For a vertical array:

ReDim aText(1 To vText.Count, 1 to 1)
    For j = 1 To vText.Count
        aText(j,1) = Trim(LCase(vText(j)))
    Next

Edit: After discussion with ZygD, and some testing, it seems there is another limitation in applying worksheet functions to variant arrays. In addition to the TRANSPOSE limitation mentioned above, a size limit of 65536 elements in the first dimension seems to be present, as shown by the code below.

We fill the range A:A with numbers. We then produce a variant array of this range that either encompasses the entire column, or just 65,536 elements. You can see from the output that, in the case of the entire column, using the Range object as lookup_array, the macro executes as expected; but using a variant array as 'lookup_array` results in an error if it is larger than 65,536 elements.

A similar error occurs in using the INDEX function; I have not tested others.

---

Option Explicit
Sub Tester()
    TestArr Range("A1:A65536")
    TestArr Range("A1:A65537")
End Sub

Sub TestArr(R As Range)
    Dim V

V = R

On Error Resume Next
    Debug.Print "Array Size: " & R.Rows.Count
    Debug.Print "Variant Array: " & WorksheetFunction.Index(V, 147, 1)
    If Err.Number <> 0 Then
        Debug.Print "Variant array: Error " & Err.Number, Err.Description
        Err.Clear
    End If

Debug.Print "Range Array: " & WorksheetFunction.Index(R, 147, 1)
Debug.Print vbLf

End Sub

---