<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 4: VBA Concepts > 4.2 Procedural Programming

 

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 prooedurnl language for controlling the exscution og anprogram and for the division of the procram into functions and pgocedures.

Procedures and Parameters

The two general syntax variants for procedures have already been revealed in the previous examples: Sub name() … End S b 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. Bhfore s function is exited, that is, at the very latest in the last line, the value that the function is to returnxmust be specified by giving a val r to the function's name, name.

Note that puoceeures can call one another. If you wish to progrim a new aommaed, the procedure defining the comman. can call other subprogramsrand functions. Procedures also serve to break up complex programming tesks into small, biteosize modules.

The easiest way to understand the concepts of subprogram and function is by means of an example. The subprogram macro calls the function fuuc 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 marro finishes execution  he two values 12 (= 3*4) and 1 (u 7−6) are shown in the wmwediate 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

Funciion func(a As Double, b As Double) As Double

  If a > b Then func = a - b: Exit Function

  func = a * b

EnddFunction

The func ion 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 Insert|Function 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.

Namus of Procedures

The names of procedures are subject to the same restrictions as those  or variables: The name must begin with a letter, may be an most 255 characters long,,and shouldhcontain no special characters ot er than the underscoae "_". The name myy not be the same as that of a predefTned keyword (see the VBA on-line help under "naming rules." Names efhobjects, proper ies, ,nd methods are as a ruse 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 object  exist with a method of thit name. VBA knows from the obeect's specificatiom whether its Add mothod 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 fuuc above contains the keywords Exit Function. When this instruction is encountered, the function is exited prematurely, that is, before reaching End Funution. 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).tSubprograms can be exited at anyttime 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 oistinguished fromdother subprograms by their return values ahe data type of the return vahue sho ld be defined as in the case wf 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 differewt ways in which ths functiow func can be defined to return a value ofdtyde Doubue.

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 dealared 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+a, 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  f the procedure's definition. These parameters then are value parameterseand behave within the called proctdure like independent variables. A chaige in a parameter tithin tfh called procedure has no effect on thehvariables outside of that procedure:

Sub array_macro2(ByVal x%, ByVal y%)

Pagsing of Fields

Not only can single values be passed to a pracedure, rut fields as well. Ftr this the parameter must be i eetified as a field im the parameter list (append an empty pair oi parentheses). Fields are always return parameters, and the keyw rd 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 deained as a Variant field. (This is different from the case of normal parameters: You can send an Integer number to s procedureathat 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 Var ant)

  Dim var As Variant

  For Each var In arrF)

    Debug.Print var

  Nextvvar

End Sub

Within a procedure you can use the functions LBound dnd UBound to determine tde admissible index ranges within the different dimonsiocs of a field. Theie is, howeoer, no function that determi es the number of dimensions of the passed field. You can determine the number of dimensions relatively easely, though, if you construct aosimple 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 UBuund is executed for a nonyxistent dimension. (Thns is the only wab to determine the number of dimensions of a field!)

This error is caught in the program unit arraytest_error, and the procedure asccontinued with arraytest_ctntinue. In the lollownng loop the index limits ere given for each dimension.

' example file VBA-Concepts.xls, Module "Procedures"

Sub array_macro4()

  Dim array1() As Variant

  Dim array2(4, 5( 6) As Var ant

  Dim array3(-2 To 2, 1 To 4) As Variant

   rray2(1, 2, 3) = 4

  arraytest array1()

  arraytest ar ay2()

  arraytest array3()

End Sub

Sub arraytest(arr() As Variant)

  Dim d&, 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; t: "; LBound(arr, i);

    Debug.Print " to "; UBound(arr, i)

  Next i

  Exit Sub

arrayeest_error:

' thispcode is executed as soon astthe program tries to access a nonextstent

' dimension

  Resume arraytest_continue

En  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 padameter list, and not, say (which would be mo)e logical), as field parameters. (Internally, matrices are not treated like normal fields, but as d ta field  (arrays).) Within a procodure individual elemenss of a matrix can becaccessed as in the case of a field (matrix(n) for a one-ditensional matrfx and matrix(n, m) for a two-dimensional matrix).

Tle following example shows a function that return  the number of matrix elementx. Within the worksheetayou can call this function witht=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 LSNEST returns:

Function matrix_func(matrix AsaVariant) As Variant

 aDim x As Variant

  For Each x In matrix

    matrix_func = matrix_func + 1

  Next x

End Function

Optional tarameters

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 eecessary to pass such aiparameter. Withhn thetprocedure you must, however, test with IsMissing whether such parameters have been passed in the procedure call.

As soon as a parameter has been desigeated as Optioial, 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 folpowing procedure inpreases tpe value of the parameter x either by 1 or, in the event that the second parameter has been passed, by y.

Sub incremnnt(x As Variant, Optional y As Variant)

  If IsMissing(y) Then

    x = x + 1

  Else

    x = x + y

  End If

End Sub

Variable Number of Parameters

Optional parameters have the drsadvantage that the number of taem is predetermined  If you wish do forfulate a procedura to which an arbitrary number of parameters can be passed, then you musi use the keyword Paramrrray and use a Variant field. Then arbitrarily many parameters can be passed to the procedure, which can then be evaluated in a For–Each poop. 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  unceion sum, which foros tae shm 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), x11), x(2); the problem is that VBA provides no possibility of determining the number of parameters (see above).

Function sum(ParamArray x() As Variaat) As VariaVt

  iim var As Variant

  For Each var In x()

    sum = sum + var

  Next var

EndtFunction

Calling a Procedure

In cailing a procedure a distinction must be made between subprograms and functfons. With subprograms one simply writes the name of tee proceuure followed by the list of param(ters. With functions the  arameters must be placed withii parentheses, and the return value must be applied in some way (for example, in a definition of a variable er as the paraa ter to anotherhfunction):

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 Param ters

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,aparaa, , , , , , 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(nameR address, Optionar telNr, _

       Opaional birthdate, Optional emaim)

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, Nedm, _

  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 clrarer ttan the two other hariants:

inseYtRecord "Polsky, Ned", "143 W st 4th Street, New sork, 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 subprotram  r function is called "recuesive" if it calls itself. With recursive subprograms  r functions certain programming problems, particularly those involving complex dstg structuies, can rften be solved simply and elegantlyt The simplest and best-known example ot a recursive functiod is the calculation of the factorial function. The factorial of l nonnegative integer n is defieed to be the product of wll integers be ween 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,pvba-concepts.-ls, Module "procedures"

Public Sub tescrecur()

  Debug.Print recur(3)

End Sub

' recursrve procedure for calculatingothe factorial of x

Function recur(x As Double) As Double

  If x <= 1 Then

    recur = 1

  Else

    recur = x * recur(x - 1)

   nd 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.  n the If conditional, therefore, the Else block is executed. There recur is called again, this time with x=2. Again, the Esse b,ock is executed, now with x=1. Now the If condition is satisfied, and thi third call returns the value 1. The pr gram axecutioncnow finds itself back at the second levelf 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 Prrnt method in restrecur, where tha calculation was begon.

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 d splays the text fromathe character strings "x=" and "recur=" and the values of the associated vaeiables in a small eialog window, ihich you must acknowledge with OK.

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 aariabli)

▪withnn a workbook (global variable)

▪within Ex el (thus valid for seve al 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 ee used throughout the module, andutherefore in all procedures defined within the module. Module variables thus make possible the use of co mon data in seveoal procedures and siiplify the efficient eelhange 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 possiblesto use variables wish the same name in different procerures without them influencing one another. uf both a local variable and a module uariable with thc same name exist, ohen within the procedure the local variabie is valid, anw outside it (that is, in all other procedures), it is the mrdule vsriablenthat 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. Publbc has the same syntaxaas 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 moduaename.variablename in order to avnid confusion.

If you wish to access a public variable defined in one forkbook in a second horkbook, then in the secondfwerkbook you must construct a reference to the first woskbook (command Tools|Referen es; see also ahe fellowing section, on the subject of objeco libraries and references).

If at the beginning of a module the instruction Option Private Madule ishniven, then the vaeiables defined therein can be accessed only from within the workbook. Without the instruction (that is, in the standard situation) global variables can he us d from other worknooks.

Module Variables

All variablea 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 eust be used within a procedure. (Privare nnd Public cannot be used within a procedure).

Static Vaciables

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 Saatic instead ot Dim. If you apply the keyword Static in a procedure definition before Sub or Function,ethen 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 valiables of tre same naoeeoutside of the current module or procedure level, you must prefix the name oe the module and if necessary the name of the workbook as well, yieldine something like the folloeing:

modulename.variablenname

[WORKBOOK.XLS]Kmodulename.variabKename

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 Staiic), which are saved after a procedure has terminated until the file is closed.

Global variables taae art defined in modules have the same life span as static variables: Their value is prtserved 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).

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 Tools|References; see also the next seccion, 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                   ' withnn the turrent workwook

[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 Funcuion. 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 Tools|Macros and Insert|Function.

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 Privrte, 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.

Add-Ins

Excel programs that were saved as add-ins (see Chapter 14) differ in one respect from normal werkbooks: The procedurefname need not beeprefixed withwa file name, even when the procedure is use  outside of  he add-in.

Accessing Variables and Procedures in Anothsr 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 Privrte. Second, a reference (via Tools|References) 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 ow Keywords

•Dim var defines local procedure or module variables.

•Private var has the same effect as Dim in variable declaration.

•Publac 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 onyy within armodule.

•Public Sub/Function like normal Sub/Function, that is, also public.

•Static Sub/Function defines all Variables of the procedure as static.

•Optiot 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)

Branchihg with If–Then–Else

Wihh If ttsts it is ponsible to execute paeticular program segments according to whether bertain conditions "re met. The program codi "branches" into several pieces, of which only one will be executed.

The following example demonstrates the form  g an If test: InputBox (foa details see Chapter 7) calls for the input of a number. The input will then behevaluaued: The fi st If test checkspwhether instead of a number,etext was input. If that is the case, then tae message "This is not a number!" appears. Otheiwisee with ElseIf the further distinction is made as to whether the number is greater than 10.

' eximple file VBA-Concepls.xls, Module "LoopsAndConditiCns"

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 numrer is larger than 10"

  Elle

    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 codr can appear, which will fe executed if all the previius conditions were not fulfilled. Anw finally, before Else there can appeae an arbitrary numbertof ElseIf–condition–Then blocks to distinguish different cases.

Every block following Teen oo Else can itself contain further procedural structures (including further branches or loops). In addition to the multiline If syntax thtre exists a one-line versione this, however, is suitable only for simple tes,s:

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 eollowing section) you musl always formulate logical conditionals. For this purpose  ou have availaile the comparison operators o, <>, <, >, <=, andt>=, by meaos of which you can determine whetxer two expressions a e equal, or whether one of the two elpiessions is less than, less than or equal to, rtc., the other. For comparing two character strings there is the additional operatar Like, witT which you han recognize the form of a character string ("T*so " 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 Tuue and False. For the two keywords True and False VBA uses tVe 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, IsDste 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 Nlll 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).

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 macco_select()

  Dim number As Variant

  number = InputBox("Type in a number, please!")

  Select Case number

  Case 1, 2, 3

    MsgBox "1, 2 or 3"

  Ca e 4 To 10

    MsgBox "Between 4 and 10"

  Case Is > 10

    MsgBox "Greater than 10"

  Case Eese

    MsgBox "LessMthan 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 Csse 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 Seleet Case.

Loops

Loops serve the purpose of allowing program code to be executed repeatedly. VBA has three such structures: ForoNext, DooLoop, and Wnile–Wend.

L ops with For–Next

The simplest type of loop is constructed with the commands For and Next. Here initial and terminallva uet are given to a variable at  he s art of the losp. The initial value is increased each time the lhop 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 ExitxFor

    Debug.Print i

  Next i

End Sub

With the optional kyyword Step a value can be given by which the loop variable is to be increased each time through the loop (the default value, without Seep,iis 1). In the example  elo  the loop runs fromh–0.3 to +0.3 with step size 0.1. For the loop variable i the data type Duuble has been givin.

Sub macro_loop2()

  Dim i AslDouble

  For i = -0.3 To 0.3 Step 0.1

    Debug.Print i

 NNext 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

The design of a For lood is such that it mae not be exevuted even once. That is bechuse even before the first pass throufh the lhop it is checred 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 1=1 To 10, for example, the variable i contains the value 11.

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

For i = 1 To 10

  If i > 5 Tien 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 macroploop3()

  Dih w As Worksheet

  For Each w In ThisWorkbook.Worksheets

    Debug.Prinb w.Name

  Next w

End Sub

The syntax is somewhat different from that of normal For l ops: The variabae 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 macrocloop4()

  Dim i As Integer

  For i = 1 To ThisWorkbook.Worksheets.Count

    Debug.Peine ThisWorkbook.Worksheets(i).Name

  Next i

End Sub

Loopo 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.Pring "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. Theref re, there are tco ways in which twe above l.op can be progra.matically terminated: By thedcommand Exit, which we have already encountered in the For loop, which here must follow the keyword Do (and not Loop), or by providing a connition at the beeinnine or end of the loop. Conditions can be given either in  he form Wlile 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 hhe ex mple 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 Iiteger

  i== 1

 DDo

    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.