vba 将范围转换为逗号分隔的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22349945/
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 range to comma delimited string
提问by David Klempfner
If I had a column like this:
如果我有一个这样的专栏：
Col1
abc
def
ghi
jkl
How can I convert it to a string like this?:
如何将其转换为这样的字符串？：
"abc,def,ghi,jkl"
回答by
You can use the Join()function to join all the elements of a 1 dimensional array with a delimiter.
您可以使用该Join()函数将一维数组的所有元素与分隔符连接起来。
The Transpose()function is used below to form the dimensional array (this approach works on a single column or row).
Transpose()下面使用该函数来形成多维数组（此方法适用于单列或单行）。
Sub Main()
Dim arr
arr = Join(Application.Transpose(Range("A2:A5").Value), ",")
MsgBox arr
End Sub
or as an UDF
或作为 UDF
Public Function Merge(r As Range) As String
Merge = Join(Application.Transpose(r.Value), ",")
End Function
回答by Przemyslaw Remin
Just in case you need heavier machinery use one of the solutions provided in the answer below. I had similar challenge for ranges containing milion of cells. In such cases JOINwill lead to crash.
万一您需要更重的机械，请使用以下答案中提供的解决方案之一。对于包含数百万个单元格的范围，我遇到了类似的挑战。在这种情况下JOIN会导致崩溃。
Check the question here: Turn Excel range into VBA string
在这里检查问题： Turn Excel range into VBA string
I have tested all the approaches provided in the above link. Solutions based on function JOINhave slow performance, or even lead to crash.
我已经测试了上述链接中提供的所有方法。基于函数的解决方案JOIN性能缓慢，甚至导致崩溃。
Ordinary loop throughall the cells is way faster than JOINfunction. The sting builderin accepted answer is even faster. With string builder, the strings consisting of millions of cells are build in seconds. This is the solution I have end up with.
通过所有单元格的普通循环比JOIN函数快得多。接受答案中的刺痛生成器甚至更快。使用字符串构建器，由数百万个单元组成的字符串可在几秒钟内构建。这是我最终得到的解决方案。
回答by Kip Bryan
Double-transpose works for doing string join on single-row values. Thanks @user2140173 and @brettdj!
双转置适用于对单行值进行字符串连接。感谢@user2140173 和@brettdj！
debug.print join(Application.Transpose(Application.Transpose(Range("A1:G1").Value)),",")
debug.print join(Application.Transpose(Application.Transpose(Range("A1:G1").Value)),",")
