Appendix B. VBA Basics

This appendix provides a short tutorial on some basic VBA programming structures. Clearly, this isn’t designed to teach you everything, and you should be familiar with basic programming concepts, such as variables and objects, but it should give you some information to help you get through the tasks in this book.

Dim and Set Statements

The Dim statement is used to declare a variable and assign it to a particular type. If the variable is an object, use the Set statement to assign an object to the variable. If the variable is not an object, use the variable, an equal sign, and the value. The following code example shows how to declare a variable called xlapp and set it to a new Excel application:

Dim xlapp as Excel.Application
Set xlapp = New Excel.Application


There are several types of loops that you can use in VBA. Here are three that I use most often.

For...Next Loop

The For...Next loop is used to go through a set of numbers and execute a block of code through each iteration. By default, VBA increments by 1 each time it comes to a Next statement. However, you can use Step to change the increment. The following example goes from 0 to 10 in increments of 2 and writes the value of the number to the Immediate Window:

Dim x As Integer
For x = 0 To 10 Step 2
  Debug.Print x
Next x

For Each...Next Loop

This loop cycles through an array or collection. To use this loop, the variable that you use either has to be a type Variant, Object, or a specific type of object (such ...

Get Integrating Excel and Access now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.