Best way to define a large vba string - i.e. heredoc equivalent?

How should I define large strings in VBA? Is there a better way than coding something like the below?

Dim largeString as String
largeString = "This is a long block of text that I want to fill " & _
              "into a form field. I need to make sure I pay attention " & _
              "to spacing and carriage return issues while doing so. " & _
              "I also have to use quotes liberally, the concatenation " & _
              "operator, and the continuance underscore to make sure " & _
              "VBA can parse my code." & vbCr & vbCr & _
              "It's kind of a pain in the ass and I wish I could use " & _
              "a heredoc instead, letting me copy and paste the block" & _
              "of text I need from anothe开发者_如何转开发r source and shove it into " & _
              "a string."

Edit: Ugh, and there's a 25 line continuation limit too? So much for nice indenting and 80 characters of width, that only gives me enough room for a couple decent paragraphs.

I prefer doing it in this way:

Dim lStr As String
lStr = ""

lStr = lStr & "This is a long block of text that I want to fill "
lStr = lStr & "into a form field. I need to make sure I pay attention "
lStr = lStr & "to spacing and carriage return issues while doing so. "
lStr = lStr & "I also have to use quotes liberally, the concatenation "
lStr = lStr & "operator, and the continuance underscore to make sure "
lStr = lStr & "VBA can parse my code." & vbCr & vbCr
lStr = lStr & "It's kind of a pain in the ass and I wish I could use "
lStr = lStr & "a heredoc instead, letting me copy and paste the block"
lStr = lStr & "of text I need from another source and shove it into "
lStr = lStr & "a string."

I think this method is easier to work with than the line continuation method and there is no line number limit to get in with this way. You can comment out individual lines, which is useful for debugging SQL strings.

When handling long strings, I find it easier to use short variable names because VBA does not have the equivalent of += operator. largeString = largeString & "" takes up too much space and gets repetitive, so shortening the string name makes the format somewhat bearable.

For very large blocks of text, write it in a text editor then copy and paste it into your procedure. Then copy

lStr = lStr & "

and paste it at the beginning of each line. The VBA editor will automatically add the quotes at the end of the line making the process simple to do.

No, this is as good as it gets.

For really long strings it might be an option to keep the string in a separate file, or use some application feature. For example, in Word, you might want to store the string in a document variable, as hidden text or AutoText. In Excel, you might consider a hidden sheet for storing long string constants.

Another way is to store the text in comments, then parse it in a function. No external files required, good readability.

' TEXT to retrieve:
'   SELECT
'   field1, field2
'   FROM table1

Function SQL_in_comments()
    SQL_in_comments = Replace(Replace(Application.VBE.ActiveCodePane.CodeModule.Lines(2, 3), "'   ", ""), "'", "")
End Function

In Excel, there's a much easier way than other answers here. Let Excel do the heavy lifting for you. This method will not require to break the string into smaller chunks -- it can handle up to 32,767 characters at once.

Put the string into a cell. Set aside a scrap worksheet for this purpose. Name the cell, eg MyLongString.

[MyLongString] = "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using 'Content here, content here', making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text, and a search for 'lorem ipsum' will uncover many web sites still in their infancy. Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like)."

Now you can refer to the name in VBA as [MyLongString]. Eg:

MsgBox [MyLongString]