which denotes the Range A1:C5 in Sheet1 of Book1. (Object names in VBA
must start with a letter, may contain up to 40 letters, numbers and/or under-
scores, but can contain no spaces or punctuation marks. This is why Sheet1
and Book1 are written withoutspaces.) Certain names are automatically
interpreted by VBA as object names, and are then capitalized by the VBA
editor as shown here, and printed in blue. It is good practice to type them in
lower case, and to let the VBA editor do the capitalization because, when the
editor does that, you will know that you are using a recognized name. The
same applies to properties and methods.
When there is no ambiguity, the more inclusive objects need not be enu-
merated. For example, when only one sheet is open, we can just use
or, when the range was earlier speciﬁed by highlighting it, simply as
Properties and methods
Objects can have speciﬁc propertiesthat can be read or assigned, such as
myArray = Range("A1:C5").Value
and methods, which are actions aﬀecting the object, such as changing its
value or its appearance, reading its address or its formula, copying or clear-
ing the contents, as in
Just as objects can contain many other objects, a speciﬁc object can have
several properties and methods. For instance, a cell can have as properties
an address, a formula, and a value, and as methods we can select it, clear it,
copy it, and so on.
The above illustrate the general structure of object-related VBA instruc-
tions: they form a sequence of increasingly more speciﬁc objects, separated
by periods, followed by either a property or a method. Objects are like
nouns, they don’t doanything. Properties again don’t do anything. Just as in
normal language, a complete sentence requires a noun, or an implied noun.
Among the objects, properties, and methods, only the last takes any action.
For that reason, a list of objects with or without a property does not make a
line of program code: they are like a series of nouns without a verb, i.e., they
make an incomplete sentence. Only the last two examples shown, myArray
= Range("A1:C5").Value e and Selection.ClearContents, lead to
action by the program, and can stand alone as a line of code, the ﬁrst
because it assigns the object to a name (myArray), the second because it
includes a method (ClearContents).
On the whole, VBA commands use self-explanatory English words, and are
Some useful macros
therefore easy toread. While this enumerative quality of VBA tends to make
it very readable, it can also lead to rather wordy code. Moreover, you may
experience diﬃculties with VBA when you want to writecode but don’t know
the exact term to use. Excel provides several help ﬁles to get you past this
initial hurdle. The speciﬁc examples given in this chapter are meant to famil-
iarize you with the most useful terms.
VBA recognizes three diﬀerent data types: textual, numerical, and logical.
There are two types of numbers: general numbers and integers, and both
can be represented in single or double precision. Note that the spreadsheet
always uses double precision, but that VBA needs to be told speciﬁcally to do
so, otherwise the macro computes in single precision instead.
Arrays can be specified by size, as in myRow(0 To 2)or myArray(1 To
3, 1 To 10), or can be left As Variant. Avoid ambiguous dimensioning
such as myRow(2)which has 3 elements, with indices 0, 1, and 2, unless
Option Base 1 is used, which deletes use of the index 0, in which case
myRow(2)has only two elements, 1 and 2. Arrays defined As Variant always
start with index 1, regardless of the Option Base statement.
Vectors and arrays are best defined first by type (or only by name), then re-
dimensioned just before use, once their sizes are known:
Dim Data As Variant, Magnitude(), index() As Single
Redim Magnitude (1 To N) As Double
Redim index (nTop to nBottom)
Redim Data (1 To iRows, 1 To iColumns)
Text can be any combination of letters, numbers, punctuation marks and
spaces. Text includes the names of variables used in computations. Here are
some examples of text:
Please enter a number between 1 and 100
Names of variables must begin with a letter, and form an unbroken string
of no more than 255 letters, numbers, and/or the underscore, _, so that only
the ﬁrst two items in the above list can serve as such. Names of variables
cannot be identical to VBA instructions, such as Value, Address, Range, but
modified names such as Xvalue, newAddress, or myRange can be used.
Capital letters can be used to make long strings of letters more easily
10.12 An overview of VBA
VB.NET PDF - Convert PDF with VB.NET WPF PDF Viewer
Create PDF from Word. Create PDF from Excel. Create PDF from PowerPoint. Create PDF from Tiff. Create PDF from Text. PDF Export. Convert PDF to Word (.docx copy highlighted text from pdf; can't copy text from pdf
readable, as in myErrorEstimate. Alternatively one can use the underscore
as a word separator, as in my_name or X_Y_value.
VBA does not require that the names of variables be dimensioned,
although doing so is good practice. Note that a variable name containing a
typo will be considered as the name of a valid new variable. Typos can be
caught by including at least one capital in each variable name, and by typing
all names in lower case. When they have been dimensioned properly, even if
only by name, the VBA editor will substitute the capitals, and failure to do so
will identify errand variable names. You can also use Option Explicit, which
requires you to dimension all variables by name, and then will catch mis-
spelled names. If you use Option Explicit, it must be the first line in the
macro module: Option Explicit. In that case you must specify the names
of the variables used in dimension statements, e.g., Dim Jones, or Dim Y, Z.
Specifying their nature(e.g., Dim Jones As String, or Dim Y as Single,
Z As Double) is good programming practice, but is not required.
Unspecified variables are considered as Variants, which are most flexible,
but which also take up most space. Large macros will run faster when, e.g.,
integers and text strings are specified as such. Text used as a string of
symbols (as in a ﬁxed cell address or range, such as B17:C20) or in a message
(e.g., Hello!) is often placed in quotation marks.
Logicals are restricted to two values:True and False.
Expressions cannot be used as stand-alone lines, but must be part of
another structure in a VBA program. Expressions can yield numerical values,
or they can return a logical, as in
pH < 7
'True when pH < 7, otherwise False
Statements are complete program instructions, i.e., they are lines of code
that can be executed as such. The following are examples of statements, with
explanatory comments where needed:
x = 1
y = A*exp(-k*t)
'VBA recognizes this as a complete instruction!
Some useful macros
'assuming the existence of a macro of this name
Dim Array As Variant
The last example, Dim, is a particular type of statement that requires some
comment. Dimensioning is not required in VBA; in order to keep matters as
simple as possible, we have therefore not used dimensioning in the simple
examples of section 10.1. Moreover, if one elects to use dimension statements,
these can be placed just before the dimensioned parameter is used; this tends
to improve program readability. With all this freedom, why use dimension
statements at all? Apart from the usual reasons of eﬃcient memory use, and
the occasional need to specify double precision (see section 10.1d), it is a good
guard against typos, as mentioned in section 10.12c.
Objects can be deﬁned in VBA as explained above, or they can simply be
highlighted (activated, clicked on). In that case there is no need to recite a
hierarchy of ever-smaller objects containing the one we mean. Instead, we
merely need to identify the selected region as the wanted one. The following
objects refer to such highlighted regions:
ActiveWorksheet'This can be a worksheet,
'a module sheet, or a dialog sheet
'This refers speciﬁcally to the topmost
'dialog sheet, and can therefore differ
'from the ActiveWorksheet
For our purposes, cells and ranges are the most useful descriptors, which
is why they are discussed below in more detail.
We have already seen how cells can be speciﬁed, in absolutecoordinates, e.g.
'Provided a Worksheet is active
'Provided the cell has been highlighted
Note that the notations C11 and (11,3) use diﬀerent conventions:
Range("C11")states the column ﬁrst, while Cell(11,3) identiﬁes the
very same cell but starts with the row number. The Cell(
notation is convenient for, say, generating the tables of multiplication from
1 to 10:
10.12 An overview of VBA
For r = 1 To 10
For c = 1 To 10
Cells(r,c).Value = r*c
The instruction Offset(
) is used to specify relative
addresses. For example, Range("C11").Offset(3,-1).Select selects
cell B14, which now will become the active cell. The following macro illus-
trates relative addressing:
ActiveCell.Value = "Center"
ActiveCell.Offset(-2,0).Value = "N"
ActiveCell.Offset(-2,1).Value = "NE"
ActiveCell.Offset(0,1).Value = "E"
ActiveCell.Offset(2,1).Value = "SE"
ActiveCell.Offset(2,0).Value = "S"
ActiveCell.Offset(2,-1).Value = "SW"
ActiveCell.Offset(0,-1).Value = "W"
ActiveCell.Offset(-2,-1).Value = "NW"
Note that, in the latter case, the active cell remains at the center. A fool-
proof macro would of course have to make sure that the active center is
located neither in the top two rows nor in the ﬁrst column of the spread-
Among the properties of a single cell are its address, the numerical valueit
contains, and (if applicable) the formulagenerating that value. Other cell
properties are typographic: cell height and width, font type, size, color, etc.
All of these properties can be read, set, copied, erased, and so on, by the
appropriate methods. Here is a sampling. The ﬁrst macro reads the value in
the active cell, multiplies it by 3, and returns that new value to the cell. It
then puts 19, √X=4 and 3+2×19=41 in the three cells to its right. The
second macro changes the cell appearance by using centered numbers
showing three decimal places, presented in bold, underlined blue 10-point
Times New Roman characters.
X = 16
Y = ActiveCell.Value
newY = Y * 3
ActiveCell.Value = newY
ActiveCell.Offset(0,1).Value = 19
ActiveCell.Offset(0,2).Value = Sqr(X)
ActiveCell.Offset(0, 3).Value = 3 + 2 * ActiveCell.Offset(0, 1).Value
Some useful macros
Selection.HorizontalAlignment = xlCenter
Selection.NumberFormat = "0.000"
Selection.Font.Bold = True
Selection.Font.Italic = False
Selection.Font.Name = "Times New Roman"
Selection.Font.Size = 10
Selection.Font.Underline = xlSingle
Selection.Font.ColorIndex = 32
Note that the VBA instruction to take a square root is Sqr, whereas in the
spreadsheet the same operation requires four letters: Sqrt.
In VBA, a Range can be any set of cells, from a single cell or a block of cells to a
collection of such blocks. The following are examples of Range speciﬁers:
In the second example, note that a Range need not be contiguous, and
that the quotation marks are around the entire list rather than around indi-
vidual cells or blocks. In the next two lines, the entire Row17 and the entire
Column4 (i.e., column D) respectively are identiﬁed. The ﬁnal examples
show the intersectionsof two ranges, by selecting those parts of row 17 or
column 4 that lie insideblock C11:E101. These, then, specify C17:E17 and
If there is any ambiguity about the worksheet or workbook where the
range is to be found, these must be speciﬁed, as in
Because Range is such a common object in VBA, it can be combined with
many diﬀerent properties and methods. You can Activate a Range, Clear it,
Copy it, Cut it, Delete it, and so on. You can also ask for the Address of a
Range, for its Areas, Cells, Columns, Count, EntireRow, Formula, Format,
etc. You can also refer to entire regions of the worksheet with instructions
such as CurrentRegion or UsedRange. For the precise deﬁnitions of these,
and examples of their use, refer to the Visual Basic Help ﬁle, which comes
with Excel but may have to be installed if it was left out during the initial
10.12 An overview of VBA
Subroutines are complete sections of computer code that can calculate
parameter values and/or take some spreadsheet actions. Subroutines can
be called from other computer code, including from other subroutines.
However, only the special subroutines called macros can be called directly
from the spreadsheet. Subroutines have the following syntax:
As all object names, the subroutine name must start with a letter, and
consist of up to 40 letters, numbers and/or underscores. Empty spaces are
not allowed, nor are punctuation marks. Separate words are therefore often
strung together, as in WeightedLeastSquares, often with caps to indicate
where spaces would have been. You can also use the underscore _ as a separ-
The arguments are a list of (zero, one, or more) parameters to be passed
intoor out ofthe subroutine, except when the parameters in question have
been declared Public, see section 10.1g. We have already encountered
numerous subroutines in sections 10.5, 10.6, and 10.7. For instance, in
section 10.7 we used the subroutines WLS0( ) and WLS1( ) to assign a
numerical value to p(of either 0 or 1) for use in the subsequent subrou-
tine WeightedLeastSquares(p), while the latter in turn farmed out its
matrix operations to the subroutines Transpose(M1,r1,c1,Mout),
Multiply(M1,r1,c1,M2,c2, Mout), and Invert(M1,r1,n,Mout). The use of
Public parameters was illustrated in section 10.9.
Macros are special subroutines that can be called directly from the spread-
sheet, either through T
acro or (when pre-arranged) by direct selec-
tion from a menu, a toolbar, or with a shortcut key. We have already
encountered numerous macros in this chapter; they all have the syntax:
While macros cannot have arguments (hence the empty brackets behind
their names), they can have subroutines. Sometimes macros are therefore
used as “drivers” for more substantial subroutines. For example, the follow-
ing subroutine speciﬁes the value of the parameter i, then calls the main
subroutine to do the work:
Dim iSign As Integer
Some useful macros
iSign = -1
This allows the main subroutine to be called conveniently by two (or
more) diﬀerent drivers, while responding slightly diﬀerently because of the
diﬀerent parameter values passed on to the subroutine. The same approach
was also used in section 10.6 to distinguish between convolution and decon-
volution, and in section 10.7 to select either the general weighted least
squares or that which forces the curve through the origin.
Functions are code fragments that return a speciﬁc numerical value. The
statements, including name = expression
As with subroutines, the list of arguments is optional, but the brackets are
required even if no argument is passed. One of the statements must deﬁne
the value to be returned, and assign it to the name of the function. For
example, Pi( ) calculates the value of
, Fact(14) computes the factorial
×2×1, and BesselK(3,1) provides the value of the
modiﬁed Bessel function of order 1 and argument 3, i.e., K
There are two types of functions: those provided with Excel, and those
written by the user. Tables 1.6-2 through 1.6-6 list a number of the mathe-
matical functions provided in Excel. In chapter 9 we already encountered
user-deﬁned functions, which act like macros, except that they pertain to a
single cell, and as output can only produce a numerical value.
A message box is primarily used to provide textual and/or numerical infor-
mation to the user; it can also carry logical information back to the program
that issued it. Message boxes ﬁgured prominently in sections 10.1 and 10.2.
In their most basic form they have the syntax
but they can also carry information, as in
MsgBox "The value of x is " & x
and their general form is
buttons and/or icons
Cancel;Abort,Retry,orIgnore;and Retry orCancel.The iconsdenotea
10.12 An overview of VBA
MsgBox "Do you want to go on?", vbYesNo + vbQuestion, _
The prompt or message will appear inside the box, the title in the blue box
A message box can also be used as a function, as in
Answer = MsgBox ("Beep?", vbYesNoCancel + vbQuestion)
If Answer = vbYes Then Beep
Apart from their obvious uses in ﬁnished macros and subroutines,
message boxes are convenient during program development for debugging,
by displaying intermediate parameter values, and for enforcing a pause in
Input boxes are meant to provide textual and/or numerical information to
the program. Apart from a prompt, as in the Message Box, they also have a
window, in which information can be typed. Several examples were already
given in section 10.2. The syntax for the input box functionis
of which only the prompt is required. The optional default displays a default
value in the window, that will be used unless it is modiﬁed. The dots denote
several additional, optional speciﬁers of, e.g., the position of the box on the
screen. “Type” speciﬁes, in a numerical code, what kind of information is
expected: 0 indicates a formula, 1 a number, 2 a text string, 4 a logical value
(i.e., True or False), 8 a cell reference (i.e., an address or address range), 16 an
error value, and 64 an array of values. More than one input format can be
indicated by specifying several types simultaneously, separated by a plus-
sign, as in Type:=2+4.
An input box is typically used as a function, as in
Password = InputBox ("Please enter your password here",
vAnswer = InputBox ("Enter the multiplier here")
Set myCell = InputBox (Prompt:="Select a cell", Type:=8)
which sets the value of myCell to that of the cell reference entered in the
window of the Input Box. Sometimes the speciﬁc numerical value of a string
must be extracted from it with the Val function, as in
myVal = Val("123")
Some useful macros
Documents you may be interested
Documents you may be interested