Chapter 4: VBA Concepts

This chapter describes the language features of VBA and provides the theoretical background for programming in VBA. The themes dealt with here include the use of variables , procedural programming ( loops , branches), and the management of objects and events. It is in the nature of the subject that this chapter is rather dry. But the information contained here is absolutely necessary for successful macro programming.

4.1 Variables and Fields

Note  

All example programs in this chapter can be found in the file VBA-Concepts.xls .

Managing Variables

Variables are placeholders for numbers , characters , or other data. Variables are used to store data during the execution of a program and to carry out computations with these data. The following example program shows a trivial application of variables:

 ' Example file VBA-Concepts.xls, Module "Variables" Option Explicit Sub  macro1  ()   Dim length, width, area   length = 3   width = 4   area = length * width   Debug.Print area End Sub 

The variables length and width store the length and width of a rectangle, and from these data the area of the rectangle is computed and stored in the variable area . The result of the computation is then output via Debug.Print into the immediate window, where it can be viewed via Ctrl+G .

The instructions Sub macro1() and End Sub are necessary because VBA can execute program code only if it is contained within one or more procedures. More detail on this theme can be found in the following section. On the other hand, for the management of variables, the lines Option Explicit and Dim length, width, area are relevant.

Definition of Variables

If the instruction Option Explicit appears at the beginning of a module, then all variables must be defined via the command Dim before they can be used. At first glance this seems an undue burden , but in reality it is an important and effective protection against typographical errors. Namely, Excel refuses to execute a procedure until it knows about all the variables appearing in it.

Note  

When you activate the option Require Variable Declaration in TOOLSOPTIONSEDITOR , Excel adds Option Explicit to every module.

Variables can be declared in such a way that they can be used only in a particular procedure, in an entire module, or in the entire workbook. In the following section we shall discuss more fully the scope of variables and the keyword Static.

Naming Variables

A variable name must begin with a letter, be of length less than 256 characters, and contain no blank spaces, periods, or any of a number of other special characters. Variable names are not case sensitive, and they may not coincide with predefined VBA keywords, such as Sub , Function , End , For , To , Next , Dim , As .

Note  

Names of objects, methods , and properties are not as a rule considered keywords, and can therefore be used as names of variables. VBA generally has no problem with this duplication and can determine from context whether what is meant is a variable, on the one hand, or a property or method, on the other. (In the case of properties or methods in which object specification is optional, if there is a variable of the same name, then the specification is no longer optional. See the section after next for more on the theme of objects.) In any case, variable names that duplicate those of objects, methods, or properties can lead to confusion in reading or analysis and for this reason are best avoided.

Variable Types (Data Types)

In the example above three variables were defined using Dim , but no variable type was specified. This is permissible in VBA ”the program then automatically chooses a suitable type. Nonetheless, it is a good idea if you know the variable types provided by VBA to define variables with the desired type included in the definition. In this way you will reduce the amount of time expended in editing, the amount of space required for the program, and the probability of introducing errors.

VBA VARIABLE TYPES
	Byte : whole number between 0 and 255; requires 1 byte of storage
	Boolean : truth value ( True , False ); 2 bytes
%	Integer : whole number between “32768 and +32767; 2 bytes
&	Long : whole number between “2147483648 and [phone]; 4 bytes
@	Currency : fixed point number with 15 places before and four after the decimal point; 8 bytes
	Decimal : This is not an independent data type, but a subtype of Variant ; the precision is 28 places; the number of places to the right of the decimal point depends on the size of the number: A number whose integer part is ten digits will have the remaining 18 places to the right of the decimal point; the allowed range of numbers is ±10 28 ; 12 bytes
#	Double : floating point number with 16-place accuracy; 8 bytes
!	Single : floating point number with 8-place accuracy; 4 bytes
	Date : for dates and times; the date is limited to the period between 1/1/100 and 12/31/9999, the time to the range 00:00 to 23:59:59; 8 bytes
$	String : a character string; the number of characters is limited only by the amount of RAM (up to 2, 147, 483, 647 characters); 10 bytes plus 2 bytes per character
	Object : objects; the variable stores a pointer to an object; 4 bytes
	Variant : Default variable type, assumes one of the above variable types according to what is required (with automatic conversion); the memory requirement is at least 16 bytes, and with character strings 22 bytes plus 2 bytes per character

In addition to the data types listed here, variables can be defined in all objects defined in Excel (for example, as a Chart or Worksheet ). In this case the variable will be considered an object variable. Working with objects is discussed in greater detail later in this chapter.

In the definition of variables with Dim the variable type can be determined either by placing the label directly after the variable name or via the As data type .

Caution  

It is syntactically allowed to place several variables between Dim and As . However, only the last variable is given the desired variable type, and all remaining variables will be considered Variant variables!

 Dim a, b, c As Integer 'only c is an integer; a and b                        'have the data type Variant! 

With the keywords DefBool , DefCur , DefDbl , DefDate , DefInt , DefLng , DefObj , DefSng , DefStr , and DefVar the default data type for variables with certain initial letters can be preset. The commands must be given at the beginning of a module (before the beginning of the first procedure), and they hold for the entire module. The effect is best understood by means of an example.

 DefSng a-f DefLng g, h 

All variables that begin with the letters a, b, c, d, e, f will have data type Single , while those beginning with g or h will be of type Long . The default data type holds for all variables that are not bound to a different data type by a Dim command.

The Data Type Variant

By far the most universal data type is Variant . It is a preset type for all variables whose type is not explicitly given. Variables of type Variant adjust themselves automatically to the data stored within them, and can thus contain integers, floating point numbers, text, data, or Excel objects. However, the administrative overhead for Variant variables is the greatest among all the data types.

Variables of type Variant , in contrast to other variables, can contain error codes as well as two special values: Empty (indicates that the variable is empty; Empty is not the same as 0 or an empty character string) and Null (indicates that no space is reserved in memory for the variable). The data type currently in residence in a Variant variable can be determined via the functions VarType , IsObject , IsError , IsEmpty , and IsNull . The functions IsNumeric and IsDate determine whether the content of variables can be transformed into a number or into a data value.

Caution  

The comparison x = Null is syntactically correct, but is handled incorrectly. Even when x is actually Null , the comparison returns Null instead of True as its result! Therefore, always use IsNull(x) !

Computing with Whole Numbers

There are certain difficulties associated with computing with whole numbers in VBA. The following example results in an overflow error. Such an error usually occurs when the allowed range of values for the number is exceeded. The multiplication below produces the value 65280, which actually can be stored easily in a Long variable (see above).

 Sub  macro_overflow  ()   Dim l As Long   l = 255 * 256  ' here an overflow error occurs End Sub 

The problem with this example is that in the multiplication of 255 and 256, Excel internally interprets the two numbers as Integer numbers and thus invokes its routine for the multiplication of numbers of type Integer . The result exceeds the permissible range for numbers of type Integer and thus leads to an error before the definition of l . A remedy exists in the form of the symbol "&", which must be placed after one of the two numbers. This signals Excel that the multiplication routine for Long numbers should be invoked:

 Sub  macro_no_overflow  ()   Dim l As Long   l = 255& * 256 'now it works! End Sub 

Links Between Differing Data Types

VBA normally carries out type conversion automatically. Depending on the format of the target variable, this can lead to loss of data. If you associate a Variant variable with the value 3.6 to an Integer variable, then the value 4 will be stored. Date values are transformed by such linkages into floating point numbers whose fractional part becomes the time and whose integer part is transformed into the date.

Definition of Custom Data Types

Using the predefined data types in Excel you can create your own custom data types. Such data types (which in other programming languages are known as structures, records, or something similar) can be used to organize data to facilitate their management.

The definition of a new data type is introduced by the command Type and ended by End Type . Within the data type one can place as many separate variables as one wishes in the form name As vartype (each on its own line). For character strings the keyword String , an asterisk, and a number can be placed at the end. In this case the length of the string is limited to the given value.

In the example below the data type article is defined, in which the name and price of an article of merchandise can be stored. In real-world applications you will probably wish to plan for additional elements such as article number and supplier. The macro here shows the use of the data type: Access to individual elements is made through affixing the element's name.

 'example file VBA-Concepts.xls, Module "Type_Article" Option Explicit Type article   artname As String   price As Currency End Type Sub  macro  ()   Dim a As article, b As article   a.artname = "nuclear minireactor"   a.price = 3.5   b = a   Debug.Print b.price End Sub 

Data types are normally valid only within the module in which they are defined. However, you can prefix the keyword with the keyword Type Public . Then the data type is valid for all modules in the workbook. The possible scopes of variables are discussed further in the following section. Fields are allowed within a custom data type. Fields, too, will be explained in the following section.

Constants

If you use symbols whose value will not change during the entire course of program execution, such symbols should be declared as constants by means of the keyword Const . You can give a data type to a constant just as with normal variables:

 Const maxsize = 3 Const Pi2 As Double = 1.570796327 'Pi/2 

In VBA there are countless constants already defined. In addition to the values True and False and the Variant values Null and Empty , there are various other values that can be used for setting properties or for evaluating methods. These constants begin with the letters vb (for Visual Basic constant) or xl (for Excel constant). The constant Pi is defined only as a method of Application and therefore must be written in the form Application.Pi .

Fields

Fields are lists of variables of the same name that are referenced by one or more index values. Fields are always used when several similar pieces of information (for example, the entries in a matrix) are to be manipulated.

Dimensioning Fields

Before a field can be used, it has to be defined. For this the command Dim is used, where after the field name the greatest permitted index is given in parentheses. The data type of the field is given as in the case of variables with a label or with the keyword As .

Note  

In the case of large fields you should think carefully about which data type is required.When you give no data type, VBA automatically selects Variant variables, which require by far the most space.With a field of 1000 elements it makes a significant difference whether 2 or 16 bytes per element are required.

 Dim a(10) As Integer 

Access to a field is always accomplished by giving an index. The example below also demonstrates that two instructions can be given on the same line if they are separated by a colon .

 a(4) = 10: a(5) = a(4)/2 

The index must lie within the range 0 to max_index (unless you select Option Base 1 ; see below). With Dim a(10) , then, a field with eleven elements is generated. If you wish, you can set the range of the field within an arbitrary interval, such as between “5 and +7:

 Dim a(-5 To 7) As Integer 

Visual Basic also permits the defining of multidimensional fields, such as in the following:

 Dim a(10, 20) As Integer 

This defines a field with 11 times 21 elements. With multidimensional fields, too, indices can be given as arbitrary intervals.

With the instruction Option Base 1 at the start of a module you make the index 0 impermissible. All fields will thereby become a bit smaller. Option Base has no influence on the indices of enumeration methods predefined by Excel. (Usually, the smallest index is in any case 1.)

Dynamic Fields

Visual Basic also supports fields whose size can vary during program execution. Such fields must first be dimensioned without explicit indices, as in the following example:

 Dim a() As Integer 

At the place in a program where the field is required to have a certain size, the command ReDim is given, as in the following example:

 ReDim a(size) 

The size of the field can later by changed with a further ReDim command. If you append the keyword Preserve , then the contents of the field are preserved:

 ReDim Preserve a(size + 10) 

Fields can be defined to be arbitrarily large and to have arbitrarily many dimensions. The only limitation comes from the amount of memory available.

Deleting Fields

The instruction Erase deletes the contents of the elements of static fields (that is, values are reset to zero, strings to the empty string, Variant variables to Empty ). With dynamic fields Erase deletes the entire field, and the reserved memory is freed. Before further use the field must be redimensioned via ReDim .

Index Range

The functions LBound and UBound return the smallest and greatest permitted index of a field. In the case of multidimensional fields the dimension whose index bound is to be returned must be given in the optional second parameter. An example of the application of these two functions appears in the next section, where, among other things, the passing of fields to procedures is handled.

Data Fields

As if normal fields weren't enough, Microsoft has promoted in VBA the concept of the "data field." Data fields are stored internally in individual Variant variables, even though they outwardly behave like fields. Many operations are possible only with normal fields, others only with data fields, and others with both types. There are no transformation functions to mediate between the two types of fields.

Data fields are created with the command Array , in which the individual field elements are listed. The Array expression is then linked to the Variant variables. The first element has, depending on the setting of Option Base , the index 0 or 1.

In practice, data fields have the advantage over normal fields that they are easier to initialize. With normal fields every element has to be defined individually, for example a(0)=1: a(1)=7: a(2)=3 . But with data fields one can simply define a= Array(1, 7, 3) . The keyword Array cannot, alas, be used for defining normal fields.

 Dim x x = Array(10, 11, 12) Debug.Print x(1)          ' returns 11 

In the example above, x actually represents a Variant field. In contrast to a normal field, which is declared with Dim x(2) , here x can be passed as a field to a procedure without an empty pair of parentheses being given.

Data fields (again in contrast to normal fields) can also be used as parameters for many Excel methods. In the first example that follows the worksheets declared as a data field are selected, while in the second example four adjacent cells of "Table 1" are filled with values:

 Sheets(Array("Table1", "Table2", "Table3")).Select Sheets("Table1").[a1:d1] = Array("abc", "def", 1, 4) 

Note  

It is not always quite clear when a data field is supported and when it is not. If you were to replace [a1:d1] by [a1:a4] in the previous example, that is, to change four adjacent cells, the definition would no longer function! The correct instruction would now be as follows:

 Sheets("Table1").[a1:a4] = _   Array(Array("abc"), Array("def"), Array(1), Array(4)) 

Thus a two-dimensional (nested) data field is required. In this case it is easier to fill in the fields individually.

Pointer  

As the above examples have already indicated, data fields are suitable for, among other things, efficiently transferring midsize ranges of cells between worksheets and program code. (It is an order of magnitude faster than accessing each individual cell !) More information about this can be found in Chapter 5.

Syntax Summary

VARIABLE TYPES (DATA TYPES)
$	String	character string
%	Integer	whole number ( ˆ’ 32768 to +32767)
&	Long	whole number ( ˆ’ 2^31 to +2^31)
!	Single	floating point number with 8 significant digits
#	Double	floating point number with 16 significant digits
@	Currency	fixed point number with 15 places before the decimal point and 4 after
	Date	date and time value
	Boolean	true or false
	Object	pointer to an object
	Variant	arbitrary data

DECLARATION OF VARIABLES AND CONSTANTS

Option Explicit

Dim var1, var2%, var3 As type

Const const1, const2#, const3 As type

PREDEFINED CONSTANTS
True	Empty	vbXxx
False	Null	xlXxx

USING VARIANT VARIABLES
IsNumeric(variable)	test whether conversion to a number is possible
IsDate(variable)	test whether conversion to a date or time is possible
IsObject(variable)	test whether is a pointer to an object
IsError(variable)	test whether is an error value
IsEmpty(variable)	test whether is empty
IsNull(variable)	test whether is not initialized
VarType(variable)	numerical value representing the data type
TypeName(variable)	character string describing the data or object type

CUSTOM DATA TYPES

  Type newtype  element1  As type  element2  As type  ...  End Type  

FIELDS
Option Base 1	smallest allowed index is 1 (instead of the default 0)
Dim field1(5), field2(10, 10)	one- and two-dimensional fields
Dim field(-3 through 3)	field with negative indices
Dim field()	temporarily empty field
Redim field4(10)	dynamic redimensioning
Redim Preserve field4(20)	as above, but data are not erased
Erase field()	erases the field
LBound(field())	returns the smallest permitted index
UBound(field())	returns the largest permitted index
L/UBound(field(), n)	as above, but for the n th dimension

DATA FIELDS
Dim x	normal variant variable
x = Array(x1, x2, )	definition