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

⧭ 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:

Therefore, the syntax of the Chr function is:

⧭ Arguments:

⧭ 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

⧭ 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.

---

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

⧭ 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.

---

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.

The post How to Use the VBA Chr Function (2 Examples) appeared first on ExcelDemy.