VBA Chr Function

The VBA Chr function is used to return the character associated with a specific character code. This function is extremely useful when working with strings and character manipulation in VBA. The function takes in the character code as an argument and returns the corresponding character. It can also be used in conjunction with the ‘Asc’ function to convert characters to their corresponding character codes.

VBA Chr Function – Purpose, Syntax and Arguments

Syntax:

Chr(Charcode)

Arguments:

• Charcode: This is the required argument for the Chr function and it represents the character code for which the corresponding character needs to be returned. The character code can be any integer value between 0 and 255.

Example:

Suppose we want to create a string that displays the alphabet in uppercase letters. We can use the Chr function inside a loop to achieve this as shown in the following code:

Sub alphabet()
Dim i As Integer
Dim uppercase As String
For i = 65 To 90
uppercase = uppercase + Chr(i)
Next i
MsgBox uppercase
End Sub

The resulting string displayed in the message box will be: ABCDEFGHIJKLMNOPQRSTUVWXYZ.

Remarks:

• The Chr function only works with character codes in the range of 0 to 255. Any character code outside this range will result in an error.
• The function can also be used to insert special characters in strings, such as tab, line feed, and carriage return, by using their respective ASCII codes.
• If the Chr function is used with a character code that is outside the ASCII character set, it will return a non-printable character.

Important Notes:

• The Chr function is not limited to just VBA, it can also be used in other programming languages such as VB.NET and C#.
• The function can be used with the ‘Asc’ function to convert a character to its corresponding character code, and vice versa.
• The Chr function is case-sensitive, meaning that a different character will be returned for uppercase and lowercase character codes.
• The character codes used by the Chr function are based on the American Standard Code for Information Interchange (ASCII) character set, which is the most commonly used character encoding system.

Understanding VBA Chr Function with Examples

Basic Syntax

Description: The Chr function is a VBA built-in function that returns a character based on its corresponding ASCII code. It is used to convert numeric values to their equivalent characters.

Code:
Chr(code)
Example:
Chr(65)
Output:
A

Explanation: The Chr function takes in one argument, which is the ASCII code corresponding to the character you want to retrieve. In this example, the ASCII code for the letter A is 65, so the function returns the character A as its output.

Multiple Arguments

Description: Apart from the ASCII code, the Chr function can also take in multiple arguments to create a string of characters. It can be used to create special characters string for formatting purposes.

Code:
Chr(firstcode, secondcode, thirdcode, ...)
Example:
Chr(65, 66, 67)
Output:
ABC

Explanation: In this example, the Chr function takes in three arguments – 65, 66, and 67, which are the ASCII codes for the letters A, B, and C respectively. When multiple codes are provided, the function concatenates the characters together and returns them as a string.

Using in a Loop

Description: The Chr function can be used in conjunction with loops to create a string of characters that follow a pattern or increase in value.

Code:
For i = 65 To 69
str = str & Chr(i)
Next i
Example:
Output:
ABCDE

Explanation: In this example, the Chr function is used within a For loop to dynamically create a string of characters by increasing the ASCII code by 1 in each iteration. The variable str is first assigned an empty string, and then the Chr function is used to add each character to the string. After the loop finishes executing, the variable str will contain the characters A, B, C, D, and E.

Using with User Input

Description: The Chr function can also be used with user input to retrieve characters based on the ASCII codes entered.

Code:
Dim code As Integer
code = InputBox("Enter an ASCII code:")
MsgBox Chr(code)
Example:
Input:
Enter an ASCII code:
65
Output:
A

Explanation: In this example, the Chr function is used to convert the user input (ASCII code) into its equivalent character. The user is prompted to enter an ASCII code, and the function converts it to the character A. This can be useful when working with input fields in forms or when creating password fields.

Using with Built-in Functions

Description: The Chr function can be used in conjunction with other built-in VBA functions to manipulate and retrieve specific characters from strings.

Code:
Mid(Chr(65), 1, 1)
Example:
Output:
A

Explanation: In this example, the Chr function is used as an argument for the Mid function, which is used to retrieve a specific number of characters from a string. In this case, the Mid function is retrieving the first character from the character A returned by the Chr function.

Conclusion

The Chr function is a powerful and versatile function in VBA that allows you to retrieve characters based on their ASCII codes. It can be used for various purposes, such as creating strings, formatting, and working with user input. By understanding the basic syntax and examples of the Chr function, you can effectively utilize it in your VBA projects.