VBA Chr

What is Excel VBA CHR Function?

The Excel VBA CHR function is a built-in function that returns a character based on the ASCII code provided. ASCII (American Standard Code for Information Interchange) is a widely used character encoding standard that assigns unique numeric codes to represent characters.

The VBA CHR function takes an ASCII code as its argument and returns the corresponding character. It allows you to convert ASCII codes to characters, enabling you to work with specific characters or manipulate strings more effectively in VBA.Let us look at an example. In this example, the VBA CHR code assigns the ASCII code 63 to the variable “asciiCode,” corresponding to the question mark symbol “?”.

The VBA CHR function then converts this ASCII code into its corresponding character assigned to the character variable. Finally, a message box is displayed to show the value of the character variable.

This example demonstrates a simple usage of the VBA CHR function to convert an ASCII code into its corresponding character.

How to use Excel VBA CHR Function?

To use the Excel VBA CHR function, follow these steps:

Step 1: Open Excel and press “ALT + F11” to open the VBA editor.

Step 2: Insert a new module by clicking “Insert” from the menu and selecting “Module.”

Step 3: In the module, write the code to use the VBA CHR function, as shown in the example below:

Sub UseCHRFunction()
Dim result As String
Dim asciiCode As Integer
‘ Assign an ASCII code to the variable
asciiCode = 65
‘ Use the CHR function to convert the ASCII code to a character
result = Chr(asciiCode)
‘ Display the result
MsgBox result
End Sub

Step 4: Press “F5” or click the “Run” button to execute the code. A message box will appear, displaying the character corresponding to the ASCII code provided (in this case, it will show “A”).

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Example #1 – Convert ASCII codes to characters

Step 1: Open Excel and press “ALT + F11” to open the VBA editor.

Step 2: Insert a new module by clicking=”Insert” from the menu and selecting “Module.”

Step 3: In the module, first create a subroutine “ConvertASCIIToCharacter().”

We then declare a variable named “result” of type String within the subroutine. This variable will store the final result of the character concatenation.

The next line of code uses the VBA CHR function to convert the ASCII code 65 to the corresponding character “A” and assigns it to the “result” variable.

The “&” operator is used to concatenate the following VBA CHR function results, which convert ASCII codes 66 and 67 to characters “B” and “C,” respectively, appending them to the “result” variable.

The final line of code displays the value stored in the “result” variable using a message box.

Step 4: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “ConvertASCIIToCharacter” and Run.

Step 5: Once you execute the macro, a message box will appear, displaying the result of the concatenation of the characters corresponding to the ASCII codes 65, 66, and 67, which are “A,” “B,” and “C,” respectively.

Here is the full code:

Sub ConvertASCIIToCharacter()
Dim result As String
‘ Convert multiple ASCII codes to characters
result = Chr(65) & Chr(66) & Chr(67)
‘ Display the result
MsgBox result
End Sub

Example #2 – Generate a character table using a loop

Step 1: In the module, first create a subroutine “GenerateCharacterTable()”:

The code starts by defining a subroutine named “GenerateCharacterTable.”

Within the subroutine, we declare two variables: “result” of type String and “asciiCode” of type Integer.

The “result” variable will store the concatenated characters and tabs, and the “asciiCode” variable will represent the current ASCII code in the loop.

Step 2: Now, we use a “For” loop to iterate through the range of ASCII codes. We use the VBA CHR function inside the loop to convert the current ASCII code to its corresponding character and append it to the “result” variable.

The “&” operator concatenates the character obtained from CHR with the existing contents of the “result” variable.

We also add a tab character (represented by vbTab) after each character to separate them visually in the table.

Step 3: Now, we add a line of code to display the value stored in the “result” variable using a message box.

Step 4: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “GenerateCharacterTable(),” and Run.

Step 5: Once you execute the code, a message box will appear, displaying a VBA CHR table of characters from ASCII codes 32 to 126, separated by tabs.

Here is the full code:

Sub GenerateCharacterTable()
Dim result As String
Dim asciiCode As Integer
‘ Loop through ASCII codes 32 to 126
For asciiCode = 32 To 126
result = result & Chr(asciiCode) & vbTab
Next asciiCode
‘ Display the result
MsgBox result
End Sub

Example #3

Let us look at another interesting example. In this example, we will explore a sequence of codes that generates a random password of a specified length using the VBA Chr function.

Step 1: First, we create a “TestGeneratePassword” subroutine in the new module. This subroutine serves as a driver program to test the GeneratePassword function.

It prompts the user to enter the desired password length and stores the input in the variable password length.

Subsequently, the “GeneratePassword” function is called, passing “passwordLength” as an argument. The generated password is then stored in the variable “generatedPassword”.

Step 2: Here, we create the “GeneratePassword” function, which takes the length parameter, representing the desired length of the password, and returns a string containing the generated password.

Inside the function, a loop runs from 1 to length, generating a random ASCII code within the range of uppercase letters using the “Rnd” and Int functions.

Step 3: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “TestGeneratePassword,” and run.

Step 4: Once we execute the code, the message box asks us to provide the password length.

Step 5: Once we provide the password length (in this example, 15), the generated ASCII code is converted to its corresponding character using the VBA Chr function, and the characters are concatenated to form the password.

Here is the full code:

Sub TestGeneratePassword()
Dim passwordLength As Integer
Dim generatedPassword As String
‘ Prompt the user for the desired password length
passwordLength = InputBox(“Enter the desired password length:”)
‘ Generate the password using the GeneratePassword function
generatedPassword = GeneratePassword(passwordLength)
‘ Display the generated password
MsgBox “Generated Password: ” & generatedPassword
End Sub

Function GeneratePassword(length As Integer) As String
Dim password As String
Dim asciiCode As Integer
‘ Generate a random password
Randomize
For i = 1 To length
‘ Generate a random ASCII code in the range of uppercase letters
asciiCode = Int((90 – 65 + 1) * Rnd + 65)
password = password & Chr(asciiCode)
Next i
GeneratePassword = password
End Function

How to Create a Button in Excel?

To create a button in Excel, follow these steps:

Step 1: Open Excel and navigate to the worksheet where you want to insert the button.

Step 2: Click the “Developer” tab in the Excel ribbon. If you don’t see the “Developer” tab, you need to enable it first. Right-click on the ribbon, choose “Customize the Ribbon,” and check the “Developer” option.

Step 3: In the “Controls” group of the “Developer” tab, click on the “Insert” button.

Step 4: In the “Form Controls” section, select the “Button” option (typically found in the top-left corner).

Step 5: Click and drag on the worksheet to create the button. The size of the button can be adjusted later.

Step 6: In the “Assign Macro” dialog box that appears, select “New” to create a new macro for the button.

Step 7: The VBA editor will open with a new subroutine created. You can write the code for the button’s functionality in this subroutine.

Step 8: Write your desired VBA code inside the subroutine to define the actions performed when the button is clicked. For example:

Sub Button1_Click()
‘Write your code here
MsgBox “Button clicked!”
End Sub

Step 9: Close the VBA editor and test the button by clicking on it. In this example, a message box will appear displaying the text “Button clicked!” when the button is clicked.

Important things to Note

1. The VBA CHR function returns a character based on the ASCII code provided. It only supports ASCII codes from 0 to 255.
2. VBA CHR codes refer to the numeric values representing ASCII codes that can be used as arguments for the VBA CHR function to convert them into corresponding characters.
3. A VBA CHR List is a string that contains a sequence of characters generated using the VBA CHR function.
4. VBA CHR tab refers to using the CHR function to insert a tab character into a string or a cell. The tab character, represented by ASCII code 9, creates horizontal spacing or indentation within a text.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Chr, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is VBA Chr Function?. We learn its syntax, how to use chr function to return the character based on ASCII code, with examples. You can learn more from the following articles –

• VBA Counter
• VBA Call Sub
• VBA Clear Content