| This is supposed to be a book about managing data using Excel, so what's a chapter about Visual Basic for Applications (VBA) doing here? As it happens, the remainder of this book is all about working with VBA. The first six chapters were concerned with the use of Excel's built-in data management capabilities. For example The worksheet functions that help you manage data The tools that help you filter and sort data The ways to populate pivot tables and data ranges The methods of importing data from sources such as databases, text files, and the Web
These are powerful techniques and they have broad applicability. For standard situations, they'll serve you very well. You'll have to do some tweaking here and there to get the results exactly as you want them, but in many cases that's all that's needed. Those standard situations include managing data that's already in an Excel workbook, and bringing data from other sources into a workbook where you can take advantage of Excel's worksheet functions, pivot tables, and its charting capabilities. Adding Objects to VBA So far, though, this book has not discussed moving data the other direction: in particular, moving it from Excel to a database. For that you really need VBA. You also need a set of tools such as those provided by ActiveX Data Objects (ADO), or its predecessor, Data Access Objects (DAO). These toolsets are also both well suited and necessary for other tasks such as manipulating a database directly from the Excel platform. You'll see much more on ADO and DAO in Chapters 8 through 12, but here's a brief overview. Both ADO and DAO are object libraries that is, collections of objects that you can use in VBA code. You use either ADO or DAO, not both simultaneously. The objects that are in the libraries are database objects. They define for VBA the objects that you find in databases: tables, queries, records, fields, and so on. By making one of these libraries, either ADO or DAO, available to your VBA code, you make it possible to manipulate data stored in a database just as you can use VBA to manipulate data stored in an Excel workbook. ADO and DAO differ from one another in various ways, and these differences are also discussed in later chapters. For now it's enough to know that ADO is newer than DAO, that you can use it with a broader range of databases than DAO, and that many developers regard it as easier to use than DAO. As you'll see, both ADO and DAO are well suited to moving data from Excel to a database, but they go well beyond that role. They're also valuable in moving data from a database to Excel, in situations where the techniques discussed in the preceding chapters won't do what you need. And you'll need one or the other when you want to modify a database's records while you're working in Excel. If you are to use ADO or DAO, you need to use it in the context of VBA. Neither behaves like an add-in such as the Solver, which is installed as an option and is then callable from the worksheet. ADO and DAO are both collections of objects, methods, and properties that extend the reach of VBA. It's important to keep in mind that you need to use VBA to take advantage of the database object model as provided by ADO or DAO. VBA is a necessary part of this interface between Excel and databases, and you need a working knowledge at least of VBA basics to manage the interface. That's what this chapter is doing here. Putting VBA in Historical Perspective In 1995, Microsoft took a step that significantly increased the power of the applications in the Office suite: It exposed the object model. That meant that Microsoft enabled users of Excel and other Office applications to write programs in BASIC that could directly manipulate an application's objects. Before VBA and the exposure of the object model, an Excel user who wanted to automate certain tasks was severely limited by the available programming language. There was an arcane macro language that you could use in Excel, but to use it was nothing like coding with popular programming languages of the time such as C, BASIC, and FORTRAN. You put your macro code on a sheet that looked much like a worksheet, and you placed a statement or function in one cell and its arguments into an adjacent cell. But VBA and the object model made things much more straightforward. Suddenly your code looked normal: Other programmers could understand and maintain it. Your code could directly manipulate Excel objects such as worksheets, ranges, names, and charts. It could bring methods to bear on objects for example, your code could invoke a range's Clear method to clear that range of its contents. It could manipulate properties, such as setting a font's Color property to Red. This is what was meant by exposing the object model. At last you had access to the hierarchy of objects: the Excel application at the top, to which belong workbooks, to which belong chart sheets and worksheets, to which belong axes and data series and cells and ranges and columns and rows, and so on. Each of those is an object. Using the Object Model Objects have methods. A worksheet range, for example, has methods such as AutoFilter, Clear, Copy, Delete, Select, and Sort (there are many others). By invoking a range's Copy method, I can put its contents on the clipboard, ready to paste elsewhere. Objects have properties. A worksheet range, for example, has properties such as Borders, Columns, Formula, Offset, and RowHeight (there are many others). By setting a range's Resize property, I can work with a range that is taller or shorter, wider or narrower. You can get at all Excel's objects, and their associated methods and properties, by using VBA in Excel. Excel's Visual Basic Editor (VBE) automatically makes them available to your code because the object model has been exposed. Databases have objects, too, as well as methods and properties that belong to the objects. Databases' objects include tables, queries, parameters, and so on. But they don't belong to the Excel object model: There's not just one overarching object model that contains, for example, Excel worksheets, Access tables, PowerPoint slides, and Word paragraphs. There's a different object model for each application. Using Other Applications' Object Models Those other object models are not automatically available to VBA in Excel. If you start the VBE from, say, Access, you'll find that all the Access database objects are automatically available to you. But they are not if you start the VBE from Excel. To use a database object in your Excel VBA code, you need to establish a reference to an object model that's compatible with the database. By establishing the reference, you let VBA know where to look for information about the objects it uses. There are several such object models, and each is stored in a library in particular, a dynamic link library, or DLL. You establish the reference by means of a simple menu command. From the VBE, choose Tools, References and fill the check box for the library you want (see Figure 7.1). Figure 7.1. When you have a choice of versions, it's usually best to choose the one with the highest version number. 
NOTE The version of DAO or ADO that's available to you depends on the version of Office (and therefore on the operating system) that you have installed on your computer. They have the same purpose: to give your code access to database objects. From the user's perspective, DAO and ADO are identical in some ways but very different in important respects. At the time this book was being written, Microsoft was focusing development efforts on ADO and regarded DAO as a legacy technology. This book highlights differences between the two where your VBA code would be affected. Elsewhere, you can regard discussion of ADO as applying to DAO as well.
After you've established the reference, you'll find that your VBA code recognizes the database objects. TIP When you compile or run your VBA code, if you get the compiler error message User-defined type not defined, it almost certainly means that you have miskeyed the name of a variable type or that you haven't yet established a reference to the type's library.
For example, with a library reference established properly, your VBA code can do each of the following, all without ever opening the database's window: Return records and fields from a table or query directly to the worksheet Filter the records returned by a query by supplying a parameter value to the query before executing it Add records to or delete records from a table Edit records Create and execute a query, and either cause the query to be saved in the database or to vanish as soon as you're through with it
The previous list is only a small sample of the tasks you can accomplish when you make the database object model available to your Excel VBA code. Managing the Database from Excel Why would you want to manipulate a database from within Excel? As it turns out, there's a variety of reasons, not all of them self-evident. Accommodating the User If people other than you ever use systems that you develop, you need to take into account the user interfaces that they're comfortable with. Most computer users feel comfortable in a worksheet context. They're used to the notion of rows and columns, and how they intersect to make cells. Their experience with worksheets might go all the way back to 1-2-3, whereas database managers such as dBASE came along later (and were never as popular as the spreadsheet applications). Most users appreciate the flexibility of the worksheet for example, that one can make a row stand for anything and a column stand for anything and are often put off by the prickliness of the database where a row must represent a record and a column must represent a field. Any time you can help a user feel more comfortable using your product, you're ahead of the game. Taking Advantage of Excel's Features Excel offers capabilities that are simply not available in databases, or that are not implemented as well as they are in Excel. Charts, for example, are implemented in Access, but they have to be embedded in forms or reports. Pivot tables are similarly available, but only in datasheets and forms. More serious is the limited function set. Although Access offers a reasonably good function list, given that it's a database manager, the list is nowhere near as extensive as Excel's. As basic a function as Median is unavailable in Access you have to create your own using VBA in Access. But if you make database records and fields available to your Excel VBA code, you can use Excel's worksheet functions to analyze the data. TIP Whether or not you ever use Excel VBA in direct conjunction with a database, bear in mind that Excel's worksheet functions are available in VBA. For example, to get the median value of an array, you might use something like this: MyMedian = Application.WorksheetFunction.Median(MyArray)
Maintaining Flexibility Earlier in this chapter, the generalized nature of Excel's worksheet layout was mentioned: that rows and columns aren't relegated to any specific role as they are in a database's datasheet view. This fact has applicability that goes beyond that of user comfort. Sometimes you encounter a situation that calls for a matrix, or an array, or a table, or a range of cells; the term matters less than the fact that you need to use rows that intersect columns. And the situation is such that you cannot regard the rows as records and the columns as fields, as demanded by the database. An example is a resource calendar. For your users to employ the calendar effectively, you'd like them to be able to reserve a resource for a particular period of time on a particular day. Excel's structure is ideal for this. You can arrange things as follows: Assign each worksheet row to represent a specific resource (a meeting room, for example, or a workstation-compatible overhead projector or a high-definition screen) Assign each worksheet column to represent a half-hour increment, so that column C might represent 0600 to 0630, column D 0630 to 0700, and so on Assign each worksheet to represent a different day
With this sort of structure, a user can quickly see that Room K has already been reserved starting at 10:30 on October 14, so that his meeting will have to use Room O. See Figure 7.2. Figure 7.2. There's no row-as-record, column-as-field straitjacket in this structure. 
This is the sort of thing that Excel's worksheets are so well suited to. The Western eye likes to see time progress from left to right, not up and down (and definitely not right to left), so time slots are best placed so that they occupy different columns. For the user's convenience, it makes sense to put available resources adjacent to one another, so that if Room K is unavailable at a particular time, it's easy to see if some other room is available. If different columns are in use to represent different time slots, that leaves different rows to represent different resources. But it's difficult to see how you would manage that in a database. You would need first to define a field as a time slot. So, each record would have perhaps 48 fields that represent whether it's in use during a particular half-hour. Each record would have to represent a particular resource on a particular day. And each record would need child records to store data such as who made the reservation, which accounts are to be charged for any expenses incurred, what refreshments are to be supplied, whether the reservation is to recur on subsequent dates, and so on. All in all, the user interface is difficult to handle with a database alone. But it's easy to do using a workbook. And it gets easier yet when you bring a database to bear on the problem, but in such a way that its use is transparent to the user. For example, you could decide that in the database a reservation is a record. It has a field that identifies the reservation's date, another to identify the start time and another for the stop time, another for the resource that's been reserved, and so forth. So doing means that you can use the workbook to display the information and the database to store the information. This approach leverages the strengths of both applications: the worksheet's capability to represent information in a visually informative and intuitive way, and the database's capability to store, locate, and retrieve large amounts of data very quickly. Notice that although the scenario as described here involves a one-way data flow only, you could not accomplish it by importing external data, whether to an external data range or to a pivot table: The layouts they use are just too restrictive. (This is, by the way, a real-world application that has been used daily by a mid-sized corporation for several years.) Reviewing the Rationale Really, the main point raised in the prior section using the strength of each application is the rationale for using Excel VBA in conjunction with a database object model such as ADO or DAO. Excel has been built and refined over several years and several releases principally as an application that analyzes, synthesizes, and displays data, and it's very good indeed at those tasks. In response to user requests, Microsoft has added certain capabilities to Excel that were not part of the original design. For example, Excel was not originally intended as a multiuser application with all the features needed to ensure data integrity when several users have a given workbook open at the same time. You can arrange for the workbook to be shared, but that feature came along relatively late in Excel's development and just doesn't work as well as it does in applications that have it built into their basic design. Similarly, databases such as Access have been developed as engines that store large amounts of data reliably and retrieve records very quickly, even from quite large data sets. They also have sophisticated methods of resolving conflicts when more than one user at a time is attempting to modify a particular record. If you have relatively few records to deal with, you're usually better off using Excel's native worksheet functions and features to manage them, as discussed in Chapters 2, "Excel's Data Management Features," and 3, "Excel's Lists, Names, and Filters." And if you have a relatively large data set, you might be better off using a database manager alone. But if you have a large data set and need a flexible user interface, or a large function set, or a strong charting capability, you need both a database and a workbook. And the best way to manage two-way communication between them is by means of VBA as enhanced with ADO. With the foregoing as rationale, you should head for the next chapter if you're already a VBA maven. Otherwise, the rest of this chapter will give you a quick introduction to the aspects of VBA most frequently used in the contexts of data management and database management. | 