Working with Numbers and Character Strings

Working with Numbers and Character Strings

Numerical Functions, Random Numbers

There is a general problem with numerical functions in that they are to some extent defined twice, once in the VBA programming language and again as worksheet functions in Excel. For this reason it can transpire that there exist several functions for the solution of a problem that look similar but do not function in exactly the same way.

The focus of this section is the many functions for rounding off numbers, not least because there are many similar functions, and this turns rounding into a very confusing business indeed.

Using Excel Worksheet 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(0.7). Worksheet functions that are not represented in VBA must be preceded by Application.WorksheetFunction, for example, Application.WorksheetFunction.Sum(…) to use the SUM function.

Even when an international version of Excel is used, the English function names must be given (see the object catalog 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.

Tip / In Excel 5/7, worksheet functions were invoked directly with Application.Name(), that is, without the property WorksheetFunction, first introduced in Excel 97, which refers to the like-named object with a list of all worksheet functions. The greatest advance in WorksheetFunctions consists in the fact that now more worksheet functions than previously can be used in VBA.
The shorthand Application.Name() is still permitted for reasons of compatibility, but the functions are not shown in the object catalog. In new code you should use WorksheetFunction to avoid potential compatibility problems in future versions of Excel.

Rounding Numbers

In Excel and in VBA there are numerous functions that supposedly "round." However, not a single one of these functions adheres to the simple mathematical formula whereby a number with fractional part 0.5 is rounded up to the nearest integer. The functions CLng and Application.WorksheetFunction.Round come the closest.

CInt 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,147,483,648), is exceeded.

The worksheet function Application.WorksheetFunction.Round comes fairly close to the mathematical rounding function, although it rounds negative numbers with fractional part equal to 0.5 down instead of up. Furthermore, the function requires a second parameter, which gives the desired number of decimal places. Thus Round(1.5, 0) returns 2; Round(-1.5, 0) returns –2; Round(1.57, 1) returns 1.6; Round(157, -1) returns 160.

Int generally rounds floating point numbers down: 1.9 becomes 1, while –1.9 becomes –2. The worksheet function Application.WorksheetFunction.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, integer, whereby the rounding is up for positive integers and down for negative. Thus Even(0.1) returns 2, while Even(-0.1) returns −2. Application.WorksheetFunction.Ceiling and Application.Worksheet- Function.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. Both of these functions are defined only for positive arguments.

Fractional 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 the sign.

With the modulo operator you can determine a remainder after 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, while it is 50 with x=230). However, the modulo operator functions 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, while Modf(0.123, 0.1) returns 0.023. The function thus defined can also be used in worksheets.

Function Modf(a, b)

Modf = Abs(a) - Application.WorksheetFunction.Ceiling(Abs(a), Abs(b))

End Function

Sign, Absolute Value

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

Trigonometric, Exponential, and Logarithmic Functions

VBA recognizes all of the basic trigonometric functions as well as the exponential and the square root functions, namely, Sin, Cos, Tan, Atn, Log, Exp, and 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 further. Here are two examples

a + Rnd * (b-a) ' returns random decimal numbers between

' a (inclusive) and b (exclusive)

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

' a (inclusive) and b (inclusive)

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

Special Functions

Aside from the functions defined in Excel and VBA (which we have by no means completely described) 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.

Character Strings

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

Tip / In certain situations many functions and properties that return a character string can return the Variant special value Null. The further processing of this value using character string functions can lead to errors. Such errors can be avoided by making a preemptive test with the function IsNull.
Note / Character strings are stored internally in Unicode format, that is, with two bytes per character. Unicode format is a worldwide universal character string format that is able to encode the many characters of Asian languages. If you are using Office in a country that uses the Latin alphabet, you will not notice the presence of Unicode format. All character string functions work as they always did when character strings were still stored in ANSI format, with one byte per character.

Functions for Working with Character Strings

The three most important character string functions are Left, Right, and Mid. Left returns the first n characters of a string, while Right returns the last n characters. With Mid it is possible to read and edit an arbitrary segment of the string. Some examples: Left("abcde",2) returns "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") returns 5.

The three functions UCase, LCase, and Trim simplify the evaluation of user provided input: UCase changes all letters in a character string to uppercase, 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.

For searching character strings the function InStr is provided. This function returns the position at which one character string is to be found within another. If the search fails to find the character string, then InStr returns the value 0. For example, InStr("abcde", "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") returns "aaaa". Space is designed especially for empty characters. For example, Space(2) returns "" (that is, a character string consisting of two empty spaces).

New Character String Functions in 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.)

Split lives up to its name and splits a character string into a one-dimensional data array. In this way one or more arbitrary separation characters can be given (the default is a space character).

a = "abc efg"

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

Tip / vbCrLf is allowed as a separation character. This will split a multiline character string (for example, a text file) into individual lines.

The inverse function of Split is Join, which joins individual strings together.

c = Join(b) 'returns c="abc efg"

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 = Filter(x, "b") ' returns y(0)="abc", y(1)="ebg"

StrReverse reverses a character string (the first character becomes the last). 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 replaces a search expression in a character string with another expression. Complex search patterns such as are possible in Perl or Unix are 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".

Comparison of Character Strings

Character strings, like numbers, can be compared with the assistance of the operators =, <, and and combinations thereof (see also the overview of operators in Chapter 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 application of character string comparison, namely a procedure that determines the file names of the current folder and sorts them, can be found in this chapter, under the heading "Working with Files."

Country-Specific Features

With the instruction Option Compare Text, which must be given at the beginning of a module and which then remains valid for the entire module, you can activate a different comparison mode. This mode takes into account the particularities of the local country's language installed under Windows. In particular, uppercase and lowercase letters now have equal values. The characters "ä" and "Ä" are now located between "A" and "B", "ß" is between "S" and "T", and so on.

Option Compare Text affects not only the comparison operators, but also the operator Like (see below) and the function InStr. With the function StrComp the comparison mode can be given independent of the chosen comparison 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.