Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Writing Excel Macros with VBA, 2nd Edition
SPECIAL OFFER: Upgrade this ebook with O’Reilly
A Note Regarding Supplemental Files
Preface
Preface to the Second Edition
The Book's Audience
Organization of This Book
The Book's Text and Sample Code
About the Code
Conventions in this Book
Obtaining the Sample Programs
How to Contact Us
Acknowledgments
1. Introduction
1.1. Selecting Special Cells
1.2. Setting a Chart's Data Point Labels
1.3. Topics in Learning Excel Programming
I. The VBA Environment
2. Preliminaries
2.1. What Is a Programming Language?
2.2. Programming Style
2.2.1. Comments
2.2.2. Readability
2.2.3. Modularity
3. The Visual Basic Editor, Part I
3.1. The Project Window
3.1.1. Project Names
3.1.2. Project Contents
3.1.2.1. The ThisWorkbook object
3.1.2.2. Sheet objects
3.1.2.3. Standard modules
3.1.2.4. Class modules
3.1.2.5. UserForm objects
3.2. The Properties Window
3.3. The Code Window
3.3.1. Procedure and Full-Module Views
3.3.2. The Object and Procedure List Boxes
3.3.2.1. A workbook or sheet object
3.3.2.2. A standard module
3.3.2.3. A UserForm object
3.4. The Immediate Window
3.5. Arranging Windows
3.5.1. Docking
4. The Visual Basic Editor, Part II
4.1. Navigating the IDE
4.1.1. General Navigation
4.1.1.1. Navigating the code window at design time
4.1.1.2. Tracing code
4.1.1.3. Bookmarks
4.2. Getting Help
4.3. Creating a Procedure
4.4. Run Time, Design Time, and Break Mode
4.5. Errors
4.5.1. Design-Time Errors
4.5.2. Compile-Time Errors
4.5.3. Run-Time Errors
4.5.4. Logical Errors
4.6. Debugging
4.6.1. Tracing
4.6.1.1. Stepping into
4.6.1.2. Step Over (Shift-F8 or choose Step Over from the Debug menu)
4.6.1.3. Step Out (Ctrl-Shift-F8 or choose Step Out from the Debug menu)
4.6.1.4. Run To Cursor (Ctrl-F8 or choose Run To Cursor from the Debug menu)
4.6.1.5. Set Next Statement (Ctrl-F9 or choose Set Next Statement from the Debug menu)
4.6.1.6. Breaking out of Debug mode
4.6.2. Watching Expressions
4.6.2.1. Quick Watch (Shift-F9)
4.6.2.2. The Locals and Watches windows
4.7. Macros
4.7.1. Recording Macros
4.7.2. Running Macros
II. The VBA Programming Language
5. Variables, Data Types, and Constants
5.1. Comments
5.2. Line Continuation
5.3. Constants
5.3.1. Enums
5.4. Variables and Data Types
5.4.1. Variable Declaration
5.4.2. The Importance of Explicit Variable Declaration
5.4.2.1. Option Explicit
5.4.3. Numeric Data Types
5.4.4. Boolean Data Type
5.4.5. String Data Type
5.4.6. Date Data Type
5.4.7. Variant Data Type
5.4.8. Excel Object Data Types
5.4.8.1. The generic As Object declaration
5.4.8.2. The Set statement
5.4.9. Arrays
5.4.9.1. The dimension of an array
5.4.9.2. Dynamic arrays
5.4.9.3. The UBound function
5.4.10. Variable Naming Conventions
5.4.11. Variable Scope
5.4.11.1. Procedure-level (local) variables
5.4.11.2. Module-level variables
5.4.12. Variable Lifetime
5.4.12.1. Static variables
5.4.13. Variable Initialization
5.5. VBA Operators
6. Functions and Subroutines
6.1. Calling Functions
6.2. Calling Subroutines
6.3. Parameters and Arguments
6.3.1. Optional Arguments
6.3.2. Named Arguments
6.3.3. ByRef Versus ByVal Parameters
6.4. Exiting a Procedure
6.5. Public and Private Procedures
6.6. Project References
6.6.1. Fully Qualified Procedure Names
7. Built-in Functions and Statements
7.1. The MsgBox Function
7.2. The InputBox Function
7.3. VBA String Functions
7.4. Miscellaneous Functions and Statements
7.4.1. The Is Functions
7.4.1.1. The IsDate function
7.4.1.2. The IsEmpty function
7.4.1.3. The IsNull function
7.4.1.4. The IsNumeric function
7.4.2. The Immediate If Function
7.4.3. The Switch Function
7.4.4. Units Conversions
7.4.5. The Beep Statement
7.5. Handling Errors in Code
7.5.1. The On Error Goto Label Statement
7.5.2. The Error Object
7.5.3. The On Error GoTo 0 Statement
7.5.4. The On Error Resume Next Statement
7.5.5. The Resume Statement
8. Control Statements
8.1. The If...Then Statement
8.2. The For Loop
8.2.1. Exit For
8.3. The For Each Loop
8.4. The Do Loop
8.5. The Select Case Statement
8.6. A Final Note on VBA
8.6.1. File-Related Functions
8.6.2. Date- and Time-Related Functions
8.6.3. The Format Function
III. Excel Applications and the Excel Object Model
9. Object Models
9.1. Objects, Properties, and Methods
9.1.1. Properties
9.1.2. Methods
9.2. Collection Objects
9.2.1. The Base of a Collection
9.3. Object Model Hierarchies
9.4. Object Model Syntax
9.5. Object Variables
9.5.1. The With Statement
9.5.2. Object Variables Save Execution Time
9.5.3. An Object Variable Is a Pointer
9.5.4. Freeing an Object Variable: the Nothing Keyword
9.5.5. The Is Operator
9.5.6. Default Members
9.5.7. Global Members
10. Excel Applications
10.1. Providing Access to an Application's Features
10.1.1. Working with Toolbars and Menus Interactively
10.1.2. Assigning Macros to Menus and Toolbars
10.2. Where to Store an Application
10.2.1. The Excel Startup Folder
10.2.2. Excel Templates
10.2.3. Excel Add-Ins
10.2.3.1. Creating an add-in
10.2.3.2. Characteristics of an add-in
10.2.3.3. Debugging add-ins
10.2.3.4. Deleting an add-in
10.3. An Example Add-In
10.3.1. Creating the Source Workbook
10.3.2. Setting Up the Custom Menus
10.3.3. Implementing the Features of the Add-In
10.3.4. Final Steps
11. Excel Events
11.1. The EnableEvents Property
11.2. Events and the Excel Object Model
11.3. Accessing an Event Procedure
11.4. Worksheet Events
11.5. WorkBook Events
11.6. Chart Events
11.7. Application Events
11.8. QueryTable Refresh Events
12. Custom Menus and Toolbars
12.1. Menus and Toolbars: An Overview
12.1.1. Menu Terminology
12.1.2. The CommandBar Object
12.1.3. Command-Bar Controls
12.1.3.1. Popup controls
12.1.3.2. Button controls
12.1.4. Adding a Menu Item
12.2. The CommandBars Collection
12.3. Creating a New Menu Bar or Toolbar
12.4. Command-Bar Controls
12.4.1. Creating a New Command-Bar Control
12.5. Built-in Command-Bar-Control IDs
12.6. Example: Creating a Menu
12.7. Example: Creating a Toolbar
12.8. Example: Adding an Item to an Existing Menu
12.9. Augmenting the SRXUtils Application
12.9.1. Creating the Data Worksheet
12.9.2. Setting Up the Custom Menus
12.9.3. Implementing the Features of the Add-in
12.9.4. Closing Any Open Add-Ins
13. Built-In Dialog Boxes
13.1. The Show Method
14. Custom Dialog Boxes
14.1. What Is a UserForm Object?
14.2. Creating a UserForm Object
14.3. ActiveX Controls
14.4. Adding UserForm Code
14.5. Excel's Standard Controls
14.6. Example: The ActivateSheet Utility
14.6.1. Back to SRXUtils
14.6.2. Create the UserForm
14.6.2.1. List box
14.6.2.2. Activate button
14.6.2.3. Cancel button
14.6.3. Create the Code Behind the UserForm
14.6.3.1. Cancel button code
14.6.3.2. ActivateSelectedSheet procedure
14.6.3.3. Activate button code
14.6.3.4. Double-click lstSheets code
14.6.3.5. Enter key event
14.6.3.6. Fill the lstSheets list box
14.6.4. Trying the Activate Utility
14.7. ActiveX Controls on Worksheets
14.7.1. Referring to a Control on a Worksheet
14.7.2. Adding a Control to a Worksheet Programmatically
15. The Excel Object Model
15.1. A Perspective on the Excel Object Model
15.2. Excel Enums
15.3. The VBA Object Browser
16. The Application Object
16.1. Properties and Methods of the Application Object
16.1.1. Members that Return Children
16.1.2. Members that Affect the Display
16.1.3. Members that Enable Excel Features
16.1.4. Event-Related Members
16.1.4.1. OnKey method
16.1.4.2. OnTime method
16.1.5. Calculation-Related Members
16.1.5.1. Calculate method
16.1.5.2. CalculateFullRebuild method
16.1.5.3. Calculation property (R/W Long)
16.1.5.4. CalculateBeforeSave property (R/W Boolean)
16.1.5.5. CheckAbort method
16.1.6. File-Related Members
16.1.6.1. DefaultFilePath property (R/W String)
16.1.6.2. DefaultSaveFormat property (R/W Long)
16.1.6.3. FileDialog property
16.1.6.4. FindFile method
16.1.6.5. GetOpenFilename method
16.1.6.6. GetSaveAsFilename method
16.1.6.7. RecentFiles property (Read-Only)
16.1.6.8. SaveWorkspace method
16.1.7. Members that Affect the Current State of Excel
16.1.8. Members that Produce Actions
16.1.8.1. ConvertFormula method
16.1.8.2. Evaluate method
16.1.8.3. Goto method
16.1.8.4. Quit method
16.1.9. Miscellaneous Members
16.1.9.1. CellFormat, FindFormat and ReplaceFormat object
16.1.9.2. InputBox method
16.1.9.3. Selection property
16.1.9.4. StatusBar property (R/W String)
16.1.9.5. Intersect method
16.1.9.6. Union method
16.2. Children of the Application Object
16.2.1. Name Objects and the Names Collections
16.2.2. The Windows Collection and Window Objects
16.2.3. The WorksheetFunction Object
17. The Workbook Object
17.1. The Workbooks Collection
17.1.1. Add Method
17.1.2. Close Method
17.1.3. Count Property
17.1.4. Item Property
17.1.5. Open Method
17.1.6. OpenText Method
17.2. The Workbook Object
17.2.1. Activate Method
17.2.2. Close Method
17.2.3. DisplayDrawingObjects Property
17.2.4. FileFormat Property (Read-Only Long )
17.2.5. Name, FullName, and Path Properties
17.2.6. HasPassword Property (Read-Only Boolean)
17.2.7. PrecisionAsDisplayed Property (R/W Boolean)
17.2.8. PrintOut Method
17.2.9. PrintPreview Method
17.2.10. Protect Method
17.2.11. ReadOnly Property (Read-Only Boolean)
17.2.12. RefreshAll Method
17.2.13. Save Method
17.2.14. SaveAs Method
17.2.15. SaveCopyAs Method
17.2.16. Saved Property (R/W Boolean)
17.3. Children of the Workbook Object
17.3.1. The CustomView Object
17.3.2. The Names Collection
17.3.3. The Sheets Collection
17.3.4. The Styles Collection and the Style Object
17.4. Example: Sorting Sheets in a Workbook
18. The Worksheet Object
18.1. Properties and Methods of the Worksheet Object
18.2. Children of the Worksheet Object
18.3. Protection in Excel XP
18.3.1. The Protection Object
18.3.2. The AllowEditRange Object
18.3.3. The UserAccess Objects
18.4. Example: Printing Sheets
18.4.1. Create the UserForm
18.4.1.1. List box
18.4.1.2. Print button
18.4.1.3. Cancel button
18.4.2. Create the Code Behind the UserForm
18.4.2.1. The Declarations section
18.4.2.2. Cancel button code
18.4.2.3. Print button code
18.4.2.4. The Form's Initialize event
18.4.2.5. The PrintSheets procedure
19. The Range Object
19.1. The Range Object as a Collection
19.2. Defining a Range Object
19.2.1. Range Property
19.2.2. Cells Property
19.2.3. Column, Columns, Row, and Rows Properties
19.2.4. Offset Property
19.3. Additional Members of the Range Object
19.3.1. Activate Method
19.3.2. AddComment Method
19.3.3. Address Property (Read-Only String)
19.3.4. AutoFill Method
19.3.5. AutoFilter Method
19.3.6. AutoFit Method
19.3.7. AutoFormat Method
19.3.8. BorderAround Method
19.3.9. Calculate Method
19.3.10. Clear Methods
19.3.11. ColumnDifferences and RowDifferences Methods
19.3.12. ColumnWidth and RowHeight Properties
19.3.13. Width, Height, Top, and Left Properties
19.3.14. Consolidate Method
19.3.15. Copy and Cut Methods
19.3.16. CopyFromRecordset Method
19.3.17. CreateNames Method
19.3.18. CurrentRegion Property
19.3.19. Delete Method
19.3.20. Dependents and DirectDependents Properties
19.3.21. Precedents and DirectPrecedents Properties
19.3.22. End Property
19.3.23. EntireColumn and EntireRow Properties
19.3.24. Fill Methods
19.3.25. Find Method
19.3.26. FindNext and FindPrevious Methods
19.3.27. Formula and FormulaR1C1 Properties
19.3.28. FormulaArray Property
19.3.29. FormulaHidden Property (R/W Boolean)
19.3.30. HasFormula Property (Read-Only)
19.3.31. HorizontalAlignment Property
19.3.32. IndentLevel Property and InsertIndent Method
19.3.33. Insert Method
19.3.34. Locked Property
19.3.35. Merge-Related Methods and Properties
19.3.36. Next and Previous Properties
19.3.37. NumberFormat Property
19.3.38. Parse Method
19.3.39. PasteSpecial Method
19.3.40. PrintOut Method
19.3.41. PrintPreview Method
19.3.42. Replace Method
19.3.43. Select Method
19.3.44. ShrinkToFit Property
19.3.45. Sort Method
19.3.46. SpecialCells Method
19.3.47. TextToColumns Method
19.3.48. Value Property
19.3.49. WrapText Property
19.4. Children of the Range Object
19.4.1. The Areas Collection
19.4.2. The Borders Collection
19.4.3. The Border Object
19.4.3.1. Color property
19.4.3.2. ColorIndex property
19.4.3.3. LineStyle property
19.4.3.4. Weight property
19.4.4. The Characters Object
19.4.5. The Comment Object
19.4.6. The Font Object
19.4.7. The FormatConditions Collection
19.4.8. The Interior Object
19.4.8.1. Color and ColorIndex properties
19.4.8.2. Pattern property
19.4.8.3. PatternColor and PatternColorIndex properties
19.4.9. The PivotField, PivotItem, and PivotTable Objects
19.4.10. The QueryTable Object
19.4.11. The Validation Object
19.5. Example: Getting the Used Range
19.6. Example: Selecting Special Cells
19.6.1. Designing the Utility
19.6.2. Designing the Dialog
19.6.2.1. The Frame control
19.6.2.2. Control names
19.6.2.3. Tab Order
19.6.2.4. Some final tips
19.6.3. Writing the Code
20. Pivot Tables
20.1. Pivot Tables
20.2. The PivotTable Wizard
20.3. The PivotTableWizard Method
20.4. The PivotTable Object
20.4.1. Naming Data Fields
20.4.2. The Complete Code
20.5. Properties and Methods of the PivotTable Object
20.5.1. Returning a Fields Collection
20.5.1.1. ColumnFields property
20.5.1.2. DataFields property
20.5.1.3. HiddenFields property
20.5.1.4. PageFields property
20.5.1.5. PivotFields property
20.5.1.6. RowFields property
20.5.1.7. VisibleFields property
20.5.2. Totals-Related Members
20.5.3. Returning a Portion of a PivotTable
20.5.3.1. ColumnRange property
20.5.3.2. DataBodyRange property
20.5.3.3. DataLabelRange property
20.5.3.4. PageRange and PageRangeCells properties
20.5.3.5. RowRange property
20.5.3.6. TableRange1 property
20.5.3.7. TableRange2 property
20.5.4. PivotSelect and PivotSelection
20.5.5. Additional Members of the PivotTable Object
20.5.5.1. AddFields method
20.5.5.2. CalculatedFields method
20.5.5.3. Errors-related properties
20.5.5.4. Null-related properties
20.5.5.5. EnableDrillDown property
20.5.5.6. Formatting properties and methods
20.5.5.7. Refreshing a pivot table
20.5.5.8. PageField-related properties
20.5.5.9. Name property
20.5.5.10. SaveData property (R/W Boolean)
20.5.5.11. ShowPages method
20.5.5.12. SourceData property
20.6. Children of the PivotTable Object
20.7. The PivotField Object
20.7.1. AutoShow-Related Members
20.7.2. Sorting Pivot Fields
20.7.3. The Fundamental Properties
20.7.3.1. Function property
20.7.3.2. NumberFormat property
20.7.3.3. Orientation property
20.7.3.4. Position property
20.7.4. Selecting Ranges
20.7.4.1. DataRange property
20.7.4.2. LabelRange property
20.7.5. Dragging Pivot Fields
20.7.6. Name, Value, and SourceName Properties
20.7.7. Grouping
20.7.8. Data Field Calculation
20.7.8.1. Calculation property
20.7.8.2. Calculations not requiring a BaseField/BaseItem
20.7.8.3. Calculations requiring a BaseField/BaseItem
20.7.9. CurrentPage Property
20.7.10. DataType Property
20.7.11. HiddenItems and VisibleItems Properties
20.7.12. MemoryUsed Property
20.7.13. ServerBased Property
20.7.14. ShowAllItems Property
20.7.15. Subtotals Method
20.8. The PivotCache Object
20.8.1. Refreshing a Pivot Cache
20.8.2. MemoryUsed Property
20.8.3. OptimizeCache Property
20.8.4. RecordCount Property
20.8.5. SourceData Property
20.8.6. Sql Property
20.9. The PivotItem Object
20.9.1. DataRange Property
20.9.2. LabelRange Property
20.9.3. IsCalculated Property
20.9.4. Name, Value, and SourceName Properties
20.9.5. Position Property
20.9.6. RecordCount Property
20.9.7. ShowDetail Property
20.9.8. Visible Property
20.10. PivotCell and PivotItemList Objects
20.10.1. ColumnItems, RowItems, and DataField Properties
20.10.2. CustomSubtotalFunction Property
20.10.3. PivotCellType Property
20.10.4. PivotTable, PivotField, and PivotItem Properties
20.11. Calculated Items and Calculated Fields
20.12. Example: Printing Pivot Tables
20.12.1. Create the UserForm
20.12.1.1. List box
20.12.1.2. Print button
20.12.1.3. Cancel button
20.12.2. Create the Code Behind the UserForm
20.12.2.1. The Declarations section
20.12.2.2. Cancel button code
20.12.2.3. Print button code
20.12.2.4. The Form's Initialize event
20.12.2.5. PrintPTs procedure
21. The Chart Object
21.1. Chart Objects and ChartObject Objects
21.2. Creating a Chart
21.2.1. Creating Chart Sheets
21.2.2. Creating Embedded Charts
21.2.3. An Example of Chart Creation
21.2.4. Z-Order and ChartObject Objects
21.3. Chart Types
21.3.1. ChartType property
21.3.1.1. ApplyCustomType method
21.4. Children of the Chart Object
21.5. The Axes Collection
21.6. The Axis Object
21.6.1. AxisGroup Property
21.6.2. Axis Titles and Their Formatting
21.6.2.1. HasTitle property (R/W Boolean)
21.6.2.2. The Border property and the Border object
21.6.3. CategoryNames Property
21.6.4. CategoryType Property and BaseUnit Property
21.6.5. Crosses and CrossesAt Properties
21.6.6. Display Units
21.6.7. Gridline-Related Properties and the Gridline Object
21.6.8. Position- and Dimension-Related Properties
21.6.9. Tick Mark-Related Properties
21.6.9.1. The TickLabels object
21.6.10. Units-Related Properties
21.6.11. ReversePlotOrder Property
21.6.12. Type Property
21.7. The ChartArea Object
21.8. The ChartGroup Object
21.8.1. UpBars and DownBars
21.8.2. DropLines
21.8.3. HiLoLines
21.8.4. SeriesCollection and Series Objects
21.8.5. SeriesLines
21.9. The ChartTitle Object
21.10. The DataTable Object
21.11. The Floor Object
21.12. The Legend Object
21.12.1. The LegendEntry Object
21.12.2. The LegendKey Object
21.13. The PageSetup Object
21.14. The PlotArea Object
21.15. The Series Object
21.15.1. Adding a New Series
21.15.2. The DataLabel Object
21.15.3. The Point Object
21.15.3.1. Explosion property
21.15.3.2. MarkerSize and MarkerStyle
21.16. Properties and Methods of the Chart Object
21.16.1. ChartWizard Method
21.16.2. Export Method
21.16.3. PrintOut Method
21.17. Example: Scrolling Through Chart Types
21.18. Example: Printing Embedded Charts
21.18.1. Create the UserForm
21.18.1.1. List box
21.18.1.2. Print button
21.18.1.3. Cancel button
21.18.2. Create the Code Behind the UserForm
21.18.2.1. The Declarations section
21.18.2.2. Cancel button code
21.18.2.3. Print button code
21.18.2.4. The Form's Initialize event
21.18.2.5. The PrintCharts procedure
21.19. Example: Setting Data Series Labels
22. Smart Tags
22.1. What Are Smart Tags?
22.2. SmartTagRecognizer Object
22.3. SmartTag Object
22.4. SmartTagAction Object
22.5. SmartTagOptions Object
IV. Appendixes
A. The Shape Object
A.1. What Is the Shape Object?
A.2. Z-Order
A.3. Creating Shapes
A.3.1. The TextFrame Object
A.3.2. The FillFormat Object
A.3.3. Examples
A.4. Diagram, DiagramNode, and DiagramNodeChildren Objects
B. Getting the Installed Printers
C. Command Bar Controls
C.1. Built-in Command-Bar Controls
D. Face IDs
E. Programming Excelfrom Another Application
E.1. Setting a Reference to the Excel Object Model
E.2. Getting a Reference to the Excel Application Object
E.2.1. An Alternative Approach
E.2.1.1. The CreateObject function
E.2.1.2. The GetObject function
E.2.1.3. No object library reference
F. High-Level and Low-Level Languages
F.1. BASIC
F.2. Visual Basic
F.3. C and C++
F.4. Visual C++
F.5. Pascal
F.6. FORTRAN
F.7. COBOL
F.8. LISP
G. New Objects in Excel XP
AllowEditRange Object
AutoRecover Object
CalculatedMember Object
CellFormat Object
CustomProperty Object
Diagram, DiagramNode and DiagramNodeChildren Objects
Error Object
ErrorCheckingOptions Object
Graphic Object
IRTDServer and IRTDUpdateEvent Objects
PivotCell and PivotItemList Objects
Protection Object
RTD Object
SmartTag Related Objects
Speech Object
SpellingOptions Object
Tab Object
UsedObjects Object
UserAccessList andUserAccess Objects
Watch Object
Index
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly
← Prev
Back
Next →
← Prev
Back
Next →