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.
