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

Question

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

Answer 1

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.

Answer 2

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.