The Basics of VBA | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Before I get into the meat of things, I suggest that you read through the material in this section to get a broad overview of where I'm heading. These are the topics that I cover in the remainder of this chapter.

Following is a quick-and-dirty summary of what VBA is all about:

Code: You perform actions in VBA by executing VBA code.

You write (or record) VBA code, which is stored in a VBA module.
Module: VBA modules are stored in an Excel workbook, but you view or edit a module by using the Visual Basic Editor (VBE).

A VBA module consists of procedures.
Procedures: A procedure is basically a unit of computer code that performs some action. VBA supports two types of procedures: Sub procedures and Function procedures.
- Sub :A Sub procedure consists of a series of statements and can be executed in a number of ways.
  
  Here's an example of a simple Sub procedure called Test : This procedure calculates a simple sum and then displays the result in a message box.
```
 Sub Test()     Sum = 1 + 1     MsgBox "The answer is " & Sum End Sub 
```
- Function : A VBA module can also have Function procedures. A Function procedure returns a single value (or possibly an array). A Function can be called from another VBA procedure or used in a worksheet formula.
Here's an example of a Function named AddTwo :
```
 Function AddTwo(arg1, arg2)     AddTwo = arg1 + arg2 End Function 
```
Objects: VBA manipulates objects contained in its host application. (In this case, Excel is the host application.)

Excel provides you with more than 100 classes of objects to manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a chart, and a drawn rectangle. Many more objects are at your disposal, and you can manipulate them by using VBA code.

Object classes are arranged in a hierarchy.

Objects can act as containers for other objects. For example, Excel is an object called Application , and it contains other objects, such as Workbook and CommandBar objects. The Workbook object contains other objects, such as Worksheet objects and Chart objects. A Worksheet object contains objects such as Range objects, PivotTable objects, and so on. The arrangement of these objects is referred to as Excel's object model.
Collections: Like objects form a collection.

For example, the Worksheets collection consists of all the worksheets in a particular workbook. The CommandBars collection consists of all CommandBar objects. Collections are objects in themselves .
Object hierarchy: When you refer to a contained or member object, you specify its position in the object hierarchy by using a period (also known as a dot ) as a separator between the container and the member.

For example, you can refer to a workbook named Book1.xlsx as
```
 Application.Workbooks("Book1.xlsx") 
```
This refers to the Book1.xlsx workbook in the Workbooks collection. The Workbooks collection is contained in the Excel Application object. Extending this to another level, you can refer to Sheet1 in Book1 as
```
 Application.Workbooks("Book1.xlsx").Worksheets("Sheet1") 
```
You can take it to still another level and refer to a specific cell as follows :
```
 Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1") 
```
Active objects: If you omit a specific reference to an object, Excel uses the active objects.

If Book1 is the active workbook, the preceding reference can be simplified as
```
 Worksheets("Sheet1").Range("A1") 
```
If you know that Sheet1 is the active sheet, you can simplify the reference even more:
```
 Range("A1") 
```
Objects properties: Objects have properties. A property can be thought of as a setting for an object. For example, a range object has properties such as Value and Name . A chart object has properties such as HasTitle and Type . You can use VBA to determine object properties and also to change them.

You refer to properties by combining the object with the property, separated by a period. For example, you can refer to the value in cell A1 on Sheet1 as
```
 Worksheets("Sheet1").Range("A1").Value 
```
VBA variables : You can assign values to VBA variables. Think of a variable as a name that you can use to store a particular value.

To assign the value in cell A1 on Sheet1 to a variable called Interest , use the following VBA statement:
```
 Interest = Worksheets("Sheet1").Range("A1").Value 
```
Object methods : Objects have methods. A method is an action that is performed with the object. For example, one of the methods for a Range object is ClearContents . This method clears the contents of the range.

You specify methods by combining the object with the method, separated by a period. For example, to clear the contents of cell A1 on the active worksheet, use this:
```
 Range("A1").ClearContents 
```
Standard programming constructs: VBA also includes all the constructs of modern programming languages, including arrays, looping, and so on.
Events: Some objects recognize specific events, and you can write VBA code that is executed when the event occurs. For example, opening a workbook triggers a Workbook_ Open event. Changing a cell in a worksheet triggers a Worksheet_Change event.

Believe it or not, the preceding section pretty much describes VBA. Now it's just a matter of learning the details.

An Analogy

If you like analogies , here's one for you. It might help you understand the relationships between objects, properties, and methods in VBA. In this analogy, I compare Excel with a fast-food restaurant chain.

The basic unit of Excel is a Workbook object. In a fast-food chain, the basic unit is an individual restaurant. With Excel, you can add workbooks and close workbooks, and the set of all the open workbooks is known as Workbooks (a collection of Workbook objects). Similarly, the management of a fast-food chain can add restaurants and close restaurants - and all the restaurants in the chain can be viewed as the Restaurants collection - a collection of Restaurant objects.

An Excel workbook is an object, but it also contains other objects, such as worksheets, charts , VBA modules, and so on. Furthermore, each object in a workbook can contain its own objects. For example, a Worksheet object can contain Range objects, PivotTable objects, Shape objects, and so on.

Continuing with the analogy, a fast-food restaurant (like a workbook) contains objects such as the Kitchen , DiningArea , and Tables (a collection). Furthermore, management can add or remove objects from the Restaurant object. For example, management can add more tables to the Tables collection. Each of these objects can contain other objects. For example, the Kitchen object has a Stove object, VentilationFan object, Chef object, Sink object, and so on.

So far, so good. This analogy seems to work. Let's see whether I can take it further.

Excel objects have properties. For example, a Range object has properties such as Value and Name , and a Shape object has properties such as Width , and Height . Not surprisingly, objects in a fast-food restaurant also have properties. The Stove object, for example, has properties such as Temperature and NumberofBurners . The VentilationFan has its own set of properties ( TurnedOn , RPM , and so forth).

Besides properties, Excel's objects also have methods, which perform operations on objects. For example, the ClearContents method erases the contents of a Range object. An object in a fast-food restaurant also has methods. You can easily envision a ChangeThermostat method for a Stove object, or a SwitchOn method for a VentilationFan object.

With Excel, methods sometimes change an object's properties. The ClearContents method for a Range changes the Range Value property. Similarly, the ChangeThermostat method on a Stove object affects its Temperature property.

With VBA, you can write procedures to manipulate Excel's objects. In a fast-food restaurant, the management can give orders to manipulate the objects in the restaurants. (" Turn on the stove and switch the ventilation fan to high.") Now is it clear?