One of the most important and widely used functions of VBA is the Chr function. In this article, I’ll show you how you can use the VBA Chr function with proper examples and illustrations.
VBA Chr Function (Quick View)
When you run it, it’ll return “A”, because 65 is the ASCII code of “A”.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Introduction to the VBA Chr Function
The Chr function in VBA takes a number between 0 to 255 and returns the corresponding ASCII character of the number.
For example, Chr (65) = A
Chr (90) = Z
If you put a number greater than 255 within the Chr function, it’ll return an error.
Therefore, the syntax of the Chr function is:
=Chr(CharCode As Long)
|Argument||Required / Optional||Explanation|
|CharCode||Required||The integer between 0 to 255 of which the corresponding ASCII character will be returned. Must be an integer between 0 to 255.|
⧭ Return Value:
Returns the corresponding ASCII character of the given integer as CharCode.
2 Examples to Use the VBA Chr Function
Now we’ll see a few examples of how to use the Chr function inside VBA codes.
Example 1: Converting any Character to LowerCase or UpperCase using the VBA Chr Function
You can convert any character to LowerCase or UpperCase using the Chr function of VBA.
Here we’ve got a data set with the IDs, Names, and Home Countries of some employees of a company called Jupyter group.
But mistakenly, the firstmost characters of the Home Countries have been made with LowerCase letters, other than UpperCase ones.
Now, we’ll develop a VBA code using the Chr function to turn the leftmost characters from LowerCase to UpperCase.
First, we have to take the leftmost character of the country names using the Left function of VBA.
First_Character = Left(Country_Name,1)
Then we convert it to its ASCII code using the Asc function of VBA.
First_Character = Asc(First_Character)
Now we convert it to the ASCII code of its LowerCase character by subtracting 32 from it. [The difference of the ASCII codes between a LowerCase and an UpperCase character is 32.]
First_Character = First_Character - 32
Then we again convert it to the character using the Chr function.
First_Character = Chr(First_Character)
Finally, we set it as the first character of the country name with the Right function of VBA.
Country_Name = First_Character + Right(Country_Name, Len(Country_Name) - 1)
So the complete VBA code is:
⧭ VBA Code:
Sub Converting_LowerCase_to_UpperCase() For i = 1 To Selection.Rows.Count For j = 1 To Selection.Columns.Count Country_Name = Selection.Cells(i, j) First_Character = Left(Country_Name, 1) First_Character = Asc(First_Character) First_Character = First_Character - 32 First_Character = Chr(First_Character) Country_Name = First_Character + Right(Country_Name, Len(Country_Name) - 1) Selection.Cells(i, j) = Country_Name Next j Next i End Sub
Select the home countries from the data set and run the Macro (Converting_LowerCase_to_UpperCase).
It’ll convert the first characters of home countries to their uppercase letters.
Read More: How to Use LCase Function in VBA in Excel (With 4 Examples)
- How to Use the VBA Environ Function (4 Examples)
- How to Use the VBA Weekday Function (2 Suitable Examples)
- VBA If – Then – Else Statement in Excel (4 Examples)
- How to Use VBA IsNumeric Function (9 Examples)
- How to Use VBA TimeValue Function (6 Relevant Examples)
Example 2: Checking whether a Password Contains A Specific Character or Not
We can use the Chr function of VBA to check whether a password contains a specific character or not.
Let’s check whether the password contains the special character “@” or not.
First, we’ll use an Input Box to take the password as input.
Password = InputBox("Enter the Password: ")
Then we set an integer called Count to keep the track of the specific character.
Count = 0
Then we have to iterate through each character of the password and check whether it is equal to Chr(64) or not. [64 is the ASCII code of “@”].
For i = 1 To Len(Password) If Mid(Password, i, 1) = Chr(64) Then Count = Count + 1 End If Next i
Finally, if Count is greater than 0, then the specific character is present one or more times. Otherwise not.
If Count > 0 Then MsgBox "The Password is Valid." Else MsgBox "The Password is not Valid." End If
So, the complete VBA code will be:
⧭ VBA Code:
Sub Checking_whether_a_Password_Contains_Numeric_Value() Password = InputBox("Enter the Password: ") Count = 0 For i = 1 To Len(Password) If Mid(Password, i, 1) = Chr(64) Then Count = Count + 1 End If Next i If Count > 0 Then MsgBox "The Password is Valid." Else MsgBox "The Password is not Valid." End If End Sub
Run the code. It’ll display an Inputbox and ask for the password.
Here I’ve inserted a password with the special character “@”.
Then I clicked OK. It’ll display “The Password is Valid.”, because there is a “@” in the password.
Related Content: Excel VBA ASC() Function – Get ASCII Value of Character
Things to Remember
Up till now, we have used the Chr function of VBA. Besides the Chr function, there are two more functions called ChrB and ChrW that perform almost the same task. If you are interested, you can learn them in detail.
Using these methods, you can use the Chr function of VBA. Do you have any questions? Feel free to ask us.