Excel VBA Asc Function
The Asc function in VBA is used to return an integer value that represents a character code corresponding to the first character of a supplied string (string provided as argument/parameter) to the function. It can be used or given in a macro code that is generally entered via Visual Basic Editor.
The Excel environment in which the macro runs in the Visual Basic Editor (VBE) that can be used to edit and debug the macro codes. It holds the macro code and links it to the Excel workbook.
The integer value returned by the Asc function is the corresponding ASCII character code in VBA. ASCII (American Standard Code for Information Interchange) is a 7-bit character encoding that has a total of 128 characters defined in it including Latin alphabets, ten Arabic numerals, some punctuation marks, and control characters. It does not include diacritical characters as they require a minimum space of 8-bit for encoding. This 8-bit coding is done with ANSI (American National Standards Institute) that has a total of 256 characters defined. ANSI is also called as extended ASCII.
Syntax of VBA Asc Function
General Syntax for ASC function is as follows:
The ASC formula syntax has the following argument:
String: Required, represents the text string whose first character’s corresponding character code is desired and is to be returned.
If the string supplied has only one character, then the function obviously returns the numeric character code for that character itself.
Examples of Excel VBA Asc
Let us see below how the Asc can be used in Excel VBA.
Let us say we have an Excel file containing some strings, and we wish to use the Asc function with these strings. Let us see the strings contained in the file:
We use the Asc function in VBA in a macro that can be written in the Visual Basic Editor and can be accessed as follows:
Go to Developer, and then click on Macros:
Now create a macro name: Under ‘Macro Name’ write a name of the macro that is desired to be created, and select PERSONAL.XLSB in the dropdown of ‘Macros In’. The macros can be stored in a personal workbook that is a hidden workbook which opens in the background whenever Excel is started. Selecting PERSONAL.XLSB would save the macros in Personal workbook thus making makes the macro always available since the Personal Workbook is not system/file specific.
Click on ‘Create’.
This would open a window with a VBA sub procedure in Visual Basic Editor as follows:
Now, define the variable Result1
Sub Code() Dim Result1 End Sub
Now assign the variable Result1 with the formula to return character code of the string:
Sub Code() Dim Result1 Result1 = Asc("Raj") End Sub
Now the resultant value of Result1 can be displayed and returned using a VBA message box (MsgBox) as follows:
Sub Code() Dim Result1 Result1 = Asc("Raj") MsgBox Result1 End Sub
Now when we run this code manually by clicking on ‘Run’ at the top of the window or by pressing F5, we get the character code of the first letter of the string: “Raj” displayed in a Message Box as follows:
So, we can see in the above screenshot that on running the macro, ‘82’ is returned in a message box. This implies that the character code for ‘R’ is 82.
Now, let us say in the above example we wish to find the character code for the string: “Karan”. To do this, we follow the same steps as above.
In the code instead Raj we will write Karan to get its character code.
Sub String2() Dim Result2 Result2 = Asc("Karan") MsgBox Result2 End Sub
Now we run this code manually or by pressing F5, and we get the character code of the first letter of the string: “Karan” displayed in a Message Box as follows:
So, we can see in the above screenshot that on running the macro, ‘75’ is returned in a message box. This implies that a character code for ‘K’ is 75.
Now let us see how the result changes for the remaining strings:
Sub String3() Dim Result2 Result2 = Asc("Heena") MsgBox Result2 End Sub
Sub String4() Dim Result2 Result2 = Asc("Arun") MsgBox Result2 End Sub
Sub String5() Dim Result2 Result2 = Asc("A") MsgBox Result2 End Sub
Sub String6() Dim Result2 Result2 = Asc("a") MsgBox Result2 End Sub
On running this sub-procedures one-by-one, the following character codes are returned in the message box (one at a time respectively). This is the list of corresponding values returned for the above strings.
So, as the above screenshot illustrates, we can see that the ASC function returns the character code as 65 for the string: “Arun”, and also for the string: “A”. This is because the character code returned is the VBA ASCII or equivalent character code for the first letter of the string in case the string is more than one character. Hence, 65 is returned for both as the initial or first character of the string: “Arun” is also ‘A’.
If the string supplied as parameter/argument to the VBA ASC function is an empty/blank string (or a string with no characters), then the function returns a run-time error.
Following code explains the same.
Sub blank() Dim result result = Asc("") MsgBox (result) End Sub
When we run this code, we get the Run-time error as follows.
So, we can see in the above screenshot that when the string supplied as parameter or argument to the ASC function is blank/empty, then the function returns a run-time error.
Things to Remember
- The string supplied to the VBA ASC function can be any valid string expression.
- The Asc function is case-sensitive.
- The integer value returned by the Asc function is in the range of 0-255.
- The ASCII codes in VBA for A-Z are 65-90, and for a-z are 97-122.
- The name given to a macro or sub-procedure cannot be the name of some default function in VBA. If named so, then the sub-procedure will show an error.
This has been a guide to VBA Asc. Here we discuss examples of Asc function in VBA to return ASCII Character codes along with examples and downloadable excel templates. Below you can find some useful excel VBA articles –