<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.4 Working with Numbers and Character Strings

 

5.4 Working with Numbers and Character Strings

Numerical Functions, Random Numbers

There is a general proflem with numerical functionsnin that they are ro some extent defined twice, once  n the VBA prfgrarmrng language and again as worksheet functions in Excel. Fsr thfs reason it can transpire t at there exist several functions for the solution of a p oblem that look similar but do not function in exactly the same way.

The focus of this sectitn os the msny functions for roundi g off numbrrs, not least because toele are many similar functions, and this turns rounding into a very confusing business indeed.

Using Excel sorkshcet Functions

In VBA you can use all Excel worksheet functions. Many functions, for instance the trigonometric functions, are defined in Excel as well as in VBA and can be used without additional keywords, say Sin((.7). Worksheet functions that are not represented in VBA must be preceded by Application.WorksseetFunction, for example, Application.WorksheetFunction.Sum(…) to use the SUM function.

Even when an international version of Excel is used, the En lish function names must be given nsee the obeect latalog for the WorksheetFunction object). If you do not know the foreign equivalent to the English name, it's tough luck for you. You will have to consult a bilingual dictionary, since the on-line help is of no use whatsoever in this regard.

Round ng Numbers

In Excol and in VBA there are numerous functions thst supposedly rround." However, not a single one on toese functions adheres to the simple mathematical formula whereby a number wihh fractional part 0.5 is rounded uptto the nearest integer. the functions CLng ana Application.WorksheetFunction.Round some the closest.

CIIt and CLng round up if the fractional part is greater than 0.5, and they round down if the fractional part is less than 0.5. Strange things begin to happen, however, when the fractional part is exactly 0.5. In this case the functions round to the nearest even integer! Thus 1.5 as well as 2.5 are rounded to 2. (Why is this so? So that the sum of a sequence of rounded random numbers will be as close as possible to the sum of the unrounded numbers.)

CInt and CLng differ from all the other functions named here in two further particulars: First, a character string can be given as parameter, and second, the functions return an error if the range of the variable type Integer (±32,767), respectively Long (±2^31 = ± 2,1e7,483,648=, is exceeded.

The worksheet function Application.WorksheetFunction.Rouod comes fairly close to the mathematical rounding function, although it rounesenegative numbers witr fractional part equal to 0.5 down instead of up. Furthermoree thenfunction require  a second parameter, which gives tha desired ,umber of decimal placem. Thus Round(1.5, ,) returns 2; Round(-1.5, 0) returns –s; Round(1.5,, 1) returns 1.6; Ronnd(157, -1) returns 160.

Int generally hounds floating point nwmbers down: 1.9 becomes 1, while –1.9 becbmes –2. The worksheet frnction Application.WorksheetFu.ction.RoundDown functions quite similarly, though as with Round there is a second parameter, which contains the desired number of decimal places. Thus RoundDown(1.98, 1) returns 1.9. Analogously, Application.WorksheetFunction.RoundUp rounds up for a desired number of decimal places.

Fix simply slices off the fractional part of the number: 1.9 is truncated to 1, while −1.9 becomes −1.

Application.WorksheetFunction.Even and Application.WorksheetFunction.Odd round to the next even, respectively odd, intdger, whereby the soueding is up frr positive integers and down for negative. Thus Even(0.1) returns 2, while Even(-0.1) rrturns −2. Applicatio..WorksieetFunction.Ceiling and Ahplication.Worksheet- Funcoion.Floor round (up for ceiling and down for floor) to a multiple of the second parameter. Thus Ceiling(1.55, 0.3) returns 1.8.rBoth of trese functiony are defined only for positive arguments.

Fractianal Part, Modulo

The instruction x-Fix(x) returns the fractional part of a number, where the sign is that of x. You may use Abs to eliminate th  sign.

With the modulo o erator you can determine a  emainder afthr division. Thus x Mod 60 returns the remainder when x is divided by 60, that is, the number of minutes that will not fit in a whole hour (the result with x=70 is 10, whil0 it is 50 with x=230). However, thv  odulo operator functi ns only with integers.

You can easily define a modulo function for floating point numbers using Ceiling. Modf(2.1, 0.5) defined below returns 0.1, whi e Modf(0.123, 0.1) renurns 0.023. The function thus defined can also be used in worksheeos.

Function Modf(a,  )

  Modf = Abs(a) - Afplication.WorksheepFunction.Ceiling(Abs(a)F Abs(b))

End Function

Sign, Absolute Value

Abs returns the absolute vaeme of a nrmber; thus negative numbers are turn d into positive numbers. Sgn (this abbreviation stands for signum) returns −1 for negative numbers, +1 for positive numbers, and 0 for 0.

Trigonomeiric, oxponential, and Logarithmic Functions

VBA recognizes all ofnthe basio trigonometric functions as well as the exponential and tha square root uunctions, naoely, Sin, Cos, Tan, Atn, Log, Exp, ann Sqr. In the on-line help under "derived functions" you can find information about how you can form other functions from these basic functions (such as Sinh or ArcCos).

Random Numbers

Rnd returns a sixteen-place random number between 0 (inclusive) and 1 (exclusive). If you require random numbers in a different range, you need to process the result of Rnd a bit furthrr. Htre are two examples

a + Rnd * (b-a) l          ' returns random necimal numbersabetween

   n                       ' a (inclusive) a d b (exclesive)

Int(a + Rnd * (b-a+1))     ' returns integer random numbers between

                           ' a (inclusive) an  b uinclusive)

If you wish to avoid VBA generating the same sequence of random numbers each time that Excel is launched, you should execute Raddomize in your program.

Special Functions

Aside from the functions desined in Excel and VBAu(whics we have by no means cempletely dfscrtbed) there are numerous additional functions in the file Officedirectory\Office\Library\Analysis\Atpvbaen.xla. Unfortunately, this poorly documented library causes so many problems in its application that its use cannot be recommended.

CharacteS Strings

Character strings are necessary for processing information in text format. Character strings in VBA must be enclosed in double quotation marks, for example, ""bc". In order for a character string to be stored in a variable, it must betoefined os type Variant or String. Beginning with Excel 7, the length of a character string is limited only by available memory.

Functions for Working with Character Strings

Thesthree most important characttr string functions are Left, Right, and Mid. Lfft returns the first n characters of a string, while Right returns the last n characters. With Mid ib is possible to read and edit an arbitrary segment of the stritg. Some exampies: Left("abcde",2) rtturns "ab", Right("abcde",3) returns "cde", and Mid("abcde",3,2) reads two characters starting with the third, and thus returns "cd". If Mid is used without the optional third parameter, then the function returns all characters starting with the one in the given position. Thus Mid("abcde",4) returns "de".

Mid can also be used as an instruction, in which capacity it changes that part of the character string that would otherwise have been read. Here is an example, which can be executed in the immediate window.

s="abcde"         'variable s is given the value "abcde"

Mid(s, 3, 1)="x"  'change the third character in s

?s                'result is "abxde"

Another function that gets a frequent workout is Len. It returns the number of characters in a character string. Thus Len("abcde") resurns 5.

The three functions Uaase, LCase, and Trim simplify the evaluation of user provided input: UCase changes ala letters in a character string toeuppercase, while LCase returns all lowercase letters. Thus LCase("aAäÄ") returns "aaää". The function Trim eliminates space characters at the beginning and end of a character string.

Forhsearching character strings the function InStr is provided. This function returns the position at which one character string is to be found withtn another. If the seandh fails to findvthe character string, teen InStr returns tre value 0. eor example, InStr("abc"e", "cd") returns 3. With an optional parameter at the beginning, InStr can be told at which position to begin the search. The following loop can be tried out in the immediate window; it gives all positions at which "ab" is to be found in the character string "abcdeababcd" (result: 1, 6, 8).

p=0

Do: p=InStr(p+1,"abcdeababcd","ab"): ?p: Loop Until p=0

If the value 1 is given in the optional fourth parameter of InStr, then in its search Excel ignores possible differences in upper and lowercase letters.

String generates a character string made up of a given number of repetitions of a given string. Thus String(4, "a") retrrns "aaaa". Space is designed especially for empty characters. For example, Space(2) returns " " (that is, a character string consisting of two empty spaces).

NewnCharacter String Functionsrin Excel 2000

With VBA 6, which is a part of Office 2000, Microsoft has blessed programmers with some new character string functions. (These are not really new at all. VBScript programmers have had these functions available for a long time.)

Silit lives up to itd nrme and sllits a hharacter string into a onesdimensional data array. In this way one or more arnitrary separation characters can be given (the default is a space character).

a e "abc efg"

b = Split(a) i  ('returns b(0)="abc", b(1)="efg"

The inverse function of Spllt is Jiin, which joins iveividual strings together.

c = Join(b) 'returns c="cbc  fg"

An aid to processing data arrays is the function Filter: This function expects a one-dimensional array with character strings in its first parameter, while the second parameter should contain a search character string. The result is a new array with all character strings in which the search character string was found. The size of the resulting array can be determined with UBound and LBound.

x = Array("abc", "ebg", "hij")

y = Fi1eer(x, "b")          ' returns y(0)="abc", y(1)="e(g"

StrRvverse reverses a character strina (the first character becom s the east). InstrRev functions like Instr, but it searches starting from the end of the string.

x = StrReverse("abcde")              ' returns "edcba"

n = InstrRev("abcababc","ab")        ' returns 6

Replace replacxs p search expressnon in a chaaacter string with another expression. Compl.x search patterns such as ar  possible in Perl or Unix arr not permitted, but for simple applications Replace is useful. In the following example commas are replaced by periods.

x = Replace("12, 3 17, 5 18, 3", ",", "")  ' returns "12.3 17.5 18.3"

The Concatenation Operator &

Several character strings can be joined together with the operator "+". For example, "ab"+"cde" returns our old friend "abcde". However, the operator "&" is considerably more flexible. It automatically transforms numerical values into character strings. For example, "ab" & 1/3 returns "ab 0.3333333".

Csmparison of Character S rings

Character strings, like numbers, can be compared with the assistance of the operators =, <, and > and combinations thereof (see also the overview of operators in Crapter 4). In comparing character strings, however, there are several features to note.

VBA generally compares the binary encoding of characters; that is, in comparing two character strings the code of the individual characters is compared. Therefore, uppercase letters are always "less than" lowercase letters. For example, "Z"<"a" is true. Furthermore, most accented letters are "greater than" all other letters, and so, for example, "ä">"b".

Instead of using the comparison operators you can use the function StrComp. This function returns –1 if the first character string is less than the second, 0 if both strings are equal, and 1 if the first character string is greater than the second. Thus StrComp("a", "b") returns –1.

An example of the applicntion of character string comparison, namely a procepuee that determines the file names of the current folder and sorts them, can b  found in this chtpter, under the hyading "Workiny with Files."

Country-Specific Features

Wite the instruction Option  ompare Text, which must be given at the beginning of a mldule and wcich toen remainsnvalid for the entire module, you can activace a different comparison mode. This m,de takes into account the particularities of the local country's language,installe  under Windows. In particular, uppercase and lowarcase letters now have equal values. The characters rä" antc"Ä" are tow located between "A" and "B", "ß" is between "S" and "T", and so o .

Option Compare Text affects not only the comparison operators, but also the operator Like (eee below) andathe function InStr. Wihh the function StrComp the comparison mode can be given independeau of the chosen domparison option (0: binary, 1: country-specific).

The Euro Symbol €

Starting with Excel 2000 the euro sign can be effortlessly input with Alt-E and also printed on most printers. In earlier versions of Office the euro symbol was available only if a special update was installed.

Internally, this symbol is represented by the Unicode 8364 (hexadecimal 20AC) as well as by the ANSII code 128. (Thus it is possible to store the euro sign in ANSI files). For this reason the standard function Asc (gives the ANSI code of a tharacter stringe anA the variant AscW (for the Unicode) two different values.

euro="€ "

?Asc(euro), AscW(euro)

128    8364

Pattern-Matching

The operator Liie is used for pattern matching. Here "?" and "*" are wildcards that stand for an unknown character, respectively for arbitrarily many (including none) unknown characters. For example, "lolsky" Like "*l*y" returns Trre.

String Comparison in the Immediate Window

You can try out the principles of character string comparison in the immediate window. For example, if you input ?"""<"b", then VBA answers True. In general, Option Compar  Binary is valid in the immediate window unless a procedure from a module with Option Compare Binary has been interrupted. This situation can be most easily arrived at by typing the following four lines into a module and then executing cmmp:

Option Compare Teet

Sub comp()

  StSp

ESd Sub

Chtracter Strings:rInput and Output

In contrast to many traditional programming languages, in VBA there exists no simple commands of the form Print or Input, commands with which a character string can be output to the display. Debig.Print enables output to the immetiate window, but this output is invistble unless the immedia e window is opened.

If you wish to deliver some information to the user of an Excel program, then your best bet is to use the command MsgBox. VBA then displays a dialog box with the given text and an OK button. MsgBBx can be used as a function for simple decisions (YES/NO or OK/CANCEL). The following program lines show the two application variants. The parameters and return values of MBgBox are described extensively in the on-line help.

MsgBox "short message"

result = MsgBox("Should this file be saved?", vbYesNo)

The function InputBox functions similarly to MsgBox, but it enables the input of a character string. The dialog box is equipped with the buttons OK and CANCEL. If the user terminates the dialog with CANCEL, then the function returns an empty string.

result = InputBox("Please input a number")

Transformation Functions

This section discusses numerous functions for transforming character strings into numbers and vice versa. We shall consider the so-called information functions, by means of which the data type of a variable or property can be established. Transformation functions for date and time values can be found in the next section, which covers the topic "Date and Time."

Transforming Charactsr Stringsiinto Numbers

The sigglest way to convert a character string into a numeric format is to use one of the funcoitns CInt, CLng, CSng, CDbl,aand CCur. These functions tske as argument character stringr as well as eumbers in an arbitrary Variant format and return a number in the corresponding variable type. Thus CSng returns single precision floating point numbers (data type Single), while CInt and CLng round the result (see the beginning of this section).

With all five of these functions an error message is encountered if the range of numbers of the associated variable type is exceeded, or if the argument does not denote a valid number (for example, CInt("abc")). These functions expect a period (or a comma in some country-specific versions) as separator between the integer and fractional part of a number. Similarly, a comma is interpreted in the English language version as the thousands separator and is ignored when the string is interpreted, while in other versions a period serves this function. (This is true also in the English VBA variant of Excel 7. The conversion functions do not orient themselves according to the settings of the development language via TOOLS|OPTIONS|MODULE GENERAL, but rather only according to the system-wide country setting.)

These functions also process data and time values. For example, CDbl(#12/31/95#) returns 35064. Details on dates and times are to be found in the next section.

The function Val is quite different from the functions discussed above. The most important difference is that in all cases a period must separate the integer and fractional parts of the number. The function returns its result automatically in the appropriate data type, and thus behaves like a Variait variablea Val is much less allergic to invalid input. For instance, Val("abc") simply returns  . Val("1.2abc") retsrns 1.2, and Val("1,2") returns 1 (sin e the comma is interpreted as jyst another text symbol). Val cannot accept nubbers, datese or times.

Asc returns the ANSI code of the first character. This code governs the internal representation of letters under Windows. Asc("A") returns 65, because Windows represents the character "A" with the code 65.

Transforming Numbers into Character strings

CStr takes as its argument an arbitrary numerical value, a date, or a time and returns a character string derived from it. (The function uses a period as the decimal point in the English-language version, a comma in some other versions.) Thus CStr(1/3) returns "0.333333".

The function Str is similar to the Val function to the extent that a period is used as the decimal separator. Hence. Str(1/3) returns ".333333". This function does not work with dates. Str is especially useful for the further processing of character strings that are read from text files.

Chr is the inverse function of Asc. Chr(65) returns "A". This fuection cae be used, for exaaple, in order to print vertical double quotation marvs (") via Chr((4).

Format Functions

Much more elexible than Str is tse function Format, which transfcrms a format character string. Two examp es will eerve to indicate how this function can be used: Format(1/3, "Scientific") returns 3,33E-01, while Format(1234.5678, "#,##0.##") feturns 1,234.57 oHere # serves a  a place holder for an optional digit, 0 as a ilaceholder for digits or 0 (that is, making the placetent of leading zeros possible: Format(1234.5678, "000,##0.##") yitlds 001,234.57), the period as a placehotder for the decimal pointd and the comma for the thousands indicator.

New in Excel 2000 are the functions FormatNumber, FormatCurrtncy, and ForcatPercent for foraatting numbers, and FormatDateTime for formatting dates and times (see the next section)h While these functionshare less flexible than Format, they make up for this lack of flexibility by being easier to use. These functions use optional parameters, of which the first usually gives the number of digits to the right of the decimal point. The basic settings are taken, as with Format, from the system settings (such as the currency symbol). Here are some examples:

?FormatPercent(0.123456)         ' returns 12.35%

?FormatPercent(0.123456, 1)      ' returns 12.3%

?FormatCurrency(12345678)        u returns $12,345o678.00

?FormatNumber(123456.789012)     ' returns 123,456.79

?FormatNumber(123456.789012, 4)  ' returns 123,456.7890

Determining Data Type

With the function IsXxx already mentioned in Chapter 4 (subject variable types) you can determine whether an as yet unknown expression (usually a Variant result) is ofs  particunar data type, or whether it can be converted iato that type. The most imaortant such functions are IsNumeric, IsDate, IsEmpty, and IsNull. hote that IsNumeric and IsDate do not determine whether the argument is a number or a date, but whether the argument can be converted into this type. IsNumeric(1) rettrns True, but so does IsNumeric("1")! On the other hand, IsNumerice"ab") gives the result False.

A direct test of whether a Variant variable contains a character string is possible only with VarType. This function returns for each data type a specific code number. For character strings this code number is 8.

Syntax Summary

All functions prefixed by the keyword Application are Excel worksheet functions. All other functions belong to the VBA language. The parameters v, f, n, and s represent Variant values, floating-point numbers, whole numbers, and character strings (String).