How to Use VBA in Excel to Capitalize All Letters (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

It is very common to use VBA in Excel to capitalize all letters in a range of cells. Capitalizing letters is a very straightforward process in Excel VBA. In this article, we will demonstrate different examples of using VBA in Excel to capitalize all letters in a range of cells.

The above video shows the overview of using VBA to capitalize all letters. Follow along to learn all the methods to perform the task.


How to Open the VBA Macro Editor in Excel

You need to follow the steps below to get into the VBA Macro Editor in your worksheet:

  • You will see the Developer tab at the top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.

Opening Visual Basic from the Developer Tab

  • A new window will appear. It is the Visual Basic Editor.  To write new code, go to Insert > Module.

Creating a New Module

  • In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.

Running VBA Module


How to Use VBA in Excel to Capitalize All Letters: 4 Suitable Examples

In this article, we have shown four methods of using VBA in Excel to capitalize all letters. Here in the dataset, we have ten different statements in lowercase letters. We will turn them into upper case letters using VBA.


1. Using VBA UCase Function to Capitalize All Letters in Excel

We can use the UCase function in VBA Excel to capitalize all letters.

VBA Code with the UCase function in Excel to Capitalize All Letters

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub UCaseFunc()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("UCase_Function").Range("B5:B14")
    Set outputRng = Sheets("UCase_Function").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = UCase(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub

VBA Breakdown

Sub UCaseFunc()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
  • This Excel VBA code creates a subroutine called UCaseFunc. It declares two range variables named myRng and outputRng, as well as two string variables named myStr and letter.
Set myRng = Sheets("UCase_Function").Range("B5:B14")
    Set outputRng = Sheets("UCase_Function").Range("C5:C14")
    outputRng = ""
  • The code sets myRng to the range of cells B5:B14 on the sheet named UCase_Function, and sets outputRng to the range of cells C5:C14 on the same sheet. It then clears the values in outputRng.
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = UCase(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub
  • Next, the code uses a For Next loop to iterate over each cell in myRng. For each cell, it converts the value to uppercase using the UCase function and assigns it to the letter. Finally, it concatenates the original value in outputRng with the uppercase letter value and assigns the result to the cell in outputRng corresponding to the current iteration of the loop.

VBA Excel Capitalize All Letters

Read More: Excel VBA to Capitalize First Letter of Each Word


2. Use VBA Proper Function to Capitalize All Letters in Excel

We can use the Proper function in VBA Excel to capitalize all letters.

VBA Code with Proper Function

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub ProperFunc()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("Proper_Function").Range("B5:B14")
    Set outputRng = Sheets("Proper_Function").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        For j = 1 To Len(myStr)
            letter = Application.WorksheetFunction.Proper(Mid(myStr, j, 1))
            outputRng.Cells(i) = outputRng.Cells(i) & letter
        Next j
    Next i
End Sub

VBA Breakdown

Sub ProperFunc()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
  • This Excel VBA code defines a subroutine called ProperFunc. It declares two range variables named myRng and outputRng, as well as two string variables named myStr and letter.
 Set myRng = Sheets("Proper_Function").Range("B5:B14")
    Set outputRng = Sheets("Proper_Function").Range("C5:C14")
    outputRng = ""
  • The code sets myRng to the range of cells B5:B14 on the sheet named Proper_Function, and sets outputRng to the range of cells C5:C14 on the same sheet. It then clears the values in outputRng.
  For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        For j = 1 To Len(myStr)
            letter = Application.WorksheetFunction.Proper(Mid(myStr, j, 1))
            outputRng.Cells(i) = outputRng.Cells(i) & letter
        Next j
    Next i
End Sub
  • Next, the code uses a loop to iterate over each cell in myRng. For each cell, it uses another loop to iterate over each character in the cell’s value. It converts each character to a proper case using the Proper function and assigns it to the letter Finally, it concatenates the original value in outputRng with the proper case letter value and assigns the result to the cell in outputRng corresponding to the current iteration of the loop.

Using Proper Function to Capitalize All Letters

Read More: Automatic Uppercase in Excel VBA


3. Use StrConv Function with vbUpperCase Argument to Capitalize All Letters in Excel

We can use the StrConv function with the vbUpperCase argument in VBA Excel to capitalize all letters.

VBA Code with StrConv Function

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub StrConvFunc_vbUpperCaseArg()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("vbUpperCase_Argument").Range("B5:B14")
    Set outputRng = Sheets("vbUpperCase_Argument").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = StrConv(myStr, vbUpperCase)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub

VBA Breakdown

Sub StrConvFunc_vbUpperCaseArg()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
  • This Excel VBA code defines a subroutine called StrConvFunc_vbUpperCaseArg. It declares two range variables named myRng and outputRng, as well as two string variables named myStr and letter.
 Set myRng = Sheets("vbUpperCase_Argument").Range("B5:B14")
    Set outputRng = Sheets("vbUpperCase_Argument").Range("C5:C14")
    outputRng = ""
  • The code sets myRng to the range of cells B5:B14 on the sheet named vbUpperCase_Argument, and sets outputRng to the range of cells C5:C14 on the same sheet. It then clears the values in outputRng.
  For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = StrConv(myStr, vbUpperCase)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub
  • Next, the code uses a loop to iterate over each cell in myRng. For each cell, it uses the StrConv function to convert the value to uppercase using the vbUpperCase argument and assigns it to the letter Finally, it concatenates the original value in outputRng with the uppercase letter value and assigns the result to the cell in outputRng corresponding to the current iteration of the loop.

Using StrConv Function to Capitalize All Letters


4. Use StrConv Function with vbProperCase Argument to Capitalize All Letters in Excel

We can use the StrConv function with the vbProperCase argument in VBA Excel to capitalize all letters.

VBA Code with StrConv Function

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub StrConvFunc_vbProperCaseArg()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("vbProperCase_Argument").Range("B5:B14")
    Set outputRng = Sheets("vbProperCase_Argument").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        For j = 1 To Len(myStr)
            letter = StrConv(Mid(myStr, j, 1), vbProperCase)
            outputRng.Cells(i) = outputRng.Cells(i) & letter
        Next j
    Next i
End Sub

VBA Breakdown

Sub StrConvFunc_vbProperCaseArg()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
  • This Excel VBA code defines a subroutine called StrConvFunc_vbProperCaseArg. It declares two range variables named myRng and outputRng, as well as two string variables named myStr and letter.
  Set myRng = Sheets("vbProperCase_Argument").Range("B5:B14")
    Set outputRng = Sheets("vbProperCase_Argument").Range("C5:C14")
    outputRng = ""
  • The code sets myRng to the range of cells B5:B14 on the sheet named vbProperCase_Argument, and sets outputRng to the range of cells C5:C14 on the same sheet. It then clears the values in outputRng.
 For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        For j = 1 To Len(myStr)
            letter = StrConv(Mid(myStr, j, 1), vbProperCase)
            outputRng.Cells(i) = outputRng.Cells(i) & letter
        Next j
    Next i
        
End Sub
  • Next, the code uses a loop to iterate over each cell in myRng. For each cell, it uses another loop to iterate over each character in the cell’s value. It converts each character to a proper case using the StrConv function with the vbProperCase argument and assigns it to the letter variable. Finally, it concatenates the original value in outputRng with the proper case letter value and assigns the result to the cell in outputRng corresponding to the current iteration of the loop.

Using StrConv Function to Capitalize All Letters


How to Capitalize First Letter in Excel with VBA

We can capitalize the first letter of each sentence by using Excel VBA.

VBA Code to Capitalize First Letter

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Capitalize_First_Letter()
    Dim myRng, outputRng As Range
    Dim myStr, myStr1, letter As String
    Set myRng = Sheets("First_Letter").Range("B5:B14")
    Set outputRng = Sheets("First_Letter").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        myStr1 = Mid(myStr, 1, 1)
        myStr1 = UCase(myStr1)
        myStr = myStr1 & Right(myStr, Len(myStr) - 1)
        outputRng.Cells(i) = myStr
    Next i
        
End Sub

VBA Breakdown

Sub Capitalize_First_Letter()
    Dim myRng, outputRng As Range
    Dim myStr, myStr1, letter As String
  • This Excel VBA code defines a subroutine called Capitalize_First_Letter. It declares three string variables named myStr, myStr1, and letter, and two range variables named myRng and outputRng.
 Set myRng = Sheets("First_Letter").Range("B5:B14")
    Set outputRng = Sheets("First_Letter").Range("C5:C14")
    outputRng = ""
  • The code sets myRng to the range of cells B5:B14 on the sheet named First_Letter, and sets outputRng to the range of cells C5:C14 on the same sheet. It then clears the values in outputRng.
   For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        myStr1 = Mid(myStr, 1, 1)
        myStr1 = UCase(myStr1)
        myStr = myStr1 & Right(myStr, Len(myStr) - 1)
        outputRng.Cells(i) = myStr
    Next i
End Sub
  • Next, the code uses a loop to iterate over each cell in myRng. For each cell, it assigns its value to the myStr It then extracts the first character of myStr using the Mid function and assigns it to the myStr1 variable. It converts myStr1 to upper case using the UCase function and assigns it back to myStr1. The code then concatenates myStr1 with the remainder of myStr (excluding the first character) using Right and Len functions, and assigns the result to myStr.

Finally, the code assigns the modified value in myStr to the corresponding cell in outputRng for the current iteration of the loop. The result is that the first letter of each string in myRng is capitalized in outputRng.

Capitalizing First Letter


How to Change Case to Capitalize Each Word in Excel with VBA

We can capitalize first letter of each word of a sentence by using Excel VBA.

VBA Code to Capitalize Each Word

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Capitalize_Each_Word()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("Each_Word").Range("B5:B14")
    Set outputRng = Sheets("Each_Word").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = Application.WorksheetFunction.Proper(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub

VBA Breakdown

Sub Capitalize_Each_Word()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("Each_Word").Range("B5:B14")
    Set outputRng = Sheets("Each_Word").Range("C5:C14")
    outputRng = ""
  • This VBA code is called Capitalize_Each_Word. It starts by defining two ranges, myRng and outputRng, both of which correspond to columns B and C of a sheet named Each_Word. Then it initializes an empty string for the outputRng.   
  For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = Application.WorksheetFunction.Proper(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub
  • The code then enters a For loop that runs for the number of cells in myRng. For each cell in myRng, the code assigns the cell’s value to myStr, then it uses the Proper function from the Excel WorksheetFunction object to capitalize the first letter of each word in myStr. The result is assigned to the letter variable and then appended to the corresponding cell in outputRng.

At the end of the loop, each cell in the outputRng range will contain a version of the corresponding cell in myRng where each word has its first letter capitalized.

Capitalizing Each Word

Read More: How to Change Case in Excel Without a Formula


How to Make Everything Lowercase in Excel

We can make everything lowercase by using Excel VBA.

VBA Code to Make Everything Lowercase

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Lowercase()
    Dim myRng, outputRng As Range
    Dim myStr, letter As String
    Set myRng = Sheets("Lower_Case").Range("B5:B14")
    Set outputRng = Sheets("Lower_Case").Range("C5:C14")
    outputRng = ""
    For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = LCase(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub

VBA Breakdown

 For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = LCase(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub
  • This VBA code defines a subroutine called Lowercase that converts all text in a specified range to lowercase letters. The range to be converted is specified as B5:B14 in the worksheet Lower_Case, and the converted text is output to the adjacent cells in column C. The subroutine begins by initializing the output range to an empty string.
 For i = 1 To myRng.Cells.Count
        myStr = myRng.Cells(i)
        letter = LCase(myStr)
        outputRng.Cells(i) = outputRng.Cells(i) & letter
    Next i
End Sub
  • It then uses a loop to iterate through each cell in the input range, converts the text to lowercase using the LCase function, and writes the result to the corresponding cell in the output range.

Making Everything Lowercase

Read More: Change Upper Case to Lower Case in Excel


Things to Remember

There are a few things to remember while using VBA in Excel to capitalize all letters:

  • Use the appropriate function.
  • Use proper syntax.
  • Select the proper range of cells to convert text.

Frequently Asked Questions

  • Are there any limitations while capitalizing all letters in Excel VBA?

One limitation is that the UCase function only converts lowercase letters to uppercase and does not affect any characters that are already uppercase.

  • Can I revert the capitalization changes made by the VBA Excel code?

Yes, you can revert the capitalization changes made by the VBA code by using the Undo command or by restoring a backup copy of the workbook.

  • Can I apply the UCase function to a range of cells?

Yes, you can apply the UCase function to a range of cells by specifying the range using the Range function.


Download the Practice Workbook

You can download this practice workbook while going through this article.


Conclusion

In this article, we have discussed 4 methods in detail for using VBA in Excel to capitalize all letters. This article will allow users to use Excel more efficiently and effectively.  If you have any questions regarding this essay, feel free to let us know in the comments.


Related Articles


<< Go Back to Change Case | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo