How to Use LCase Function in VBA in Excel (With 4 Examples)

How to Use LCase Function in VBA in Excel (With 4 Examples)

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.

Download the Practice Workbook

VBA LCase Function in Excel (Quick View)

Overview of the LCASE function

VBA LCase Function: Syntax & Arguments

VBA LCase Function Syntax & Arguments

Summary

LCASE function converts a string to lower-case.

Syntax

LCase(string)

Arguments

Argument Required or Optional Value
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.

How to Use the LCase Function in VBA Excel (N Examples)

Example 1: Convert a String into Lowercase and Present it in a Message Box

In this example, we will see how to print a message by converting them into lowercase and 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.

Step 1: Under the Developer tab, select the Marcos option (Shortcut Alt+F8)

Open macros

Step 2: Now give a name in the Macro name field (For me I have given Lowecase_Function) then click on Create button

Name the macro function

Step 3: Write the code in the VBA window

VBA code for message box

Code:

Sub Lowecase_Function()
  MsgBox LCASE("This is a Sample Text")
  MsgBox LCASE("THIS IS A SAMPLE TEXT")
End Sub

Step 4: Now save the code and press the Run button then see the output (Shortcut F5)

Click on the run button

Step 5: Now we will get two windows with results. Click on the OK button to go to the next window

 VBA output

Example 2: Convert the String into Lowercase 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 into separate column named Updated Name

Convert the String into Lowercase and Show them in a Separate Column

Step 1: Under the Developer tab, select Button from the Insert section

Select button

Step 2: Give a name of the Assign Macro field then press the OK button

Assign macro

Step 3: Now give the name of the button

Give a name of the button

Step 4: Right Click on the button and select the Assign macro option

right click on the button

Step 5: Now write the code in the VBA window

VBA code

Code:

Sub Lower_Case_Button()
 Dim k As Long
 For k = 4 To 15
  Cells(k, 3).Value = LCASE(Cells(k, 2).Value)
 Next k
End Sub

Code Explanation 

  • Dim k As Long this is declaring the K variable as long type.
  • For k = 4 To 15 this means K is an iteration variable and it will count from 4 to 15
  • 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.

Step 6: Click on the button and see the result

Click on the button and see the VBA code result


Similar Readings:


Example 3: Convert String into Lowercase which Contains Non-Letter or Digit Character

Let’s consider the above dataset with another column ID. In 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 change on any numbers or special characters, it only modifies alphanumeric characters.

Convert String into Lowercase which Contains Non-Letter or Digit Character

Step 1: Like the previous example go to the VBA window and write the code and save it

VBA code with Lcase function

Code:

Sub Lowecase_example3()
 Dim rg As Range
 Set rg = Selection
 For Each Cell In rg
   Cell.Value = LCASE(Cell)
 Next Cell
End Sub

Step 2: Now go to the worksheet and select the ID column

Now go to the worksheet and select the ID column

Step 3: Again, shift to the VBA window and run the code (Shortcut F5)

Run the VBA code

Step 4: It will automatically shift you to the worksheet and show the output

Output of the VBA code

Example 4: Automatically Generate Email ID by Taking Username as Input Using VBA LCase and TRIM Function

Now let’s see how we can easily generate email by taking usernames as input. Here our task is to take input names and return an email address using names.

Step 1: Go to the Macros option and name your program. For me, I have declared it as Email_Generate and click on the Create button

Generate email macro name

Step 2: Now write the code and run it

Email generate VBA code

Code:

Sub Email_Generate()

 Dim name As String, address As String
 IB = InputBox("Enter your first name")
 Lst_IB = InputBox("Enter your last name")
 MsgBox ("Your email is: " + Trim(LCase(IB)) + Trim(LCase(Lst_IB)) + "@xyz.com")
 
End Sub

Code Explanation

  • Dim name As String, address As String this declaring name and address as string data type.
  • IB = InputBox(“Enter your first name”) We are taking the first name as input into the IB variable using this code.
  • Lst_IB = InputBox(“Enter your last name”) using this code we are taking the last name as input into the Lst_IB variable.
  • Trim(LCase(IB)) and Trim(LCase(Lst_IB)) are removing spaces and converting the names into lowercase.

Step 3: Now a window will be pop up and you must enter your name first and last name

Enter first name and last name

Step 4: After press, the OK button the auto-generated email will be shown in the window

Auto generated email

Things to Remember

Common Errors When they show
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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo