Md. Abdullah Al Murad
When working in VBA, we often need to convert certain strings into lowercase, uppercase, or proper case strings. In this article, we will talk about lowercase transformation. There is a built-in function in VBA named LCase that can convert any string into lowercase. This article will share the complete idea of how the VBA LCase function works in Excel independently and then with other Excel functions.
A Quick View of VBA LCase Function in Excel:
Download Practice Workbook
You can download the following Excel workbook to practice while reading this article. Keep our site in the trusted-site list to make the macro work.
Introduction to VBA LCase Function in Excel: Syntax & Arguments
Summary:
LCASE function converts a string to lower-case.
Syntax:
LCase(string)
Arguments:
| Argument | Requirement | Explanation |
|---|---|---|
| string | Required | Pass the text that you want to convert to lowercase |
Note:
- This function affects only the Uppercase characters of the text string. Any character other than the Uppercase text characters is left unchanged.
- Using a null character (or a reference to an empty cell) will return a null character.
3 Examples of Using VBA LCase Function in Excel
Example 1: Convert a String into Lowercase Using LCase Function and Present It in a Message Box
In this example, we will see how to print a message by converting them into lowercase which will be displayed in a pop-up window. For this, our text will be declared in the VBA code. For this moment we will not load the data from the worksheet. Now let’s see the process.
Steps:
- Under the Developer tab, select the Marcos option (Shortcut Alt+F8).
- Now give a name in the Macro name field (For me I have given Lowecase_Function_Example1) then click on Create button.
- Write the code in the VBA window.
Code:
Sub Lowercase_Function_Example1()
MsgBox LCase("THIS IS A SAMPLE TEXT.")
MsgBox LCase("MAN IS MORTAL.")
End Sub
- Now save the code and press the Run button then see the output (Shortcut F5).
- Now we will get two windows with results. Click on the OK button to go to the next window.
Read More: How to Use VBA UCASE Function in Excel (4 Examples)
Example 2: Convert Strings into Lowercase with LCase Function and Show Them in a Separate Column
Now let’s have a dataset of some foods with their names and price. But all the names of the foods are given in upper case format. Now our task is to convert all the foods’ name into lowercase and shows them in a separate column named Updated Name.
Steps:
- Under the Developer tab, select Button from the Insert section. (Form Controls)
- Give a name in the Assign Macro field then press the OK button.
- Now give the name of the button.
- Right Click on the button and select the Assign macro option.
- Give a suitable name and click on the New button.
- Now write the code in the VBA window.
Code:
Sub Lowercase_Button()
Dim k As Long
For k = 5 To 16
Cells(k, 3).Value = LCase(Cells(k, 2).Value)
Next k
End SubCode Explanation:
- Dim k As Long this is declaring the K variable as long type.
- For k = 5 To 16 this means K is an iteration variable and it will count from 5 to 16.
- Cells(k, 3).Value = LCASE(Cells(k, 2).Value) Here we are assigning the 2nd column values (B column) to 3rd column (C column) by converting them into lowercase using LCase function.
- Click on the button and see the result.
Read More: How to Use VBA Case Statement (13 Examples)
Similar Readings
- How to Use VBA DateDiff Function in Excel (9 Examples)
- Random Number Excel Formula (5 Examples)
- Use VBA ChDir Function in Excel (4 Suitable Examples)
- How to Use VBA IsError Function (6 Examples)
- How to Use VBA While Wend Statement in Excel (4 Examples)
Example 3: Convert String into Lowercase Which Contains Non-Letter or Digit Characters Using LCase Function
Let’s consider the above dataset with another column ID. IDs are generated with alphabetic characters, numbers, and sometimes with special characters. Now our task is to convert the ID into lowercase. In addition, LCase doesn’t do any changes to any numbers or special characters, it only modifies alphanumeric characters.
Steps:
- Like the previous example go to the VBA window and write the code and save it.
Code:
Sub Lowercase_NonLetters()
Dim rg As Range
Set rg = Selection
For Each Cell In rg
Cell.Value = LCase(Cell)
Next Cell
End Sub- Now go to the worksheet and select the ID column.
- Again, shift to the VBA window and run the code (Shortcut F5).
- It will automatically shift you to the worksheet and show the output.
Read More: Create Custom VBA functions and Use them in the Worksheet
Things to Remember
Some Common Errors with LCase Functions Are:
- Output Not Showing
>> Compile the code before assigning it to any button.
- “Can’t find project or library”
>> It usually means there is something out there somewhere (variable, subroutine name, etc.) with that same name. It would be best to track that down and change it so it avoids conflict, you should be able to avoid the problem by changing that line of code to this:
Target.Value = VBA.LCase(Target.Value)
Conclusion
To conclude, I have tried to give a summary of the VBA LCase function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. That’s it all about the LCase function. If you have any inquiries or feedback, please let us know in the comment section. Also please visit our blog for more Excel-related articles.
Further Readings
- How to Use VBA Forecast Function in Excel (3 Ideal Examples)
- Call a Sub in VBA in Excel (4 Examples)
- How to Create a Body Mass Index Calculator in Excel Using VBA
- Use VBA Asc Function (5 Practical Examples)
- How to Use VBA Large Function in Excel (4 Suitable Examples)
- Use VBA StrComp in Excel (5 Common Examples)
- How to Use IsNull Function in Excel VBA (5 Examples)
