What is a CHR VBA Function?
The CHR function is a built-in function in Excel VBA that falls under the category of String/Text Functions and is used to get the character equivalent to the ASCII code. The equivalent Excel Workspace function of CHR is Application.WorksheetFunction.CHAR.
VBA Chr Function Syntax
Given below is the Chr syntax.
This function has one argument. Where,
CharCode = 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 Unicode character coding standard and is formed by 128 symbols in the character set. The symbols consist of uppercase, lowercase letters, numbers, special characters including control characters and punctuation marks. Every symbol in the character set has an equivalent Decimal value (0 to 127), a Hexadecimal value and an Octal value.
Example of CHR VBA Function in Excel
Step 1: In excel sheet add 2 header texts in cell 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 VBA CHR function.
Step 2: Follow the steps shown in the next section to create a Button in Excel workbook (please scroll the article) and change its caption to ‘Click here’.
Step 3: Write the following code snippet in VB that uses cell A2 value as an input to VBA CHR function and prints the resultant output in cell C2. The code reads the value from cell A2 which is given as an input to function CHR. The result is of CHR is retrieved in String char1 and then assigned to cell C2.
4.6 (247 ratings)
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
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 which the corresponding character is to be found in cell A2.
Step 5: Click on the button ‘Click here’ to print the result in cell C2.
Observe that the result is printed in cell C2. The code snippet we wrote in the step 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.
Here, the input is entered as 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 respective output in the cell C2 as shown below.
E.g. CHR (37) = % and so on.
How to Create a Button in Excel?
As a VBA function, it can be used in macro code which is 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
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 – > Options – >Customer Ribbon ->Developer -> OK
As a result, a new toolbar option named ‘Developer’ will be added to the workbook as shown in the picture below.
Step 2: Saving the workbook
Save the Excel Workbook as “Excel Macro-Enabled Workbook”.
Step 3: Insert a form control in a workbook
- Click on the Developer tab. And in the Controls sub-section, click on ‘Insert’ option in VBA.
- Choose the first control i.e. Button
- 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. There you can specify the macro name which will be used in the VB code as we learn more. E.g. Button1_Click. Click OK.
- A button will then be automatically inserted in the workbook. The caption text of the button is editable and can be edited by double-clicking on the button.
Step 4: Write VB code
- Select the button and click on the very first option from the left under the ‘Code’ sub-section of Developer tab i.e. ‘Visual Basic’.
- This will launch a new window of VBA project as shown in the picture below.
- 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 ‘File’ menu i.e. ‘View Microsoft Excel’ as shown below.
Things to Remember
- CHR VBA function can return printable as well as 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, Space, Esc are non-printable characters.
- CHR is a VBA function and cannot be used in Excel as it is. Its corresponding function in Excel is Application.WorksheetFunction.CHAR
This has been a guide to VBA CHR. Here we discuss how to get a character of the ASCII code in Excel using VBA CHR function along with examples and codes. You can learn more about VBA from the following articles –