1
Introduction to Visual BASIC for Applications: Part II
Scope of Variables
An important consideration when writing programs is the scope of the variables, functions and
subroutines. When a variable is defined inside a function or subroutine, it exists only inside that function or
subroutine. When that function or subroutine is not active, the value of the variable is not retained or
accessible by other functions or subroutines. We sometimes want variables to retain their values and be
available to other functions or subroutines. To do this we declare them outside the function or subroutine so
they become available to any function or subroutine in the Module. If certain variables need to be available
to functions and subroutines outside the Module, they can be declared using the key word Public, but this
is usually not a good idea. Why?
Becoming Familiar with the Use of Objects
Until this point you have learned basic aspects of the use of macros and of programming with VB. Now
it is worthwhile to turn our attention to some “big picture” aspects of programming with VB, particularly the
use of objects. When writing applications using VB in Excel it is helpful to think in terms of objects. Excel
objects include the Excel application itself, a workbook, a worksheet in a workbook, a range in a worksheet,
a chart sheet, a chart on a chart sheet, and many other components. These objects (more than 100) are
organized following an object hierarchy (Excel’s object model), which can be manipulated by using Visual
Basic for Applications (VBA).
In Excel’s object model, as may be the case in other applications, objects are containers for other objects.
Excel is an object called “Application” and contains other objects such as “Workbook” objects. The
contained object is referred to as a member object. The position of a member object in a hierarchy is
specified using a period as a separator between the container object and the member. Thus, a workbook
named “Mybook.xls” can be referred to as
Application.Workbooks(“Mybook.xls”)
Similarly, the worksheet “Sheet1” in such a workbook can be referred to as
Application.Workbooks(“Mybook.xls”).Worksheets(“Sheet1”)
If the specific reference to an object is omitted, the current active object is used as default. For example,
if workbook “Mybook.xls” and worksheet “Sheet1” are active, you can directly refer to a given cell to store
a value as, e.g., [A5] = 100.5. If you are writing code on a module associated with a given workbook, this
workbook is then active by default. You can activate “Sheet1” as shown before by simply stating
Woksheets(“Sheet1”).Activate.
In addition to Excel objects that you can use and manipulate, you can create your own objects, which
provides a convenient way to write your programs. All objects are defined by a class, which is a template
from which objects can be created. A “class module” is a special type of VB module that you can insert into
a project. To do this, choose “Insert/Class Module” from the VB editor. The code that you write in a Class
Module cannot be executed directly. You must “instantiate” an object of this class using a regular Module
sheet.
One characteristic of objects is that they may have Properties, Methods, and Events. Object properties
are attributes that describe the object. They represent the data of an object (although they are not the data
