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

by

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.

More Related Contents:

  1. Does VBA have Dictionary Structure?
  2. Get reference to additional Inbox
  3. How to create and write to a txt file using VBA
  4. Save text file UTF-8 encoded with VBA
  5. Outlook VBA – Run a code every half an hour
  6. Excel VBA deleting rows in a for loop misses rows
  7. Download attachment from Outlook and Open in Excel
  8. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  9. Embed picture in outlook mail body excel vba
  10. Where does VBA Debug.Print log to?
  11. How do I display a messagebox with unicode characters in VBA?
  12. VBA Adding a class to a collection [duplicate]
  13. What is the function of FormulaR1C1?
  14. Rounding a number to the nearest 5 or 10 or X
  15. excel hyperlink to nothing
  16. How to add headers to a multicolumn listbox in an Excel userform using VBA
  17. How to send files via HTTP_POST with Excel using VBA?
  18. Distinguish visible and invisible attachments with Outlook VBA
  19. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  20. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  21. Set a cell value from a function
  22. What are the differences between VBA 6.0 and VBA 7.0?
  23. How to name an object within a PowerPoint slide?
  24. How to determine if a date falls on the weekend?
  25. What are the differences between using the New keyword and calling CreateObject in Excel VBA?
  26. Excel VBA wildcard search
  27. VBA Check if file (from website) exists
  28. How to prevent showing backing private fields of class properties in the Locals window?
  29. Copy last column with data on specified row to the next blank column
  30. Excel VBA- Iterate through columns in one workbook, pasting information in corresponding workbook

Leave a Comment