Excel VBA Run-time error '13': Type mismatch, when passing a Variant Array to Application.Index method

Question

I'm running a code to create a daily report, first I was working with cells and offsets to compare the date but that was slow, now I'm using arrays to improve the code, but I'm encountering a problem when trying to use some application functions, first I tried VLookup and all I got was Run-time error '13': Type mismatch. Now I'm working with Match and Index methods and I get the same error, I can't seem to find what my error is.

I'm passing a Variant Array, that I get this way:

Public wsrgcmes As Variant
Public wshtte As Variant

With Sheets("Resumen general de casos")
    wsrgcmes = .Range(.Cells(1, 2), .Cells(lRow, lCol)).Value
End With

And the error comes here, when I try to execute the Application.Index method.

Sub gen_informe()    
     temp = Application.Index(wsrgcmes, 0, 1)        
End Sub

I ran only this line Application.Index(wsrgcmes, 0, 1) in the debug window and I got the same error. The Variant Array wsrgcmes contains a table with numeric and string values. Any help I cant get, I appreciate it. Thanks!

Values of wbrgc:

Answer 1

When you get the values of a range to a Variant, it is always a 1-based 2D array:

Thus row index 0 is out of the array bounds, and Application.Index raises a type mismatch error in that case.

Public Sub TestMe()        
    Dim a As Variant
    a = Range("A1:D5").Value2
    Debug.Print Application.Index(a, 1, 2)    
End Sub

---

From the comment of @Mathieu Guindon:

Application is, in COM terms, an extensible interface - it's extended with WorksheetFunction members at run-time, so Application.Index does work, even though it's a late-bound member call (no intellisense). It behaves slightly different than its early-bound equivalent, in that instead of raising a run-time error given an error result, it returns the error result (e.g. #VALUE!).

Both syntaxes are perfectly valid, and each demand different error-handling mechanics (early-bound: On Error..., late-bound: IfError(...))

Answer 2

Application.Index has a limit for number of columns and rows. If you exceed this you will hit a type mismatch error. So this might be the problem.

See the following for ways to work around this:

how-do-i-slice-an-array-in-excel-vba

I am not sure what the current limit is. It was more than 65,536 rows or 65,536 columns. The solution in the links is essentially to use arrays to slice rather than Application.Index.

If you pay attention to @Vityata's answer and Index appropriately you can probably determine the tipping point at which you tip over into type mismatch. In some circumstances you can also work around this by processing columns/rows in chunks to stay under the threshold. I give an example of working in chunks to get around this here: Slice array to use index on larger than 65000.

You could completely avoid Index and use TimWilliams helper function .

Using @TimWilliams helper function you would have

Sub test 'your other sub

temp = GetColumn(wsrgcmes,1)

End Sub


Function GetColumn(arr, colNumber)
    Dim arrRet, i As Long
    ReDim arrRet(1 To UBound(arr, 1), 1 To 1)
    For i = 1 To UBound(arr, 1)
        arrRet(i, 1) = arr(i, colNumber)
    Next i
    GetColumn = arrRet
End Function