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

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

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 & Its 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.

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *