VBA in a Nutshell

VBA IN A NUTSHELL

Here is a quick summary of what VBA is all about. More detailed description will follow.

You perform actions in VBA by writing (or recording) code in a VBA module. You view and edit VBA modules using the Visual Basic Editor (VBE).

A VBA module consists of Sub procedures. A Sub procedure is computer code that perform some action on or with objects (discussed in a moment).

The Sub procedure, for example, displays the result of 1 plus 1:
```
Sub Test( )
    Sum = 1 + 1
    MsgBox "The answer is " & Sum
End Sub
```
A VBA module can also have Function procedures.A Function procedure returns a single value. It can be called from another VBA procedure or used as a function in a worksheet formula. The following Function procedure accepts two numbers (arguments) and returns the sum of those values.
```
Function AddTwo(arg1, arg2)
    AddTwo = arg1 + arg2
End Function
```
VBA manipulates objects. Excel provides more than 100 objects, such as workbooks, worksheets, cell ranges and charts, that you can manipulate.
Objects are arranged in a hierarchy. Objects act as containers for other objects. At the top of the object hierarchy is Excel, which is an object called Application, and it can contain other objects such as workbook objects and CommandBar objects. The workbook object contains objects such as worksheet objects and chart objects. A worksheet object in turn contains Range objects and PivotTable objects.

Objects of the same type form a collection. For example the Worksheets collection consists of all the worksheets in a particular workbook. Collections are themselves objects.

You refer to an object by specifying its position in the object hierarchy, using a dot as a separator. For example, you refer to Range A1 in worksheet sheet1 in workbook Book1.xls as
```
Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")
```
If you omit specific reference, Excel uses the active objects. For example, if Book1 and Sheet1 are active, then the preceeding reference can be simplified to
```
Range("A1")
```
Objects have properties. You can think of a property as a setting for an object. For example, a Range object has such properties as Value and Address. A Chart object has such properties as HasTitle and Type. You can use VBA to determine object properties and to change properties.
You refer to a property of an object by combining the object name with the property name, separated by a period. For example you can refer to the value in cell A1 on Sheet1 by
```
Worksheets("Sheet1").Range("A1").Value
```
You can assign values to variables.A variable is a named element that stores data. You can use variables to store such things as values, text or property settings. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following statement:
```
Interest = Worksheets("Sheet1").Range("A1").Value
```
Objects have methods. A method is an action Excel performs with an object. For example, one of the methods for a Range object is ClearContents. This method clear the contents of the range.
You specify a method by combining the object with the method separated by a dot. For example, the following statement clears the contents of cell A1:
```
Worksheets("Sheet1").Range("A1").ClearContents
```
VBA includes all the constructs of modern programming languages, including arrays, branches and loops.