Page 140 - Excel Progamming Weekend Crash Course

h540629 ch09.qxd 9/2/03 9:34 AM Page 115

Session 9 — Working with Text 115

Displaying Unicode Characters

While Windows supports Unicode, this does not mean that you can automati-
cally display any Unicode character on-screen (or, for that matter, print it).
The operating system’s regional settings, and specifically which languages are
installed, determine which characters are available.

The Asc, AscB, and AscW Functions
You learned earlier that VBA uses numbers internally to represent letters, numbers, and
other characters. Theses three functions return the numerical code corresponding to a char-
acter. The syntax is:
Asc(str)
AscB(str)
AscW(str)

The str argument is a string containing the character of interest. If str contains more
than one character, the first one is used. If str is an empty string, an error occurs. The
three functions differ as follows:

Asc. Returns the ASCII code for the character.
AscB. Returns the first byte of the character’s Unicode code.
AscW. Returns the character’s Unicode code.

The Asc function is useful when you need to determine what characters are in a string.
For example, you might want to verify that text entered by the user contains only letters —
no numbers or punctuation marks. Sure, you could go through the text a character at a time
and compare each one with “a,” “b”, and on through “z,” but that’s rather clunky. Using
Asc, you can take advantage of the fact that the codes for the letters are 97 to 122 (for low-
ercase) and 65 to 90 (uppercase), greatly simplifying the comparisons you need to make.
The function shown in Listing 9-1 provides an example. This function verifies that a
string is a number — that is, contains only the characters 0, 1, and so on, through 9. It
returns True if the passed string is all numbers; otherwise, it returns False. The function
makes use that the ASCII codes for the digits 0 through 9 are 48 through 57 respectively.
The function works by going through the string a character at a time and seeing if the
ASCII value is within that range. If only a single character fails the test, the string is not a
number, and the function returns False. The code makes use of VBA’s Len and Mid functions.
You may be able to figure out what they do on your own; however, they are covered later in
this session.