VBA Asc

The Asc function in VBA returns an integer value representing a character code corresponding to the function's first character of a supplied string (string provided as argument/parameter). It can be used or given in a macro code generally entered via Visual Basic Editor.

We can use the Excel environment in which the macro runs in the Visual Basic Editor (VBE) 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. We can perform this 8-bit coding with ANSI (American National Standards Institute), which has 256 characters defined. ANSI is also called extended ASCII.

The general syntax for the ASC function is as follows:

The ASC formula syntax has the following argument:

String: Required. It represents the text string whose first character's corresponding character code is desired and returned.

If the string supplied has only one character, then the function returns the numeric character code for that character itself.

Let us see below how we can use the Asc in Excel VBA.

Example #1

We have an Excel file containing some strings. We wish to use the Asc function with these strings. But, first, 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:

First, go to "Developer," and then click on "Macros."

Now, create a macro name: Under "Macro Name," write the name of the macro desired to be created and select PERSONAL.XLSB in the dropdown of 'Macros In.' We can store the macros in a personal workbook, a hidden workbook that opens in the background whenever we start Excel. Selecting PERSONAL.XLSB would save the macros in the Personal workbook, thus making the macro always available since the personal workbook is not system/file specific.

Click on "Create."

It would open a window with a VBA subprocedure in Visual Basic Editor as follows:

Now, define the variable Result1

Code:

Sub Code() Dim Result1 End Sub

Now, assign the variable Result1 with the formula to return the character code of the string:

Code:

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:

Code:

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. It implies that the character code for "R" is 82.

Now, 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.

Instead of Raj, we will write Karan to get its character code in the code.

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. It implies that the character code for 'K' is 75.

Now, let us see how the result changes for the remaining strings:

Code:

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 these sub-procedures one by one, the following character codes return in the message box (one at a time, respectively). It is the list of corresponding values returned for the above strings.

So, as the above screenshot, we can see that the ASC function returns the character code as 65 for the string: "Arun" and the string: "A" 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 returns for both as the initial or first character of the string: "Arun" is also "A."

Example #2

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.

The 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 a parameter or argument to the ASC function is blank/empty, the function returns a run-time error.

• 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.