How to Use the VBA Chr Function (2 Examples)

Syntax of the VBA Chr Function

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)

Quick View to Use the VBA Chr Function

When you run it, it’ll return “A”, because 65 is the ASCII code of “A”.

Output of Using the VBA Chr Function


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Introduction to the VBA Chr Function

⧭ Overview:

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.

⧭ Syntax:

Syntax of the VBA Chr Function

Therefore, the syntax of the Chr function is:

=Chr(CharCode As Long)

⧭ Arguments:

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

VBA Code to Use the VBA Chr Function

⧭ Output:

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.

Image 7.

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


Similar Readings:


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

VBA Code to Use the VBA Chr Function

⧭ Output:

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.


Conclusion

Using these methods, you can use the Chr function of VBA. Do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo