VBA Chr

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA CHR Function

VBA CHR is a built-in function that falls under the String/Text Functions category used to get the character equivalent to the ASCII code. For example, the equivalent Excel Workspace function of CHR is Application.WorksheetFunction.CHAR.

Table of contents

Given below is the Chr syntax.

vba chr syntax

This function has one argument. Where,

Charco de = This is a required parameter. It is the ASCII code for which an equivalent character is to be retrieved.

The function returns a string value denoting the character equivalent to the given ASCII code. ASCII is a subset of the Unicode character coding standard formed by 128 symbols in the character set, including uppercase, lowercase letters, numbers, special characters, control characters, and punctuation marks. In addition, every symbol in the character set has an equivalent Decimal value (0 to 127), a Hexadecimal value, and an Octal value.

vba-chr

Example

Step 1: In the Excel sheet, add two header texts in cells A1 and C1, as shown in the figure below. Column A to enter the ASCII code and column C to print the corresponding character calculated using the CHR function.

vba chr example 1.11

Step 2: Follow the steps in the next section to create a Button in the Excel workbook (please scroll the article) and change its caption to "Click here."

vba chr example 1.12

Step 3: Write the following code snippet in VBA. The code reads the value from cell A2, which is given as an input to function CHR. The result is retrieved in String char1 and then assigned to cell C2.

Code:

Sub Button1_Click()
'This function returns the character for the value entered in cell A2
Dim char1 As String
'Declare char1 variable as String

char1 = Chr(Range("A2").Value)
'Read value from cell A2

Range("C2").Value = char1
'Print output in cell C2

End Sub
vba chr example 1.13

Step 4: Save the VBA Excel code and go back to the Excel workbook to enter the input value in cell A2, as shown below.

Enter 65 as an ASCII input for the corresponding character to be found in cell C2.

vba chr example 1.14

Step 5: Click the "Click here" button to print the result in cell C2.

vba chr example 1.15

Observe the result printed in cell C2. The code snippet we wrote in step 3 is responsible for reading the input from cell A2, running the Chr function on it, and printing the value in cell C2, as shown below.

VBA CHR GIF 1

Here, the input given is 65, and the output received is A. So, Chr (65) = A.

Step 6: Try changing the input in cell A2 and observe that you get the respective output in cell C2, as shown below.

E.g. CHR (37) = % and so on.

VBA CHR GIF 2

How to Create a Button in Excel?

As a VBA function, we can use it in excel macro code, entered through the Microsoft Visual Basic Editor integrated into MS Excel. Refer to the steps given below to know more.

Step 1: Turn on Developer Mode in Excel

For any VBA function to use in Excel, one must turn on the Developer Mode from File->Options Menu, as shown in the figure below.

Click on File – > Excel Options – >Customize Ribbon ->Developer -> OK

vba chr example 1.1

As a result, a new toolbar option named "Developer" will be added to the workbook, as shown in the picture below.

vba chr example 1.2

Step 2: Saving the workbook

Save the Excel Workbook as an “Excel Macro-Enabled Workbook.”

vba chr example 1.3

Step 3: Insert a form control in a workbook

  • Click on the "Developer" tab. And in the "Controls" sub-section, click on the "Insert" option in VBA.
example 1.4
  • Choose the first control, "Button (Form Control)."
vba chr example 1.5
  • Observe that the workbook cursor changes to a drawable icon.
  • As you try to draw a button, a new dialog window named ‘Assign Macro’ will launch. As we learn more, you can specify the macro name you will use in the VB code. E.g., Button1_Click. Click "OK."
example 1.6
  • A button will then automatically get inserted into the workbook. The button caption text is editable, and we can edit it by double-clicking on the button.
vba chr example 1.7

Step 4: Write VB code

  • Select the button and click on the first option from the left under the "Code" sub-section of the "Developer" tab, i.e., "Visual Basic."
example 1.8
  • It will launch a new window of the VBA project, as shown in the picture below.
example 1.9
  • As shown in the above figure, an empty skeleton for the earlier created macro, i.e., Button1_Click, is populated in the VB code window.
  • You can write the macro definition as per your intention. Here, we will see an example of the VBA CHAR function in the following section.

Step 5: Switching between Excel workbook and VB IDE

You can switch between the Excel workbook and the VB IDE by clicking on the extreme left icon below the "File" menu, i.e., "View Microsoft Excel," as shown below.

example 1.10

Things to Remember

  • The CHR function can return printable and non-printable characters present on the keyboard and understood by the computer. E.g., letters, numbers, and other special characters are printable characters. However, other keys such as "Enter," "Spacebar," and "Esc" are non-printable characters.
  • The CHR is a VBA function and cannot be used in Excel as it is. Its corresponding function in Excel is Application.WorksheetFunction.CHAR.