Share via

Formatting String Variables

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The VBA String Editor add-in assists in the formatting of strings for inclusion in Microsoft® Visual Basic® for Applications (VBA) code. Complex strings, such as SQL statements, can be entered as standard text, and the String Editor then formats the string with the proper quotation marks and other symbols and inserts it into your code.

Using the VBA String Editor reduces the time you spend building strings and hunting syntax errors by making it easier to create strings to embed long scripts or complicated SQL statements into VBA code.

Note   The String Editor makes it possible for you to create new strings or to update existing formatted strings easily. To update existing strings, highlight the string you want to update in the code, and select the String Editor Add-in. Then, follow these steps to edit:

To format strings using the VBA String Editor

  1. To open the VBA String Editor, launch the Visual Basic Editor (ALT + F11) from a Microsoft® Office application.

  2. Open a VBA project. Place your cursor in the active code pane where you want to add a formatted string.

    Note   The VBA String Editor will not activate unless an active code pane is open.

  3. From the Add-ins menu, select VBA String Editor. If the VBA String Editor is not listed under the Add-Ins menu, you must load it into the Add-Ins menu through the Add-In Manager, located in the Add-Ins menu.

  4. In the String Editor text field, enter the text that you want to format as a string.

  5. If there are variables within the entered text, you can designate them in one of two ways.

    1. Select the variable name in the text field, and click the Toggle to String/NonString button. This changes the variable name to a bold blue font, distinguishing the variable from the rest of the entered text.

    2. Before you enter the variable name, click the Toggle to String/NonString button. All text entered is now in the same bold blue font, indicating that you are in NonString mode.

      After you have finished entering the variable name, click the Toggle toString/NonString button to toggle back to String mode.

  6. Select the Concatenation Symbol toolbar button to toggle between the "&" and the "+" concatenation symbols for the selected text.

    Note   Although either symbol will work as a concatenation operator, the "+" symbol also is used as an addition operator. If a string contains numerical characters, the "+" symbol will cause those numbers to be added together rather than displayed as a string. For best results, always use the "&" symbol when you want to concatenate strings.

  7. If you must cut, copy, or paste text, select the appropriate toolbar button.

  8. Click Update when you are finished. The VBA String Editor screen closes, and the formatted string is placed into the code.

See Also

Rapid Application Development Tools | Formatting Code Commenter and Error Handler Templates