Learning Visual Basic for Applications

While VBA provides a lot of power to manipulate Excel it can be quite frustrating learning how to build appropriate macros.

In order to learn VBA for Excel it is useful to have an understanding of how VBA interacts with Excel.

VBA thinks of Excel as a set of objects.

Excel consists of Objects such Worksheets, Ranges, Cells, Charts, Fonts etc.
Each of these objects has a set of properties which in turn have values.

VBA manipulates these objects.

The syntax VBA uses to access these is Object.Property = Value

By entering code in a module using the Visual Basic Editor, VBA can manipulate the objects.

For example two properties of the object Font are bold and size.

The following VBA code manipulates the Font object.
font.FontStyle = true
font.size="14"

Cells on a Worksheet can also be accessed.
For example, the following code

Range("A1").Value = 15
places the value 15 in cell A1.

How do we know what the Excel Objects are called ?

One of the difficulties is using VBA in Excel is knowing the names of the Excel Objects.

How did we know that cell A1 in the example above is known as Range("A1") ?

One way to find out is to use the Macro Recorder to record a suitable macro and examine and edit the code produced.
In this way you can gain an insight into the way VBA works and relates to the Objects that make up Excel.

Recording and examining code is one of the best ways to learn VBA.

The Visual Basic Editor is like any other text editor you can copy and paste the code from one macro to another.

You can then test the effect by running the macro.

Bulding Bigger Macros

Here are a few tips to ease the process and help you learn VBA and build complex macros.

Repeat the steps for the next small task.

When all the steps are working you can cut and paste the individual steps together.

This is best achieved by combining couple of macros together.

Test this macro and repeat the process by adding each additional task in the order required.