- Shopping Bag ( 0 items )
PART I—Friday Evening.
SESSION 1–Microsoft Excel Programming—Why and How.
SESSION 2–The VBA Code Editor.
SESSION 3–The Excel Object Model.
SESSION 4–Syntax and Data in VBA.
PART II–Saturday Morning.
SESSION 6–Control Constructs.
SESSION 7–Procedures and Modules.
SESSION 8–Working with Dates and Times.
SESSION 9–Working with Text.
SESSION 10–Using Ranges and Selections.
PART III–Saturday Afternoon.
SESSION 11–Working with Columns, Rows, and Cells.
SESSION 12–Programming with Custom Formulas.
SESSION 13–Programming with Excel’s Built-In Functions.
SESSION 14–Formatting a Worksheet.
SESSION 15–Find and Replace Operations.
SESSION 16–Creating Custom Toolbars.
PART IV–Saturday Evening.
SESSION 17–Introduction to Charts.
SESSION 18–Advanced Charting Techniques.
SESSION 19–Creating Custom Dialog Boxes with User Forms.
SESSION 20–Controls for User Forms.
PART V–Sunday Morning.
SESSION 21–Advanced User Form Techniques.
SESSION 22–A User Form Example.
SESSION 23–Working with Events.
SESSION 24–Security Considerations.
SESSION 25–Debugging and Distributing an Application.
SESSION 26–Defining and Using Custom Classes.
PART VI–Sunday Afternoon.
SESSION 27–Handling Runtime Errors.
SESSION 28–Database Tasks.
SESSION 29–Creating Add-Ins.
SESSION 30–Adding Online Help to Your Application.
Appendix A–Answers to Part Reviews.
Appendix B–What’s on the Web Site.
Microsoft Excel Programming - Why and How
30 Min. To Go
Most people think of Excel as merely a spreadsheet program, and with good reason - Excel is a spreadsheet program. As a spreadsheet program, Excel is a powerful application that provides a wide range of tools for the manipulation, analysis, and display of data. The majority of users never go beyond using Excel in this way - truth be told, many users have no need.
Under the surface, however, Excel is much more than an application program. It provides a sophisticated programming language that enables you to control any and all aspects of the program. Anything you can do with the keyboard and mouse you can also do with programming. For the power user, programming turns Excel into a flexible development tool for the creation of custom solutions to your data manipulation and analysis needs. This session takes a look at the advantages of Excel programming, and provides necessary background information on the technologies that are involved.
Advantages of Programming
Programming offers several important advantages to the Excel user. As mentioned earlier in this session, these advantages will not be relevant to all users, but they can apply to a surprisingly large percentage of situations.
Just about anything that a program can do in Excel can also be accomplished by a user with a keyboard and mouse. In a race, however, the program is always faster. Even if you are an expert user and do not need to spend any time figuring out how to perform the required tasks, a program will still beat you by a huge margin. What might take you half an hour to perform manually will be done in a few seconds by a program.
Even the most skilled typist hits the wrong key now and then, and every "mouse master" has been known to click the wrong button or command once in a while. In contrast, programs do not make mistakes - they are reliable servants, carrying out your commands over and over again with complete accuracy.
This is not to say that programs cannot contain errors. A program will do exactly what the programmer tells it to do; if your instructions are wrong or incomplete, the resulting program can cause errors. Dealing with program errors is an important topic, enough so that an entire session is devoted to it.
In many organizations, adherence to data-processing standards is an important aspect of maximizing productivity. For example, each sales representative may be required to submit a weekly summary spreadsheet. If those spreadsheets all follow the same structure and format, it is fairly straightforward to automate the process of extracting data into a summary report. The slightest deviation from the proper format, however, is likely to throw a wrench into the gears. By using programming for the individual spreadsheets rather than manual data entry, you can ensure that there are no deviations from the correct spreadsheet format.
Integrating with Other Applications
Excel does not always work alone - it has the capability to share data and interact with other applications. These capabilities are most developed with, but not restricted to, other Microsoft Office applications. For example, an Excel program could use Outlook to create an e-mail message containing data from a spreadsheet and then send the message to a list of recipients. Programming is not required for Excel to interact with other programs, but it makes tasks possible that would be difficult or impractical to perform otherwise.
What exactly is programming? It's really not as mysterious as it may sound. Perhaps you already have some experience with computer programming of one sort of another. If not, this section gives you some background information about what programming is and how it works.
Programming is really nothing more than creating instructions that tell the computer what to do. In an office, for example, you might ask your assistant to make copies - that's an instruction. A computer program is the same - you tell the computer what to do. The primary difference is that computers are really dumb and can't figure out the fine points on their own, so you must tell them exactly what to do in excruciating detail. Explicit instructions are at the heart of any program.
Some instructions manipulate data. This can be as simple as adding two numbers, or as complex as creating a chart. Other instructions control the execution of the program itself. For example, a program could be designed to perform one task on weekdays and another task on weekends. Still other instructions control how the program interacts with the user, such as how it responds to the user's selections from a menu or dialog box.
Every computer program works with data. This data can come in many forms - text, numbers, and graphics - but for now you don't need to be concerned with these details. A fundamental part of programming consists of handling the data that the program uses. You need a specific place to keep the data, and you also need to be able to get at the data when necessary. From this perspective, data can be divided into two categories:
Data stored outside the program. For the most part, this category comprises data that is stored in the cells of an Excel worksheet. Your program does not need to create storage for such data, although the program can read and write it.
Data stored within the program. For data that is not stored elsewhere, a program needs to create a storage location. A program uses variables to store internal data. As you'll see in later sessions, Excel programming offers a wide range of internal data storage capabilities.
When you are programming with Excel, the data-handling capabilities available to you are quite impressive. They provide a variety of data types that are specialized for storing different kinds of data. You learn more about these data types in later sessions.
The VBA Language
You will use the VBA language to write Excel programs. VBA stands for Visual Basic for Applications, and it is one of the two essential parts of Excel programming. The name Visual Basic for Applications reflects the fact that VBA is based on Microsoft's Visual Basic programming language, and that it is designed for programming within applications - specifically, the applications that comprise the Microsoft Office suite (Excel, Word, Access, PowerPoint, and Outlook). VBA is relatively easy to learn, as programming languages go, but does not sacrifice power and flexibility.
The task of programming in VBA is simplified by the VBA Editor, which is part of your Excel installation. You can open the VBA Editor by pressing Alt+F11 when in Excel, or by selecting Tools [right arrow] Macro [right arrow] Visual Basic Editor from the menu. The VBA Editor is shown in Figure 1-1. The blank window is where you enter your program's VBA code. Other elements in the Editor provide tools for organizing, running, and debugging your programs. Later sessions cover these features.
20 Min. To Go
The Excel Object Model
The other essential component of Excel programming is the Excel object model. To understand the object model, it helps to have some background information about the inner workings of programs such as Excel.
As computer programming evolved over the years, programs have gotten more powerful and, unavoidably, more complicated. With increasing complexity came an increased possibility of errors, and programmers found themselves spending more and more time tracking down and fixing the causes of these errors. It soon became apparent that many, if not most, program errors were caused by unexpected and unintended interactions between various parts of a program. If a programmer could reduce or eliminate these interactions, errors would be drastically curtailed.
At the same time, programmers found themselves writing the same program functionality over and over again. Most Windows programs have a menu, for example, and programmers would have to write the code for a menu from scratch for each new program. It would be much better if the code for a menu could be written once and then reused as needed in new programs.
These (and other) factors were the impetus behind the development of a programming technique called object-oriented programming, or OOP. With OOP, a program is viewed as a collection of related sections, or modules, that have different functions. Some of these modules are part of the program's interface, such as menus, toolbars, and dialog boxes. Other modules relate to the data with which the program works, such as (for Excel) workbooks, worksheets, and cells. Each of these modules is an object; see the "Objects and Classes" sidebar for more information on nomenclature.
OOP offers numerous advantages compared with older traditional methods of programming. These include:
Reduced errors. By design, objects are self-contained units that are isolated from each other as much as possible. An object's interactions with the rest of the program are tightly controlled, and unintended interactions (and the resulting errors) are eliminated.
Code reuse. An object - or more accurately, a class - is by its very nature reusable, not only in the same program, but in other programs as well.
Excel, as well as the other Office programs, was created using OOP techniques. Under the skin, therefore, Excel consists of a large collection of objects that work together to provide the program's functionality. You'll see how this relates to programming Excel in the next section.
Components and Automation
The objects that are part of the Excel application are written so that they are available to other programs. In computer talk, the objects are said to be exposed. This is part of the Component Object Model (COM) technology that is central to the Windows operating system itself as well as to most applications that run on Windows. The term component or COM component is used to refer to objects that are exposed in this manner; therefore, the objects that are exposed by Excel are sometimes referred to as components. Note that a single component may expose more than one class.
How does a programmer make use of exposed components? The answer is another COM technology called automation (called OLE - object linking and embedding - automation in the past). Automation permits an external program to access and control exposed components. Automation also permits components to interact with each other - for example, you could embed an Excel spreadsheet in a Word document. For the present purposes, automation permits a VBA program to use the Excel components. Other programming languages, such as C++ or Java, can use automation too, but that is not relevant here.
The COM components that your VBA programs can use exist as files on your hard disk and were installed as part of the Office or Excel installation. A component can operate in two ways:
An automation client controls and makes use of classes exposed by other components.
An automation server exposes classes for use by other components.
An automation component can act in one or both of these roles. For programming Excel, VBA is acting as a client, and the Excel components are acting as servers. Some Excel components act as clients to manipulate other components.
The result of this arrangement is that an Excel programmer has VBA, a powerful programming language, as well as access to all of the components that comprise the Excel application. This is an extremely powerful combination - Excel is your well-trained and capable servant, and VBA is the means you use to tell it what to do. The sum of all the components exposed by Excel is referred to as the Excel object model.
The Excel object model is covered in detail later in the book, primarily in Session 3.
Macros and Programming
If you have ever used Excel's macro feature, you have already done some Excel programming. A macro is a sequence of user actions that is recorded and can be played back later to duplicate the original actions. This saves time because you don't have to manually redo the steps each time. To record a macro, select Macro from Excel's Tools menu and then select Record New Macro. As you perform actions in Excel, they are translated into the corresponding VBA commands. When you stop the recording, the resulting VBA is saved and can be played back as needed. Recording macros can be a useful tool for the Excel programmer.
Recording macros is covered in more detail in Session 2.
Designing Your Custom Application
When creating a custom Excel program, as with all programming, it is important to do some planning before you start writing code. The importance of planning cannot be overemphasized. For a simple project it may take only a few minutes; for a large, complex project, it may take days. In either case it is going to save you time and hassles down the road.
Before you begin, make certain that you know what is needed. A lot of problems can arise when there is a misunderstanding between the client and the programmer. You may write a great program, but if it is not what the customer needs you have wasted your time! Don't assume that you know what is wanted - make sure, in detail.
After you know your goal, start planning the details of the program. For simple programs this step may be trivial because there is only one way to accomplish the desired ends. With more complex programs, you have choices to make. How many worksheets and workbooks will be needed? Will any user forms be required? How will the functionality be divided up? Should you define any classes for the project? Of course, plans that you make at this stage are not set in stone - you can always modify them later as circumstances dictate. Even so, having some plan at the outset, even if it is an incomplete plan, is a real help.
Excerpted from Excel Programming Weekend Crash Course by Peter G. Aitken Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.