在 VBA 中从范围创建数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37689847/
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
Creating an Array from a Range in VBA
提问by basaltanglia
I'm having a seemingly basic problem but can't find any resources addressing it.
我遇到了一个看似基本的问题，但找不到任何资源来解决它。
Simply put, I just want to load the contents of a Range of cells (all one column) into an Array.
简单地说，我只想将一系列单元格（所有一列）的内容加载到一个数组中。
I am able to accomplish this by means of
我能够通过
DirArray = Array(Range("A1"), Range("A2"))
But for some reason, I cannot create the array when expressed this way:
但出于某种原因，当以这种方式表达时，我无法创建数组：
DirArray = Array(Range("A1:A2"))
My real Range is much longer (and may vary in length), so I don't want to have to individually enumerate the cells this way. Can anyone tell me how to properly load a whole Range into an Array?
我的实际范围要长得多（并且长度可能会有所不同），所以我不想以这种方式单独枚举单元格。谁能告诉我如何将整个范围正确加载到数组中？
With the latter code:
使用后一个代码：
MsgBox UBound(DirArray, 1)
And
和
MsgBox UBound(DirArray)
Return 0, whereas with the former they return 1.
返回 0，而前者返回 1。
回答by vacip
Just define the variable as a variant, and make them equal:
只需将变量定义为变体，并使它们相等：
Dim DirArray As Variant
DirArray = Range("a1:a5").Value
No need for the Array command.
不需要 Array 命令。
回答by Vityata
If we do it just like this:
如果我们这样做：
Dim myArr as Variant
myArr = Range("A1:A10")
the new array will be with two dimensions. Which is not always somehow comfortable to work with:
新数组将具有两个维度。使用它并不总是很舒服：
To get away of the two dimensions, when getting a single column to array, we may use the built-in Excel function “Transpose”. With it, the data becomes in one dimension:
为了摆脱二维，当获取单个列到数组时，我们可以使用内置的 Excel 函数“转置”。有了它，数据就变成了一维：
If we have the data in a row, a single transpose will not do the job. We need to use the Transpose function twice:
如果我们有连续的数据，单个转置将无法完成这项工作。我们需要两次使用 Transpose 函数：
回答by brettdj
Using Value2gives a performance benefit. As per Charles Williams blog
使用Value2会带来性能优势。根据查尔斯·威廉姆斯的博客
Range.Value2 works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And thats probably why its faster than .Value when retrieving numbers.
Range.Value2 的工作方式与 Range.Value 相同，不同之处在于它不检查单元格格式并转换为日期或货币。这可能就是为什么它在检索数字时比 .Value 更快的原因。
So
所以
DirArray = [a1:a5].Value2
Bonus Reading
奖励阅读
- Range.Value: Returns or sets a Variant value that represents the value of the specified range.
- Range.Value2: The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types.
- Range.Value：返回或设置表示指定范围值的 Variant 值。
- Range.Value2：此属性与 Value 属性之间的唯一区别是 Value2 属性不使用货币和日期数据类型。
回答by Paolo
In addition to solutions proposed, and in case you have a 1D range to 1D array, i prefer to process it through a function like below. The reason is simple: If for any reason your range is reduced to 1 element range, as far as i know the command Range().Value will not return a variant array but just a variant and you will not be able to assign a variant variable to a variant array (previously declared).
除了提出的解决方案，如果你有一个一维范围到一维数组，我更喜欢通过如下函数来处理它。原因很简单：如果由于任何原因您的范围减少到 1 个元素范围，据我所知，命令 Range().Value 不会返回变体数组，而只是一个变体，您将无法分配变体变量到变体数组（先前声明）。
I had to convert a variable size range to a double array, and when the range was of 1 cell size, i was not able to use a construct like range().value so i proceed with a function like below.
我必须将可变大小范围转换为双数组，当范围为 1 个单元格大小时，我无法使用 range().value 这样的构造，因此我继续执行如下所示的函数。
Public Function Rng2Array(inputRange As Range) As Double()
Dim out() As Double
ReDim out(inputRange.Columns.Count - 1)
Dim cell As Range
Dim i As Long
For i = 0 To inputRange.Columns.Count - 1
out(i) = inputRange(1, i + 1) 'loop over a range "row"
Next
Rng2Array = out
End Function
