Writing Excel Macros

To order this book through Amazon.com, please click here

About the Book

To gain complete control over Microsoft Excel, you need to tap into the Excel object model using the Visual Basic For Applications (VBA) programming language. The purpose of Writing Excel Macros is to provide the reader with the necessary skills to write Excel VBA programs. No previous knowledge of VBA programming is necessary to read this book. On the other hand, the book is sufficiently detailed to give you a solid and thorough understanding of both the VBA language and the Excel object model.

Writing Excel Macros focuses on:

• The Visual Basic Editor and the Excel VBA programming environment.

• The VBA programming language (which is the same programming language used by Microsoft Excel, Word, Access, and PowerPoint, as well as the retail editions of Visual Basic).

• The Excel object model.

Personally, I hate long, wordy, overblown 1000+ page books half of which seem to be devoted to the author's "humorous" anecdotes, so I wrote Writing Excel Macros in a terse, no-nonsense manner that is characteristic of all my books. Instead of a slow-paced tutorial with a lot of hand-holding, I tried to give you the insight you need to program effectively. The book includes several useful examples that solve practical programming problems, like dealing with Excel charts and pivot tables.

Preface

This book is intended for those who want to learn how to program Microsoft Excel 97 or later.

We should begin by addressing the question, "Why would anyone want to program Microsoft Excel?" The answer is simple: to get more power out of this formidable application. As you will see, there are many things that you can do at the programming level that you cannot do at the user-interface level, that is, with the menus and dialog boxes of Excel. Chapter 1 provides some concrete examples of this.

This book provides an introduction to programming the Excel object model using Visual Basic for Applications (VBA). However, it is not intended to be an encyclopedia of Excel programming. The goal here is to acquaint you with the main points of Excel programming-enough so that you can continue your education (as we all do) on your own. The point is that, after reading this book, you should not need to rely on any other source except the Excel VBA help file or a good Excel VBA reference book and a nice object browser (such as my object browser, a coupon for which is included in the back of this book).

It has been my experience that introductory programming books (and, sadly, most trade computer books) tend to do a great deal of handholding (to put the matter euphemistically). They cover concepts at a very slow pace primarily by padding them heavily with overblown examples and irrelevant anecdotes that only the author could conceivably find amusing, especially the second or third time that we are forced to read them while looking for a few facts. Frankly, I find such unprofessionalism incredibly infuriating. In my opinion, it does the reader a great disservice to take perhaps 400 pages of information and pad it with another 600 pages of irrelevant junk.

There is no doubt in my mind that we need much more professionalism from our authors, but it is not easy to find writers who have both the knowledge to write about a subject and the training (or talent) to do so in a pedagogical manner. (I should hasten to add that there are a number of excellent authors in this area-it's just that there are not nearly enough of them.) Moreover, publishers tend to encourage the creation of 1000 page plus tombs because of the general feeling among the publishers that a book must be physically wide enough to stand out on the bookshelf! I shudder to think that this might, in fact, be true. (I am happy to say that O'Reilly does not seem to have succumbed to this opinion.)

On the other hand, Writing Excel Macros is not a book in which you will find much handholding. (Nor will you find much handholding in any of my books.) The book proceeds at a relatively rapid pace from a general introduction to programming, through an examination of the Visual Basic for Applications programming language to an overview of the Excel object model. Given the enormity of the subject, not everything is covered, nor should it be. Nevertheless, the essentials of both the VBA language and the Excel object model are covered so that, when you have finished the book, you will know enough about Excel VBA to begin creating effective working programs.

I have tried to put my experience as a professor (about 20 years) and my experience writing books (about 30 of them) to work here to create a true learning tool for my readers. Hopefully, this is a book that can be read (perhaps more than once) and also serve as a useful reference.

Table of Contents

 Preface

1.  Introduction
    Selecting Special Cells
    Setting a Chart's Data Point Labels
2.  Preliminaries
    What Is a Programming Language?
    Programming Style
3.  The Visual Basic Editor, Part I
    The Project Window
    The Properties Window
    The Code Window
    The Immediate Window
    Arranging Windows
4.  The Visual Basic Editor, Part II
    Navigating the IDE
    Getting Help
    Creating a Procedure
    Run Time, Design Time, and Break Mode
    Errors
    Debugging
    Macros
5.  VBA I: Variables, Data Types and Constants
    Comments
    Line Continuation
    Constants
    Variables and Data Types
    VBA Operators
6.  VBA II: Functions and Subroutines
    Calling Functions
    Calling Subroutines
    Parameters and Arguments
    Exiting a Procedure
    Public and Private Procedures
    Project References
7.  VBA III: Built-In Functions and Statements
    The MsgBox Function
    The InputBox Function
    VBA String Functions
    Miscellaneous Functions and Statements
    Handling Errors in Code
8.  VBA IV: Control Statements
    The If … Then Statement
    The For Loop
    The For Each Loop
    The Do Loop
    The Select Case Statement
    A Final Note on VBA
9.  Object Models
    Objects, Properties and Methods
    Collection Objects
    Object Model Hierarchies
    Object Model Syntax
    Object Variables
10.  Excel Applications
    Providing Access to an Application's Features
    Where to Store an Application
    Excel Templates
    Excel Add-Ins
    An Example Add-In
11.  Excel Events
    The EnableEvents Property
    Events and the Excel Object Model
    Accessing an Event Procedure
    Worksheet Events
    WorkBook Events
    Chart Events
    Application Events
    QueryTable Refresh Events
12.  Custom Menus and Toolbars
    Menus and Toolbars: An Overview
    The CommandBars Collection
    Creating a New Menu Bar or Toolbar
    Command Bar Controls
    Built-In Command Bar Control IDs
    Example: Creating a Menu
    Example: Creating a Toolbar
    Augmenting the SRXUtils Application
13.  Built-In Dialog Boxes
    The Show Method
14.  Custom Dialog Boxes
    What Is a UserForm Object?
    Creating a UserForm Object
    ActiveX Controls
    Adding UserForm Code
    Excel's Standard Controls
    Example: The ActivateSheet Utility
    ActiveX Controls on Worksheets
15.  The Excel Object Model
    A Perspective on the Excel Object Model
    Excel Enums
    The VBA Object Browser
16.  The Application Object
    Properties and Methods of the Application Object
    Children of the Application Object
17.  The Workbook Object
    The Workbooks Collection
    The Workbook Object
    Children of the Workbook Object
    Example: Sorting Sheets in a Workbook
18.  The Worksheet Object
    Properties and Methods of the Worksheet Object
    Children of the Worksheet Object
    Example: Printing Sheets
19.  The Range Object
    The Range Object as a Collection
    Defining a Range Object
    Additional Members of the Range Object
    Children of the Range Object
    Example: Getting the Used Range
    Example: Selecting Special Cells
20.  Pivot Tables
    Pivot Tables
    The PivotTable Wizard
    The PivotTableWizard Method
    The PivotTable Object
    Properties and Methods of the PivotTable Object
    Children of the PivotTable Object
    The PivotField Object
    The PivotCache Object
    The PivotItem Object
    Calculated Items and Calculated Fields
    Example: Printing Pivot Tables
21.  The Chart Object
    Chart Objects and ChartObject Objects
    Creating a Chart
    Chart Types
    Children of the Chart Object
    The Axes Collection
    The Axis Object
    The ChartArea Object
    The ChartGroup Object
    The ChartTitle Object
    The DataTable Object
    The Floor Object
    The Legend Object
    The PageSetup Object
    The PlotArea Object
    The Series Object
    Properties and Methods of the Chart Object
    Example: Scrolling Through Chart Types
    Example: Printing Embedded Charts
    Example: Setting Data Series Labels
A. The Shape Object
    The Shape Object
    Z-Order
    Creating Shapes
B. Getting the Installed Printers
C. Command Bar Controls and Face IDs
    Built-In Command Bar Controls
    Face IDs
D. Programming Excel from Another Application
E. High-Level and Low-Level Languages
    BASIC
    Visual Basic
    C and C++
    Visual C++
    Pascal
    FORTRAN
    COBOL
    LISP

To order this book through Amazon.com, please click here
Return to Roman Press Home Page
Return to computer books' main page