VBA 将多个变量连接成字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17793938/
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
VBA Concatenate many variables into string
提问by Richard Todd
I'm doing a relatively messy SQL string in VBA by concatenating many variables into SQL String and then executing the string in ADO.
我通过将许多变量连接到 SQL 字符串中，然后在 ADO 中执行该字符串，在 VBA 中做了一个相对混乱的 SQL 字符串。
As an example here's some code:
例如，这里有一些代码：
Sql = "insert into mydb.dbo.table values ('" & var1 & "','" & var2 & "','" & double1 & "," & double2 & "," & double3 & ")"
I did not want to copy the entire code because frankly doing this for 27 variables might bore someone to death. Anyway the above code I repeat for 27 values in SQL (the SQL table obviously has 27 columns). However after 21 concatenations like this the string does not concatenate any further.
我不想复制整个代码，因为坦率地说，为 27 个变量这样做可能会让某人感到厌烦。无论如何，我对 SQL 中的 27 个值重复了上面的代码（SQL 表显然有 27 列）。然而，在像这样连接 21 次之后，字符串不再连接。
The total string char length is 208 so surely it cannot be the VBA maximum of 252. Is there a maximum number of concated values to enter to a VBA string? Is there some other method I could use to achieve this goal?
总字符串字符长度是 208，所以它肯定不能是 VBA 的最大值 252。是否有最大数量的连接值可以输入到 VBA 字符串？我可以使用其他方法来实现这个目标吗？
Many Thanks
非常感谢
采纳答案by Richard Todd
I did not find an answer to this. I was able to reconstruct the SQL tables to a better design, but the problem still remains with concatenating many variables in one string.
我没有找到答案。我能够将 SQL 表重建为更好的设计，但问题仍然存在，将多个变量连接到一个字符串中。
回答by Transformer
Maximum Size of string in VBA is more 65536 char...depends on application version but in a single line you can not write this much characters.In case of VBA a single line can have maximum 1021 char. so break your line of code.e.g.
VBA 中字符串的最大大小是更多 65536 个字符...取决于应用程序版本，但在一行中你不能写这么多字符。在 VBA 的情况下，一行最多可以有 1021 个字符。所以打破你的代码行。例如
sql = "Insert into....................."
sql = sql & "rest of the query"
回答by Dick Kusleika
I'm not sure what you mean by 'does not concatenate any further', but I generally use an array for unwieldy strings.
我不确定您所说的“不再连接”是什么意思，但我通常将数组用于笨重的字符串。
Dim aSql(1 to 27) As String
aSql(1) = "'" & var1 & "'"
aSql(2) = "'" & var2 & "'"
aSql(3) = double1
aSql(4) = double2
...
adCmd.Execute "INSERT INTO mydb.dbo.table VALUES (" & Join(aSql,",") & ");"
