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:

string builder vba

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

string builder menu

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

preview text

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.

insert variable

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:

preview string procedure

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:

insert string var

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

string builder output

Format Builder

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

open format builder

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

open format builder

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

format builder

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

custom number formats

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

< Previous

Copy Paste Builder

Next >

Array Builder