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

Answer 3

When you feed the INDEX funciton a 0, you're telling it "I want the entire row or column".

If you assign Application.Index(wsrgcmes, 0, 1) to a variant, it works fine...you've got a variant with an array in it.

If you try to print Application.Index(wsrgcmes, 0, 1) to the immediate window, you get an error because you're trying to print an array. You need to wrap it in a JOIN function.

I don't know why you are getting an error on Application.Index(wsrgcmes, 14630, 1) but my guess is that at the time you did it, wsrgcmes wasn't populated or wasn't that dimension. I'd need to see a screenshot of both the exact part of the code you were using when the error occurred as well as a screenshot of the array in the Watch Window.

I don't believe your issue is caused by the limitations of INDEX, because you are nowhere near them.

The below code shows the limitations of INDEX when called from VBA:

 Sub IndexLimitations()

Dim v       As Variant
Dim i       As Long
Dim j       As Long
Dim k       As Long
Dim vArray  As Variant
Dim vItem   As Variant

vArray = Array(65536, 65537, 1048576, 1048577)

For Each vItem In vArray
    i = vItem
    ReDim v(1 To i, 1 To 2)
    For j = 1 To i
        For k = 1 To 2
            v(j, k) = j * k
        Next k
    Next j
    Debug.Print "Rows dimension test: " & i
    Debug.Print Application.Index(v, i, 1)
    Debug.Print ""

    ReDim v(1 To 2, 1 To i)
    For j = 1 To i
        For k = 1 To 2
            v(k, j) = j * k
        Next k
    Next j

    Debug.Print "Columns dimension test: " & i
    Debug.Print Application.Index(v, 1, i)
    Debug.Print ""

Next vItem

End Sub

Here's what it returns:

Rows dimension test: 65536
 65536 

Columns dimension test: 65536
 65536 

Rows dimension test: 65537
 65537 

Columns dimension test: 65537
Error 2023

Rows dimension test: 1048576
 1048576 

Columns dimension test: 1048576
Error 2023

Rows dimension test: 1048577
 1048577 

Columns dimension test: 1048577
Error 2023

The learning here is that you are limited to 65536 columns if using the INDEX function to return a column, but you don't seem to be limited to the number of rows in the grid. I suspect this is related to the TRANSPOSE bug I mention in passing at http://dailydoseofexcel.com/archives/2013/10/23/dictionaries-can-be-rude/

Answer 4

I also have this problem, see here . I think that application.Indexcannot be applied to arrays containing different types of variables. I had a 100 X 2 array containing double type in the first column and string in the second one. I could not use application.Index to paste contents from my array to the excel sheet, even if I selected contents of the same type (from just column 1 or 2). Then I substituted the string type values vith numerical double values, and this time application.Index worked correctly. However, we still don't know if application.Index works only with numerical types.