VBA LCASE FUNCTION

What is Excel VBA LCase Function?

In Excel VBA, the LCase function converts a given string to a lowercase. It can be beneficial when you want to ensure consistent casing for text data or when performing case-insensitive comparisons. The LCase function doesn’t modify the original string; it returns a new string with all characters converted to lowercase.

Consider the following example:

This example converts the string “Hello World” to “hello world.” A new string is created here, and the original string remains the same. The output is shown below. It is printed in the immediate tab.

Syntax of LCase

The syntax of the LCase function in VBA is as follows:

LCase(StringExpression)

Where:

“StringExpression” is the string you want to convert to lowercase.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Convert Text in Lowercase in VBA?

Examples

Let us view a few examples to understand more about the VBA LCase function.

Example #1

You have a product itinerary, and to make sorting easier, the manager tasks you to convert all of them to lowercase but doesn’t want to replace the original values. You must work on the list below and print them in lowercase in the next column.

This can be done using the VBA LCase function.

• Step 1: Create a subroutine to convert all the products to Lowercase.

• Step 2: Declare a Range variable to store the size of the table, one to find the size of the table, a Worksheet variable to store the sheet in which the subroutine is working, and an iterative variable.

• Step 3: Set the worksheet in which this subroutine will be working.

• Step 4: Find the size of the table. This is done by utilising the XlUp function.

XlUp function simulates the “up” arrow key in the keyboard. This is used to find the last non-empty cell in the row and the return of rows with non-empty cells. This makes the size of the table dynamic and susceptible to changes.

• Step 5: Set the range of the table in which the for loop will work.

• Step 6: Initialize a for loop that runs through the table and converts all the strings to lowercase and then prints it in the adjacent column.

To print it in the adjacent column, we use the VBA Offset function and add 1 at the Y component of the function to shift by one column.

Code:

Sub ConvertProductNamesToLowercase()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim cell As Range
Set ws = ThisWorkbook.Sheets(“Example_1”)
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Set rng = ws.Range(“A1:A” & lastRow)
For Each cell In rng
cell.Offset(0, 1).Value = LCase(cell.Value)
Next cell
End Sub

• Step 7: Run the subroutine mentioned above. The output is shown below:

Example #2

In an establishment, you have emails of users stored in a database. You need to create a search function that can return the mail ID you’re asking for irrespective of the case used. This can be done using the VBA LCase function.

• Step 1: Create a subroutine to create a search function.

• Step 2: Initialize variables to store worksheet variables, table size, range of the table, a string to store the value input by the user, a boolean variable to check its availability, and an iterative variable.

• Step 3: Declare the worksheet name this will work in.

• Step 4: Find the last row of the table with the XlUp function. It returns the last non-empty cell value to the variable.

• Step 5: Set the range of the table with the value of the last row found previously.

• Step 6: Initialize an Input box to accept the user’s input for the search box.

• Step 7: Convert the input into Lowercase.

• Step 8: Initialize a FOR loop running through the table and check if the value searched for exists.

• Step 9: Create a message box for conditions to check whether the value exists. Print the address of the value using the VBA Address function. It returns the cell value of the value searched for.

Code:

Sub SearchEmailAddress()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim searchEmail As String
Dim emailFound As Boolean
Dim cell As Range
Set ws = ThisWorkbook.Sheets(“Example_2”)
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Set rng = ws.Range(“A1:A” & lastRow)
searchEmail = InputBox(“Enter the email address to search:”, “Search Email Address”)
searchEmail = LCase(searchEmail)
For Each cell In rng
If LCase(cell.Value) = searchEmail Then
emailFound = True
Exit For
End If
Next cell
If emailFound Then
MsgBox “Email address found in cell- ” & cell.Address
Else
MsgBox “Email address not found.”
End If
End Sub

• Step 10: Run the Module. The output is shown below.

Example #3

You are managing a social media company. In social media, all users store their names as their Usernames. As a tech manager, you must create a simple application that will find the usernames without the added burden of case sensitivity. This can be done by creating a UserForm in Excel VBA and then applying the VBA LCase function in range to all the searches.

• Step 1: Click the “Insert” and “Userform” buttons to create a new userform or blank page.

• Step 2: Create the UserForm and customize the UserForm to your liking.

Change the properties of the UserForm as shown below.

• Step 3: Add a command button and Textbox with the names txtUsername, and btnCheck, respectively.

• Step 4: After customizing the UserForm, double-click the Button to declare our subroutine.

• Step 5: Declare variables to accept the username search, an iterative variable, and a Boolean variable to return true for a match.

• Step 6: Take the string value gotten from the textbox and convert it into Lowercase.

• Step 7: Initialize a FOR loop running through the table. Check whether the name exists. If so, return True using the Boolean variable.

Specify the sheet in which the UserForm should work.

• Step 8: Create a message box to give conditions for found or not found values.

Code:

Private Sub btnCheck_Click()
Dim searchUsername As String
Dim cell As Range
Dim usernameFound As Boolean
searchUsername = LCase(txtUsername.Value)
For Each cell In Worksheets(“Example_3”).Range(“A2:A10”)
If LCase(cell.Value) = searchUsername Then
usernameFound = True
Exit For
End If
Next cell
If usernameFound Then
MsgBox “Username found in the list!”
Else
MsgBox “Username not found.”
End If
End Sub

• Step 9: Run the UserForm and view the output.

Important Things to Note

• You can use the VBA LCase function for case-insensitive string comparisons.
• You must trim or clean input strings before using the VBA LCase function.
• For more efficiency, consider alternatives for large datasets. You can test LCase with special characters to ensure expected behavior.
• Validate user inputs and implement error handling using the VBA LCase function.
• Maintain consistency when using LCase throughout your code.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA LCase Function, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA LCase Function. We learn how to convert text into lowercase using LCase Function in Excel VBA, along with examples. You can learn more from the following articles –

• VBA Random Number
• VBA Progress Bar
• VBA Do Until Loop