5.4 Working with Numbers and Character Strings
5.4 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.
| Tip | If you wish to use the euro sign as the default currency symbol, then you must change the country setup in the control panel. Then Format and FormatCurrency will automatically use ‚ instead of the previously set currency symbol. |
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 character string) and the variant AscW (for the Unicode) two different values.
euro=" " ?Asc(euro), AscW(euro) 128 8364 | Tip | More information on the subject of the euro and Excel can be found later in this chapter. There the euro add-in with the function EuroConvert is described. Furthermore, you will find there several macros to help you in the conversion of your tables to the euro. |
Pattern-Matching
The operator Like 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, "Polsky" Like "*l*y" returns True .
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 ?"a"<"b" , then VBA answers True . In general, Option Compare 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 comp :
Option Compare Text Sub comp() Stop End Sub
Character Strings: Input 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. Debug.Print enables output to the immediate window, but this output is invisible unless the immediate 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. MsgBox 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 MsgBox 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") | Tip | In addition to MsgBox and InputBox there exists the Excel-specific variant Application.InputBox , which enables the input of formulas and cell references. More on this topic can be found in Chapter 7. |
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 Character Strings into Numbers
The simplest way to convert a character string into a numeric format is to use one of the functions CInt , CLng , CSng , CDbl , and CCur . These functions take as argument character strings as well as numbers 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 TOOLSOPTIONSMODULE 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 Variant variable. Val is much less allergic to invalid input. For instance, Val("abc") simply returns 0. Val("1.2abc") returns 1.2, and Val("1,2") returns 1 (since the comma is interpreted as just another text symbol). Val cannot accept numbers, dates, 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 function can be used, for example, in order to print vertical double quotation marks (") via Chr(34) .
Format Functions
Much more flexible than Str is the function Format , which transforms a format character string. Two examples will serve to indicate how this function can be used: Format(1/3, "Scientific") returns 3,33E-01, while Format(1234.5678, "#,##0.##") returns 1,234.57. Here # serves as a place holder for an optional digit, 0 as a placeholder for digits or 0 (that is, making the placement of leading zeros possible: Format(1234.5678, "000,##0.##") yields 001,234.57), the period as a placeholder for the decimal point, and the comma for the thousands indicator.
| Tip | The enormous number of predefined formats and placeholder symbols (for dates and times as well) for defining various formats is documented in the on-line help under Format . (Click on FORMAT FUNCTION EXAMPLE.) |
New in Excel 2000 are the functions FormatNumber , FormatCurrency , and FormatPercent for formatting numbers, and FormatDateTime for formatting dates and times (see the next section). While these functions are 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) ' returns ,345,678.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 of a particular data type, or whether it can be converted into that type. The most important such functions are IsNumeric , IsDate , IsEmpty , and IsNull . Note 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) returns True , but so does IsNumeric("1") ! On the other hand, IsNumeric("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 ).
| ROUNDING | |
|---|---|
| CInt(v) | rounds to nearest even integer at 0.5 |
| CLng(v) | rounds to nearest even integer at 0.5 |
| Int(f) | always rounds down |
| Fix(f) | deletes the digits to the right of the decimal point |
| WorksheetFunction.Round(f, n) | rounds up at 0.5, to n digits to right of decimal point |
| WorksheetFunction.RoundDown(f, n) | always rounds down ( n decimal digits) |
| WorksheetFunction.RoundUp(f, n) | always rounds up ( n decimal digits) |
| WorksheetFunction.Even(f) | rounds to the next even number, up or down depending on the sign |
| WorksheetFunction.Odd(f) | rounds to the next odd number, up or down depending on the sign |
| WorksheetFunction.Ceiling(f1, f2) | rounds up to a multiple of f2 |
| WorksheetFunction.Floor(f1, f2) | rounds down to a multiple of f2 |
| OTHER NUMERIC FUNCTIONS | |
|---|---|
| Abs(f) | removes the sign |
| Sgn(f) | returns, according to sign ˆ’ 1, 0, 1 |
| Sqr(f) | square root |
| Sin(f), Cos(f), Tan(f) | trigonometric functions |
| Atn(f) | inverse tangent |
| Log(f), Exp(f) | logarithmic and exponential functions |
| Rnd | random number between 0 and 1 |
| Randomize | initializes the random number generator |
| CHARACTER STRINGS | |
|---|---|
| Left(s, n) | returns the first n characters |
| Right(s, n) | returns the last n characters |
| Mid(s, n) | returns all characters from the n th on |
| Mid(s, n1, n2) | returns n2 characters from the n1 th character |
| Mid(s1, n1, n2) = s2 | inserts s2 into s1 |
| Len(s) | returns the length of the character string |
| InStr(s1, s2) | searches for s2 in s1 ; result: position or 0 |
| InStr(n, s1, s2) | as above, but search begins with the n th character |
| InStr(n, s1, s2, 1) | as above, but case-insensitive |
| InStrRev(s1, s2 [, n]) | like InStr , but search from back to front |
| Split(s, "x") | splits s at the location of the character "x" ; returns array |
| Join(array, "x") | joins an array of character strings (with "x" inserted at the joins) |
| Filter(array, "x") | returns array of all character strings that contain "x" |
| Replace(s, "x", "y") | replaces all "x" in s with "y" |
| UCase(s) | changes all lowercase to uppercase |
| LCase(s) | changes all uppercase to lowercase |
| Trim(s) | deletes spaces at beginning and end |
| String(n, "x") | returns the character string "x" repeated n times |
| Space(n) | returns n spaces |
| Option Compare Text | then "a"="A" and "A"<" "<"B" |
| StrComp(s1, s2) | ˆ’ 1 if s1<s2 , 0 if s1=s2 , else +1 |
| StrComp(s1, s2, 0) | as above, but always binary comparison |
| StrComp(s1, s2, 1) | as above, but always country-specific comparison |
| MsgBox "text" | displays the text in a dialog box |
| MsgBox("text", buttons) | as above; enables a choice to be made |
| InputBox("text") | enables the input of a character string |
| TRANSFORMATION FUNCTIONS | |
|---|---|
| CInt(v) | returns an integer |
| CLng(v) | as above, but a long integer, thus within a greater range |
| CSng(v) | simple floating point number |
| CDbl(v) | double floating point number |
| CCur(v) | number in currency format |
| CBool(v) | Boolean value ( True/False ) |
| CDate(v) | date/time |
| CStr(v) | character string |
| Val(s) | returns the value of the character string |
| Str(v) | transforms a number into a character string |
| Format(v, s) | returns a character string, where the format instruction in s is applied |
| FormatNumber(v, n) | formats x as a number with n decimal places |
| FormatCurrency(v, n) | formats x as a currency value with n decimal places |
| FormatPercent(v, n) | formats x as a percentage with n decimal places |
| Asc(s) | returns the ANSI code of the first character |
| AscW(s) | returns the Unicode of the first character |
| Chr(n) | returns the character with code n (0 “255) |
| DETERMINING DATA TYPE | |
|---|---|
| IsNumeric(variable) | test whether conversion to a number is possible |
| IsDate(variable) | test whether conversion to a date or time is possible |
| IsArray(variable) | test whether not a variable, but a field |
| IsError(variable) | test whether error value |
| IsMissing(variable) | test whether optional parameter is missing |
| IsEmpty(variable) | test whether empty |
| IsNull(variable) | test whether uninitialized |
| IsObject(variable) | test whether reference to an object |
| VarType(variable) | numeric value given by the data type |
| TypeName(variable) | character string that describes the data or object type |
EAN: 2147483647
Pages: 134