Excel 2010 Bible

Chapter 39: Introducing Visual Basic for Applications

817

that this workbook has one worksheet, named Summary. Your VBA code can refer to the

Summary sheet in any of the following ways:

Workbooks(“Sales.xlsx”).Worksheets(“Summary”)

Workbooks(1).Worksheets(1)

Workbooks(1).Sheets(1)

Application.ActiveWorkbook.ActiveSheet

ActiveWorkbook.ActiveSheet

ActiveSheet

The method that you use is determined by how much you know about the workspace. For exam-

ple, if more than one workbook is open, the second or third method is not reliable. If you want to

work with the active sheet (whatever it may be), any of the last three methods would work. To be

absolutely sure that you’re referring to a specific sheet on a specific workbook, the first method is

your best choice.

Methods

Objects also have methods. You can think of a method as an action taken with an object. For example,

Range objects have a Clear method. The following VBA statement clears a Range, an action that is

equivalent to selecting the Range and then choosing Home ➪ Editing ➪ Clear ➪ Clear All:

Range(“A1:C12”).Clear

In VBA code, methods look like properties because they are connected to the object with a “dot.”

However, methods and properties are different concepts.

Variables

Like all programming languages, VBA enables you to work with variables. In VBA (unlike in some

languages), you don’t need to declare variables explicitly before you use them in your code

(although doing so is definitely a good practice).

Note
If your VBA module contains an Option Explicit statement at the top of the module, then you must
declare all variables in the module. Undeclared variables will result in a compile error, and your procedures
will not run. n

In the following example, the value in cell A1 on Sheet1 is assigned to a variable named Rate:

Rate = Worksheets(“Sheet1”).Range(“A1”).Value

You then can work with the variable Rate in other parts of your VBA code. Note that the variable

Rate is not a named range, which means that you can’t use it as such in a worksheet formula.