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?

Answer 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.

Answer 2

Split the query into several sections:

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

Answer 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