vba 将变量数组转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34202578/
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
Convert Variable Array to String
提问by Rémi
I am trying to convert a variable array to a string using vba. I have tried 2 methods but non of them work, they both seem to bloc on at the same point.
我正在尝试使用 vba 将变量数组转换为字符串。我尝试了 2 种方法，但它们都不起作用，它们似乎都在同一点上。
Dim cell As Range
Dim val As Variant
For Each cell In Range("packing_list[Code]")
val = cell.Value
Next cell
MsgBox Join(val, "//")
and
和
Dim oSh As Worksheet
Dim CodeRange As Variant
Set oSh = ActiveSheet
CodeRange = oSh.Range("packing_list[Code]").Value
MsgBox Join(CodeRange , "//")
They both error on the MsgBox line. What do I do wrong ?
他们都在 MsgBox 行上出错。我做错了什么？
Thanks
谢谢
回答by Branden Keck
The value you are trying to join is not an array of strings. Join is supposed to be used on arrays
您尝试加入的值不是字符串数组。Join 应该用于数组
Here is the link to the Microsoft instructions: https://msdn.microsoft.com/en-us/library/b65z3h4h%28v=vs.90%29.aspx
这是 Microsoft 说明的链接：https: //msdn.microsoft.com/en-us/library/b65z3h4h%28v=vs.90%29.aspx
Their example is:
他们的例子是：
Dim TestItem() As String = {"Pickle", "Pineapple", "Papaya"}
Dim TestShoppingList As String = Join(TestItem, ", ")
You code should look something like:
您的代码应该类似于：
Dim i As Integer
Dim cell As Range
Dim val() As Variant '() indicate it is an array
i = 0
For Each cell In Range("packing_list[Code]")
ReDim Preserve val(0 to i) As Variant 'must resize array to fit number of items
val(i) = cell.Value 'i is the position of the item in the array
i = i + 1 'increment i to move to next position
Next cell
'Now that you have an array of values (i.e. ("String1", "String2", ...) instead of just "String" you can:
MsgBox Join(val, "//")
回答by brettdj
Tranposecan be used to produce a 1D array or strings for an individual column or row.
Tranpose可用于为单个列或行生成一维数组或字符串。
So for A1:A10you could used just
所以A1:A10你可以只用
MsgBox Join(Application.Transpose([a1:a10]), ",")
to work on a row you need a second Transpose, so for A1:K1
要连续工作，您需要一秒钟Transpose，因此对于A1:K1
MsgBox Join(Application.Transpose(Application.Transpose([a1:k1])), ",")
回答by Simon Wray
It looks like you think your valand CodeRangevariables are Arrays, when in fact they are not. You have declared them as Variants, but not Variant Arrays, which I suspect is you goal. Add brackets to declare a variable as an Array: Dim CodeRange() as Variant
看起来您认为您的变量val和CodeRange变量是数组，但实际上它们不是。您已将它们声明为Variants，但未声明为，Variant Arrays我怀疑这是您的目标。添加括号以将变量声明为数组：Dim CodeRange() as Variant
See this: How do I declare an array variable in VBA?
请参阅： 如何在 VBA 中声明数组变量？
As @Brandon Keck says, Join is expecting an Array.
正如@Brandon Keck 所说，Join 期待一个数组。
