Databases Reference
In-Depth Information
1
Accessing Data in Excel: A
Macro Writer's Perspective
1.1
INTRODUCTION
A macro writer must have an understanding of Excel that is different from that of someone who
utilizes it as a spreadsheet application. The programmer must have a fundamental understanding
of how Excel interacts with its environment, processes information, and accesses information.
Excel utilizes Visual Basic for Applications (VBA) as its macro language. VBA has broader func-
tionality than just programming macros in Excel. It has much of the same functionality as Visual Basic
(VB), the language upon which it was derived. VBA also has the ability to interact with applications
outside of Excel that provide a great deal of flexibility. Examples of such functionality are covered in
this text in Chapters 7-10. In Chapter 2, examples are given showing how to utilize VBA to interact
with the Windows API to accomplish some handy tasks such as browsing for a directory or obtaining
the username of the person currently logged into the windows session. At the top of the Excel hierarchy
is the Object. Loosely speaking, objects can be thought of as “things,” meaning, if it can be seen in the
Excel environment, it is an object. Workbooks, Worksheets, and Charts are all examples of objects in
the Excel VBA environment. VBA utilizes objects to access information in Excel and perform manip-
ulations on elements in the Excel environment. Table 1.1 lists some of the most common objects in VBA.
The most common objects utilized when working with Excel VBA are the Workbook, the
Worksheet, and the Range. A fourth object, which is slightly more abstract, is the Application. An
application is simply an instance of Excel that is running. Several application objects may be
running on the same machine, but when the application object is called in VBA, it can refer only
to objects that are contained in the application that the code was called from. These four objects
are illustrated in Figure 1.1.
In Figure 1.1, the application can be thought of as the running program, which encompasses all
elements in the figure. The Workbook is named “Book2,” and it contains three Worksheets named
“Sheet1,” “Sheet2,” and “Sheet3,” respectively. Sheet1 is the active sheet in this Workbook because it
has the focus of the program at this time. Within Sheet1, a Range of Cells has been selected. In this
case, the range is from column B row 2 to column D row 4 (B2:D4). To successfully manipulate data
using VBA, it is necessary to have a mastery of the Application, Workbook, Worksheet, and Range
objects.
1.2
THE WORKBOOK
The Workbook is the topmost object in the Excel paradigm. Typically when gathering information,
it is necessary to specify, first, which Workbook has the information; next, which sheet in the
Workbook has the information; and, finally, what cell or range of cells has the information that is
sought or to be acted upon.
Although several Excel Workbooks may be open in an application, only one will the active
Workbook, or the Workbook that has the focus of the program. It is easy to determine from the
VBA code which Workbook is active by means of the following code snippet that can be executed
in the immediate (debug) window.
Debug.Print ActiveWorkbook.Name
1
