Microsoft Excel Power

Lesson 2: The Basics of VBA

Variables

Variables are used to hold certain values that we will work with. There are various types that are available in VBA, the standard numeric and text that you may be familiar with, but also some specific to Excel. Perhaps the most important of these Excel related ones, are variables of type Range. Quite simply, these variables hold a collection of cells. A typical (if there is such a thing) list of variables used in a macro might be the following:

Option Explicit
Dim rng As Range
Dim i As Long
Dim lLastRow As Integer
Dim lLastCol As Integer
Dim Name as String
Dim fVar As Single
Dim GreaterThan5 As Boolean

The texts will explain all about variables, but they are all used in a similar way; you decide what type of data you need to store, choose a meaningful name (this is more important than a meaningful macro name as there are generally many more of them), and then define the variable with a Dim statement. One bit of advice, get into the habit of specifying "Option Explicit" as the first statement in your macro.

The reason for this is that VBA is a very accommodating language in that if it sees a variable that is not defined, it will automatically define it for you (usually as type variant). This is terrific if you wanted a new variable, but not so good if you just misspelled a previously defined variable. This type of mistake is probably the most common error you will make in programming in VBA and is very difficult to spot.

Now you can see from the list that we have the standard variable types along with this Range type. This type of variable is important because most of what we do in Excel relates to a group or Range of cells.

Defining a Range

This is generally the most difficult area for people new to programming in VBA/Excel, it was for me, but once it clicks, you will wonder what all of the fuss was about. A range can be as simple as a singe cell, or a complex pattern of cells. If we wanted to define a range consisting of a single cell, say "A1", then we would use the following;

Range("A1")

This definition creates more of an object than a variable (see the texts for a discussion of objects), and as such it has certain properties and methods. For example, you can find out the value or even set the value with the following:

x = Range("A1").value

Or

Range("A1").Value = "Bill"

I can also select a range of cells using

Range("A1").Select

So, why are ranges important? Well, they define the area or scope over which your actions take place. In general, you will define a range of cells and then perform some action on them.

A more complex range of cells would be defined by

Range("C4:E8")

Both of these are shown in tab {Ranges} in lesson 2.xls.

There is a multitude of ways of referring to a range of cells and we will touch on some of the more useful ones in the next few lessons. Another nice feature of VBA is that having defined a range, you can give it a name, thus making it easier to refer to later in your code.

  1  2  3  4  5