Excel macros - Too many line continuations

Question

I have a "large" SQL query (like 200 lines)...

dim query as string
query = "..................................." & _
        "..................................." & _
           .... Like a lot lines later...
        "..................................."

function query,"sheet 1"

When I do this, Excel says "Too many line continuations."

What is the best way to avoid this?

Solution 1

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.

Solution 2

Split the query into several sections:

query = _
  "............" & _
  "............" & _
  "............"
query = query & _
  "............" & _
  "............" & _
  "............"
query = query & _
  "............" & _
  "............" & _
  "............"

Solution 3

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 text X( thankyou

(Still waiting for better ways to do this...) thankyou :P

Comments

[Nicolás]

write a shorter query :P

[KyleMit]

MSDN - Too many line continuations

[Ben]

"Your code has more than 25 physical lines joined with line-continuation characters, or more than 24 consecutive line-continuation characters in a single line. Make some of the constituent lines physically longer to reduce the number of line-continuation characters needed, or break the construct into more than one statement." --MSDN