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

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.

I prefer doing it in this way:

Dim lStr As StringlStr = ""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 & vbCrlStr = 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.

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 table1Function SQL_in_comments()    SQL_in_comments = Replace(Replace(Application.VBE.ActiveCodePane.CodeModule.Lines(2, 3), "'   ", ""), "'", "")End Function