1

VBA Cheat-Sheet and Tutorial

Charlie Nuttelman
University of Colorado at Boulder
VBA References to Information in Excel
VBA refers to information in Excel in several ways. These include the Range,
Selection, and ActiveCell
object, the Cells property, and the Offset property. Column and Rows can also be
utilized. Here are
some examples:
 Range(“D22”)  refers to cell D22
 Range(“A1:B5”)  refers to the range A1:B5
 Range(“Pressure”)  refers to the named range “Pressure”
 Range(“D:D”)  refers to entire column D
 Range(“3:3”)  refers to entire row 3
 Range(“A1:B5”).Cells(2,2)  refers to the 2nd row, 2nd column of range A1:B5
 Range(“A1:B5”).Range(“B2”)  also refers to the 2nd row, 2nd column of range
A1:B5
 Range(“D22”).Offset(3,1)  refers to the cell that is 3 rows down, 1 column to
the
right of cell D22
 Cells(4,5)  refers to the 4th row, 5th column, or simply cell E4
 Columns(“D:F”)  refers to columns D, E, and F
 Columns(2)  refers to column B
 Rows(2)  refers to row 2
 Rows(“2:3”)  refers to rows 2 and 3
 Selection.Cells(3,4)  refers to the cell that is in row 3, column 4 of the
current
selection
 ActiveCell  refers to the active cell
 ActiveCell.Offset(-1,1)  refers to the cell that is 1 row up and one column to
the
right of the active cell
 ActiveCell.Offset(-1,1).Activate  activates the cell that is 1 row up and one
column to the right of the active cell. Note that .Select and .Activate should be
avoided, there
are normally other ways to place information into cells rather than selecting or
activating them
first.
Moving Data Between Excel & VBA
There are several ways to move data to and from VBA/Excel.
Importing data from Excel: Let’s say we wanted to define a local variable to VBA,
x, in terms of values in
an Excel spreadsheet. There are a couple ways we can do this and the “VBA
References to Information in
Excel” examples (above) can oftentimes be implemented to do this. Some examples:
2
 x = Cells(2,2).Value  assigns the value in B2 to x (note that .Value is
optional)
 x = ActiveCell.Value  assigns the value in the active cell to x
 x = Range(“A1:B5”).Cells(2,2)  this would assign to variable x the value in the
2nd
row, 2nd column of range A1:B5
 x = Range(“A1:B5”).Value  this would assign to variable x the range A1:B5; NOTE:
in
this case, x must be declared as a variant type since x is an array here
Exporting data to Excel: Moving data from VBA to Excel is essentially the opposite
of importing data.
Some examples assuming we have already defined a local VBA variable y:
 Cells(2,2).Value = y  places the value y into B2
 ActiveCell.Value = y  places y into the active cell
 Range(“A1:B5”).Cells(2,2) = y  places y into the 2nd row, 2nd column of range
A1:B5
Other ways to modify the contents of cells: Here are some other examples in which
we can utilize VBA to
modify the contents of cells:
 Range(“A1:B5”).Value = 23  places 23 in each cell of range A1:B5
 Selection = 10  places a 10 in each cell of the current selection
 Range(“D22”) = 2 * a  multiplies local variable a by 2 and places in cell D22
For Each: The For Each statement in VBA is a nice way to quickly make a change to
the contents of an
array in Excel. The first example below will add 5 to each value in the range
A1:C3. The second example
will change the value in a selection to 9999 if and only if it is equal to 5. This
is a nice way to enable
conditional statements in an array. Importantly, c must be declared as a variant
type.
Sub ForEach()
For Each c In Worksheets("Sheet1").Range("A1:C3")
c.Value = c.Value + 5
Next c
End Sub
Sub ForEachIf()
For Each c In Selection
If c.Value = 5 Then
c.Value = 9999
End If
Next c
End Sub
The second sub here will make the following transformation:
3
Importing Arrays from Excel Into VBA – Fixed size & place
Oftentimes it is necessary to import data in an array format from Excel into VBA.
Maybe you want to bring
in the data and do a calculation on it, etc. When the size and spreadsheet location
are fixed (e.g., cells
C4:F8 are always used and size nor location never change), you should use one of
the approaches outlined
here.
Direct Import of an Array: Arrays can be directly imported into VBA from Excel
using Range objects.
Importantly, the variable array in VBA must be declared as a variant type. In the
following example, if we
wanted to import A1:C3 into VBA, the variable a would have to be declared as a
variant.
Sub GetArray1()
a = Range("A1:C3").Value
End Sub
Also, this approach is not tremendously useful because in order to do calculation
on each component of
array a we could first need to extract each component of the array and re-define as
a single or double
type. For example, the following does NOT WORK and we cannot just create a new
array b by multiplying
array a by 2:
Sub GetArray1()
a = Range("A1:C3").Value
b = 2 * a
End Sub
(This gives us a type mismatch error)
Nevertheless, if we are interested in using single values of an imported array, the
method described above
works well. For example, if we wanted to extract the value in cell (2,2) (row 2,
column 2 of array a) then
we could easily do this and assign it to value c:
Sub GetArray1()
a = Range("A1:C3").Value
c = a(2, 2)
End Sub
We can also use other references including but not limited to Selection (current
selection) and
Range(“Temp”) [if Temp is a named array in Excel, for example], and include the
Offset property if we
wish.
Iteration Import: Nested For... Next loops can be used nicely to import arrays from
Excel and be stored
as local arrays in VBA. Please see the For... Next Loops section later in this
tutorial for more information
about count-controlled iteration. T