将Listobject Range传递给数组，并得到错误“超出范围".

问题描述

为了在循环Listobject的数据范围的思想值时提高代码的速度，我想将列的值传递给数组并在数组中循环，而不是循环遍历Listobject的值.

In order to increase speed of my code when looping thought values of a datarange of a Listobject I want to pass the values of a column to an array and loop through the array instead of looping throught the values of the Listobject.

此处从那里我详细说明了这段代码.

I got an insight hereFrom there I elaborated this code.

'passing the list object to a variable
Dim SCtbl As ListObject
    Set SCtbl = ThisWorkbook.Worksheets("sc").ListObjects(1)

'dimensioning the array (as variant)
Dim ListofSC As Variant
    ListofSC = SCtbl.ListColumns("long_shortcut").DataBodyRange.Value
MsgBox (LBound(ListofSC))
MsgBox (UBound(ListofSC))
MsgBox (ListofSC(1))

第一个消息给出结果1第二条消息给出结果708(列表对象的行项目)

The first message gives result 1The second message gives result 708 (the row items of the listobject)

但是访问元素时，我的下标超出了元素1的范围.

But when accesing the elements I get Subscript out of range in element 1.

ListofSC实际上是尺寸为1的普通数组吗?如果可以，为什么我不能访问这些值?

Is actually ListofSC a normal array of dimension 1?If so why cant I access the values?

谢谢.

推荐答案

将数据从Excel范围复制到Variant时，Excel返回二维数组.如果源范围是单列，则可以通过将数组第二维的索引设置为1来访问元素，例如:

When you copy data from an Excel range to a Variant, Excel returns a 2-D array. When your source range is a single column you can access the elements by setting the index of the 2nd dimension of the array to 1, e.g.:

MsgBox (ListofSC(1, 1))

希望有帮助

这篇关于将Listobject Range传递给数组，并得到错误“超出范围".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！