vba Excel VBA中范围内的数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1549191/
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
Array from Range in Excel VBA
提问by Kevin Boyd
Well I've been struggling with the little bit of code and can't seem to get around it ...
I'm trying to get an array from a range of cells, the array however is showing up to be 1 element wide.
Well here's the code:
好吧，我一直在为一点代码而苦苦挣扎，似乎无法绕过它......我试图从一系列单元格中获取一个数组，但是该数组显示为 1 个元素宽。
那么这里的代码：
Dim item As Variant
MsgBox Range("D19:H19").Count
item = Range("D19:H19").Value
MsgBox LBound(item) & " " & UBound(item)
as per my understanding item should contain a 2D array... however I'm getting the following result 1st MsgBox prints 5 2nd MsgBox prints 1 1
根据我的理解，项目应该包含一个二维数组...但是我得到以下结果 1st MsgBox prints 5 2nd MsgBox prints 1 1
What's going wrong?
怎么了？
回答by imfrancisd
The problem is in LBound and UBound
问题出在 LBound 和 UBound
jtolle was correct about the LBound and UBound.
jtolle 关于 LBound 和 UBound 是正确的。
LBound(item, 2)
UBound(item, 2)
However, item must not be dimmed as an array (you'll get an error).
但是， item 不能作为数组变暗（你会得到一个错误）。
I think this is what you want
我想这就是你想要的
Dim item As Variant
MsgBox Range("D19:H19").Count
item = Range("D19:H19").Value
MsgBox LBound(item, 2) & " " & UBound(item, 2)
For i = LBound(item, 2) To UBound(item, 2)
MsgBox item(1, i)
Next
回答by jtolle
Your item should contain a 2-D array as expected. If you stick a breakpoint in your code and look at the little "Locals" window in the VBA editor, you should see that. Your calls to LBound and UBound are getting the bounds in the first dimension. If you call Lbound(item,2) and UBound(item,2), you should get 1 and 5 as you expect.
您的项目应包含预期的二维数组。如果您在代码中插入断点并查看 VBA 编辑器中的“本地”小窗口，您应该会看到。您对 LBound 和 UBound 的调用正在获得第一维的边界。如果你调用 Lbound(item,2) 和 UBound(item,2)，你应该得到 1 和 5。
EDIT: That is, once you've made the assignment, item would look like something you could have declared as such:
编辑：也就是说，一旦你完成了任务， item 看起来就像你可以这样声明的东西：
Dim item(1 to 1, 1 to 5)
昏暗项目（1 到 1、1 到 5）
One of the banes of VBA programming is that arrays can have arbitrary lower bounds. So all of your code needs to be aware of that.
VBA 编程的一大难题是数组可以具有任意下限。所以你的所有代码都需要意识到这一点。
回答by JimmyPena
Try this:
尝试这个：
Dim item As Variant
MsgBox Range("D19:H19").Count
item = Application.Transpose(Range("D19:H19").Value)
MsgBox LBound(item) & " " & UBound(item)
回答by Atmocreations
That's correct as is. Even if you select an array of cells, you still have the option to select one single cell out of the array (and step for example with tab through the items of this array)
这是正确的。即使您选择了一组单元格，您仍然可以选择从数组中选择一个单元格（例如，使用 Tab 键遍历该数组的项目）
.Value
only gets you the content of the currently single-selected cell.
只会让您获得当前单选单元格的内容。
if you want the enumeration of the array, you may call the .Cells()-method of the Range-object
如果要在阵列的枚举，你可以调用.Cells()的的-方法Range-object
Assuming that D19 until H19 contain "a" through "e" respectively, calling
假设 D19 到 H19 分别包含“a”到“e”，调用
Range("D19:H19").Cells(2)
returns you "b". Note that this is a one-based array and can be 2-dimensional. Cells()takes at most 2 parameters to specify the inner offset from the selection's origin.
还给你"b"。请注意，这是一个基于一的数组，可以是二维的。Cells()最多需要 2 个参数来指定从选择的原点的内部偏移。
hope that clarifies... regards
希望澄清......问候
回答by Nick
if you want a 1D array, to join it for an IN clause, for example, you should transpose your range. I've found you have to transpose twice for a row, once for a column of data like this:
例如，如果你想要一个一维数组，将它加入一个 IN 子句，你应该转置你的范围。我发现你必须为一行转置两次，一次是为一列数据，如下所示：
Dim rngRow As Range, rngColumn As Range
Set rngRow = Sheets(1).Range("A1", "Z1")
Set rngColumn = Sheets(1).Range("A1", "A20")
Dim arrRowValues, arrColValues
arrRowValues = WorksheetFunction.Transpose(WorksheetFunction.Transpose(rngRow))
arrColValues = WorksheetFunction.Transpose(rngColumn)
Dim numList As String, stringList As String
numList = Join(arrRowValues, ",")
stringList = "'" & Join(arrColValues, "','") & "'"
worth a play.
值得一玩。
