vba Excel 宏 - 太多的续行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2806151/
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
Excel macros - Too many line continuations
提问by pojomx
I have a "large" SQL query (like 200 lines)...
我有一个“大”的 SQL 查询（比如 200 行）...
dim query as string
query = "..................................." & _
"..................................." & _
.... Like a lot lines later...
"..................................."
function query,"sheet 1"
When I do this, Excel says "Too many line continuations."
当我这样做时，Excel 会说“太多的行延续”。
What is the best way to avoid this?
避免这种情况的最佳方法是什么？
回答by GSerg
There's only one way -- to use less continuations.
只有一种方法——使用更少的延续。
This can be done by putting more text on a line or by using concatenation expressed differently:
这可以通过在一行上放置更多文本或使用不同表达的串联来完成：
query = ".........."
query = query & ".........."
query = query & ".........."
But the best is to load the text from an external source, as a whole.
但最好是从外部来源整体加载文本。
回答by pojomx
So far I found this...
到目前为止，我发现了这个......
Call AddToArray(query, "...")
Call AddToArray(query, "...")
... a lot lines later...
Call AddToArray(query, "...")
*edit: Forgot to add:
*编辑：忘记添加：
Sub AddToArray(myArray As Variant, arrayElement As Variant)
If Not IsArrayInitialized(myArray) Then
ReDim myArray(0)
myArray(0) = arrayElement
Else
ReDim Preserve myArray(UBound(myArray) + 1)
myArray(UBound(myArray)) = arrayElement
End If
End Sub
Source: link textX( thankyou
来源：链接文字X（谢谢
(Still waiting for better ways to do this...) thankyou :P
（仍在等待更好的方法来做到这一点......）谢谢：P
回答by Guffa
Split the query into several sections:
将查询拆分为几个部分：
query = _
"............" & _
"............" & _
"............"
query = query & _
"............" & _
"............" & _
"............"
query = query & _
"............" & _
"............" & _
"............"
回答by Jozef Jano?ko
why not use VBA to help with the VBA concatenation?
为什么不使用 VBA 来帮助 VBA 连接？
Check this code of mine (it is very primitive and feel free to adjust it), it basically takes whatever data you have on worksheet called "Fast_string" in columns "A:E" and in column F it prepares you the code for the concatenation using preliminary variable "prelim_string". You just use this and then copy-paste the solution from column F into your code... you're welcome ;)
检查我的这段代码（它非常原始并且可以随意调整它），它基本上采用您在“A：E”列中称为“Fast_string”的工作表上的任何数据，并在F列中为您准备连接代码使用初步变量“prelim_string”。您只需使用它，然后将 F 列中的解决方案复制粘贴到您的代码中……不客气；)
Sub FAST_STRING()
Dim cel As Range, lastcel As Range, prel_r As String, i As Integer, cr As Integer
With ThisWorkbook.Worksheets("Fast_string")
Set lastcel = .Cells(10000, 1).End(xlUp)
For Each cel In .Range(.Cells(1, 1), lastcel)
cr = cel.row
prel_r = ""
For i = 1 To 5
If .Cells(cr, i) = "" Then
prel_r = prel_r & " "
Else
prel_r = prel_r & " " & Replace(.Cells(cr, i).Value, """", """""")
End If
Next i
If cr = 1 Then
prel_r = "Prelim_string =" & """" & prel_r & """" & " & chr(10) & _"
ElseIf cr / 20 = Round(cr / 20) Then
prel_r = "Prelim_string = Prelim_string & " & """" & prel_r & """" & " & chr(10) & _"
Else
prel_r = """" & prel_r & """" & " & chr(10) & _"
End If
If cr = lastcel.row Or (cr + 1) / 20 = Round((cr + 1) / 20) Then prel_r = Left(prel_r, Len(prel_r) - 14)
cel(1, 6) = prel_r
Next cel
End With
End Sub
