ReDim Preserve数组从索引3开始 [英] ReDim Preserve array to start at index 3

问题描述

我用一个范围填充数组，并希望具有与实际范围相同的字段并在数组中记录数字.
换句话说:如果数组是从A5:D10派生的，则myArr(5,2)应该引用B5.

I populate an array with a range, and would like to have the same field and record numbers in the array as on the actual range.
In other words: if the array is derived from A5:D10, myArr(5,2) should refer to B5.

Dim myArr As Variant
myArr = sht.Range("A3:M" & LRow)

'Redesign array references
    'This works
    ReDim Preserve myArr(1 To LRow -2, 0 To 12)

    'This does not work
    ReDim Preserve myArr(3 To LRow, 1 To 13)

下标超出范围

Subscript out of range

以上错误消息提示我可能会尝试超出索引范围，但它们与工作索引的大小相同.我在这里做什么错了?

The error message above suggest I may try to go out of bounds with the indexes, but they're the same size as the working indexes. What am I doing wrong here?

推荐答案

在多维数组中，您只能重新设计ReDim最后一个数组维.

In a multi dimensional array you can only redesign ReDim the last array dimension.

例如，您可以在多维数组中进行

For example in a multi dimensional array you can do

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 10, 1 To 25)

但是你不能做

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 15, 1 To 20)

你也不能做

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(1 To 10, 5 To 20)

或

Dim myArr(1 To 10, 1 To 20)
ReDim Preserve myArr(5 To 10, 1 To 20)

ReDim声明说:

如果使用Preserve关键字，则只能调整最后一个数组维的大小，而根本不能更改维数.
…
同样，使用Preserve时，只能通过更改上限来更改数组的大小.更改下限会导致错误."

"If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all.
…
Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error."

解决此问题的唯一方法是使用新维度定义新数组，并使用循环将数据移入该新数组.但是后来就想像这样将一个范围的值立即加载到数组中的想法

The only solution to get around this would be to define a new array with the new dimensions and shift the data into this new array using a loop. But then the idea of loading a range of values into an array at once like

myArr = sht.Range("A3:M" & LRow).Value

完全没有用.实际上，在这种情况下，将前两行不需要的数据也加载到数组中比循环移动数组要快.

is completly useless. Actually in this case loading the not needed data of the first 2 rows into the array too should be faster than shifting the array with a loop.

使用

myArr = sht.Range("A1:M" & LRow).Value

，您可以使用myArr(5, 2)来引用B5.将额外的2行也加载到数组中应该不会有太大的区别.

and you can use myArr(5, 2) to refer to B5. Loading the additional 2 lines into the array too should not make a big difference.

这篇关于ReDim Preserve数组从索引3开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！