4.2 Procedural Programming

Procedural programming languages are distinguished by the fact that program code is written in small, separate units. These program units (procedures) can be called by one another, and parameters can be passed between them. Almost all of the currently popular programming languages, such as Pascal, C, and modern dialects of Basic, belong to the family of procedural programming languages (unlike, say, the languages LISP and Prolog).

This section describes the characteristic commands of a procedural language for controlling the execution of a program and for the division of the program into functions and procedures.

Procedures and Parameters

The two general syntax variants for procedures have already been revealed in the previous examples: Sub name () End Sub defines a subprogram (=macro, ="procedural macro"). Procedures of this type can carry out certain actions (for example, save the current workbook), but they do not return a value. For this reason there exists a second type of procedure, the function (=function macro, = user -defined function). Functions are introduced by the keyword Function name() and are terminated by End Function . Before a function is exited, that is, at the very latest in the last line, the value that the function is to return must be specified by giving a value to the function's name, name .

Note that procedures can call one another. If you wish to program a new command, the procedure defining the command can call other subprograms and functions. Procedures also serve to break up complex programming tasks into small, bite- size modules.

The easiest way to understand the concepts of subprogram and function is by means of an example. The subprogram macro calls the function func twice. In func it is tested whether the first parameter is larger than the second. If that is the case, the function calculates the difference between the two parameters and returns this value. If not, the function returns the product of the two values. After macro finishes execution the two values 12 (= 3*4) and 1 (= 7 ˆ’ 6) are shown in the immediate window.

 ' example file VBA-Concepts.xls, Module "Procedures" Sub  macro  ()   Dim result1 As Double, result2 As Double   result1 = func(3, 4)   result2 = func(7, 6)   Debug.Print result1, result2 End Sub Function  func  (a As Double, b As Double) As Double   If a > b Then func = a - b: Exit Function   func = a * b End Function

The function func can also be used in worksheets: Input arbitrary values into A1 and B1, and in C1 place the formula = func(A1, B1) . You will see that the function thus defined is executed without further ado. (Note that in worksheet formulas, parameters are separated by a comma.) In InsertFunction the function func is placed in the category User Defined (though without on-line help). Practical examples for user-defined functions can be found in Chapter 5.

Names of Procedures

The names of procedures are subject to the same restrictions as those for variables : The name must begin with a letter, may be at most 255 characters long, and should contain no special characters other than the underscore "_". The name may not be the same as that of a predefined keyword (see the VBA on-line help under "naming rules." Names of objects, properties, and methods are as a rule not considered keywords and can therefore also be used as the names of procedures. It is allowed, for example, to define a procedure with the name Add , even though many objects exist with a method of that name. VBA knows from the object's specification whether its Add method or your Add procedure is meant .

It is not allowed to give the same name to two procedures residing in the same module. Procedures with the same name in different modules are, however, allowed, but then when the procedure is called, the name of the module must be prefixed to it. (More on this below.)

Premature Exiting of Procedures

The example func above contains the keywords Exit Function . When this instruction is encountered , the function is exited prematurely, that is, before reaching End Function . Exit Function can be placed anywhere within the function. However, the return value of the function must have been previously determined by a reference to the function's name ( otherwise , the function will return, depending on its data type, 0, the empty character string, False , or Empty ). Subprograms can be exited at any time via the instruction Exit Sub . There is no return value to worry about for this type of procedure.

The Data Type of the Value Returned by the Function

Functions are distinguished from other subprograms by their return value. The data type of the return value should be defined as in the case of defining a variable. In the example above no data type was defined, and func therefore returns its result in the default data type Variant . The two lines below demonstrate the two different ways in which the function func can be defined to return a value of type Double .

 Function func(a, b) As Double Function func#(a, b)

The Parameter List

The rules that hold for the definition of the data type of a variable and a function hold, naturally, as well for the parameters of subprograms and functions. For reasons of efficiency and reliability, data types should be given for all the parameters of a procedure. If both parameters of the function func are declared as Double , then the two definition options look as follows :

 Function func(a As Double, b As Double) As Double Function func#(a#, b#)

It can be seen from this example that the use of such type declaration characters leads to clearer and shorter definitions.

Value and Return Parameters

Normally, parameters in VBA procedures are return parameters. This means that their content can be changed by the procedure and that this change affects the variable in the calling procedure. Let us consider an example to demonstrate this principle:

 Sub  array_macro1  ()   Dim a%, b%   a = 4: b = 6   array_macro2 a, b   Debug.Print a, b End Sub Sub  array_macro2  (x%, y%)   x = x * 2   y = y / 2 End Sub

After array_macro1 has finished executing, the values 8 and 3 have been output to the direct window. Thus array_macro2 has altered the values of the two variables a and b from array_macro1 . The parameters x and y in array_macro2 are called return parameters because the change in the variables is returned to its origin, the calling procedure. (In more sophisticated programming languages such parameter passing is called "by reference" or "by pointer," because not the data themselves are passed, but a pointer, or reference, to the memory location allocated to the variable in question.) A return value is then possible only if at the time the procedure is called, the actual name of a variable is given. In the macro call array_macro2 1, 2 no value can be returned (1 and 2 are constants), nor can it happen in the case of compound expressions like array_macro2 a+1, b/c .

If you wish to avoid the possibility that a procedure can change the variables sent to it, then you must append the keyword ByVal in the parameter list of the procedure's definition. These parameters then are value parameters and behave within the called procedure like independent variables. A change in a parameter within the called procedure has no effect on the variables outside of that procedure:

 Sub array_macro2(ByVal x%, ByVal y%)

Passing of Fields

Not only can single values be passed to a procedure, but fields as well. For this the parameter must be identified as a field in the parameter list (append an empty pair of parentheses). Fields are always return parameters, and the keyword ByVal is not allowed.

The variable type of a field that is given when a procedure is called must coincide with the variable type of the procedure's parameters. Thus it is not allowed to pass an Integer field to a procedure whose parameter is defined as a Variant field. (This is different from the case of normal parameters: You can send an Integer number to a procedure that expects a Variant value; it will be automatically converted.)

The example below shows a loop in which all of the elements of the field are output to the immediate window. In the case of multidimensional fields, in this loop first the first index is varied (that is, the sequence f(0, 0), f(1, 0), ; f(0, 1), f(1, 1), ; etc.). This behavior is not documented, and it is possible that in future versions of VBA this will change. The For loop will be discussed more fully below:

 Sub  array_macro3  (arr() As Variant)   Dim var As Variant   For Each var In arr()     Debug.Print var   Next var End Sub

Within a procedure you can use the functions LBound and UBound to determine the admissible index ranges within the different dimensions of a field. There is, however, no function that determines the number of dimensions of the passed field. You can determine the number of dimensions relatively easily, though, if you construct a simple error-handling routine.

In the example below three fields are dimensioned; the first is empty, the second has three dimensions, and the third has two. The procedure array_macro4 calls the procedure arraytest for each of these fields and passes as parameters the individual fields. In arraytest first a loop is run that deliberately causes en error as soon as UBound is executed for a nonexistent dimension. (This is the only way to determine the number of dimensions of a field!)

This error is caught in the program unit arraytest_error , and the procedure is continued with arraytest_continue . In the following loop the index limits are given for each dimension.

Pointer

Information on For loops can be found below in this section. The mechanisms of error handling will be described in Chapter 6.

 ' example file VBA-Concepts.xls, Module "Procedures" Sub  array_macro4  ()   Dim array1() As Variant   Dim array2(4, 5, 6) As Variant   Dim array3(-2 To 2, 1 To 4) As Variant   array2(1, 2, 3) = 4   arraytest array1()   arraytest array2()   arraytest array3() End Sub Sub  arraytest  (arr() As Variant)   Dim i&, dimensions%   On Error GoTo arraytest_error   For i = 1 To 10: dimensions = UBound(arr, i): Next i arraytest_continue:   dimensions = i - 1   Debug.Print dimensions, " Dimensions"   For i = 1 To dimensions     Debug.Print "Dimension "; i; ": "; LBound(arr, i);     Debug.Print " to "; UBound(arr, i)   Next i   Exit Sub arraytest_error: ' this code is executed as soon as the program tries to access a nonexistent ' dimension   Resume arraytest_continue End Sub

The following output is produced in the immediate window by array_macro4:

 0          dimensions 3          dimensions Dimension 1 : 0 to 4 Dimension 2 : 0 to 5 Dimension 3 : 0 to 6  2         dimensions Dimension 1 : -2 to 2 Dimension 2 : 1 to 4

Matrices as Parameters

Excel matrices can be passed as parameters to procedures. Matrices are linked regions of cells in Excel worksheets. Excel has its own worksheet functions, such as LINEST , that return not a single value, but a matrix containing several values. See also Chapter 5, where the use of the function LINEST and the programming of custom matrix functions are described.

Excel automatically converts matrices into one- or two-dimensional fields. Nonetheless, matrix parameters must be given in the form of normal Variant variables in the parameter list, and not, say (which would be more logical), as field parameters. (Internally, matrices are not treated like normal fields, but as data fields (arrays).) Within a procedure individual elements of a matrix can be accessed as in the case of a field ( matrix(n) for a one-dimensional matrix and matrix(n, m) for a two-dimensional matrix).

The following example shows a function that returns the number of matrix elements. Within the worksheet you can call this function with = matrix_func(D17:D19) , provided that D17:D19 is formatted as a matrix (select the region of cells, input formulas or numbers , and close with Shift+Ctrl+Return). The function returns the result 3; = matrix_func(LINEST( )) returns the number of resultant cells that the function LINEST returns:

 Function  matrix_func  (matrix As Variant) As Variant   Dim x As Variant   For Each x In matrix     matrix_func = matrix_func + 1   Next x End Function

Optional Parameters

Under normal circumstances, when a procedure is called it has passed all the parameters defined in the procedure. With the keyword Optional it is not necessary to pass such a parameter. Within the procedure you must, however, test with IsMissing whether such parameters have been passed in the procedure call.

As soon as a parameter has been designated as Optional , all further parameters will be so declared. (That is, it is necessary first to define all the nonoptional parameters and then the optional ones in the parameter list.)

The following procedure increases the value of the parameter x either by 1 or, in the event that the second parameter has been passed, by y .

 Sub  increment  (x As Variant, Optional y As Variant)   If IsMissing(y) Then     x = x + 1   Else     x = x + y   End If End Sub

Note

Starting with Excel 97 optional parameters can be of any data type. (Previously, only the type Variant was allowed.) However, IsMissing functions only for Variant parameters. If you use another variable type, the parameter contains simply 0, or an empty character string when no parameter is passed.

Variable Number of Parameters

Optional parameters have the disadvantage that the number of them is predetermined. If you wish to formulate a procedure to which an arbitrary number of parameters can be passed, then you must use the keyword ParamArray and use a Variant field. Then arbitrarily many parameters can be passed to the procedure, which can then be evaluated in a For “Each loop. ParamArray is not compatible with Optional : You must decide between optional parameters and a variable number of parameters. Note further that all parameters of a ParamArray are value parameters. A change in the original variables is thus not possible.

The example below shows the function sum , which forms the sum of all the parameters passed to it. The For loop is dealt with further below. You can refer to the individual parameters with x(0) , x(1) , x(2) ; the problem is that VBA provides no possibility of determining the number of parameters (see above).

 Function  sum  (ParamArray x() As Variant) As Variant   Dim var As Variant   For Each var In x()     sum = sum + var   Next var End Function

Calling a Procedure

In calling a procedure a distinction must be made between subprograms and functions. With subprograms one simply writes the name of the procedure followed by the list of parameters. With functions the parameters must be placed within parentheses, and the return value must be applied in some way (for example, in a definition of a variable or as the parameter to another function):

 macro1                         ' subprogram without parameters macro2 para1, para2            ' subprogram with two parameters result = func1()               ' function without parameters result = func2(para1, para2)   ' function with two parameters

If you wish to call a procedure whose name is given in a character string variable, you can use the method Application.Run :

 result = Application.Run("func2", para1, para2)

Named Parameters

With procedures having many optional parameters the form of parameter passing described above often leads to very unclear instructions. For example, if the first, second, and eighth parameters are given, the call looks something like this:

 macro para1, para2, , , , , , para8

It now requires careful counting to establish what meaning the parameter para8 actually possesses. For this reason there exists an alternative form of parameter passing, under which only those parameters that are really necessary need be given. So that VBA can know what meaning the parameter names have, the parameter name must be given as well.

The concept can be easily understood with the help of an example. Assume that you have written the following program as part of a database application:

 Sub insertRecord(name, address, Optional telNr, _        Optional birthdate, Optional email)

When this procedure is called you must give at least the name and address. If you furthermore wish to give the birthdate, then both calling variants, first the traditional one, then the one with named parameters, look as follows:

 insertRecord "Polsky, Ned", "143 West 4th Street, New York, NY 10012, ", , _   #10/20/1928# insertRecord name:="Polsky, Ned", _   address:="143 West 4th Street, New York, NY 10012", birthdate:=#10/20/1928#

Although it is nowhere documented, there is also the possibility of combining both forms of parameter passing. You can give the first n parameters as unnamed, and the rest named. Thus there is a third variant of insertRecord , one that is shorter and clearer than the two other variants:

 insertRecord "Polsky, Ned", "143 West 4th Street, New York, NY 10012", _   birthdate:=#10/20/1928#

When you are working with named parameters, the order of the parameters is irrelevant. However, it is always necessary that all nonoptional parameters be given. The concept of named parameters holds, of course, for all VBA methods and functions. The automatic macro recording process uses named parameters intensively, which can lead to instructions like the following:

 ActiveChart.ChartWizard Source:=Range("A1:A4"), Gallery:=xlColumn, _   Format:=6, PlotBy:=xlColumns, CategoryLabels:=0, SeriesLabels:=0, _   HasLegend:=1

Recursion

A subprogram or function is called "recursive" if it calls itself. With recursive subprograms or functions certain programming problems, particularly those involving complex data structures, can often be solved simply and elegantly. The simplest and best-known example of a recursive function is the calculation of the factorial function. The factorial of a nonnegative integer n is defined to be the product of all integers between 1 and n . The factorial of 5, for example, is 1*2*3*4*5=120. The recursive possibilities arise from the observation that n ! = n *( n ˆ’ 1)! for n ? 1.

 ' example file, vba-concepts.xls, Module "procedures" Public Sub  testrecur  ()   Debug.Print recur(3) End Sub ' recursive procedure for calculating the factorial of x Function  recur  (x As Double) As Double   If x <= 1 Then     recur = 1   Else     recur = x * recur(x - 1)   End If End Function

The execution of the program for calculating the factorial of 3 runs as follows: The function recur is called; x has the value 3. In the If conditional, therefore, the Else block is executed. There recur is called again, this time with x =2. Again, the Else block is executed, now with x =1. Now the If condition is satisfied, and the third call returns the value 1. The program execution now finds itself back at the second level, in which x =2. The result is then multiplied by the returned value 1 (yielding 2), which is returned to the first level, where x =3. There the return value 2 is multiplied by x =3 and returned to the Print method in restrecur , where the calculation was begun.

In the course of this calculation there were therefore three simultaneous variables x all with different values. With each new invocation of the function a new (local) variable x is defined. (See also the following section for a discussion of the scope of a variable.)

If this foray into the factorial function is your first contact with recursive functions, then you have probably been having a rough time grasping all the details of the program's execution. Add the following as the first and last lines to the factorial function:

 MsgBox "x=" & x & " recur=" & recur

You can then better follow the path of the program as it runs. MsgBox displays the text from the character strings "x=" and "recur=" and the values of the associated variables in a small dialog window, which you must acknowledge with OK.

Note	You can also use Visual Basic's debugging facilities to follow the calculation better (see Chapter 6).

Realm of Validity of Variables and Procedures

All variables and procedures can be used only in a particular realm of validity. This realm is the region of program code or in Excel in which a variable can be read and changed, or a procedure called. VBA recognizes, according to the setting, three or four realms of validity:

within a procedure (local variable)
within a module (module variable)
within a workbook (global variable)
within Excel (thus valid for several Excel files)

For variables all four levels exist, while for procedures only the last three are valid.

Variables and Constants

Variables that are defined at the start of a module (though outside a procedure) with Dim can be used throughout the module, and therefore in all procedures defined within the module. Module variables thus make possible the use of common data in several procedures and simplify the efficient exchange of data.

In contradistinction to these variables, there exist variables that are defined within a procedure. These "local" variables can be used only within the code of the procedure in which they are defined.

This concept makes it possible to use variables with the same name in different procedures without them influencing one another. If both a local variable and a module variable with the same name exist, then within the procedure the local variable is valid, and outside it (that is, in all other procedures), it is the module variable that holds sway.

Public (= Global) Variables

Instead of the keyword Dim at the level of a module (outside of procedures) the keyword Public can be used. Public has the same syntax as Dim (see above).

The variables thus defined can then be used in all modules of the workbook. If there are public variables with the same name in several modules, then access should occur in the form modulename.variablename in order to avoid confusion.

If you wish to access a public variable defined in one workbook in a second workbook, then in the second workbook you must construct a reference to the first workbook (command ToolsReferences; see also the following section, on the subject of object libraries and references).

If at the beginning of a module the instruction Option Private Module is given, then the variables defined therein can be accessed only from within the workbook. Without the instruction (that is, in the standard situation) global variables can be used from other workbooks.

Module Variables

All variables defined outside of a procedure with Private or Dim are considered module variables. Here Private functions the same as Dim , but it is perhaps clearer (since the realm of validity of the variable is so unambiguous).

Local Variables

Local variables are defined with Dim . The command must be used within a procedure. ( Private and Public cannot be used within a procedure).

Static Variables

Local variables are normally deleted when a procedure has finished executing and the memory that has been allocated to them is released. If you wish to preserve the value of a variable that it had the last time a procedure was called, you must then define the variable to be "static." For this you use Static instead of Dim . If you apply the keyword Static in a procedure definition before Sub or Function , then all variables of the procedure will be defined as static.

Procedure Parameters

Parameters of procedures act locally (defined by Dim ) within a procedure. In recursive procedure calls ByVal parameters act like static variables; that is, their old values remain available for use after the recursive return.

Combined Variable Names

When you wish to access variables of the same name outside of the current module or procedure level, you must prefix the name of the module and if necessary the name of the workbook as well, yielding something like the following:

 modulename.variablenname [WORKBOOK.XLS].modulename.variablename

Life Span of Variables

Normally, the content of variables is saved only as long as the code in the valid realm is being executed. For example, a local variable loses its validity (and its content) as soon as the procedure's code has finished executing. The only exceptions to this rule are the static variables (keyword Static ), which are saved after a procedure has terminated until the file is closed.

Global variables that are defined in modules have the same life span as static variables: Their value is preserved until the file is closed.

Regardless of the type, the contents of all variables are erased when the program code is edited (and therefore recompiled).

Note

The content of a variable, whether local, global, or static, is never saved in the Excel file of the workbook. If you wish to do so, you must copy the content of the variable into the cell of a worksheet before it is saved, and when the file is opened the value can then be read. For automatic execution of the necessary code you can use event procedures (see the section after next one).

Constants

In general, what holds for variables holds for constants as well. Constants that are to be valid in all the modules of a workbook must be preceded by the keyword Public .

Procedures

Procedures are generally categorized as public, that is, they can be used in all modules of the same workbook. If a procedure is to be used in other workbooks as well, then in every workbook a reference must be made to the workbook in which the procedure is defined (with the command ToolsReferences; see also the next section, on the theme of object libraries and references).

If procedures of the same name exist within a workbook, then the module name must be prefixed to the procedure call. Procedures of the same name in different workbooks must be identified uniquely by the file name of the workbook.

 modulename.macro                   ' within the current workbook [WORKBOOK.XLS].macro               ' in another workbook [WORKBOOK.XLS].module name.macro   ' in another workbook

If you wish to prevent the use of a procedure outside of the module in which it is defined, then you must write Private before the keyword Sub or Function . This should be done for all procedures that are designed to be used only within a particular module. The declaration "private" not only prevents accidental and therefore erroneous use, but also simplifies the selection of macros in ToolsMacros and InsertFunction.

When you employ the instruction Option Private Module at the beginning of a module, then all procedures of that module will be closed outside of that worksheet. The option is confusingly formulated: The procedures can be used in other modules of the same workbook. This option is therefore less restrictive than the keyword Private .

As with Private , you can prefix a procedure with the keyword Public . This keyword has no effect, however, since procedures are public by default. The program code will, however, be somewhat clearer.

Note	When you declare a procedure as `Private` , this has the advantage that this procedure will not be listed in the forms "macro" and "assign macro." This makes these forms easier to read ( especially in the case of large projects or when several workbooks are open ).

Add-Ins

Excel programs that were saved as add-ins (see Chapter 14) differ in one respect from normal workbooks: The procedure name need not be prefixed with a file name, even when the procedure is used outside of the add-in.

Accessing Variables and Procedures in Another Workbook

To access variables and procedures belonging to another workbook from within a workbook, two conditions must be fulfilled: First, the variables in the other workbook must be declared as Public , and procedures may not be declared as Private . Second, a reference (via ToolsReferences) must be made to the workbook whose variables or procedures are to be used. If like-named variables or procedures exist, then the variable names must be preceded by the module or file name (see above).

Internal Management of Variables

Excel stores the names of all variables and procedures that appear during the course of program development in an invisible table. Experience has shown that variable names change frequently during the programming process, and many test procedures are deleted. Therefore, the internal name table becomes full of data trash over time, resulting in bloated Excel files. The only method of clearing this invisible table consists in saving all modules as text files, then deleting all modules, and finally creating new modules out of the text files. This convoluted method really makes sense only if you wish to pass along the application in the form of an add-in.

Summary of Keywords

Dim var defines local procedure or module variables.
Private var has the same effect as Dim in variable declaration.
Public var defines global variables (possible only at the module level); the realm of validity (scope) depends on the setting of Option Private Module .
Static var defines local static variables (possible only at the level of a procedure).
Sub/Function defines public procedures; scope depends on Option Private Module .
Private Sub/Function defines local procedures, usable only within a module.
Public Sub/Function like normal Sub/Function , that is, also public.
Static Sub/Function defines all Variables of the procedure as static.
Option Private Module limits the scope of public variables and procedures to the current workbook. Without this option the variables and procedures can also be used in other workbooks, to the extent that references have been made.

Branching (Tests)

Branching with If “Then “Else

With If tests it is possible to execute particular program segments according to whether certain conditions are met. The program code "branches" into several pieces, of which only one will be executed.

The following example demonstrates the form of an If test: InputBox (for details see Chapter 7) calls for the input of a number. The input will then be evaluated: The first If test checks whether instead of a number, text was input. If that is the case, then the message "This is not a number!" appears. Otherwise, with ElseIf the further distinction is made as to whether the number is greater than 10.

 ' example file VBA-Concepts.xls, Module "LoopsAndConditions" Sub  macro_if  ()   Dim number As Variant   number = InputBox("Type in a number, please!")   If Not IsNumeric(number) Then     MsgBox "This is not a number!"   ElseIf number > 10 Then     MsgBox "The number is larger than 10"   Else     MsgBox "The number is greater than or equal to 10"   End If End Sub

This example illustrates just about everything there is to explain about If tests. The general syntax is introduced by If condition Then and terminated by End If . In between there can be a simple piece of code (one or more lines), which will then be executed only if the condition is satisfied. Additionally, after Else another piece of code can appear, which will be executed if all the previous conditions were not fulfilled. And finally, before Else there can appear an arbitrary number of ElseIf “condition “Then blocks to distinguish different cases.

Every block following Then or Else can itself contain further procedural structures (including further branches or loops). In addition to the multiline If syntax there exists a one-line version. This, however, is suitable only for simple tests:

 If nr > 10 Then MsgBox "..." If nr < 5 Then nr = nr * 2 Else MsgBox "..."

Formulating and Evaluating Logical Conditionals

In If branches and in loops (see the following section) you must always formulate logical conditionals. For this purpose you have available the comparison operators =, <>, <, >, <=, and >=, by means of which you can determine whether two expressions are equal, or whether one of the two expressions is less than, less than or equal to, etc., the other. For comparing two character strings there is the additional operator Like , with which you can recognize the form of a character string ("T*son" for "Thompson", "Thomson", or "Tomson").

The result of a comparison is a truth value. In the binary system of a computer there are only the two values True and False . For the two keywords True and False VBA uses the values ˆ’ 1 and 0 internally. In logical conditionals every value unequal to 0 is considered to be True , that is, in the instruction If 3 Then, the If condition is satisfied (is true, since nonzero).

In addition to conditionals various functions can also return a truth value, such as the IsXxx functions, which determine whether an expression corresponds to a particular data type ( IsNumeric , IsDate etc.).

In VBA you can join several comparisons together. In this case as well the result of the entire expression is a truth value. The first example below tests whether a is less than 5 or greater than 10, while the second example tests whether a is between these two values. In the third example the goal is to exclude the possibility that a contains the value Null or has not yet been allocated.

 If a < 5 Or a > 10 Then ... ' If a > 5 And a < 10 Then ... ' If Not (IsEmpty(a) Or IsNull(a)) Then ...

The joining operators are And and Or as well as the less frequently used Xor , Imp , and Eqv . With Not a truth value can be inverted (analogous to the minus sign for addition or reciprocal for multiplication).

Caution

VBA does no optimization in evaluating conditionals. A test of the form If x>=0 And Sqr(x)<3 leads in the case of negative x to an error. (In many programming languages the second part of the conditional is not evaluated if the first part evaluates to false, since in the case of an And conditional the result is False if even one part is False .)

Pointer

Later in this chapter we give an overview of the operators defined in VBA. For information on working with character strings (including the comparison of strings), see Chapter 5.

Branching with Select Case

An alternative to the If test is another branching structure, introduced by the keyword Select Case . This variant can lead in many cases to the formulation of more easily readable branching code. This branching structure is also most easily understood by means of an example, one that also determines the range of values of an input number. Note that this time it is not tested whether the input is actually a number. If you input the character "x", then VBA will respond with an error message (since the character "x" cannot be compared with a number).

 Sub  macro_select  ()   Dim number As Variant   number = InputBox("Type in a number, please!")   Select Case number   Case 1, 2, 3     MsgBox "1, 2 or 3"   Case 4 To 10     MsgBox "Between 4 and 10"   Case Is > 10     MsgBox "Greater than 10"   Case Else     MsgBox "Less than 1"   End Select End Sub

The syntax of Select Case can be easily understood from this example. Following Select Case the expression to be analyzed must be given. This holds for the entire branching structure (which in comparison to If branching represents a limitation). In the following Case branches, conditions must be formulated that the expression is to satisfy . These can be individual values, ranges indicated by the keyword To , or Is conditionals similar to what occurs in an If branch. Here Is represents a reference to the expression given at the start of the Select Case .

Loops

Loops serve the purpose of allowing program code to be executed repeatedly. VBA has three such structures: For “Next , Do “Loop , and While “Wend .

Loops with For “Next

The simplest type of loop is constructed with the commands For and Next . Here initial and terminal values are given to a variable at the start of the loop. The initial value is increased each time the loop is executed until finally the terminal value is reached.

The following example shows the simplest variant of a For loop. The variable i runs over the values from 1 to 5.

 Sub  macro_loop1  ()   Dim i As Integer   For i = 1 To 10     If i > 5 Then Exit For     Debug.Print i   Next i End Sub

With the optional keyword Step a value can be given by which the loop variable is to be increased each time through the loop (the default value, without Step , is 1). In the example below the loop runs from “0.3 to +0.3 with step size 0.1. For the loop variable i the data type Double has been given.

 Sub  macro_loop2  ()   Dim i As Double   For i = -0.3 To 0.3 Step 0.1     Debug.Print i   Next i End Sub

However, the result of this loop is not completely convincing. In the immediate window the following values are output:

 -0.3 -0.2 -0.1 2.77555756156289E-17 0.1 0.2

The repeated addition of 0.1 has resulted in round-off error. This round-off error results not only in the rather unattractive display of 2.77555756156289E-17, but also in the sad fact that the terminal value 0.3 is never reached. (At the end of the loop i has the value 0.30000000000000006. This value is minimally greater than 0.3 and leads to the loop being broken off prematurely.) The problem can be solved by a more careful formulation of the loop condition:

 For i = -0.3 To 0.300000001 Step 0.1

Note	Round-off error in floating point numbers is one of the characteristics of all programming languages. They are not a particular weakness of VBA (even if there are, in fact, programming languages in which the above error does not occur). As a programmer you should always be aware of this problem.

The design of a For loop is such that it may not be executed even once. That is because even before the first pass through the loop it is checked whether the loop conditions are valid. A loop that begins For i=5 To 1 will thus not be executed even once (unless a negative step size is assigned with Step ). At the end of the For loop the loop variable contains the value for which the loop condition first failed to be satisfied. After the loop For i=1 To 10 , for example, the variable i contains the value 11.

For loops can be prematurely exited with Exit For . In the following example the values 1, 2, 3, 4, 5 are output. With For i=6 , the If condition is fulfilled, which leads to a premature exit from the loop

 For i = 1 To 10   If i > 5 Then Exit For   Debug.Print i Next i

Loops with For Each “Next

In VBA there exists a special form of the For loop that is designed especially for working with fields and enumeration methods. (Enumeration methods will be expounded in the next section. With them you can access associated objects in a group , such as all sheets of a workbook or all tools on a toolbar.) The example below outputs the names of all the worksheets in the current workbook:

 Sub  macro_loop3  ()   Dim w As Worksheet   For Each w In ThisWorkbook.Worksheets     Debug.Print w.Name   Next w End Sub

The syntax is somewhat different from that of normal For loops: The variable is given after For Each , and the collection object after the keyword In . During the processing of the loop the collection object can be accessed directly via the loop variable.

In using a For-Each loop you should not assume that the elements will be processed in a particular order. It is not documented in what order the elements are associated to a loop variable. However, with most collection objects instead of a For-Each loop you can use a traditional loop and access the individual elements with indices as in the case of fields.

 Sub  macro_loop4  ()   Dim i As Integer   For i = 1 To ThisWorkbook.Worksheets.Count     Debug.Print ThisWorkbook.Worksheets(i).Name   Next i End Sub

Loops with Do “Loop

For loops are in one respect inflexible : It is determined at the outset how many times the loop will execute. The keywords Do and Loop assist in the formulation of more general loops. In their simplest form these two commands can be used to create an infinite loop:

 Sub  macro_loop5  ()   Do     Debug.Print "and so on"   Loop End Sub

Once you have started such a loop, you can interrupt it only with the keyboard combination Ctrl+Break . Infinite loops are seldom useful in practice. Therefore, there are two ways in which the above loop can be programmatically terminated: By the command Exit , which we have already encountered in the For loop, which here must follow the keyword Do (and not Loop ), or by providing a condition at the beginning or end of the loop. Conditions can be given either in the form While or Until . In the first case the loop is executed until the given condition is satisfied. In the second case the effect is the opposite : The loop is executed until the condition becomes true (or, alternatively, while the condition is not satisfied).

Although it may seem at first glance that it makes no difference whether the condition is given at the beginning or at the end, this is not the case: If you give the condition While , then it can happen that the loop will, in fact, never execute even once. If, on the other hand, the condition is formulated with Loop , the loop must execute at least once.

In the example below, the variable i is increased by 1 each time through the loop until it is broken off when the value 11 is reached.

 Sub  macro_loop6  ()   Dim i As Integer   i = 1   Do     Debug.Print i     i = i + 1   Loop Until i > 10 End Sub

Loops with While “Wend

Loops with While Wend offer nothing new with respect to the loops with Do Loop . The only difference is that there is now the possibility of terminating the loop prematurely with Exit .

 While i < 10   i = i + 1: Debug.Print i Wend ' Do While i < 10   ' the same loop [[?]]   i = i + 1: Debug.Print i Loop

Syntax Summary

Below, square brackets enclose optional commands, which may be given but do not have to be.

DEFINITION OF PROCEDURE
Sub macro( [parameterlist] )

[Exit Sub]	exit program prematurely

End Sub

Function func( [parameterlist] ) [As data type]

[ func = : Exit Function]	exit function prematurely

func =
End Function

DEFINITION OF THE PARAMETER LIST
para1, para2, para3	3 parameters in default data type Variant
para As datatype	parameters in given data type
para() [As datatype]	Field
ByVal para [As datatype]	value parameter
Optional para [As datatype]	optional parameter
ParamArray para()	List with variable number of parameters

EVALUATION OF PARAMETER IN PROCEDURE CODE
true_false = IsMissing(para)	test whether an optional parameter was passed
For Each x In para()	loop through all parameters of an argument list

Next x

PROCEDURE CALL
macro x1, x2, x3	traditional input of all parameters (subprogram)
result = function(x1, x2, x3)	traditional input of all parameters (function)
macro para1:=x1, para3:=x3	named parameters (subprogram)
result = function (para1:=x1)	named parameters (function)
Application.Run "macro", para1	call procedure (procedure name as character string)
result = Application.Run( )	call function (function name as character string)

DEFINITION OF VARIABLES AT THE MODULE LEVEL
Dim var	module variable
Private var	module variable (same effect as Dim )
Public var	public variable (all modules)
Option Private Module	public variable accessible only within the workbook (also with reference)

DEFINITION OF VARIABLES AT THE PROCEDURE LEVEL
Dim var	local variable, usable only in the procedure
Static var	as above, but retains the value
Static Sub/Function name()	all variables of the procedure are static

DEFINITION OF PROCEDURES
Sub/Function name()	public, for all worksheets
Private Sub/Function name()	usable only in the current module
Option Private Module	public procedures usable only with the workbook (also with reference)

BRANCHING WITH IF “ THEN
If condition Then command	one-line variant
If condition Then k1 Else k2	one-line variant with Else
If condition Then	multiline variant
commands
ElseIf condition Then	optional, arbitrarily many cases
commands
Else	optional
commands
End If

BRANCHES WITH SELECT “ CASE
Select Case expression
Case possibility1	arbitrarily many cases
commands
Case Else	optional
commands
End Select

POSSIBILITIES IN SELECT “ CASE
value	single value
value1, value2, value3	enumeration
value 1To value2	range of values
Is operator comparison value	comparison condition with =, <, or >

LOOPS WITH WHILE “ WEND
While condition
commands
Wend

LOOPS WITH FOR “ NEXT
For var=start To end [Step value]
commands
[If condition Then Exit For]
commands
Next var

LOOPS WITH FOR EACH “ NEXT
For Each var In enumeration method or field()
commands
[If condition Then Exit For]
commands
Next var

LOOPS WITH DO “ LOOP
Do [While condition or Until condition]	variant 1
commands
[If condition Then Exit Do]
commands
Loop
Do	variant 2
commands
[If condition Then Exit Do]
commands
Loop [While condition or Until condition]