VBA Application.Index with array causes type mismatch error 13

Question

My macro throws a type mismatch error when I use

Myarr= Application.Index(arr,0,1)

I have tried adding option explicit and defining the variables as variants but nothing seems to work.

The arr array is created from a CSV file and that contains 100000 rows and 11 columns.
The arr looks fine when I check it in the watch window (I can see the values for each row and column)

Here is the code:

Sub ArrTest()
    Dim Myarr 
    Dim Arr 
    Dim wb As Workbook 
    Set wb = Workbooks.Open("F:\People.csv")
    Arr = wb.Sheets(1).Range("A1").CurrentRegion.Value
    Myarr = Application.Index(Arr, 0, 2)
End Sub

Can anyone suggest what I am doing wrong?

L42 · Accepted Answer · 2015-02-12 23:17:13Z

2

Many of the worksheet functions have a limit of just over 65k or so when it comes to the upper bound of input arrays, so you may be hitting that. Works for me with 65k, fails with 66k rows.

Sub ArrTest()
    Dim Myarr
    Dim Arr

    Arr = Range("a1:C65000").Value
    Myarr = Application.Index(Arr, 0, 1) '<<< OK

    Arr = Range("a1:C66000").Value
    Myarr = Application.Index(Arr, 0, 1) '<<<fails

End Sub

If you want to be able to handle more than 65k upper bound, then you will need to use a loop to populate your array "slice"

edited Feb 12, 2015 at 23:17

L42

19.8k11 gold badges47 silver badges70 bronze badges

answered Feb 12, 2015 at 23:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kerry Over a year ago

Thank you Tim! That solved it. The size of the array was the problem.

Collectives™ on Stack Overflow

VBA Application.Index with array causes type mismatch error 13

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related