Converting Specific Words to Lowercase Using VBA in Excel

In this lesson, we will go through a VBA macro that converts specific words in an Excel worksheet to lowercase. This macro is useful when you need to standardize the case of certain words across your worksheet. We will break down the code line by line to help you understand how it works.

This VBA macro is designed to process a given range in an Excel worksheet and convert specific words to lowercase. These words are defined in a string and then split into an array for comparison with the words in each cell.

Here is the full code

Sub ConvertCertainWordsToLowerCase()

    Dim WS As Worksheet
    Dim WrdArray() As String
    Dim IndividualWords() As String
    Dim CertainWordsString As String
    Dim ResultString As String
    Dim i As Integer
    Dim j As Integer
    
    Set WS = Worksheets("Sheet1")
    
    ' Define the words to convert to lowercase
    CertainWordsString = "with,and,or,for,to,from"
    WrdArray() = Split(CertainWordsString, ",")
    
    ' Loop through each cell in the used range
    For Each MyCell In ActiveSheet.UsedRange
        If MyCell.Value <> "" Then
            MyCell.Value = Trim(MyCell.Value)
            IndividualWords() = Split(MyCell.Value)
            
            ' Loop through words in the cell
            For i = LBound(IndividualWords) To UBound(IndividualWords)
                ' Loop through target words
                For j = LBound(WrdArray) To UBound(WrdArray)
                    ' Compare and convert to lowercase if match is found
                    If StrComp(IndividualWords(i), WrdArray(j), vbTextCompare) = 0 Then
                        IndividualWords(i) = LCase(WrdArray(j))
                        Exit For
                    End If
                Next j
            Next i
            
            ' Reassemble the sentence
            ResultString = ""
            For i = LBound(IndividualWords) To UBound(IndividualWords)
                ResultString = ResultString & " " & IndividualWords(i)
            Next i
            
            MyCell.Value = Trim(ResultString)
        End If
    Next
    
    MsgBox "Completed!", vbInformation, ""
    
End Sub

Below is the step-by-step explanation of the above code.

Declaring Variables

Dim WS As Worksheet
Dim WrdArray() As String
Dim IndividualWords() As String
Dim CertainWordsString As String
Dim ResultString As String
Dim i As Integer
Dim j As Integer

• WS: This variable holds the reference to the worksheet.
• WrdArray(): An array to store the specific words that need to be converted to lowercase.
• IndividualWords(): An array to hold the words from each cell.
• CertainWordsString: A string containing the specific words, separated by commas.
• ResultString: A string to build the final result after processing each cell.
• i, j: Integer variables used as counters in the loops.

Setting the Worksheet

Set WS = Worksheets("Sheet1")

This line sets the worksheet (Sheet1) to the variable WS.

Defining Certain Words

CertainWordsString = "with,and,or,for,to,from"
WrdArray() = Split(CertainWordsString, ",")

• CertainWordsString: Contains the words that need to be converted to lowercase.
• Split: This function splits the string into an array, using the comma as a delimiter.

Loop Through Each Cell in the Used Range

For Each MyCell In ActiveSheet.UsedRange
    If MyCell.Value <> "" Then

This loop goes through each cell in the used range of the active sheet. If the cell is not empty, it proceeds to process the cell's value.

Trim and Split Cell Value

MyCell.Value = Trim(MyCell.Value)
IndividualWords() = Split(MyCell.Value)

• Trim: Removes any leading or trailing spaces from the cell's value.
• Split: Splits the cell value into an array of words.

Compare and Convert Words

'Loop through words of sentence
For i = LBound(IndividualWords) To UBound(IndividualWords)
    'Loop through certain words
    For j = LBound(WrdArray) To UBound(WrdArray)
        'Compare two array elements
        If StrComp(IndividualWords(i), WrdArray(j), vbTextCompare) = 0 Then
            IndividualWords(i) = WrdArray(j)
            Exit For
        End If
    Next j
Next i

• Outer Loop: Iterates through each word in the cell.
• Inner Loop: Iterates through each word in the certain words array.
• StrComp: Compares the words, ignoring case (vbTextCompare).
• If Condition: If a match is found, the word is converted to lowercase, and the inner loop exits.

Reconstructing the Sentence

ResultString = ""
'create result sentence
For i = LBound(IndividualWords) To UBound(IndividualWords)
    ResultString = ResultString & " " & IndividualWords(i)
Next i
MyCell.Value = Trim(ResultString)

• Loop: Concatenates each word from the array into ResultString.
• Trim: Ensures there are no leading or trailing spaces in the final result.

Final Message

MsgBox "Completed!", vbInformation, ""

Displays a message box indicating that the macro has completed its execution.

This is the result you will get when run the above macro.

This VBA macro provides an efficient way to standardize the case of specific words in your Excel worksheet. By understanding each line of the code, you can modify and expand it to fit your specific needs.