VBA ASC

Excel VBA ASC

ASCII which is American Standard Code for Information Interchange has introduced a proper structure for each number and alphabets. And these ASC Codes are frequently used in many programming languages. In VBA also, we have these ASC Codes applicable in many ways. These codes are created for each programming languages so that computer can understand which is being fed to it. ASCII is the numerical representation of every word and alphabet.

In ASC, we have different types of characters printing characters (which can be seen) to non-printing characters (which cannot be seen such as any process or spaces). For example, we someone says “Good Morning” to you then it is being communicated with sound waves. Now if same we want to feed this into a computer or send a message to anyone then the computer will convert the text “Good Morning” into ASC Codes and then the message will get transferred to that person. But again that person will receive the message in a text, not in some codes.

Formula For ASC Function in Excel VBA

Let us look below the formula for ASC function in VBA.

How to Use the ASC Function in Excel VBA?

Below are the different examples to use ASC function in Excel using VBA code.

Excel VBA ASC – Example #1

We can get the table of ASC from the ASCII website. We have already shown that table for reference. Below is the table which has Dec, Hex, HTML ASC codes for special non-printing characters and alphabets from A to Z.

And moreover, we have ASC codes for special characters and some mathematical signs as well as shown below starting from 128 to 255.

In total, we have 255 ASC codes which can be used in VBA. Now we will implement these codes in VBA also.

• Go to VBA Insert menu option and open a Module as shown below.

• Now in the opened module write the subcategory of VBA ASC function as shown below.

Code:

Sub VBA_ASC()

End Sub

• After that define a variable DIM with any name, let’s say it is “A” and assign it Integers. Although we will be giving text and numbers as an input to variable A but using Integer because VBA ASC function considers integers as an output in which input will be as a string. We will see this further.

Code:

Sub VBA_ASC()

Dim A As Integer

End Sub

• Now assign variable A to ASC function as shown below.

• Now select any word or alphabet as per need inside the brackets as shown below.

Code:

Sub VBA_ASC()

Dim A As Integer

A = Asc("B")

End Sub

• Now to see the ASC code value it is best to use the message box. Assign a MsgBox to variable A as shown below.

Code:

Sub VBA_ASC()

Dim A As Integer

A = Asc("B")

MsgBox A

End Sub

• Once done, then click on the play button which is located below the menu bar to run the code.

We will see, the ASC code of alphabet “B” is coming as 66. This is the same code mentioned in ACS Table which we have been at the starting of example-1.

Excel VBA ASC – Example #2

In this example, we will see, how a complete word will be encoded with the help of VBA ASC. For this we will use the same code structure which we have seen in example-1, only keeping only brackets of ASC function blank as shown below.

Now let’s insert any word into those brackets. Let’s consider that word “Excel”

Code:

Sub VBA_ASC1()

Dim A As Integer

A = Asc("Excel")

MsgBox A

End Sub

Now compile the code and run it after that. We will see ASC function has generated the ASC code as 69 as shown below.

And if we match this ASC Code with the list which we saw in example-1, then we will see that for capital E which is at the start of Excel is marked as 69.

Excel VBA ASC – Example #3

In this example, we will see how a lower case letter of the same word can give ASC code. For this we will consider the same word which we have seen above example “Excel” but we will keep “e” in lower case. Consider the same body of code which we have used above.

Now in that bracket write “excel” in lower case.

Code:

Sub VBA_ASC2()

Dim A As Integer

A = Asc("excel")

MsgBox A

End Sub

Again, compile and run the code. We will see, for lower case “e” the ASC Code is coming as 101 as shown below.

If we match this with the list shown in example-1, we will see, for each lower case alphabet, there is separate ASC code given which is different than that of Upper case letters.

Excel VBA ASC – Example #4

In this example, we will see how a special character can be used to get the ASC code out of it. Again, we will continue using the same code format that we have seen in example-1.

Now insert any special character which is allowed to be used in VBA coding. We mostly use an ampersand (&) in VBA in different forms. So we will continue with the same here as well.

Code:

Sub VBA_ASC3()

Dim A As Integer

A = Asc("&")

MsgBox A

End Sub

Again compile the code and run. We will see the message box containing ASC Code as 38 as shown below.

And if we match this code with the table shown in example-1 we will see, for the ampersand (&) ASC code is defined as 38.

Pros of Excel VBA ASC

• ASC Code is very revolutionary when it got introduced, as it is very helpful in standardizing the transmission of computer codes and messages.
• For each letter respective of upper and lower cases, ASCII has given separate ASC code so that word in upper case and lower case will get encode as it is fed.

Things to Remember

• VBA doesn’t consider ASC codes of complex mathematical symbols which are the Extended ASCII Codes. An only simple character can be used to get it Dec ASC Code.
• Upper and lower case alphabets have different ASC codes.
• VBA generates only Dec of ASC codes.
• Once done, must save the file in Macro enabled excel format. So that VBA code can be retained.

Recommended Articles

This is a guide to VBA ASC Function. Here we discuss how to use ASC function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Match
2. VBA StrComp
3. VBA Web Scraping
4. VBA XML