Excel VBA LCase Function
LCase is an inbuilt function in vba which is used to convert an input string provided to it in the lowercase, it takes a single argument which is the string as an input and the output generated by this function is a string, the one thing to keep in mind is that this function converts all the function to lowercase, not just any single character.
You must have tried the same formula (LOWER) as excel in VBA and you have not found it. Because in VBA lowercase is named slightly different. In VBA it is in shortcut name i.e. “LCASE”. Here “L” stands for “LOWER”, so formula reads “LOWERCASE”.
- String: is nothing but the text value we are trying to convert to Lower Case. We can supply the text directly to the formula, it can be a cell reference, and it can be through variable as well.
How to Convert Text in Lowercase in VBA?
Let’s try to convert the text value “Hello Good Morning” to the lower case by using the LCASE function.
Step 1: Start the subprocedure by naming the excel macro.
Sub LCase_Example1() End Sub
Step 2: Declare the variable k as String.
Sub LCase_Example1() Dim k As String End Sub
Step 3: Assign the value to the variable “k” by applying the “LCASE” function.
Step 4: Here string is our desired text value that we are trying to convert to lower case and the desired string value is “Hello Good Morning”.
Sub LCase_Example1() Dim k As String k = LCase("Hello Good Morning") End Sub
Step 5: Now show the result of the variable “k” in the message box.
Sub LCase_Example1() Dim k As String k = LCase("Hello Good Morning") MsgBox k End Sub
Ok, coding is done. Let’s run the code to see the result.
So LCase converted the text value “Hello Good Morning” to “hello good morning” with the simple coding technique.
We have seen how LCASE function works in VBA. In the above example, we have directly supplied the value to the formula itself. Now we will see how can we use cell reference value to the formula.
Assume you have the word “Hello Good Morning” in the cell A1 like the below image.
Step 1: We will convert the cell A1 value to the lower case by showing the result in Range B1 cell, so the code will be Range (“B1”).Value =
Sub LCase_Example2() Range("B1").Value End Sub
Step 2: In cell B1 through LCASE function we will store the result, so open the function.
Step 3: In this example, the VBA string value is a cell reference, not direct value. So give the cell reference as Range (“A1”).Value.
Sub LCase_Example2() Range("B1").Value = LCase(Range("A1").Value) End Sub
So, ok we are done with the VBA coding part. Run the code and see the magic in the B1 cell.
Converting a single cell value or a single direct value isn’t the biggest challenge. When we need to deal with the “n” number of values in the worksheet then we need to apply loops to loop through all the cells and convert them to lower case values.
Assume below is the data you have in an excel worksheet.
If you are not aware of loops then you need to go back to the basics of VBA coding, refer our articles on “VBA Loops” to have a fair bit of knowledge on loops. The below code will convert the above names to the lower case.
Sub LCase_Example3() Dim k As Long For k = 2 To 8 Cells(k, 2).Value = LCase(Cells(k, 1).Value) Next k End Sub
This will convert all the text values from row 2 to row 8 in the lowercase function.
Based on your cells you can increase the limit of the loop from 8 to whatever the last row number of your data.
This has been a guide to VBA LCase. Here we discussed how to use Excel VBA LCase function to convert the text into lowercase with the help of practical examples and downloadable excel template. Below are some useful articles related to VBA –