Writing Excel Macros

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
• The Visual Basic Editor
• Handling your code
• The Excel Object Model

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

Chapter 1: Introduction

Selecting Special Cells
Setting a Chart's Data Point Labels

Chapter 2: Preliminaries

What Is a Programming Language?
Programming Style

Chapter 3: The Visual Basic Editor, Part I

The Project Window
The Properties Window
The Code Window
The Immediate Window
Arranging Windows

Chapter 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

Chapter 5: VBA I: Variables, Data Types and Constants

Comments
Line Continuation
Constants
Variables and Data Types
VBA Operators

Chapter 6: VBA II: Functions and Subroutines

Calling Functions
Calling Subroutines
Parameters and Arguments
Exiting a Procedure
Public and Private Procedures
Project References

Chapter 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

Chapter 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

Chapter 9: Object Models

Objects, Properties and Methods
Collection Objects
Object Model Hierarchies
Object Model Syntax
Object Variables

Chapter 10: Excel Applications

Providing Access to an Application's Features
Where to Store an Application
Excel Templates
Excel Add-Ins
An Example Add-In

Chapter 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

Chapter 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

Chapter 13: Built-In Dialog Boxes

The Show Method

Chapter 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

Chapter 15: The Excel Object Model

A Perspective on the Excel Object Model
Excel Enums
The VBA Object Browser

Chapter 16: The Application Object

Properties and Methods of the Application Object
Children of the Application Object

Chapter 17: The Workbook Object

The Workbooks Collection
The Workbook Object
Children of the Workbook Object
Example: Sorting Sheets in a Workbook

Chapter 18: The Worksheet Object

Properties and Methods of the Worksheet Object
Children of the Worksheet Object
Example: Printing Sheets

Chapter 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

Chapter 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

Chapter 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

Appendix A: The Shape Object

Appendix B: Getting the Installed Printers

Appendix C: Command Bar Controls and Face IDs

Appendix D: Programming Excel from Another Application

Appendix E: High-Level and Low-Level Languages