简单的 VBA 数组连接不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8482759/
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
Simple VBA array join not working
提问by JoshG
I'm puzzled why I can't msgbox this joined array. I can do it just fine if I create a static array with typed out values, but with a range of values from excel I keep getting "Invalid Procedure Call or Argument"
我很困惑为什么我不能 msgbox 这个连接的数组。如果我创建一个带有输入值的静态数组，我可以做得很好，但是对于来自 excel 的一系列值，我不断收到“无效的过程调用或参数”
I've done a fair amount of research but I'm not able to find any examples of this issue. What am I doing wrong here?
我已经做了大量的研究，但我找不到任何关于这个问题的例子。我在这里做错了什么？
Sub From_sheet_make_array()
Dim myarray() As Variant
Dim dudeString As String
myarray() = Range("B2:B10").Value
dudeString = Join(myarray(), ", ")
MsgBox dudeString
End Sub
回答by brettdj
A variant array created directly from a sheet range is 2D (ie it has rows and columns) - Joinrequires a 1D array.
直接从工作表范围创建的变体数组是二维的（即它有行和列）-Join需要一维数组。
So you would need to do something like this
所以你需要做这样的事情
[Updatedto read range into variant array, then to convert variant array into 1D array for joining - avoids cell loop]
[更新为将范围读入变体数组，然后将变体数组转换为一维数组以进行连接 - 避免单元格循环]
Note also that using TRANSPOSEas Issun has below on a single column does force a 1D ouctome immediately. So another alternative would be to loop through the columns or rows of a 2D variant array, and TRANSPOSEthem column by column (or row by row) to quickly produce a 1D array.
另请注意，TRANSPOSE在单列上使用Issun 下面的内容确实会立即强制使用 1D octome。因此，另一种替代方法是循环遍历 2D 变体数组的列或行，并TRANSPOSE逐列（或逐行）快速生成一维数组。
Sub From_sheet_make_array()
Dim X
Dim lngRow As Long
Dim myArray()
X = Range("B2:B10").Value2
ReDim myArray(1 To UBound(X, 1))
For lngRow = 1 To UBound(X, 1)
myArray(lngRow) = X(lngRow, 1)
Next
Dim dudeString As String
dudeString = Join(myArray, ", ")
MsgBox dudeString
End Sub
回答by aevanko
The cookie goes to brettdj as resizing a 1D array and populating it is the best way to go (fastest) but I wanted to offer a more lesser-known compact solution in the case that you don't plan to use this on long arrays. It's not as fast than the 1D approach, but not slow like concatenation, but it's convenient when you want to write together fast code (easier not to make typos with one-liners)!
cookie 转到 brettdj 作为调整一维数组的大小并填充它是最好的方法（最快），但我想提供一个鲜为人知的紧凑解决方案，以防您不打算在长数组上使用它。它不如一维方法快，但也不像串联那样慢，但是当你想一起编写快速代码时很方便（用单行代码更容易不打错字）！
myArray = Range("B2:B10").value
myString = Join(WorksheetFunction.Transpose(myArray), ", ")
or even just:
甚至只是：
myString = Join(WorksheetFunction.Transpose(Range("B2:B10").value), ", ")
