带有数组的VBA Application.Index导致类型不匹配错误13 [英] VBA Application.Index with array causes type mismatch error 13

问题描述

当我使用

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

我试图显式添加选项并将变量定义为变量，但似乎无济于事。

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

arr 数组是从CSV文件创建的，包含100000行和11列。
在监视窗口中检查 arr 看起来不错（我可以看到每一行和每一列的值）

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)

以下是代码：

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?

推荐答案

许多工作表函数都有当涉及到输入数组的上限时，一个超过65k左右的限制，所以您可能会遇到这个问题。为我工作65k，失败66k行。

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

如果您希望能够处理超过65k的上限，那么您将需要使用循环来填充数组切片

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"

这篇关于带有数组的VBA Application.Index导致类型不匹配错误13的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！