How to Capitalize All Letters in VBA

When working with Excel spreadsheets, you might find yourself in need of changing text case for better consistency or readability. VBA, or Visual Basic for Applications, is a powerful tool that can help you achieve this automatically. In this tutorial, you will learn how to capitalize all letters in a string using VBA, which can be particularly helpful in situations where you need to ensure all data follows a uniform format.

Steps to Capitalize Letters Using VBA

Capitalizing letters with VBA is a straightforward process where you will use the UCase function, which is built into VBA to convert all letters of a string to uppercase.

1. Access the VBA Editor

To begin, you need to access the VBA editor within Excel. You can do this by pressing ALT + F11 on your keyboard.

2. Create a Sub Procedure

Once the editor is open, you can create a new sub procedure where you’ll write the code to capitalize letters. This is done by typing Sub followed by the name of your procedure, like so:

3. Define Your String Variable

Inside the Sub Procedure, define a variable to store the string that you want to capitalize.

4. Use the UCase Function

To change the text to all uppercase, you’ll use the UCase function. This function takes a string argument and converts all its letters to uppercase.

5. Output the Result

To see the result of your code, you can output the capitalized string back into the spreadsheet or use a message box. To display it in a message box, use the following code:

Alternatively, if you wish to put the result back into a cell in the spreadsheet, you can specify the cell reference:

Full Code

Below is the full code for the Sub Procedure to capitalize text. You can copy and paste this code into the VBA editor to capitalize a predefined string and output the result in a message box:

Example File Content

Assuming that you have the string “sample text” in cell A1 of your Excel worksheet, the code provided will change it to “SAMPLE TEXT” and display it in a message box upon execution.


Once the code is run, expect to see a message box pop up with the following data:


You have now learned how to capitalize all letters in a string using VBA’s UCase function. This powerful feature can be extended to transform entire columns or rows by looping through each cell, giving you the ability to standardize text data in bulk. By incorporating this function into your VBA routines, you can automate text formatting and ensure consistency across your data sets.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba