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

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 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.

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.

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.

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

• 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.

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.

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.

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.