book

Writing Excel Macros with VBA, 2nd Edition

Name: Writing Excel Macros with VBA, 2nd Edition
Author: Steven Roman PhD
ISBN: 9780596003593

by Steven Roman PhD

June 2002

Beginner

570 pages

16h 48m

English

O'Reilly Media, Inc.

Read now

Unlock full access

A Note Regarding Supplemental Files
Preface
Preface to the Second EditionThe Book’s AudienceOrganization of This BookThe Book’s Text and Sample CodeAbout the CodeConventions in this BookObtaining the Sample ProgramsHow to Contact UsAcknowledgments
1. Introduction
1.1. Selecting Special Cells1.2. Setting a Chart’s Data Point Labels1.3. Topics in Learning Excel Programming
I. The VBA Environment
2. Preliminaries
2.1. What Is a Programming Language?2.2. Programming Style2.2.1. Comments2.2.2. Readability2.2.3. Modularity
3. The Visual Basic Editor, Part I
3.1. The Project Window3.1.1. Project Names3.1.2. Project Contents3.1.2.1. The ThisWorkbook object3.1.2.2. Sheet objects3.1.2.3. Standard modules3.1.2.4. Class modules3.1.2.5. UserForm objects3.2. The Properties Window3.3. The Code Window3.3.1. Procedure and Full-Module Views3.3.2. The Object and Procedure List Boxes3.3.2.1. A workbook or sheet object3.3.2.2. A standard module3.3.2.3. A UserForm object3.4. The Immediate Window3.5. Arranging Windows3.5.1. Docking
4. The Visual Basic Editor, Part II
4.1. Navigating the IDE4.1.1. General Navigation4.1.1.1. Navigating the code window at design time4.1.1.2. Tracing code4.1.1.3. Bookmarks4.2. Getting Help4.3. Creating a Procedure4.4. Run Time, Design Time, and Break Mode4.5. Errors4.5.1. Design-Time Errors4.5.2. Compile-Time Errors4.5.3. Run-Time Errors4.5.4. Logical Errors4.6. Debugging4.6.1. Tracing4.6.1.1. Stepping into4.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 mode4.6.2. Watching Expressions4.6.2.1. Quick Watch (Shift-F9)4.6.2.2. The Locals and Watches windows4.7. Macros4.7.1. Recording Macros4.7.2. Running Macros
II. The VBA Programming Language
5. Variables, Data Types, and Constants
5.1. Comments5.2. Line Continuation5.3. Constants5.3.1. Enums5.4. Variables and Data Types5.4.1. Variable Declaration5.4.2. The Importance of Explicit Variable Declaration5.4.2.1. Option Explicit5.4.3. Numeric Data Types5.4.4. Boolean Data Type5.4.5. String Data Type5.4.6. Date Data Type5.4.7. Variant Data Type5.4.8. Excel Object Data Types5.4.8.1. The generic As Object declaration5.4.8.2. The Set statement5.4.9. Arrays5.4.9.1. The dimension of an array5.4.9.2. Dynamic arrays5.4.9.3. The UBound function5.4.10. Variable Naming Conventions5.4.11. Variable Scope5.4.11.1. Procedure-level (local) variables5.4.11.2. Module-level variables5.4.12. Variable Lifetime5.4.12.1. Static variables5.4.13. Variable Initialization5.5. VBA Operators
6. Functions and Subroutines
6.1. Calling Functions6.2. Calling Subroutines6.3. Parameters and Arguments6.3.1. Optional Arguments6.3.2. Named Arguments6.3.3. ByRef Versus ByVal Parameters6.4. Exiting a Procedure6.5. Public and Private Procedures6.6. Project References6.6.1. Fully Qualified Procedure Names

7. Built-in Functions and Statements
7.1. The MsgBox Function7.2. The InputBox Function7.3. VBA String Functions7.4. Miscellaneous Functions and Statements7.4.1. The Is Functions7.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 Function7.4.3. The Switch Function7.4.4. Units Conversions7.4.5. The Beep Statement7.5. Handling Errors in Code7.5.1. The On Error Goto Label Statement7.5.2. The Error Object7.5.3. The On Error GoTo 0 Statement7.5.4. The On Error Resume Next Statement7.5.5. The Resume Statement
8. Control Statements
8.1. The If...Then Statement8.2. The For Loop8.2.1. Exit For8.3. The For Each Loop8.4. The Do Loop8.5. The Select Case Statement8.6. A Final Note on VBA8.6.1. File-Related Functions8.6.2. Date- and Time-Related Functions8.6.3. The Format Function
III. Excel Applications and the Excel Object Model
9. Object Models
9.1. Objects, Properties, and Methods9.1.1. Properties9.1.2. Methods9.2. Collection Objects9.2.1. The Base of a Collection9.3. Object Model Hierarchies9.4. Object Model Syntax9.5. Object Variables9.5.1. The With Statement9.5.2. Object Variables Save Execution Time9.5.3. An Object Variable Is a Pointer9.5.4. Freeing an Object Variable: the Nothing Keyword9.5.5. The Is Operator9.5.6. Default Members9.5.7. Global Members
10. Excel Applications
10.1. Providing Access to an Application’s Features10.1.1. Working with Toolbars and Menus Interactively10.1.2. Assigning Macros to Menus and Toolbars10.2. Where to Store an Application10.2.1. The Excel Startup Folder10.2.2. Excel Templates10.2.3. Excel Add-Ins10.2.3.1. Creating an add-in10.2.3.2. Characteristics of an add-in10.2.3.3. Debugging add-ins10.2.3.4. Deleting an add-in10.3. An Example Add-In10.3.1. Creating the Source Workbook10.3.2. Setting Up the Custom Menus10.3.3. Implementing the Features of the Add-In10.3.4. Final Steps
11. Excel Events
11.1. The EnableEvents Property11.2. Events and the Excel Object Model11.3. Accessing an Event Procedure11.4. Worksheet Events11.5. WorkBook Events11.6. Chart Events11.7. Application Events11.8. QueryTable Refresh Events
12. Custom Menus and Toolbars
12.1. Menus and Toolbars: An Overview12.1.1. Menu Terminology12.1.2. The CommandBar Object12.1.3. Command-Bar Controls12.1.3.1. Popup controls12.1.3.2. Button controls12.1.4. Adding a Menu Item12.2. The CommandBars Collection12.3. Creating a New Menu Bar or Toolbar12.4. Command-Bar Controls12.4.1. Creating a New Command-Bar Control12.5. Built-in Command-Bar-Control IDs12.6. Example: Creating a Menu12.7. Example: Creating a Toolbar12.8. Example: Adding an Item to an Existing Menu12.9. Augmenting the SRXUtils Application12.9.1. Creating the Data Worksheet12.9.2. Setting Up the Custom Menus12.9.3. Implementing the Features of the Add-in12.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 Object14.3. ActiveX Controls14.4. Adding UserForm Code14.5. Excel’s Standard Controls14.6. Example: The ActivateSheet Utility14.6.1. Back to SRXUtils14.6.2. Create the UserForm14.6.2.1. List box14.6.2.2. Activate button14.6.2.3. Cancel button14.6.3. Create the Code Behind the UserForm14.6.3.1. Cancel button code14.6.3.2. ActivateSelectedSheet procedure14.6.3.3. Activate button code14.6.3.4. Double-click lstSheets code14.6.3.5. Enter key event14.6.3.6. Fill the lstSheets list box14.6.4. Trying the Activate Utility14.7. ActiveX Controls on Worksheets14.7.1. Referring to a Control on a Worksheet14.7.2. Adding a Control to a Worksheet Programmatically
15. The Excel Object Model
15.1. A Perspective on the Excel Object Model15.2. Excel Enums15.3. The VBA Object Browser
16. The Application Object
16.1. Properties and Methods of the Application Object16.1.1. Members that Return Children16.1.2. Members that Affect the Display16.1.3. Members that Enable Excel Features16.1.4. Event-Related Members16.1.4.1. OnKey method16.1.4.2. OnTime method16.1.5. Calculation-Related Members16.1.5.1. Calculate method16.1.5.2. CalculateFullRebuild method16.1.5.3. Calculation property (R/W Long)16.1.5.4. CalculateBeforeSave property (R/W Boolean)16.1.5.5. CheckAbort method16.1.6. File-Related Members16.1.6.1. DefaultFilePath property (R/W String)16.1.6.2. DefaultSaveFormat property (R/W Long)16.1.6.3. FileDialog property16.1.6.4. FindFile method16.1.6.5. GetOpenFilename method16.1.6.6. GetSaveAsFilename method16.1.6.7. RecentFiles property (Read-Only)16.1.6.8. SaveWorkspace method16.1.7. Members that Affect the Current State of Excel16.1.8. Members that Produce Actions16.1.8.1. ConvertFormula method16.1.8.2. Evaluate method16.1.8.3. Goto method16.1.8.4. Quit method16.1.9. Miscellaneous Members16.1.9.1. CellFormat, FindFormat and ReplaceFormat object16.1.9.2. InputBox method16.1.9.3. Selection property16.1.9.4. StatusBar property (R/W String) 16.1.9.5. Intersect method16.1.9.6. Union method16.2. Children of the Application Object16.2.1. Name Objects and the Names Collections16.2.2. The Windows Collection and Window Objects16.2.3. The WorksheetFunction Object
17. The Workbook Object
17.1. The Workbooks Collection17.1.1. Add Method17.1.2. Close Method17.1.3. Count Property17.1.4. Item Property17.1.5. Open Method17.1.6. OpenText Method17.2. The Workbook Object17.2.1. Activate Method17.2.2. Close Method17.2.3. DisplayDrawingObjects Property17.2.4. FileFormat Property (Read-Only Long )17.2.5. Name, FullName, and Path Properties17.2.6. HasPassword Property (Read-Only Boolean)17.2.7. PrecisionAsDisplayed Property (R/W Boolean) 17.2.8. PrintOut Method17.2.9. PrintPreview Method17.2.10. Protect Method17.2.11. ReadOnly Property (Read-Only Boolean)17.2.12. RefreshAll Method17.2.13. Save Method17.2.14. SaveAs Method17.2.15. SaveCopyAs Method17.2.16. Saved Property (R/W Boolean) 17.3. Children of the Workbook Object17.3.1. The CustomView Object17.3.2. The Names Collection17.3.3. The Sheets Collection17.3.4. The Styles Collection and the Style Object17.4. Example: Sorting Sheets in a Workbook
18. The Worksheet Object
18.1. Properties and Methods of the Worksheet Object18.2. Children of the Worksheet Object18.3. Protection in Excel XP18.3.1. The Protection Object18.3.2. The AllowEditRange Object18.3.3. The UserAccess Objects18.4. Example: Printing Sheets18.4.1. Create the UserForm18.4.1.1. List box18.4.1.2. Print button18.4.1.3. Cancel button18.4.2. Create the Code Behind the UserForm18.4.2.1. The Declarations section18.4.2.2. Cancel button code18.4.2.3. Print button code18.4.2.4. The Form’s Initialize event18.4.2.5. The PrintSheets procedure
19. The Range Object
19.1. The Range Object as a Collection19.2. Defining a Range Object19.2.1. Range Property19.2.2. Cells Property19.2.3. Column, Columns, Row, and Rows Properties19.2.4. Offset Property19.3. Additional Members of the Range Object19.3.1. Activate Method19.3.2. AddComment Method19.3.3. Address Property (Read-Only String)19.3.4. AutoFill Method19.3.5. AutoFilter Method19.3.6. AutoFit Method19.3.7. AutoFormat Method19.3.8. BorderAround Method19.3.9. Calculate Method19.3.10. Clear Methods19.3.11. ColumnDifferences and RowDifferences Methods19.3.12. ColumnWidth and RowHeight Properties19.3.13. Width, Height, Top, and Left Properties19.3.14. Consolidate Method19.3.15. Copy and Cut Methods19.3.16. CopyFromRecordset Method19.3.17. CreateNames Method19.3.18. CurrentRegion Property19.3.19. Delete Method19.3.20. Dependents and DirectDependents Properties19.3.21. Precedents and DirectPrecedents Properties19.3.22. End Property19.3.23. EntireColumn and EntireRow Properties19.3.24. Fill Methods19.3.25. Find Method19.3.26. FindNext and FindPrevious Methods19.3.27. Formula and FormulaR1C1 Properties19.3.28. FormulaArray Property19.3.29. FormulaHidden Property (R/W Boolean)19.3.30. HasFormula Property (Read-Only)19.3.31. HorizontalAlignment Property19.3.32. IndentLevel Property and InsertIndent Method19.3.33. Insert Method19.3.34. Locked Property19.3.35. Merge-Related Methods and Properties19.3.36. Next and Previous Properties19.3.37. NumberFormat Property 19.3.38. Parse Method19.3.39. PasteSpecial Method19.3.40. PrintOut Method19.3.41. PrintPreview Method19.3.42. Replace Method19.3.43. Select Method19.3.44. ShrinkToFit Property19.3.45. Sort Method19.3.46. SpecialCells Method19.3.47. TextToColumns Method19.3.48. Value Property19.3.49. WrapText Property19.4. Children of the Range Object19.4.1. The Areas Collection19.4.2. The Borders Collection19.4.3. The Border Object19.4.3.1. Color property19.4.3.2. ColorIndex property19.4.3.3. LineStyle property19.4.3.4. Weight property19.4.4. The Characters Object19.4.5. The Comment Object19.4.6. The Font Object19.4.7. The FormatConditions Collection19.4.8. The Interior Object19.4.8.1. Color and ColorIndex properties19.4.8.2. Pattern property19.4.8.3. PatternColor and PatternColorIndex properties19.4.9. The PivotField, PivotItem, and PivotTable Objects19.4.10. The QueryTable Object19.4.11. The Validation Object19.5. Example: Getting the Used Range19.6. Example: Selecting Special Cells19.6.1. Designing the Utility19.6.2. Designing the Dialog19.6.2.1. The Frame control19.6.2.2. Control names19.6.2.3. Tab Order19.6.2.4. Some final tips19.6.3. Writing the Code
20. Pivot Tables
20.1. Pivot Tables20.2. The PivotTable Wizard20.3. The PivotTableWizard Method20.4. The PivotTable Object20.4.1. Naming Data Fields20.4.2. The Complete Code20.5. Properties and Methods of the PivotTable Object20.5.1. Returning a Fields Collection20.5.1.1. ColumnFields property20.5.1.2. DataFields property20.5.1.3. HiddenFields property20.5.1.4. PageFields property20.5.1.5. PivotFields property20.5.1.6. RowFields property20.5.1.7. VisibleFields property20.5.2. Totals-Related Members20.5.3. Returning a Portion of a PivotTable20.5.3.1. ColumnRange property20.5.3.2. DataBodyRange property20.5.3.3. DataLabelRange property20.5.3.4. PageRange and PageRangeCells properties20.5.3.5. RowRange property20.5.3.6. TableRange1 property20.5.3.7. TableRange2 property20.5.4. PivotSelect and PivotSelection20.5.5. Additional Members of the PivotTable Object20.5.5.1. AddFields method20.5.5.2. CalculatedFields method20.5.5.3. Errors-related properties20.5.5.4. Null-related properties20.5.5.5. EnableDrillDown property20.5.5.6. Formatting properties and methods20.5.5.7. Refreshing a pivot table20.5.5.8. PageField-related properties20.5.5.9. Name property20.5.5.10. SaveData property (R/W Boolean) 20.5.5.11. ShowPages method20.5.5.12. SourceData property20.6. Children of the PivotTable Object20.7. The PivotField Object20.7.1. AutoShow-Related Members20.7.2. Sorting Pivot Fields20.7.3. The Fundamental Properties20.7.3.1. Function property20.7.3.2. NumberFormat property20.7.3.3. Orientation property20.7.3.4. Position property20.7.4. Selecting Ranges20.7.4.1. DataRange property20.7.4.2. LabelRange property20.7.5. Dragging Pivot Fields20.7.6. Name, Value, and SourceName Properties20.7.7. Grouping20.7.8. Data Field Calculation 20.7.8.1. Calculation property 20.7.8.2. Calculations not requiring a BaseField/BaseItem20.7.8.3. Calculations requiring a BaseField/BaseItem20.7.9. CurrentPage Property20.7.10. DataType Property20.7.11. HiddenItems and VisibleItems Properties20.7.12. MemoryUsed Property20.7.13. ServerBased Property20.7.14. ShowAllItems Property20.7.15. Subtotals Method20.8. The PivotCache Object20.8.1. Refreshing a Pivot Cache20.8.2. MemoryUsed Property20.8.3. OptimizeCache Property20.8.4. RecordCount Property20.8.5. SourceData Property20.8.6. Sql Property20.9. The PivotItem Object20.9.1. DataRange Property20.9.2. LabelRange Property20.9.3. IsCalculated Property20.9.4. Name, Value, and SourceName Properties20.9.5. Position Property20.9.6. RecordCount Property20.9.7. ShowDetail Property20.9.8. Visible Property20.10. PivotCell and PivotItemList Objects20.10.1. ColumnItems, RowItems, and DataField Properties20.10.2. CustomSubtotalFunction Property20.10.3. PivotCellType Property20.10.4. PivotTable, PivotField, and PivotItem Properties20.11. Calculated Items and Calculated Fields20.12. Example: Printing Pivot Tables20.12.1. Create the UserForm20.12.1.1. List box20.12.1.2. Print button20.12.1.3. Cancel button20.12.2. Create the Code Behind the UserForm20.12.2.1. The Declarations section20.12.2.2. Cancel button code20.12.2.3. Print button code20.12.2.4. The Form’s Initialize event20.12.2.5. PrintPTs procedure
21. The Chart Object
21.1. Chart Objects and ChartObject Objects21.2. Creating a Chart21.2.1. Creating Chart Sheets21.2.2. Creating Embedded Charts21.2.3. An Example of Chart Creation21.2.4. Z-Order and ChartObject Objects21.3. Chart Types21.3.1. ChartType property21.3.1.1. ApplyCustomType method21.4. Children of the Chart Object21.5. The Axes Collection21.6. The Axis Object21.6.1. AxisGroup Property21.6.2. Axis Titles and Their Formatting21.6.2.1. HasTitle property (R/W Boolean) 21.6.2.2. The Border property and the Border object21.6.3. CategoryNames Property21.6.4. CategoryType Property and BaseUnit Property21.6.5. Crosses and CrossesAt Properties21.6.6. Display Units21.6.7. Gridline-Related Properties and the Gridline Object21.6.8. Position- and Dimension-Related Properties21.6.9. Tick Mark-Related Properties21.6.9.1. The TickLabels object21.6.10. Units-Related Properties21.6.11. ReversePlotOrder Property21.6.12. Type Property21.7. The ChartArea Object21.8. The ChartGroup Object21.8.1. UpBars and DownBars21.8.2. DropLines21.8.3. HiLoLines21.8.4. SeriesCollection and Series Objects21.8.5. SeriesLines21.9. The ChartTitle Object21.10. The DataTable Object21.11. The Floor Object21.12. The Legend Object21.12.1. The LegendEntry Object21.12.2. The LegendKey Object21.13. The PageSetup Object21.14. The PlotArea Object21.15. The Series Object21.15.1. Adding a New Series21.15.2. The DataLabel Object21.15.3. The Point Object21.15.3.1. Explosion property21.15.3.2. MarkerSize and MarkerStyle21.16. Properties and Methods of the Chart Object21.16.1. ChartWizard Method21.16.2. Export Method21.16.3. PrintOut Method21.17. Example: Scrolling Through Chart Types21.18. Example: Printing Embedded Charts21.18.1. Create the UserForm21.18.1.1. List box21.18.1.2. Print button21.18.1.3. Cancel button21.18.2. Create the Code Behind the UserForm21.18.2.1. The Declarations section21.18.2.2. Cancel button code21.18.2.3. Print button code21.18.2.4. The Form’s Initialize event21.18.2.5. The PrintCharts procedure21.19. Example: Setting Data Series Labels
22. Smart Tags
22.1. What Are Smart Tags?22.2. SmartTagRecognizer Object22.3. SmartTag Object22.4. SmartTagAction Object22.5. SmartTagOptions Object
IV. Appendixes
A. The Shape Object
A.1. What Is the Shape Object?A.2. Z-OrderA.3. Creating ShapesA.3.1. The TextFrame ObjectA.3.2. The FillFormat ObjectA.3.3. ExamplesA.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 ModelE.2. Getting a Reference to the Excel Application ObjectE.2.1. An Alternative ApproachE.2.1.1. The CreateObject functionE.2.1.2. The GetObject functionE.2.1.3. No object library reference
F. High-Level and Low-Level Languages
F.1. BASICF.2. Visual BasicF.3. C and C++F.4. Visual C++F.5. PascalF.6. FORTRANF.7. COBOLF.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
Copyright

Content preview from Writing Excel Macros with VBA, 2nd Edition

Appendix A. The Shape Object

Now we want to take a brief look at the issue of drawing pictures using VBA code. Since this subject is not fundamental to Excel VBA programming, we will be very brief, but hopefully this introduction will give you the necessary background for further study using the VBA help files.

What Is the Shape Object?

Each Excel sheet (chartsheet or worksheet) and each Excel chart has a drawing layer upon which we can place drawing objects. A drawing object is represented by a Shape object.

As usual, the Shape objects for a sheet are stored in a Shapes collection. The Chart object and the Worksheet object both have a Shapes property that returns the collection of all Shape objects drawn on the chart or worksheet.

There is also a ShapeRange object that holds a collection of selected Shape objects, much as a Range object can contain a collection of selected cells. The ShapeRange object allows us to set the properties of a subcollection of all Shape objects.

The Shape-related objects are shown in Figure A-1.

Figure A-1. The Shape-related objects

Z-Order

Every Shape object has an order, called its z-order , that indicates the object’s relative position with respect to an imaginary z-axis that comes directly out of the monitor at right angles, towards the user, as pictured in Figure A-2.

Figure A-2. Illustrating z-order

The read-only ZOrderPosition property of a Shape ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596003595Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design