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
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 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
Object, or a specific type of object (such ...