Excel VBA - 分配数组更改 LBound 和 UBound
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19483124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - assigning an array changes LBound and UBound
提问by OldUgly
I have a some very large data sets in Excel that I need to parse through - and doing it in an array is faster than looping through the data in the worksheet. Loading all of the data into an array is causing memory problems (the data sets ARE that large), so I plan on loading subsets of the data into an array, processing that, then loading another subset. I was hoping to use the array "feature" of defining the LBound and UBound to help me keep track of where I am in the worksheet. But I find that assigning the worksheet values to the array changes the bounds. The following code demonstrates the problem ...
我在 Excel 中有一些非常大的数据集需要解析 - 在数组中执行它比循环遍历工作表中的数据要快。将所有数据加载到数组中会导致内存问题（数据集很大），所以我计划将数据的子集加载到数组中，处理它，然后加载另一个子集。我希望使用定义 LBound 和 UBound 的数组“功能”来帮助我跟踪我在工作表中的位置。但我发现将工作表值分配给数组会改变边界。下面的代码演示了这个问题......
Sub myTest3()
Dim myRange As Range
Dim myArray As Variant
Dim myOffset As Long
myOffset = 10
Set myRange = Worksheets("RawData").Range("A1").CurrentRegion
ReDim myArray(myOffset To myRange.Rows.Count, myRange.Columns.Count)
MsgBox LBound(myArray, 1) & " to " & UBound(myArray)
Set myRange = myRange.Offset(myOffset, 0).Resize(myRange.Rows.Count - myOffset, myRange.Columns.Count)
myArray = myRange.Value2
MsgBox LBound(myArray, 1) & " to " & UBound(myArray)
End Sub
The first MsgBox gives me "10 to 10931". The second MsgBox gives me "1 to 10921".
第一个 MsgBox 给了我“10 到 10931”。第二个 MsgBox 给了我“1 到 10921”。
Any ideas on maintaining the bounds on the array as I originally defined them? I know looping through the worksheet to make the assignment would do it, but it would be slow.
关于保持数组边界的任何想法，就像我最初定义的那样？我知道循环遍历工作表以进行分配会做到这一点，但会很慢。
Thanks in advance.
提前致谢。
采纳答案by Mike Woodhouse
Excel VBA doesn't work the way you want it to in this situation. When you execute myArray = myRange.Value2the original content of myArraywas replaced. The Redimmed array was thrown away. Excel/VBA doesn't look at the target, it replaces it, or, probably more correctly, it creates a new array and makes the myaArrayvariable point to that.
在这种情况下，Excel VBA 无法按照您希望的方式工作。执行时myArray = myRange.Value2原来的内容myArray被替换了。将Redim配有阵列扔掉。Excel/VBA 不会查看目标，而是替换它，或者更准确地说，它会创建一个新数组并使myaArray变量指向该数组。
So you're going to need a bit more code to get you where you want to be. I'd consider putting the code to grab the next chunk into a separate function and doing the bookkeeping there:
所以你需要更多的代码才能让你到达你想去的地方。我会考虑将获取下一个块的代码放入一个单独的函数中，并在那里进行簿记：
Function ChunkAtOffset(rng As Range, rowsInChunk As Long, colsInChunk As Long, offsetRows As Long) As Variant
' Note: doesn't cater for the case where there are fewer than 'offsetRows' in the target
Dim arr As Variant, result As Variant
Dim r As Long, c As Long
arr = rng.offset(offsetRows).Resize(rowsInChunk, colsInChunk).Value2
ReDim result(offsetRows To offsetRows + rowsInChunk - 1, 1 To colsInChunk)
For r = 1 To rowsInChunk
For c = 1 To colsInChunk
result(offsetRows - 1 + r, c) = arr(r, c)
Next
Next
ChunkAtOffset = result
End Function
If I run this:
如果我运行这个：
Sub myTest4()
Dim curReg As Range, ary As Variant, offset As Long
With Range("A1")
Set curReg = .CurrentRegion
Do
ary = ChunkAtOffset(.CurrentRegion, 10, .CurrentRegion.Columns.Count, offset)
Debug.Print LBound(ary, 1) & " to " & UBound(ary)
offset = offset + 10
Loop Until offset >= .CurrentRegion.Rows.Count
End With
End Sub
... I now get this:
......我现在明白了：
0 to 9
10 to 19
20 to 29
