CODEWITHSUNDEEP
excelvba
pojomx
pojomx

Reputation: 790

Excel macros - Too many line continuations

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?

Upvotes: 15

Views: 36593

Answers (4)

Jozef Janočko
Jozef Janočko

Reputation: 511

why not use VBA to help with the VBA concatenation?

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 ;)

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

Upvotes: 0

pojomx
pojomx

Reputation: 790

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

Upvotes: 1

Guffa
Guffa

Reputation: 700262

Split the query into several sections:

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

Upvotes: 4

GSerg
GSerg

Reputation: 78155

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.

Upvotes: 15

Related Questions