将Listobject Range传递给数组,并得到错误“超出范围". [英] Passing Listobject Range to array and getting error "out of 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 here From 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 1 The 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传递给数组,并得到错误“超出范围".的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!