Cover image for Writing Excel macros
Writing Excel macros
Roman, Steven.
Personal Author:
First edition.
Publication Information:
Sebastopol, Calif. : O'Reilly, 1999.
Physical Description:
xix, 529 pages : illustrations ; 24 cm
General Note:
"Covers Office 97 & 2000"--Cover.

"Developing macros to automate and customize Excel"--Cover.

Includes index.
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.6 .R65 1999 Adult Non-Fiction Central Closed Stacks

On Order



Microsoft Excel is an enormously powerful and flexible application. Yet despite its powerful feature set, there is a great deal that Excel either does not allow you to do or does not allow you to do easily through its user interface. And in spite of Excel's reputation as the most widely used spreadsheet application, the majority of its users do not venture beyond the basics of creating spreadsheets and perhaps dabbling with macros. Consequently, these users aren't getting all the power out of this formidable application.With Writing Excel Macros you will learn there are many things 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. And learning how to get more power out of Excel will mean you can be more effective in your work. Writing Excel Macros offers a solid introduction to writing VBA macros and programs and provides Excel users and programmers unfamiliar with the Excel object model with an excellent overview to writing VBA macros and programs. The essentials of 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.In particular, the book focuses on:

Programming languages. Brief overview of programming and programming languages, as well as information on Variables, Data Types, and Constants, Functions and Subroutines, and more. The Visual Basic Editor. Before tackling the basics of the programming language that Excel uses, the reader is acquainted with the VBA environment--the Visual Basic Editor. Handling your code. An overview of where to store your code and how to activate it from an Excel spreadsheet. The Excel object model. An in-depth overview of the Excel object model, including the Application, Workbook, Worksheet, and Range objects. Appendices. Details on the Shape object; getting the Installed Printers; Command Bar Controls and Face IDs; programming Excel from another application; and more. The information in this book is written in a succinct, practical manner that is characteristic of Steve Roman's straightforward approach. Readers will find useful examples throughout the book that deal with specific programming problems and allow them to gain hands-on experience in the VBA environment. Whether your interest in Excel programming is so you can be more effective in your work, or you want to learn how to write Excel programs for others to use, this book offers a solid introduction to writing VBA macros and programs and shows you how to get more power out of Excel at the programming level.

Reviews 1

Library Journal Review

In terms of environments, you can't get any more programmable than Microsoft's Excel spreadsheet. There are lots that can be done with the built-in functions, but programmers still need functionality not designed into the system. Users unaccustomed to writing macros will be lost in macro programming hell if they don't have Roman's book. Roman assumes his readers are already real programmers but not yet familiar with the Excel object model and Visual Basic for Applications (VBA). His manual starts first with an introduction and then includes a discussion of the Visual Basic Editor and the VBA language and practical examples of the Excel object model. Definitely not for beginners, this is written specifically for Microsoft Version 8 (Office 97) and Version 9 (Office 2000). (c) Copyright 2010. Library Journals LLC, a wholly owned subsidiary of Media Source, Inc. No redistribution permitted.

Table of Contents

Prefacep. xiii
1. Introductionp. 1
Selecting Special Cellsp. 1
Setting a Chart's Data Point Labelsp. 3
Topics in Learning Excel Programmingp. 5
2. Preliminariesp. 7
What Is a Programming Language?p. 7
Programming Stylep. 9
I. The VBA Environmentp. 15
3. The Visual Basic Editor, Part Ip. 17
The Project Windowp. 17
The Properties Windowp. 22
The Code Windowp. 23
The Immediate Windowp. 26
Arranging Windowsp. 27
4. The Visual Basic Editor, Part IIp. 29
Navigating the IDEp. 29
Getting Helpp. 31
Creating a Procedurep. 31
Run Time, Design Time, and Break Modep. 32
Errorsp. 33
Debuggingp. 37
Macrosp. 43
II. The VBA Programming Languagep. 47
5. Variables, Data Types, and Constantsp. 49
Commentsp. 49
Line Continuationp. 49
Constantsp. 50
Variables and Data Typesp. 53
VBA Operatorsp. 70
6. Functions and Subroutinesp. 71
Calling Functionsp. 71
Calling Subroutinesp. 73
Parameters and Argumentsp. 73
Exiting a Procedurep. 78
Public and Private Proceduresp. 78
Project Referencesp. 79
7. Built-in Functions and Statementsp. 81
The MsgBox Functionp. 83
The InputBox Functionp. 85
VBA String Functionsp. 86
Miscellaneous Functions and Statementsp. 89
Handling Errors in Codep. 92
8. Control Statementsp. 97
The If...Then Statementp. 97
The For Loopp. 98
The For Each Loopp. 100
The Do Loopp. 100
The Select Case Statementp. 101
A Final Note on VBAp. 102
III. Excel Applications and the Excel Object Modelp. 105
9. Object Modelsp. 107
Objects, Properties, and Methodsp. 107
Collection Objectsp. 109
Object Model Hierarchiesp. 111
Object Model Syntaxp. 112
Object Variablesp. 113
10. Excel Applicationsp. 120
Providing Access to an Application's Featuresp. 121
Where to Store an Applicationp. 125
An Example Add-Inp. 134
11. Excel Eventsp. 138
The EnableEvents Propertyp. 139
Events and the Excel Object Modelp. 139
Accessing an Event Procedurep. 139
Worksheet Eventsp. 140
WorkBook Eventsp. 141
Chart Eventsp. 141
Application Eventsp. 142
QueryTable Refresh Eventsp. 144
12. Custom Menus and Toolbarsp. 145
Menus and Toolbars: An Overviewp. 145
The CommandBars Collectionp. 148
Creating a New Menu Bar or Toolbarp. 150
Command-Bar Controlsp. 151
Built-in Command-Bar-Control IDsp. 153
Example: Creating a Menup. 155
Example: Creating a Toolbarp. 156
Example: Adding an Item to an Existing Menup. 159
Augmenting the SRXUtils Applicationp. 160
13. Built-In Dialog Boxesp. 169
The Show Methodp. 173
14. Custom Dialog Boxesp. 175
What Is a UseForm Object?p. 175
Creating a UserForm Objectp. 176
ActiveX Controlsp. 177
Adding UserForm Codep. 178
Excel's Standard Controlsp. 179
Example: The ActiveSheet Utilityp. 181
ActiveX Controls on Worksheetsp. 186
15. The Excel Object Modelp. 191
A Perspective on the Excel Object Modelp. 191
Excel Enumsp. 195
The VBA Object Browserp. 197
16. The Application Objectp. 199
Properties and Methods of the Application Objectp. 200
Children of the Application Objectp. 225
17. The Workbook Objectp. 230
The Workbooks Collectionp. 230
The Workbook Objectp. 236
Children of the Workbook Objectp. 244
Example: Sorting Sheets in a Workbookp. 247
18. The Worksheet Objectp. 250
Properties and Methods of the Worksheet Objectp. 251
Children of the Worksheet Objectp. 258
Example: Printing Sheetsp. 262
19. The Range Objectp. 267
The Range Object as a Collectionp. 269
Defining a Range Objectp. 270
Additional Members of the Range Objectp. 276
Children of the Range Objectp. 309
Example: Getting the Used Rangep. 323
Example: Selecting Special Cellsp. 325
20. Pivot Tablesp. 338
Pivot Tablesp. 338
The PivotTable Wizardp. 341
The PivotTableWizard Methodp. 344
The PivotTable Objectp. 348
Properties and Methods of the PivotTable Objectp. 353
Children of the PivotTable Objectp. 369
The PivotField Objectp. 369
The PivotCache Objectp. 386
The PivotItem Objectp. 387
Calculated Items and Calculated Fieldsp. 392
Example: Printing Pivot Tablesp. 394
21. The Chart Objectp. 401
Chart Objects and ChartObject Objectsp. 402
Creating a Chartp. 403
Chart Typesp. 410
Children of the Chart Objectp. 414
The Axes Collectionp. 415
The Axis Objectp. 417
The ChartArea Objectp. 429
The ChartGroup Objectp. 430
The ChartTitle Objectp. 434
The DataTable Objectp. 435
The Floor Objectp. 436
The Legend Objectp. 436
The PageSetup Objectp. 438
The PlotArea Objectp. 439
The Series Objectp. 439
Properties and Methods of the Chart Objectp. 447
Example: Scrolling Through Chart Typesp. 452
Example: Printing Embedded Chartsp. 455
Example: Setting Data Series Labelsp. 460
IV. Appendixesp. 469
A. The Shape Objectp. 471
B. Getting the Installed Printersp. 482
C. Command Bar Controlsp. 486
D. Face IDsp. 497
E. Programming Excel from Another Applicationp. 503
F. High-Level and Low-Level Languagesp. 508
Indexp. 519