vba 如何将 Excel 范围分配给二维数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14035772/
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
How to assign an Excel Range to a 2D array?
提问by arun_roy
Could you please say- how a Excel Range("G2:AA1000") can be assigned to a 2D array? If possible how to return back that 2D array to the same range after performing some operation on that 2D array?After assignment a Range to an 2D array,How each row will be identified from that 2D matrix?
你能说 - 如何将 Excel 范围（“G2：AA1000”）分配给二维数组？如果可能的话，如何在对该二维数组执行某些操作后将该二维数组返回到相同的范围？在将范围分配给二维数组后，如何从该二维矩阵中识别每一行？
Thanks,
谢谢，
采纳答案by SeanC
There is an easy way to make changes to an area using an array, and write it out to the same place, or somewhere else.
有一种简单的方法可以使用数组对区域进行更改，并将其写出到同一个地方或其他地方。
This example code will copy data from one area to another, using an array:
此示例代码将使用数组将数据从一个区域复制到另一个区域：
Sub example()
Dim testdata()
testdata = Range("A1:B13")
Range("D1:E13") = testdata ' simple copy
Range("G1") = testdata ' copy only 1 cell
Range("I1:K22") = testdata 'try to copy too much
End Sub
The testdataarray starts from 1, and will extend to the number of columns and rows specified in the range. In this case, testdata(1,1)refers to the data obtained from A1, testdata(1,2)refers to B1, finishing up with testdata(13,1)referring to A13, and testdata(13,2)referring to B13.
该testdata数组从1开始，并将扩展到范围中指定的列数和行数。在这种情况下，testdata(1,1)指的是从A1 中获得的数据，testdata(1,2)指的是B1，最后是testdata(13,1)指A13，testdata(13,2)指的是B13。
Setting the range equal to the array in the next line copies the array into the specified location.
设置范围等于下一行中的数组，将数组复制到指定位置。
- If the area is smaller than the original array, it will copy only enough of the array to fill that space, so
Range("D1")=testdatawill only place one cell on the sheet. - If you specify a larger area, then #N/Awill fill the area that is not in the space covered by array elements, so
Range("A1:A3")=testdatawill fill A1 and A2 with data from the array, but A3 will have #N/A
- 如果该区域小于原始数组，它只会复制足够的数组来填充该空间，因此
Range("D1")=testdata只会在工作表上放置一个单元格。 - 如果指定更大的区域，则#N/A将填充不在数组元素覆盖的空间中的区域，因此
Range("A1:A3")=testdata将使用数组中的数据填充 A1 和 A2，但 A3 将具有#N/A
Result of example program:
Note: A1:B13 is the original data, which gets copied with the subsequent range(??)=testdata
示例程序的结果：
注：A1:B13 为原始数据，与后续程序一起复制range(??)=testdata
回答by chuff
Here's a worked-out example of reading a range of data from a worksheet, operating on the array, and then writing it back out to the same worksheet.
这是一个从工作表中读取一系列数据，对数组进行操作，然后将其写回到同一个工作表中的示例。
Sub RangeArray()
Dim Rng As Range
Dim Arr()
Dim ArrItem
Dim i As Long, j As Long
Dim rUB as Long, cUB as Long
Set Rng = Worksheets("Sheet1").Range("A1:G19")
rUB = Rng.Rows.Count 'Row upper bound
cUB = Rng.Columns.Count ' Column upper bound
ReDim Arr(1 To rUB, 1 To cUB)
'Read worksheet range into array
For i = 1 To rUB
For j = 1 to cUB
Arr(i, j) = Rng.Cells(i, j).Value
Next
Next
'Do something to array
For i = 1 To rUB
For j = 1 To cUB
If i <> j Then
Arr(i, j) = Arr(i, j) / (i * j)
End If
Next
Next
'Write array back to worksheet
Set Rng = Worksheets("Sheet1").Range("I1")
For i = 1 To rUB
For j = 1 To cUB
Rng.Offset(i - 1, j - 1).Value = Arr(i, j)
Next
Next
End Sub
回答by l--''''''---------''''''''''''
One way to loop through a range is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.
循环遍历范围的一种方法是将 For...Next 循环与 Cells 属性一起使用。使用 Cells 属性，您可以将循环计数器（或其他变量或表达式）替换为单元格索引号。在以下示例中，变量 counter 被替换为行索引。该过程循环遍历范围 C1:C20，将绝对值小于 0.01 的任何数字设置为 0（零）。
Sub RoundToZero1()
For Counter = 1 To 20
Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
Next Counter
End Sub
