Writing Excel Macros

by Roman, Steven

• ISBN13: 9781565925878
• ISBN10: 1565925874
• Format: Paperback
• Copyright: 1999-05-01
• Publisher: Oreilly & Associates Inc

Summary

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 howto 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.

Table of Contents

Preface	xiii
Introduction	1	(6)
Selecting Special Cells	1	(2)
Setting a Chart's Data Point Labels	3	(2)
Topics in Learning Excel Programming	5	(2)
Preliminaries	7	(8)
What Is a Programming Language?	7	(2)
Programming Style	9	(6)
I. The VBA Environment	15	(32)
The Visual Basic Editor, Part I	17	(12)
The Project Window	17	(5)
The Properties Window	22	(1)
The Code Window	23	(3)
The Immediate Window	26	(1)
Arranging Windows	27	(2)
The Visual Basic Editor, Part II	29	(18)
Navigating the IDE	29	(2)
Getting Help	31	(1)
Creating a Procedure	31	(1)
Run Time, Design Time, and Break Mode	32	(1)
Errors	33	(4)
Debugging	37	(6)
Macros	43	(4)
II. The VBA Programming Language	47	(58)
Variables, Data Types, and Constants	49	(22)
Comments	49	(1)
Line Continuation	49	(1)
Constants	50	(3)
Variables and Data Types	53	(17)
VBA Operators	70	(1)
Functions and Subroutines	71	(10)
Calling Functions	71	(2)
Calling Subroutines	73	(1)
Parameters and Arguments	73	(5)
Exiting a Procedure	78	(1)
Public and Private Procedures	78	(1)
Project References	79	(2)
Built-in Functions and Statements	81	(16)
The MsgBox Function	83	(2)
The InputBox Function	85	(1)
VBA String Functions	86	(3)
Miscellaneous Functions and Statements	89	(3)
Handling Errors in Code	92	(5)
Control Statements	97	(8)
The If... Then Statement	97	(1)
The For Loop	98	(2)
The For Each Loop	100	(1)
The Do Loop	100	(1)
The Select Case Statement	101	(1)
A Final Note on VBA	102	(3)
III. Excel Applications and the Excel Object Model	105	(364)
Object Models	107	(13)
Objects, Properties, and Methods	107	(2)
Collection Objects	109	(2)
Object Model Hierarchies	111	(1)
Object Model Syntax	112	(1)
Object Variables	113	(7)
Excel Applications	120	(18)
Providing Access to an Application's Features	121	(4)
Where to Store an Application	125	(9)
An Example Add-In	134	(4)
Excel Events	138	(7)
The EnableEvents Property	139	(1)
Events and the Excel Object Model	139	(1)
Accessing an Event Procedure	139	(1)
Worksheet Events	140	(1)
WorkBook Events	141	(1)
Chart Events	141	(1)
Application Events	142	(2)
QueryTable Refresh Events	144	(1)
Custom Menus and Toolbars	145	(24)
Menus and Toolbars: An Overview	145	(3)
The CommandBars Collection	148	(2)
Creating a New Menu Bar or Toolbar	150	(1)
Command-Bar Controls	151	(2)
Built-in Command-Bar-Control IDs	153	(2)
Example: Creating a Menu	155	(1)
Example: Creating a Toolbar	156	(3)
Example: Adding an Item to an Existing Menu	159	(1)
Augmenting the SRXUtils Application	160	(9)
Built-In Dialog Boxes	169	(6)
The Show Method	173	(2)
Custom Dialog Boxes	175	(16)
What Is a UserForm Object?	175	(1)
Creating a UserForm Object	176	(1)
ActiveX Controls	177	(1)
Adding UserForm Code	178	(1)
Excel's Standard Controls	179	(2)
Example: The ActivateSheet Utility	181	(5)
ActiveX Controls on Worksheets	186	(5)
The Excel Object Model	191	(8)
A Perspective on the Excel Object Model	191	(4)
Excel Enums	195	(2)
The VBA Object Browser	197	(2)
The Application Object	199	(31)
Properties and Methods of the Application Object	200	(25)
Children of the Application Object	225	(5)
The Workbook Object	230	(20)
The Workbooks Collection	230	(6)
The Workbook Object	236	(8)
Children of the Workbook Object	244	(3)
Example: Sorting Sheets in a Workbook	247	(3)
The Worksheet Object	250	(17)
Properties and Methods of the Worksheet Object	251	(7)
Children of the Worksheet Object	258	(4)
Example: Printing Sheets	262	(5)
The Range Object	267	(71)
The Range Object as a Collection	269	(1)
Defining a Range Object	270	(6)
Additional Members of the Range Object	276	(33)
Children of the Range Object	309	(14)
Example: Getting the Used Range	323	(2)
Example: Selecting Special Cells	325	(13)
Pivot Tables	338	(63)
Pivot Tables	338	(3)
The PivotTable Wizard	341	(3)
The PivotTable Wizard Method	344	(4)
The PivotTable Object	348	(5)
Properties and Methods of the PivotTable Object	353	(16)
Children of the PivotTable Object	369	(1)
The PivotField Object	369	(17)
The PivotCache Object	386	(1)
The PivotItem Object	387	(5)
Calculated Items and Calculated Fields	392	(2)
Example: Printing Pivot Tables	394	(7)
The Chart Object	401	(68)
Chart Objects and ChartObject Objects	402	(1)
Creating a Chart	403	(7)
Chart Types	410	(4)
Children of the Chart Object	414	(1)
The Axes Collection	415	(2)
The Axis Object	417	(12)
The ChartArea Object	429	(1)
The ChartGroup Object	430	(4)
The ChartTitle Object	434	(1)
The DataTable Object	435	(1)
The Floor Object	436	(1)
The Legend Object	436	(2)
The PageSetup Object	438	(1)
The PlotArea Object	439	(1)
The Series Object	439	(8)
Properties and Methods of the Chart Object	447	(5)
Example: Scrolling Through Chart Types	452	(3)
Example: Printing Embedded Charts	455	(5)
Example: Setting Data Series Labels	460	(9)
IV. Appendixes	469	(50)
A. The Shape Object	471	(11)
B. Getting the Installed Printers	482	(4)
C. Command Bar Controls	486	(11)
D. Face IDs	497	(6)
E. Programming Excel from Another Application	503	(5)
F. High-Level and Low-Level Languages	508	(11)
Index	519