Text and Text Formatting Builders

Working with text is an important part of working with VBA. You need to be able to communicate information to users, whether it be a simple message box, UserForm, or text in Excel.

The String Builder converts your written text into a usable string of text. It interprets line-breaks, special characters (quotations), and makes use of variables within the text. (no more web searches for how to type a line-break in VBA!).

The Format Builder allows you to format numbers into your desired format (dates, currency, etc.).

String Builder

First, you’ll need an existing procedure to work with:

This procedure will output this MessageBox:

Notice how the number is unformatted. Also, what if we want to include a more comprehensive message including text? This is where the String and Format Builders come in handy.

Next navigate to the String Builder

Now start typing your text. Notice how line-breaks and quotations are automatically handled in the Preview area.

Usually, your number will be stored as a variable. To insert a variable into your string, move the cursor to the location within the text where you’d like the variable to appear.

Select the variable from the drop down and click Insert.

This will open the Format Builder, which we will discuss below. After setting the format, return to the String Builder menu.

In the Preview Procedure Window you’ll see the string of text that you created:

Click “Move to Mouse Location” to move your inserted code elsewhere in the procedure.

Inserting the String

This code will result in an error because the text string is floating in the middle of the procedure. It either needs to be moved to within a text function (like MsgBox) or added to a string variable. We will add it to a string variable by checking the box near the top of the form:

Click OK to close the form and Edit your procedure code to output the new string variable and you’ll see the formatted response:

Format Builder

The Format Builder can be opened on it’s own:

It can be opened from the String Builder by inserting a variable or by clicking edit:

Select your desired format and notice how the preview area shows the format text and the code to format:

You can also use the Custom Format tool to create more complex formats.

Note: Due to a framework update, the Custom Formats may not always display properly. We’ve attempted to handle most common scenarios.

Start Automating Excel

Get AutoMacro