Introducing the String Builder dialog

Nothing simpler than defining a String value in VBA, just put the value between double-quotes: str = "Hello world!". At times however what you want to do is slightly more involved and here the String Builder makes life just a bit easier, for example

  • when you want to mix fixed text with other variables, or
  • when you want to distribute text over several line.

It makes sense to want to greet a user by his name 'Hello Peter!'. Here, the code would be str = "Hello " & strUserName & "!". Split the string in three parts, connect them using the ampersand (&) no big deal

Multiline String Builder

Sometimes, in a msgbox, a textbox or a label control, you want to distribute text over multiple lines. To achieve the line break you can insert vbNewLine (or the older vbCrLf) between the strings that represent the line. In addition, to see more clearly what the end result will look like, you can add a VBA line break: at the position where you want to break, type a space( ) followed by an underscore (_). You can then continue on the next line. There is quite a bit of formatting involved to achieve this:

  • adding double quotes around the text string
  • adding ampersand (&) to connect the different strings
  • adding the line break vbNewLine
  • correct use of double quotes inside the text string
  • formatting with space and underscore

The above is no rocket science, but doing it with the String Builder is much easier / saves time: just type the different lines in the top text area (use enter to add a new line). The bottom text area shows the correct code that will be inserted when you press the OK button, for example

Hello [strUserName]!
Have a good day!

will appear as

"Hello " & strUserName & "!" & vbNewLine & _
"Have a good day!"