| |
| |
| Preface | |
| |
| |
| |
| Introduction | |
| |
| |
| Selecting Special Cells | |
| |
| |
| Setting a Chart's Data Point Labels | |
| |
| |
| Topics in Learning Excel Programming | |
| |
| |
| |
| The VBA Environment | |
| |
| |
| |
| Preliminaries | |
| |
| |
| What Is a Programming Language? | |
| |
| |
| Programming Style | |
| |
| |
| |
| The Visual Basic Editor, Part I | |
| |
| |
| The Project Window | |
| |
| |
| The Properties Window | |
| |
| |
| The Code Window | |
| |
| |
| The Immediate Window | |
| |
| |
| Arranging Windows | |
| |
| |
| |
| The Visual Basic Editor, Part II | |
| |
| |
| Navigating the IDE | |
| |
| |
| Getting Help | |
| |
| |
| Creating a Procedure | |
| |
| |
| Run Time, Design Time, and Break Mode | |
| |
| |
| Errors | |
| |
| |
| Debugging | |
| |
| |
| Macros | |
| |
| |
| |
| The VBA Programming Language | |
| |
| |
| |
| Variables, Data Types, and Constants | |
| |
| |
| Comments | |
| |
| |
| Line Continuation | |
| |
| |
| Constants | |
| |
| |
| Variables and Data Types | |
| |
| |
| VBA Operators | |
| |
| |
| |
| Functions and Subroutines | |
| |
| |
| Calling Functions | |
| |
| |
| Calling Subroutines | |
| |
| |
| Parameters and Arguments | |
| |
| |
| Exiting a Procedure | |
| |
| |
| Public and Private Procedures | |
| |
| |
| Project References | |
| |
| |
| |
| Built-in Functions and Statements | |
| |
| |
| The MsgBox Function | |
| |
| |
| The InputBox Function | |
| |
| |
| VBA String Functions | |
| |
| |
| Miscellaneous Functions and Statements | |
| |
| |
| Handling Errors in Code | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Excel Applications and the Excel Object Model | |
| |
| |
| |
| Object Models | |
| |
| |
| Objects, Properties, and Methods | |
| |
| |
| Collection Objects | |
| |
| |
| Object Model Hierarchies | |
| |
| |
| Object Model Syntax | |
| |
| |
| Object Variables | |
| |
| |
| |
| Excel Applications | |
| |
| |
| Providing Access to an Application's Features | |
| |
| |
| Where to Store an Application | |
| |
| |
| An Example Add-In | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| Example: Adding an Item to an Existing Menu | |
| |
| |
| Augmenting the SRXUtils Application | |
| |
| |
| |
| Built-In Dialog Boxes | |
| |
| |
| The Show Method | |
| |
| |
| |
| 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 | |
| |
| |
| |
| The Excel Object Model | |
| |
| |
| A Perspective on the Excel Object Model | |
| |
| |
| Excel Enums | |
| |
| |
| The VBA Object Browser | |
| |
| |
| |
| The Application Object | |
| |
| |
| Properties and Methods of the Application Object | |
| |
| |
| Children of the Application Object | |
| |
| |
| |
| The Workbook Object | |
| |
| |
| The Workbooks Collection | |
| |
| |
| The Workbook Object | |
| |
| |
| Children of the Workbook Object | |
| |
| |
| Example: Sorting Sheets in a Workbook | |
| |
| |
| |
| The Worksheet Object | |
| |
| |
| Properties and Methods of the Worksheet Object | |
| |
| |
| Children of the Worksheet Object | |
| |
| |
| Protection in Excel XP | |
| |
| |
| Example: Printing Sheets | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| PivotCell and PivotItemList Objects | |
| |
| |
| Calculated Items and Calculated Fields | |
| |
| |
| Example: Printing Pivot Tables | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Smart Tags | |
| |
| |
| What Are Smart Tags? | |
| |
| |
| Smart TagRecognizer Object | |
| |
| |
| SmartTag Object | |
| |
| |
| SmartTagAction Object | |
| |
| |
| SmartTagOptions Object | |
| |
| |
| |
| Appendixes | |
| |
| |
| |
| The Shape Object | |
| |
| |
| |
| Getting the Installed Printers | |
| |
| |
| |
| Command Bar Controls | |
| |
| |
| |
| Face IDs | |
| |
| |
| |
| Programming Excelfrom Another Application | |
| |
| |
| |
| High-Level and Low-Level Languages | |
| |
| |
| |
| New Objects in Excel XP | |
| |
| |
| Index | |