Excel VBA CHR Function
VBA CHR is a built-in function 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.
Given below is the 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 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.
Step 1: In the 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 CHR function.
Step 2: Follow the steps shown in the next section to create a Button in the Excel workbook (please scroll the article) and change its caption to ‘Click here.’
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.
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 codeVBA Excel CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. 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 C2.
Step 5: Click on the button ‘Click here’ to print the result in cell C2.
Observe the result 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 the 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 excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. 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 in ExcelDeveloper Mode In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.
For any VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions. 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 an “Excel Macro-Enabled Workbook.”
Step 3: Insert a form control in a workbookForm Control In A WorkbookExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.
- Click on the Developer tab. And in the Controls sub-section, click on the ‘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 the Developer tab, i.e., ‘Visual Basic.’
- This will launch a new window of the 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 the ‘File’ menu, i.e., ‘View Microsoft Excel’ as shown below.
Things to Remember
- CHR 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 the VBA CHR function along with examples and codes. You can learn more about VBA from the following articles –