book

Excel® 2007 VBA Programmer's Reference

Name: Excel® 2007 VBA Programmer's Reference
ISBN: 9780470046432

by John Green, Stephen Bullen, Rob Bovey, Michael Alexander

March 2007

Intermediate to advanced

1173 pages

26h 1m

English

Wrox

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Introduction
Early Spreadsheet MacrosThe XLM Macro LanguageExcel 5Excel 97Excel 2000Excel 2002Excel 2003Excel 2007Excel 2007 VBA Programmer's ReferenceVersion IssuesWhat You Need to Use this BookConventions UsedIn Case of a Crisis...FeedbackQuestions?
1. Primer in Excel VBA
1.1. Using the Macro Recorder1.1.1. Recording Macros1.1.1.1. Macro Security1.1.1.2. The Personal Macro Workbook1.1.2. Running Macros1.1.2.1. Shortcut Keys1.1.2.2. Absolute and Relative Recording1.1.3. The Visual Basic Editor1.1.3.1. Code Modules1.1.3.2. Procedures1.1.3.3. The Project Explorer1.1.3.4. The Properties Window1.1.4. Other Ways to Run Macros1.1.4.1. Worksheet Buttons1.1.4.2. Forms Controls1.1.4.3. ActiveX Controls1.1.4.4. Quick Access Toolbar1.1.4.5. Event Procedures1.2. User-Defined Functions1.2.1. Creating a UDF1.2.1.1. Direct Reference to Ranges1.2.2. What UDFs Cannot Do1.3. The Excel Object Model1.3.1. Objects1.3.1.1. Collections1.3.1.2. Properties1.3.1.3. Methods1.3.1.4. Events1.3.2. Getting Help1.3.2.1. The Object Browser1.3.3. Experimenting in the Immediate Window1.4. The VBA Language1.4.1. Basic Input and Output1.4.1.1. Parameters Specified by Position1.4.1.2. Parameters Specified by Name1.4.1.3. Constants1.4.1.4. Return Values1.4.1.4.1. Parentheses1.4.1.5. InputBox1.4.2. Calling Functions and Sub Procedures1.4.2.1. The Call Statement1.4.3. Parentheses and Argument Lists1.4.3.1. Without the Call Statement1.4.3.2. An Important Subtlety Regarding Parentheses1.4.3.3. With the Call Statement1.4.4. Variable Declaration1.4.4.1. Option Explicit1.4.5. Scope and Lifetime of Variables1.4.6. Variable Type1.4.6.1. Declaring Variable Type1.4.6.2. Declaring Function and Parameter Types1.4.6.3. Constants1.4.6.4. Variable Naming Conventions1.4.7. Object Variables1.4.7.1. With...End With1.4.8. Making Decisions1.4.8.1. If Statements1.4.8.2. Block If1.4.8.3. Select Case1.4.9. Looping1.4.9.1. Do...Loop1.4.9.2. For...Next Loop1.4.9.3. For Each...Next Loop1.4.10. Arrays1.4.10.1. Multi-Dimensional Arrays1.4.10.2. Dynamic Arrays1.4.11. Run-Time Error-Handling1.4.11.1. On Error Resume Next1.5. Summary
2. The Application Object
2.1. Globals2.2. The Active Properties2.3. Display Alerts2.4. Screen Updating2.5. Evaluate2.6. InputBox2.7. StatusBar2.8. SendKeys2.9. OnTime2.10. OnKey2.11. Worksheet Functions2.12. Caller2.13. Summary
3. Workbooks and Worksheets
3.1. The Workbooks Collection3.1.1. Getting a Filename from a Path3.1.2. Files in the Same Directory3.1.3. Overwriting an Existing Workbook3.1.4. Saving Changes3.2. The Sheets Collection3.2.1. Worksheets3.2.2. Copy and Move3.2.3. Grouping Worksheets3.3. The Window Object3.3.1. Synchronizing Worksheets3.4. Summary
4. Using Ranges
4.1. Activate and Select4.2. Range Property4.2.1. Shortcut Range References4.2.2. Ranges on Inactive Worksheets4.2.3. Range Property of a Range Object4.2.4. Cells Property4.2.5. Cells Used in Range4.2.6. Ranges of Inactive Worksheets4.2.7. More on the Cells Property of the Range Object4.2.8. Single-Parameter Range Reference4.3. Offset Property4.4. Resize Property4.5. SpecialCells Method4.5.1. Last Cell4.5.2. Deleting Numbers4.6. CurrentRegion Property4.7. End Property4.7.1. Referring to Ranges with End4.8. Summing a Range4.9. Columns and Rows Properties4.9.1. Areas4.10. Union and Intersect Methods4.11. Empty Cells4.12. Transferring Values between Arrays and Ranges4.12.1. Deleting Rows4.13. Summary
5. Using Names
5.1. Naming Ranges5.1.1. Using the Name Property of the Range Object5.2. Special Names5.3. Storing Values in Names5.4. Storing Arrays5.5. Hiding Names5.6. Working with Named Ranges5.7. Searching for a Name5.7.1. Searching for the Name of a Range5.7.2. Determining which Names Overlap a Range5.8. Summary

6. Data Lists
6.1. Structuring the Data6.2. Sorting a Range6.2.1. Older Excel Versions6.3. Creating a Table6.4. Sorting a Table6.5. AutoFilter6.5.1. AutoFilter Object6.5.2. Filter Object6.5.3. Date Custom Filter6.5.4. Adding Combo Boxes6.5.4.1. Date Format Problems6.5.4.2. Getting the Exact Date6.5.5. Copying the Visible Rows6.5.6. Finding the Visible Rows6.6. Advanced Filter6.7. Data Form6.8. Summary
7. PivotTables
7.1. Creating a PivotTable Report7.1.1. PivotCaches7.1.2. PivotTables Collection7.2. PivotFields7.2.1. CalculatedFields7.3. PivotItems7.3.1. Grouping7.3.2. Visible Property7.3.3. CalculatedItems7.4. PivotCharts7.5. External Data Sources7.6. Summary
8. Charts
8.1. Chart Sheets8.1.1. The Recorded Macro8.1.2. Adding a Chart Sheet Using VBA Code8.2. Embedded Charts8.2.1. Using the Macro Recorder8.2.2. Adding an Embedded Chart Using VBA Code8.3. Editing Data Series8.4. Defining Chart Series with Arrays8.5. Converting a Chart to Use Arrays8.6. Determining the Ranges Used in a Chart8.7. Chart Labels8.8. Summary
9. Event Procedures
9.1. Worksheet Events9.1.1. Enable Events9.1.2. Worksheet Calculate9.2. Chart Events9.2.1. Before Double Click9.3. Workbook Events9.3.1. Save Changes9.4. Headers and Footers9.5. Summary
10. Adding Controls
10.1. Form and ActiveX Controls10.2. ActiveX Controls10.2.1. Scrollbar Control10.2.2. Spin Button Control10.2.3. CheckBox Control10.2.4. Option Button Controls10.3. Forms Controls10.4. Dynamic ActiveX Controls10.5. Controls on Charts10.6. Summary
11. Text Files and File Dialog
11.1. Opening Text Files11.2. Writing to Text Files11.3. Reading Text Files11.4. Writing to Text Files Using Print11.4.1. Reading Data Strings11.4.2. Flexible Separators and Delimiters11.5. FileDialog11.5.1. FileDialogFilters11.5.2. FileDialogSelectedItems11.5.3. Dialog Types11.5.4. Execute Method11.5.5. MultiSelect11.6. Summary
12. Working with XML and the Open XML File Formats
12.1. The Basics of Using XML Data in Excel12.1.1. XML Fundamentals12.1.1.1. The XML Declaration12.1.1.2. Processing Instructions12.1.1.3. Comments12.1.1.4. Elements and the Root Element12.1.1.5. Attributes12.1.1.6. Namespaces12.1.1.7. Viewing and Editing an XML Document12.1.2. Consuming XML Data Directly12.1.3. Creating and Managing Your Own XML Maps12.1.3.1. Creating Your Own XML Schema Description12.1.3.2. Creating Your Own XML Map12.2. Using VBA to Program XML Processes12.2.1. Programming XML Maps12.2.1.1. Open an XML Document Directly into a List12.2.1.2. Programmatically Changing XML Map Properties12.2.1.3. Refresh Your XML Data12.2.1.4. Turn Your XML Lists into Hard Data12.2.1.5. Creating Your Own XSD12.2.1.6. Creating a Custom XML List12.2.1.7. Importing Data into an Existing XML Map12.2.1.8. Exporting to an XML File12.2.2. Leveraging DOM and XPath to Manipulate XML Files12.2.2.1. Loading XML into a DOM Document12.2.2.2. Using DOM with ADO to Convert Excel Data to XML12.2.2.3. Traversing and Modifying XML Files with DOM and XPath12.2.2.3.1. Return all Employee IDs12.2.2.3.2. Return all Nodes for Any Employee with an Invoice Amount over $300012.2.2.3.3. Trap the Node that Contains the FirstName Mike12.2.2.3.4. Find and Edit the Node that Contains the FirstName Mike12.2.2.3.5. Find and Delete all Nodes for the Employee Bullen12.3. Using VBA to Program Open XML Files12.3.1. Programming Open XML Files with VBA12.3.1.1. Programmatically Unzipping an Excel Container12.3.2. Programmatically Zipping an Excel Container12.3.2.1. Edit the sharedStrings XML File to Implement Mass Updates to Text12.3.2.2. Unprotect a Worksheet via Open XML Manipulation12.3.2.3. Updating Connection Strings12.4. Summary
13. UserForms
13.1. Displaying a UserForm13.2. Creating a UserForm13.3. Directly Accessing Controls in UserForms13.4. Stopping the Close Button13.5. Maintaining a Data List13.6. Modeless UserForms13.6.1. Progress Indicator13.7. Variable UserForm Name13.8. Summary
14. RibbonX
14.1. Overview14.2. Prerequisites14.3. Adding the Customizations14.4. XML Structure14.5. RibbonX and VBA14.6. Control Types14.6.1. Basic Controls14.6.2. Container Controls14.7. Control Attributes14.8. Control Callbacks14.9. Managing Control Images14.10. Other RibbonX Elements, Attributes, and Callbacks14.10.1. Sharing Controls among Multiple Workbooks14.10.2. Updating Controls at Run Time14.10.3. Hooking Built-In Controls14.10.4. RibbonX in Dictator Applications14.10.5. Customizing the Office Menu14.10.6. Customizing the QAT14.10.7. Controlling Tabs, Tab Sets, and Groups14.11. Dynamic Controls14.11.1. dropDown, comboBox, and gallery14.11.2. dynamicMenu14.12. CommandBar Extensions for the Ribbon14.13. RibbonX Limitations14.14. Summary
15. Command Bars
15.1. Toolbars, Menu Bars, and Popups15.2. Excel's Built-in Command Bars15.3. Controls at All Levels15.3.1. FaceIds15.4. Creating New Menus15.4.1. The OnAction Macros15.5. Passing Parameter Values15.6. Deleting a Menu15.7. Creating a Toolbar15.8. Popup Menus15.9. Showing Popup Command Bars15.9.1. Table-Driven Command Bar Creation15.10. Summary
16. Class Modules
16.1. Creating Your Own Objects16.2. Property Procedures16.3. Creating Collections16.3.1. Class Module Collection16.4. Encapsulation16.5. Trapping Application Events16.6. Embedded Chart Events16.7. A Collection of UserForm Controls16.8. Referencing Classes Across Projects16.9. Summary
17. Add-ins
17.1. Hiding the Code17.2. Creating an Add-in17.3. Closing Add-ins17.4. Code Changes17.5. Saving Changes17.6. Interface Changes17.7. Installing an Add-in17.8. AddinInstall Event17.9. Removing an Add-in from the Add-ins List17.10. Summary
18. Automation Add-Ins and COM Add-Ins
18.1. Automation Add-Ins18.1.1. A Simple Add-In — Sequence18.1.2. Registering Automation Add-Ins with Excel18.1.2.1. Through the Excel User Interface18.1.2.2. Using VBA18.1.2.3. In the Registry18.1.3. Using Automation Add-Ins18.1.3.1. In the Worksheet18.1.3.2. In VBA18.1.4. Introducing the IDTExtensibility2 Interface18.1.4.1. A Complex Add-In — RandUnique18.1.4.2. A QuickSort Routine18.2. COM Add-Ins18.2.1. The IDTExtensibility2 Interface (Continued)18.2.2. Registering a COM Add-In with Excel18.2.3. The COM Add-In Designer18.2.3.1. Linking to Excel18.2.3.1.1. Responding to Excel's Events18.2.3.1.2. Adding CommandBar Controls18.2.3.2. Using a COM Add-In from VBA18.2.3.2.1. Adding Ribbon Controls18.2.3.3. Creating Custom Task Panes18.2.3.4. Showing VBA UserForms as Task Panes18.2.3.5. Putting it all Together18.2.3.6. Linking to Multiple Office Applications18.3. Summary
19. Interacting with Other Office Applications
19.1. Establishing the Connection19.1.1. Late Binding19.1.2. Early Binding19.2. Opening a Document in Word19.3. Accessing an Active Word Document19.4. Creating a New Word Document19.5. Access and ADO19.6. Access, Excel, and, Outlook19.7. Better than Mail Merge19.7.1. Readable Document Variables19.8. Summary
20. Data Access with ADO
20.1. An Introduction to Structured Query Language (SQL)20.1.1. The SELECT Statement20.1.2. The INSERT Statement20.1.3. The UPDATE Statement20.1.4. The DELETE Statement20.2. An Overview of ADO20.2.1. The Connection Object20.2.1.1. Connection Object Properties20.2.1.1.1. The ConnectionString Property20.2.1.1.2. The ConnectionTimeout Property20.2.1.1.3. The State Property20.2.1.2. Connection Object Methods20.2.1.2.1. The Open Method20.2.1.2.2. The Execute Method20.2.1.2.3. The Close Method20.2.1.3. Connection Object Events20.2.1.4. Connection Object Collections20.2.1.4.1. Errors Collection20.2.1.4.2. The Properties Collection20.2.2. The Recordset Object20.2.2.1. Recordset Object Properties20.2.2.1.1. The ActiveConnection Property20.2.2.1.2. The BOF and EOF Properties20.2.2.1.3. The CursorLocation Property20.2.2.1.4. The Filter Property20.2.2.1.5. The State Property20.2.2.2. Recordset Object Methods20.2.2.2.1. The Open Method20.2.2.2.2. The Close Method20.2.2.2.3. The Move Methods20.2.2.2.4. The NextRecordset Method20.2.2.3. Recordset Object Events20.2.2.4. Recordset Object Collections20.2.2.4.1. The Fields Collection20.2.2.4.2. The Properties Collection20.2.3. The Command Object20.2.3.1. Command Object Properties20.2.3.1.1. The ActiveConnection Property20.2.3.1.2. The CommandText Property20.2.3.1.3. The CommandType Property20.2.3.2. Command Object Methods20.2.3.2.1. The CreateParameter Method20.2.3.2.2. The Execute Method20.2.3.3. Command Object Collections20.2.3.3.1. The Parameters Collection20.2.3.3.2. The Properties Collection20.2.4. Using ADO in Microsoft Excel Applications20.2.5. Using ADO with Microsoft Access20.2.5.1. Connecting to Microsoft Access20.2.5.2. Retrieving Data from Microsoft Access Using a Plain Text Query20.2.5.3. Retrieving Data from Microsoft Access Using a Stored Query20.2.5.4. Inserting, Updating, and Deleting Records with Plain Text SQL in Microsoft Access20.2.6. Using ADO with Microsoft SQL Server20.2.6.1. Connecting to Microsoft SQL Server20.2.6.2. Microsoft SQL Server Stored Procedures20.2.6.3. Multiple Recordsets20.2.6.4. Disconnected Recordsets20.2.7. Using ADO with Non-Standard Data Sources20.2.7.1. Querying Microsoft Excel Workbooks20.2.7.2. Inserting and Updating Records in Microsoft Excel Workbooks20.2.7.3. Querying Text Files20.3. Summary
21. Managing External Data
21.1. The External Data User Interface21.1.1. Get External Data21.1.2. Manage Connections21.2. The QueryTable and ListObject21.2.1. A QueryTable from a Relational Database21.2.2. A Query Table Associated with a ListObject21.2.3. QueryTables and Parameter Queries21.2.4. QueryTables from Web Queries21.2.5. A QueryTable from a Text File21.2.6. Creating and Using Connection Files21.2.6.1. Office Data Connect Files21.2.6.2. Web Query Files21.3. The WorkbookConnection Object and the Connections Collection21.4. External Data Security Settings21.5. Summary
22. The Trust Center and Document Security
22.1. The Trust Center22.1.1. Trusted Publishers22.1.2. Trusted Locations22.1.3. Add-ins22.1.4. ActiveX Settings22.1.5. Macro Settings22.1.6. Message Bar22.1.7. External Content22.1.8. Privacy Options22.2. Automating Document Inspection22.2.1. The RemoveDocumentInformation Method22.2.2. The DocumentInspectors Collection22.3. Summary
23. Browsing OLAP Data Sources with Excel
23.1. Analyzing OLAP Data via Pivot Tables23.1.1. Connecting to an OLAP Data Source23.1.2. Browsing the OLAP Data Source23.2. Understanding the MDX behind OLAP-based Pivot Tables23.2.1. The Basics of MDX23.2.1.1. Deciphering Excel's MDX Queries23.2.1.2. Create an MDX Log23.2.1.2.1. NON EMPTY23.2.1.2.2. Hierarchize23.2.1.2.3. DrilldownLevel23.2.1.2.4. Various Server-Side Settings23.3. Browsing OLAP Data Sources without Pivot Tables23.3.1. Using ADO to Return Flattened Recordsets23.3.2. Using ADO MD to Get Cube Schema Information23.3.3. Creating an Inventory of Dimensions, Hierarchies, and Levels23.4. Creating Offline Cubes23.4.1. Creating an Offline Cube Manually23.4.2. Using the CreateCubeFile Method23.4.3. Creating an Offline Cube Using ADO MD and VBA23.5. Summary
24. Excel and the Internet
24.1. What Can the Internet Do for You?24.2. Using the Internet for Storing Workbooks24.3. Using the Internet as a Data Source24.3.1. Opening Web Pages as Workbooks24.3.2. Using Web Queries24.3.3. Parsing Web Pages for Specific Information24.4. Using the Internet to Publish Results24.4.1. Setting Up a Web Server24.4.2. Saving Worksheets as Web Pages24.4.3. Creating Interactive Web Pages24.5. Using the Internet as a Communication Channel24.5.1. Communicating with a Web Server24.5.1.1. Sending Data from the Client to the Server Application24.5.1.1.1. URL Encoding24.5.1.1.2. POSTing Data24.6. Summary
25. International Issues
25.1. Changing Windows Regional Settings and the Office 2007 UI Language25.2. Responding to Regional Settings and the Windows Language25.2.1. Identifying the User's Regional Settings and Windows Language25.2.2. VBA Conversion Functions from an International Perspective25.2.2.1. Implicit Conversion25.2.2.2. Date Literals25.2.2.3. The IsNumeric and IsDate Functions25.2.2.4. The CStr Function25.2.2.5. The CDbl, CSng, CLng, CInt, CByte, CCur, and CDec Functions25.2.2.6. The CDate and DateValue Functions25.2.2.7. The CBool25.2.2.8. The Format Function25.2.2.9. The FormatCurrency, FormatDateTime, FormatNumber, and FormatPercent Functions25.2.2.10. The Str Function25.2.2.11. The sNumToUS Function25.2.2.12. The Val Function25.2.2.13. Application.Evaluate25.2.2.13.1. The IsDateUS Function25.2.2.13.2. The DateValueUS Function25.3. Interacting with Excel25.3.1. Sending Data to Excel25.3.2. Reading Data from Excel25.3.3. The Rules for Working with Excel25.4. Interacting with Users25.4.1. Paper Sizes25.4.2. Displaying Data25.4.3. Interpreting Data25.4.4. The xxxLocal Properties25.4.5. The Rules for Working with Your Users25.5. Excel 2007's International Options25.6. Features That Don't Play by the Rules25.6.1. The OpenText Function25.6.2. The SaveAs Function25.6.3. The ShowDataForm Sub Procedure25.6.4. Pasting Text25.6.5. PivotTable Calculated Fields and Items, and Conditional Format and Data Validation Formulas25.6.6. Web Queries25.6.7. =TEXT() Worksheet Function25.7. The Range.Value, Range.Formula, and Range.FormulaArray Properties25.8. The Range.AutoFilter Method25.9. The Range.AdvancedFilter Method25.10. The Application.Evaluate, Application.ConvertFormula, and Application.ExecuteExcel4Macro Functions25.11. Responding to Office 2007 Language Settings25.11.1. Where Does the Text Come From?25.11.1.1. Regional Settings Location25.11.1.2. Office UI Language Settings25.11.1.3. Language Version of Windows25.11.2. Identifying the Office UI Language Settings25.11.3. Creating a Multilingual Application25.11.3.1. A Suggested Approach25.11.3.2. How to Store String Resources25.11.4. Working in a Multilingual Environment25.11.4.1. Allow Extra Space25.11.4.2. Using Excel's Objects25.11.4.3. Using SendKeys25.11.4.4. Using RibbonX25.11.5. The Rules for Developing a Multilingual Application25.12. Some Helpful Functions25.12.1. The bWinToNum Function25.12.2. The bWinToDate Function25.12.3. The sFormatDate Function25.12.4. The ReplaceHolders Function25.13. Summary
26. Programming the VBE
26.1. Identifying VBE Objects in Code26.1.1. The VBE Object26.1.2. The VBProject Object26.1.3. The VBComponent Object26.1.4. The CodeModule Object26.1.5. The CodePane Object26.1.6. The Designer Object26.2. Starting Up26.3. Adding Menu Items to the VBE26.4. Working with Workbooks26.5. Working with Code26.6. Working with UserForms26.7. Working with References26.8. COM Add-ins26.9. Summary
27. Programming with the Windows API
27.1. Anatomy of an API Call27.2. Interpreting C-Style Declarations27.3. Constants, Structures, Handles, and Classes27.4. What If Something Goes Wrong?27.5. Wrapping API Calls in Class Modules27.6. Some Example Classes27.6.1. A High-Resolution Timer Class27.6.2. Class Module CHighResTimer27.6.3. Freeze a UserForm27.6.4. A System Info Class27.7. Modifying UserForm Styles27.7.1. Window Styles27.7.2. The CFormChanger Class27.8. Resizable UserForms27.8.1. Absolute Changes27.8.2. Relative Changes27.8.3. The CFormResizer Class27.9. Summary
A. Excel 2007 Object Model
A.1. Common Properties with Collections and Associated ObjectsA.1.1. Common Collection PropertiesA.1.2. Common Collection MethodsA.1.3. Common Object PropertiesA.2. Excel Objects and Their Properties, Methods, and EventsA.2.1. AboveAverage ObjectA.2.2. AboveAverage PropertiesA.2.2.1. AboveAverage PropertiesA.2.2.2. AboveAverage MethodsA.2.2.3. AboveAverage Object ExampleA.2.3. Action Object and the Actions CollectionA.2.3.1. Action PropertiesA.2.4. Add-In Object and the Addins CollectionA.2.5. Add-In Common PropertiesA.2.5.1. Add-In PropertiesA.2.5.2. AddIn Object and the AddIns Collection ExampleA.2.6. Adjustments ObjectA.2.6.1. Adjustments Common PropertiesA.2.6.2. Adjustments PropertiesA.2.6.3. Adjustments Object ExampleA.2.7. AllowEditRange Object and the AllowEditRanges CollectionA.2.7.1. AllowEditRanges Collection PropertiesA.2.7.2. AllowEditRanges Collection MethodsA.2.7.3. AllowEditRange PropertiesA.2.7.4. AllowEditRange MethodsA.2.7.5. AllowEditRange Object ExampleA.2.8. Application ObjectA.2.8.1. Application Common PropertiesA.2.8.2. Application PropertiesA.2.8.3. Application MethodsA.2.8.4. Application EventsA.2.8.5. Application Object ExampleA.2.9. Areas CollectionA.2.9.1. Areas Common PropertiesA.2.9.2. Areas PropertiesA.2.9.3. Areas Collection ExampleA.2.10. AutoCorrect ObjectA.2.10.1. AutoCorrect Common PropertiesA.2.10.2. AutoCorrect PropertiesA.2.10.3. AutoCorrect MethodsA.2.10.4. AutoCorrect Object ExampleA.2.11. AutoFilter ObjectA.2.11.1. AutoFilter Common PropertiesA.2.11.2. AutoFilter PropertiesA.2.11.3. AutoFilter MethodsA.2.11.4. AutoFilter Object ExampleA.2.12. AutoRecover ObjectA.2.12.1. AutoRecover Common PropertiesA.2.12.2. AutoRecover PropertiesA.2.12.3. AutoRecover Object ExampleA.2.13. Axis Object and the Axes CollectionA.2.13.1. Axis Common PropertiesA.2.13.2. Axis PropertiesA.2.13.3. Axis MethodsA.2.13.4. Axis Object and the Axes Collection ExampleA.2.14. AxisTitle ObjectA.2.14.1. AxisTitle Common PropertiesA.2.14.2. AxisTitle PropertiesA.2.14.3. AxisTitle MethodsA.2.14.4. AxisTitle Object ExampleA.2.15. Border Object and the Borders CollectionA.2.15.1. Borders Collection PropertiesA.2.15.2. Border Common PropertiesA.2.15.3. Border PropertiesA.2.15.4. Border Object and the Borders Collection ExampleA.2.16. CalculatedFields CollectionA.2.17. CalculatedItems CollectionA.2.18. CalculatedMember Object and the CalculatedMembers CollectionA.2.18.1. CalculatedMembers Common PropertiesA.2.18.2. CalculatedMembers Collection PropertiesA.2.18.3. CalculatedMembers Collection MethodsA.2.18.4. CalculatedMember Common PropertiesA.2.18.5. CalculatedMember PropertiesA.2.18.6. CalculatedMember MethodsA.2.18.7. CalculatedMembers Collection and CalculatedMember Object ExampleA.2.19. CalloutFormat ObjectA.2.19.1. CalloutFormat Common PropertiesA.2.19.2. CalloutFormat PropertiesA.2.19.3. CalloutFormat MethodsA.2.19.4. CalloutFormat Object ExampleA.2.20. CellFormat ObjectA.2.20.1. CellFormat Common PropertiesA.2.20.2. CellFormat PropertiesA.2.20.3. CellFormat MethodsA.2.20.4. CellFormat Object ExampleA.2.21. Characters ObjectA.2.21.1. Characters Common PropertiesA.2.21.2. Characters PropertiesA.2.21.3. Characters MethodsA.2.21.4. Characters Object ExampleA.2.22. Chart Object and the Charts CollectionA.2.22.1. Charts Collection Properties and MethodsA.2.22.2. Chart Common PropertiesA.2.22.3. Chart PropertiesA.2.22.4. Chart MethodsA.2.22.5. Chart EventsA.2.22.6. Chart Object and the Charts Collection ExampleA.2.23. ChartArea ObjectA.2.23.1. ChartArea Common PropertiesA.2.23.2. ChartArea PropertiesA.2.23.3. ChartArea MethodsA.2.23.4. ChartArea Object ExampleA.2.24. ChartColorFormat ObjectA.2.24.1. ChartColorFormat Common PropertiesA.2.24.2. ChartColorFormat PropertiesA.2.25. ChartFillFormat ObjectA.2.25.1. ChartFillFormat Common PropertiesA.2.25.2. ChartFillFormat PropertiesA.2.25.3. ChartFillFormat MethodsA.2.25.4. ChartFillFormat Object ExampleA.2.26. ChartFormat ObjectA.2.26.1. ChartFormat Common PropertiesA.2.26.2. ChartFormat PropertiesA.2.27. ChartGroup Object and the ChartGroups CollectionA.2.27.1. ChartGroup Common PropertiesA.2.27.2. ChartGroup PropertiesA.2.27.3. ChartGroup MethodsA.2.27.4. ChartGroup Object and the ChartGroups Collection ExampleA.2.28. ChartObject Object and the ChartObjects CollectionA.2.28.1. ChartObjects Collection Properties and MethodsA.2.28.2. ChartObject Common PropertiesA.2.28.3. ChartObject PropertiesA.2.28.4. ChartObject MethodsA.2.28.5. ChartObject Object and the ChartObjects Collection ExampleA.2.29. ChartTitle ObjectA.2.29.1. ChartTitle Common PropertiesA.2.29.2. ChartTitle PropertiesA.2.29.3. ChartTitle MethodsA.2.29.4. ChartTitle Object ExampleA.2.30. ChartView ObjectA.2.30.1. ChartView Common PropertiesA.2.30.2. ChartView PropertiesA.2.31. ColorFormat ObjectA.2.31.1. ColorFormat Common PropertiesA.2.31.2. ColorFormat PropertiesA.2.31.3. ColorFormat Object ExampleA.2.32. ColorScale ObjectA.2.33. ColorScale PropertiesA.2.33.1. ColorScale PropertiesA.2.33.2. ColorScale MethodsA.2.33.3. ColorScale Object ExampleA.2.34. End SubColorScaleCriterion and the ColorScaleCriteria CollectionA.2.35. ColorScaleCriteria Common PropertiesA.2.35.1. ColorScaleCriterion PropertiesA.2.36. ColorStop Object and ColorStops CollectionA.2.36.1. ColorStops Common PropertiesA.2.36.2. ColorStops MethodsA.2.36.3. ColorStop Common PropertiesA.2.36.4. ColorStops MethodsA.2.37. Comment Object and the Comments CollectionA.2.37.1. Comment Common PropertiesA.2.37.2. Comment PropertiesA.2.37.3. Comment MethodsA.2.37.4. Comment Object and the Comments Collection ExampleA.2.38. ConditionValue ObjectA.2.39. ConditionValue PropertiesA.2.39.1. ConditionValue PropertiesA.2.39.2. ConditionValue MethodsA.2.40. Connections ObjectA.2.40.1. Connections Common PropertiesA.2.40.2. Connections MethodsA.2.40.2.1. Connections ExampleA.2.41. ConnectorFormat ObjectA.2.41.1. ConnectorFormat Common PropertiesA.2.41.2. ConnectorFormat PropertiesA.2.41.3. ConnectorFormat MethodsA.2.41.4. ConnectorFormat Object ExampleA.2.42. ControlFormat ObjectA.2.42.1. ControlFormat Common PropertiesA.2.42.2. ControlFormat PropertiesA.2.42.3. ControlFormat MethodsA.2.42.4. ControlFormat Object ExampleA.2.43. CubeField Object and the CubeFields CollectionA.2.43.1. CubeFields Common PropertiesA.2.43.2. CubeFields Collection MethodsA.2.43.3. CubeField Common PropertiesA.2.43.4. CubeField PropertiesA.2.43.5. CubeField MethodsA.2.44. CustomProperty Object and the CustomProperties CollectionA.2.44.1. CustomProperties Common PropertiesA.2.44.2. CustomProperties Collection PropertiesA.2.44.3. CustomProperties Collection MethodsA.2.44.4. CustomProperty Common PropertiesA.2.44.5. CustomProperty PropertiesA.2.44.6. CustomProperty MethodsA.2.44.7. CustomProperty Object ExampleA.2.45. CustomView Object and the CustomViews CollectionA.2.45.1. CustomView Common PropertiesA.2.45.2. CustomView PropertiesA.2.45.3. CustomView MethodsA.2.45.4. CustomView Object and the CustomViews Collection ExampleA.2.46. Databar ObjectA.2.47. Databar PropertiesA.2.47.1. Databar PropertiesA.2.47.2. Databar MethodsA.2.47.3. Databar Object ExampleA.2.48. DataLabel Object and the DataLabels CollectionA.2.48.1. DataLabels Collection Properties and MethodsA.2.48.2. DataLabel Common PropertiesA.2.48.3. DataLabel PropertiesA.2.48.4. DataLabel MethodsA.2.48.5. DataLabel Object and the DataLabels Collection ExampleA.2.49. DataTable ObjectA.2.49.1. DataTable Common PropertiesA.2.49.2. DataTable PropertiesA.2.49.3. DataTable MethodsA.2.49.4. DataTable Object ExampleA.2.50. DefaultWebOptions ObjectA.2.50.1. DefaultWebOptions Common PropertiesA.2.50.2. DefaultWebOptions PropertiesA.2.50.3. DefaultWebOptions Object ExampleA.2.51. Dialog Object and the Dialogs CollectionA.2.51.1. Dialogs Common PropertiesA.2.51.2. Dialog Common PropertiesA.2.51.3. Dialog MethodsA.2.51.4. Dialog Object and the Dialogs Collection ExampleA.2.52. DisplayUnitLabel ObjectA.2.52.1. DisplayUnitLabel Common PropertiesA.2.52.2. DisplayUnitLabel PropertiesA.2.52.3. DisplayUnitLabel MethodsA.2.52.4. DisplayUnitLabel Object ExampleA.2.53. DownBars ObjectA.2.53.1. DownBars Common PropertiesA.2.53.2. DownBars PropertiesA.2.53.3. DownBars MethodsA.2.54. DropLines ObjectA.2.54.1. DropLines Common PropertiesA.2.54.2. DropLines PropertiesA.2.54.3. DropLines MethodsA.2.55. Error Object and the Errors CollectionA.2.55.1. Errors Common PropertiesA.2.55.2. Errors Collection PropertiesA.2.55.3. Error Common PropertiesA.2.55.4. Error PropertiesA.2.55.4.1. Errors Collection ExampleA.2.56. ErrorBars ObjectA.2.56.1. ErrorBars Common PropertiesA.2.56.2. ErrorBars PropertiesA.2.56.3. ErrorBars MethodsA.2.56.4. ErrorBars Object ExampleA.2.57. ErrorCheckingOptions Collection ObjectA.2.57.1. ErrorCheckingOptions Common PropertiesA.2.57.2. ErrorCheckingOptions Collection PropertiesA.2.58. FillFormat ObjectA.2.58.1. FillFormat Common PropertiesA.2.58.2. FillFormat PropertiesA.2.58.3. FillFormat MethodsA.2.58.4. FillFormat Object ExampleA.2.59. Filter Object and the Filters CollectionA.2.59.1. Filters Common PropertiesA.2.59.2. Filter Common PropertiesA.2.59.3. Filter PropertiesA.2.60. Floor ObjectA.2.60.1. Floor Common PropertiesA.2.60.2. Floor PropertiesA.2.60.3. Floor MethodsA.2.60.4. Floor Object ExampleA.2.61. Font ObjectA.2.61.1. Font Common PropertiesA.2.61.2. Font PropertiesA.2.61.3. Font Object ExampleA.2.62. FormatColor ObjectA.2.62.1. FormatColor Common PropertiesA.2.62.2. FormatColor PropertiesA.2.63. FormatCondition Object and the FormatConditions CollectionA.2.63.1. FormatConditions Common PropertiesA.2.63.2. FormatConditions MethodsA.2.63.3. FormatCondition Common PropertiesA.2.63.4. FormatCondition PropertiesA.2.64. FormatCondition MethodsA.2.64.1. FormatCondition Object and the FormatConditions Collection ExampleA.2.65. FreeformBuilder ObjectA.2.65.1. FreeformBuilder Common PropertiesA.2.65.2. FreeformBuilder MethodsA.2.65.3. FreeformBuilder Object ExampleA.2.66. Graphic ObjectA.2.66.1. Graphic Common PropertiesA.2.66.2. Graphic PropertiesA.2.66.3. Graphic Object ExampleA.2.67. Gridlines ObjectA.2.67.1. Gridlines Common PropertiesA.2.67.2. Gridlines PropertiesA.2.67.3. Gridlines MethodsA.2.67.4. Gridlines Object ExampleA.2.68. GroupShapes CollectionA.2.69. HeaderFooter ObjectA.2.69.1. HeaderFooter PropertiesA.2.70. HiLoLines ObjectA.2.70.1. HiLoLines Common PropertiesA.2.70.2. HiLoLines PropertiesA.2.70.3. HiLoLines MethodsA.2.71. HPageBreak Object and the HPageBreaks CollectionA.2.71.1. HPageBreak Common PropertiesA.2.71.2. HPageBreak PropertiesA.2.71.3. HPageBreak MethodsA.2.71.4. HPageBreak Object and the HPageBreaks Collection ExampleA.2.72. Hyperlink Object and the Hyperlinks CollectionA.2.72.1. Hyperlink Common PropertiesA.2.72.2. Hyperlink PropertiesA.2.72.3. Hyperlink MethodsA.2.72.4. Hyperlink Object and the Hyperlinks Collection ExampleA.2.73. Icon ObjectA.2.73.1. Icon Common PropertiesA.2.74. IconCriterion and the IconCriteria CollectionA.2.75. IconCriteria Common PropertiesA.2.75.1. IconCriterion PropertiesA.2.76. IconSet and the IconSets CollectionA.2.77. IconSets Common PropertiesA.2.78. IconSet PropertiesA.2.78.1. IconSet PropertiesA.2.79. IconSetCondition ObjectA.2.80. IconSetCondition PropertiesA.2.80.1. IconSetCondition PropertiesA.2.80.2. IconSetCondition MethodsA.2.80.3. IconSetCondition Object ExampleA.2.81. Interior ObjectA.2.81.1. Interior Common PropertiesA.2.81.2. Interior PropertiesA.2.81.3. Interior Object ExampleA.2.82. IRtdServer ObjectA.2.82.1. IRtdServer MethodsA.2.83. IRTDUpdateEvent ObjectA.2.83.1. IRTDUpdateEvent PropertiesA.2.83.2. IRTDUpdateEvent MethodsA.2.84. LeaderLines ObjectA.2.84.1. LeaderLines Common PropertiesA.2.84.2. LeaderLines PropertiesA.2.84.3. LeaderLines MethodsA.2.84.4. LeaderLines Object ExampleA.2.85. Legend ObjectA.2.85.1. Legend Common PropertiesA.2.85.2. Legend PropertiesA.2.85.3. Legend MethodsA.2.85.4. Legend Object ExampleA.2.86. LegendEntry Object and the LegendEntries CollectionA.2.86.1. LegendEntry Common PropertiesA.2.86.2. LegendEntry PropertiesA.2.86.3. LegendEntry MethodsA.2.86.4. LegendEntry Object and the LegendEntries Collection ExampleA.2.87. LegendKey ObjectA.2.87.1. LegendKey Common PropertiesA.2.87.2. LegendKey PropertiesA.2.87.3. LegendKey MethodsA.2.88. LinearGradient ObjectA.2.88.1. LinearGradient Common PropertiesA.2.88.2. LinearGradient PropertiesA.2.89. LineFormat ObjectA.2.89.1. LineFormat Common PropertiesA.2.89.2. LineFormat PropertiesA.2.89.3. LineFormat Object ExampleA.2.90. LinkFormat ObjectA.2.90.1. LinkFormat Common PropertiesA.2.90.2. LinkFormat PropertiesA.2.90.3. LinkFormat MethodsA.2.90.4. LinkFormat Object ExampleA.2.91. ListColumn and ListColumns CollectionA.2.92. ListColumns Common PropertiesA.2.92.1. ListColumn Common PropertiesA.2.92.2. ListColumn PropertiesA.2.92.3. ListColumn MethodsA.2.92.4. ListColumns Object ExampleA.2.93. ListDataFormat ObjectA.2.93.1. ListDataFormat Common PropertiesA.2.93.2. ListDataFormat PropertiesA.2.94. ListObject Object and the ListObjects CollectionA.2.95. ListObjects Common PropertiesA.2.96. ListObject PropertiesA.2.96.1. ListObject PropertiesA.2.96.2. ListObject MethodsA.2.96.3. List Object ExampleA.2.97. ListRow Object and the ListRows CollectionA.2.98. ListRows Common PropertiesA.2.99. ListRow PropertiesA.2.99.1. ListRow PropertiesA.2.99.2. ListRow MethodsA.2.100. Mailer ObjectA.2.100.1. Mailer Common PropertiesA.2.100.2. Mailer PropertiesA.2.101. MultiThreadedCalculation ObjectA.2.101.1. MultiThreadedCalculation Common PropertiesA.2.101.2. MultiThreadedCalculation PropertiesA.2.102. Name Object and the Names CollectionA.2.102.1. Name Common PropertiesA.2.102.2. Name PropertiesA.2.103. Name MethodsA.2.103.1. Name Object and the Names Collection ExampleA.2.104. ODBCConnection ObjectA.2.104.1. ODBCConnection Common PropertiesA.2.104.2. ODBCConnection PropertiesA.2.104.3. ODBCConnection MethodsA.2.105. ODBCError Object and the ODBCErrors CollectionA.2.105.1. ODBCError Common PropertiesA.2.105.2. ODBCError PropertiesA.2.105.3. ODBCError Object and the ODBCErrors Collection ExampleA.2.106. OLEDBConnection ObjectA.2.106.1. OLEDBConnection Common PropertiesA.2.106.2. OLEDBConnection PropertiesA.2.106.3. OLEDBConnection MethodsA.2.107. OLEDBError Object and the OLEDBErrors CollectionA.2.107.1. OLEDBError Common PropertiesA.2.107.2. OLEDBError PropertiesA.2.107.3. OLEDBError Object and the OLEDBErrors Collection ExampleA.2.108. OLEFormat ObjectA.2.108.1. OLEFormat Common PropertiesA.2.108.2. OLEFormat PropertiesA.2.108.3. OLEFormat MethodsA.2.109. OLEObject Object and the OLEObjects CollectionA.2.109.1. OLEObjects Collection Properties and MethodsA.2.109.2. OLEObject Common PropertiesA.2.109.3. OLEObject PropertiesA.2.109.4. OLEObject MethodsA.2.109.5. OLEObject EventsA.2.110. Outline ObjectA.2.110.1. Outline Common PropertiesA.2.110.2. Outline PropertiesA.2.110.3. Outline MethodsA.2.110.4. Outline Object ExampleA.2.111. Page Object and the Pages CollectionA.2.111.1. Page PropertiesA.2.112. PageSetup ObjectA.2.112.1. PageSetup Common PropertiesA.2.112.2. PageSetup PropertiesA.2.112.3. PageSetup MethodsA.2.112.4. PageSetup Object ExampleA.2.113. Pane Object and the Panes CollectionA.2.113.1. Pane Common PropertiesA.2.113.2. Pane PropertiesA.2.113.3. Pane MethodsA.2.113.4. Pane Object and the Panes Collection ExampleA.2.114. Parameter Object and the Parameters CollectionA.2.114.1. Parameters Collection Properties and MethodsA.2.114.2. Parameter Common PropertiesA.2.114.3. Parameter PropertiesA.2.114.4. Parameter MethodsA.2.114.5. Parameter Object and the Parameters Collection ExampleA.2.115. Phonetic Object and the Phonetics CollectionA.2.115.1. Phonetics Collection Properties and MethodsA.2.115.2. Phonetic Common PropertiesA.2.115.3. Phonetic PropertiesA.2.116. PictureFormat ObjectA.2.116.1. PictureFormat Common PropertiesA.2.116.2. PictureFormat PropertiesA.2.116.3. PictureFormat MethodsA.2.116.4. PictureFormat Object ExampleA.2.117. PivotAxis ObjectA.2.117.1. PivotAxis Common PropertiesA.2.117.2. PivotAxis PropertiesA.2.118. PivotCache Object and the PivotCaches CollectionA.2.118.1. PivotCache Common PropertiesA.2.118.2. PivotCache PropertiesA.2.118.3. PivotCache MethodsA.2.118.4. PivotCache Object and the PivotCaches Collection ExampleA.2.119. PivotCell ObjectA.2.119.1. PivotCell Common PropertiesA.2.119.2. PivotCell PropertiesA.2.120. PivotField Object, PivotFields Collection, and the CalculatedFields CollectionA.2.120.1. PivotField Common PropertiesA.2.120.2. PivotField PropertiesA.2.120.3. PivotField MethodsA.2.120.4. PivotField Object, PivotFields Collection, and the CalculatedFields ExampleA.2.121. PivotFilter Object and the PivotFilters CollectionA.2.121.1. PivotFilter Common PropertiesA.2.121.2. PivotFilter PropertiesA.2.121.3. PivotFilter MethodsA.2.121.4. PivotFilters Object ExampleA.2.122. PivotFormula Object and the PivotFormulas CollectionA.2.122.1. PivotFormula Common PropertiesA.2.122.2. PivotFormula PropertiesA.2.122.3. PivotFormula MethodsA.2.123. PivotItem Object, PivotItems Collection, and the CalculatedItems CollectionA.2.123.1. PivotItem Common PropertiesA.2.123.2. PivotItem PropertiesA.2.123.3. PivotItem MethodsA.2.123.4. PivotItem Object, PivotItems Collection, and the CalculatedItems Collection ExampleA.2.124. PivotItemList ObjectA.2.124.1. PivotItemList Common PropertiesA.2.124.2. PivotItemList PropertiesA.2.124.3. PivotItemList MethodsA.2.125. PivotLayout ObjectA.2.125.1. PivotLayout Common PropertiesA.2.125.2. PivotLayout PropertiesA.2.126. PivotLine Object, the PivotLines Collection, and the PivotLinesCells CollectionA.2.126.1. PivotLine PropertiesA.2.126.2. PivotLine PropertiesA.2.127. PivotTable Object and the PivotTables CollectionA.2.127.1. PivotTable Common PropertiesA.2.127.2. PivotTable PropertiesA.2.127.3. PivotTable MethodsA.2.127.4. PivotTable Object and the PivotTables Collection ExampleA.2.128. PlotArea ObjectA.2.128.1. PlotArea Common PropertiesA.2.128.2. PlotArea PropertiesA.2.128.3. PlotArea MethodsA.2.128.4. PlotArea Object ExampleA.2.129. Point Object and the Points CollectionA.2.129.1. Point Common PropertiesA.2.129.2. Point PropertiesA.2.129.3. Point MethodsA.2.129.4. Point Object and the Points Collection ExampleA.2.130. Protection ObjectA.2.130.1. Protection PropertiesA.2.130.2. Protection Object ExampleA.2.131. PublishObject Object and the PublishObjects CollectionA.2.131.1. PublishObjects MethodsA.2.131.2. PublishObject Common PropertiesA.2.131.3. PublishObject PropertiesA.2.131.4. PublishObject MethodsA.2.131.5. PublishObject Object and the PublishObjects Collection ExampleA.2.132. QueryTable Object and the QueryTables CollectionA.2.132.1. QueryTables MethodsA.2.132.2. QueryTable Common PropertiesA.2.132.3. QueryTable PropertiesA.2.132.4. QueryTable MethodsA.2.132.5. QueryTable EventsA.2.132.6. QueryTable Object and the QueryTables Collection ExampleA.2.133. Range Object and the Ranges Collection ObjectA.2.133.1. Range Common PropertiesA.2.133.2. Range PropertiesA.2.133.3. Range MethodsA.2.134. RecentFile Object and the RecentFiles CollectionA.2.134.1. RecentFile Common PropertiesA.2.134.2. RecentFile PropertiesA.2.134.3. RecentFile MethodsA.2.134.4. RecentFile Object and the RecentFiles Collection ExampleA.2.135. RectangularGradient ObjectA.2.135.1. RectangleGradient Common PropertiesA.2.135.2. RectangleGradient PropertiesA.2.136. RoutingSlip ObjectA.2.136.1. RoutingSlip Common PropertiesA.2.136.2. RoutingSlip PropertiesA.2.136.3. RoutingSlip MethodsA.2.137. RTD ObjectA.2.137.1. RTD PropertiesA.2.137.2. RTD MethodsA.2.138. Scenario Object and the Scenarios CollectionA.2.138.1. Scenarios MethodsA.2.138.2. Scenario Common PropertiesA.2.138.3. Scenario PropertiesA.2.138.4. Scenario MethodsA.2.138.5. Scenario Object and the Scenarios Collection ExampleA.2.139. Series Object and the SeriesCollection CollectionA.2.139.1. SeriesCollection MethodsA.2.139.2. Series Common PropertiesA.2.139.3. Series PropertiesA.2.139.4. Series MethodsA.2.140. SeriesLines ObjectA.2.140.1. SeriesLines Common PropertiesA.2.140.2. SeriesLines PropertiesA.2.140.3. SeriesLines MethodsA.2.140.4. SeriesLines Object ExampleA.2.141. ServerViewableItems CollectionA.2.141.1. ServerViewableItems MethodsA.2.141.2. ServerViewableItems Collection ExampleA.2.142. ShadowFormat ObjectA.2.142.1. ShadowFormat Common PropertiesA.2.142.2. ShadowFormat PropertiesA.2.142.3. ShadowFormat MethodsA.2.142.4. ShadowFormat Object ExampleA.2.143. Shape Object and the Shapes CollectionA.2.143.1. Shapes Collection Properties and MethodsA.2.143.2. Shape Common PropertiesA.2.143.3. Shape PropertiesA.2.143.4. Shape MethodsA.2.144. ShapeNode Object and the ShapeNodes CollectionA.2.144.1. ShapeNodes Collection MethodsA.2.144.2. ShapeNode Common PropertiesA.2.144.3. ShapeNode PropertiesA.2.144.4. ShapeNode Object and the ShapeNodes Collection ExampleA.2.145. ShapeRange CollectionA.2.145.1. ShapeRange PropertiesA.2.145.2. ShapeRange MethodsA.2.145.3. ShapeRange Collection ExampleA.2.146. Sheets CollectionA.2.146.1. Sheets Common PropertiesA.2.146.2. Sheets PropertiesA.2.146.3. Sheets MethodsA.2.147. SheetViews ObjectA.2.148. SmartTag Object and the SmartTags Collection ObjectA.2.148.1. SmartTags Collection Common PropertiesA.2.148.2. SmartTag Common PropertiesA.2.148.3. SmartTag PropertiesA.2.148.4. SmartTag MethodsA.2.149. SmartTagAction Object and the SmartTagActions Collection ObjectA.2.149.1. SmartTagAction Common PropertiesA.2.149.2. SmartTagAction PropertiesA.2.149.3. SmartTagAction MethodsA.2.150. SmartTagOptions Collection ObjectA.2.150.1. SmartTagOptions Collection PropertiesA.2.151. SmartTagReconizer Object and the SmartTagRecognizers Collection ObjectA.2.151.1. SmartTagRecognizers Collection PropertiesA.2.151.2. SmartTagRecognizer Common PropertiesA.2.151.3. SmartTagRecognizer PropertiesA.2.152. Sort ObjectA.2.152.1. Sort Common PropertiesA.2.152.2. Sort PropertiesA.2.152.3. Sort MethodsA.2.153. SortField Object and the SortFields CollectionA.2.153.1. SortFields Common PropertiesA.2.153.2. SortFields MethodsA.2.153.3. SortField Common PropertiesA.2.153.4. SortField PropertiesA.2.153.5. SortField MethodsA.2.154. SoundNote ObjectA.2.154.1. SoundNote MethodsA.2.155. Speech ObjectA.2.155.1. Speech PropertiesA.2.155.2. Speech MethodsA.2.155.3. Speech Object ExampleA.2.156. SpellingOptions Collection ObjectA.2.156.1. SpellingOptions Collection PropertiesA.2.156.2. SpellingOptions Collection Object ExampleA.2.157. Style Object and the Styles CollectionA.2.157.1. Style Common PropertiesA.2.157.2. Style PropertiesA.2.157.3. Style MethodsA.2.157.4. Style Object and the Styles Collection ExampleA.2.158. Tab ObjectA.2.158.1. Tab Common PropertiesA.2.158.2. Tab PropertiesA.2.158.3. Tab Object ExampleA.2.159. TableStyle Object and the TableStyles Collection ObjectA.2.159.1. TableStyles Collection Common PropertiesA.2.159.2. TableStyle Common PropertiesA.2.159.3. TableStyle PropertiesA.2.159.4. TableStyle MethodsA.2.159.5. TableStyle Object and TableStyles ExampleA.2.160. TableStyleElement Object and the TableStyleElements Collection ObjectA.2.160.1. TableStyleElement Common PropertiesA.2.160.2. TableStyleElement PropertiesA.2.160.3. TableStyleElement MethodsA.2.161. TextEffectFormat ObjectA.2.161.1. TextEffectFormat Common PropertiesA.2.161.2. TextEffectFormat PropertiesA.2.161.3. TextEffectFormat MethodsA.2.161.4. TextEffectFormat Object ExampleA.2.162. TextFrame ObjectA.2.162.1. TextFrame Common PropertiesA.2.162.2. TextFrame PropertiesA.2.162.3. TextFrame MethodsA.2.162.4. TextFrame Object ExampleA.2.163. TextFrame2 ObjectA.2.163.1. TextFrame2 Common PropertiesA.2.163.2. TextFrame2 PropertiesA.2.163.3. ThreeDFormat MethodsA.2.164. ThreeDFormat ObjectA.2.164.1. ThreeDFormat Common PropertiesA.2.164.2. ThreeDFormat PropertiesA.2.164.3. ThreeDFormat MethodsA.2.164.4. ThreeDFormat Object ExampleA.2.165. TickLabels ObjectA.2.165.1. TickLabels Common PropertiesA.2.165.2. TickLabels PropertiesA.2.165.3. TickLabels MethodsA.2.165.4. TickLabels Object ExampleA.2.166. Top10 ObjectA.2.167. Top10 PropertiesA.2.167.1. Top10 PropertiesA.2.167.2. Top10 MethodsA.2.167.3. Top10 Object ExampleA.2.168. TreeviewControl ObjectA.2.168.1. TreeviewControl Common PropertiesA.2.168.2. TreeviewControl PropertiesA.2.169. Trendline Object and the Trendlines CollectionA.2.169.1. Trendline Common PropertiesA.2.169.2. Trendline PropertiesA.2.169.3. Trendline MethodsA.2.169.4. Trendline Object and the Trendlines Collection ExampleA.2.170. UniqueValues ObjectA.2.171. UniqueValues PropertiesA.2.171.1. UniqueValues PropertiesA.2.171.2. UniqueValues MethodsA.2.171.3. UniqueValues Object ExampleA.2.172. UpBars ObjectA.2.172.1. UpBars Common PropertiesA.2.172.2. UpBars PropertiesA.2.172.3. UpBars MethodsA.2.173. UsedObjects Collection ObjectA.2.174. UserAccess Collection ObjectA.2.174.1. UserAccess Collection PropertiesA.2.174.2. UserAccess Collection MethodsA.2.175. UserAccessList Collection ObjectA.2.175.1. UserAccessList MethodsA.2.175.2. UserAccessList Object ExampleA.2.176. Validation ObjectA.2.176.1. Validation Common PropertiesA.2.176.2. Validation PropertiesA.2.176.3. Validation MethodsA.2.176.4. Validation Object ExampleA.2.177. VPageBreak Object and the VPageBreaks CollectionA.2.177.1. VPageBreak Common PropertiesA.2.177.2. VPageBreak PropertiesA.2.177.3. VPageBreak MethodsA.2.177.4. VPageBreak Object and the VPageBreaks Collection ExampleA.2.178. Walls ObjectA.2.178.1. Walls Common PropertiesA.2.178.2. Walls PropertiesA.2.178.3. Walls MethodsA.2.178.4. Walls Object ExampleA.2.179. Watch Object and the Watches Collection ObjectA.2.179.1. Watches Collection MethodsA.2.179.2. Watch Common PropertiesA.2.179.3. Watch PropertiesA.2.179.4. Watch MethodsA.2.179.5. Watch Object ExampleA.2.180. WebOptions ObjectA.2.180.1. WebOptions Common PropertiesA.2.180.2. WebOptions PropertiesA.2.180.3. WebOptions MethodsA.2.180.4. WebOptions Object ExampleA.2.181. Window Object and the Windows CollectionA.2.181.1. Windows Collection Properties and MethodsA.2.181.2. Window Common PropertiesA.2.181.3. Window PropertiesA.2.181.4. Window MethodsA.2.181.5. Window Object and the Windows Collection ExampleA.2.182. Workbook Object and the Workbooks CollectionA.2.182.1. Workbooks PropertiesA.2.182.2. Workbooks MethodsA.2.182.3. Workbook Common PropertiesA.2.182.4. Workbook PropertiesA.2.182.5. Workbook MethodsA.2.182.6. Workbook EventsA.2.183. WorkbookConnection ObjectA.2.184. Worksheet Object and the Worksheets CollectionA.2.184.1. Worksheets Collection Properties and MethodsA.2.184.2. Worksheet Common PropertiesA.2.184.3. Worksheet PropertiesA.2.184.4. Worksheet MethodsA.2.184.5. Worksheet EventsA.2.185. WorksheetFunction ObjectA.2.185.1. WorksheetFunction Common PropertiesA.2.185.2. WorksheetFunction MethodsA.2.185.3. WorksheetFunction Object ExampleA.2.186. WorksheetView ObjectA.2.186.1. WorksheetView PropertiesA.2.187. XmlDataBinding ObjectA.2.187.1. XmlDataBinding MethodsA.2.188. XmlMap Object and the XMLMaps CollectionA.2.188.1. XMLMap Common PropertiesA.2.188.2. XmlMap PropertiesA.2.188.3. XmlMap MethodsA.2.189. XmlNameSpace Object and the XMLNameSpacesCollectionA.2.189.1. XMLNameSpace Common PropertiesA.2.189.2. XMLNameSpace PropertiesA.2.190. XmlSchema Object and the XmlSchemas CollectionA.2.190.1. XMLSchema Common PropertiesA.2.190.2. XMLSchema PropertiesA.2.191. XPath ObjectA.2.191.1. XPath Common PropertiesA.2.191.2. XPath PropertiesA.2.191.3. XPath Methods
B. VBE Object Model
B.1. Links between the Excel and VBE Object ModelsB.1.1.B.1.1.1. Excel to VBEB.1.1.2. VBE to ExcelB.2. Common Properties and MethodsB.3. AddIn Object and Add-Ins CollectionB.3.1.B.3.1.1. Add-In PropertiesB.3.2. Add-Ins Collection MethodsB.4. CodeModule ObjectB.4.1.B.4.1.1. CodeModule PropertiesB.4.2. CodeModule MethodsB.5. CodePane Object and CodePanes CollectionB.5.1.B.5.1.1. CodePane PropertiesB.5.2. CodePane MethodsB.5.2.1. CodePanes Collection PropertiesB.6. CommandBarEvents ObjectB.6.1.B.6.1.1. CommandBarEvents EventsB.7. Events ObjectB.7.1.B.7.1.1. Events PropertiesB.8. LinkedWindows CollectionB.8.1.B.8.1.1. LinkedWindows Collection MethodsB.9. Property Object and Properties CollectionB.9.1.B.9.1.1. Property PropertiesB.10. Reference Object and References CollectionB.10.1.B.10.1.1. Reference PropertiesB.10.1.2. References Collection MethodsB.10.1.3. References Collection EventsB.11. End FunctionReferencesEvents ObjectB.11.1.B.11.1.1. ReferencesEvents EventsB.12. VBComponent Object and VBComponents CollectionB.12.1.B.12.1.1. VBComponent PropertiesB.12.1.2. VBComponent MethodsB.12.1.3. VBComponents Collection MethodsB.13. VBE ObjectB.13.1.B.13.1.1. VBE PropertiesB.14. VBProject Object and VBProjects CollectionB.14.1. VBProject Common PropertiesB.14.1.1. VBProject PropertiesB.14.1.2. VBProject MethodsB.14.1.3. VBProjects Collection MethodsB.15. Window Object and Windows CollectionB.15.1. Window Common PropertiesB.15.1.1. Window PropertiesB.15.1.2. Window MethodsB.15.1.3. Windows Collection Methods
C. Office 2007 Object Model
C.1. Common Properties with Collections and Associated ObjectsC.1.1. Common Collection PropertiesC.1.2. Common Object PropertiesC.2. Office Objects and Their Properties and EventsC.2.1. BulletFormat2 ObjectC.2.1.1. BulletFormat2 PropertiesC.2.1.2. BulletFormat2 MethodsC.2.2. COMAddinObject and the COMAddins Collection ObjectC.2.2.1. COMAddins Collection PropertiesC.2.2.2. COMAddins Collection MethodsC.2.2.3. COMAddinPropertiesC.2.2.4. COMAddin Object ExampleC.2.3. CommandBar Object and the CommandBars Collection ObjectC.2.3.1. CommandBars Collection PropertiesC.2.3.2. CommandBars Collection MethodsC.2.3.3. CommandBars Collection EventsC.2.3.4. CommandBar PropertiesC.2.3.5. CommandBar MethodsC.2.4. CommandBarButton ObjectC.2.4.1. CommandBarButton PropertiesC.2.4.2. CommandBarButton MethodsC.2.4.3. CommandBarButton EventsC.2.5. CommandBarComboBox ObjectC.2.5.1. CommandBarComboBox PropertiesC.2.5.2. CommandBarComboBox MethodsC.2.5.3. CommandBarComboBox EventsC.2.6. CommandBarControl Object and the CommandBarControls Collection ObjectC.2.6.1. CommandBarControls Collection PropertiesC.2.6.2. CommandBarControls Collection MethodsC.2.6.3. CommandBarControl PropertiesC.2.6.4. CommandBarControl MethodsC.2.7. CommandBarPopup ObjectC.2.7.1. CommandBarPopup PropertiesC.2.7.2. CommandBarPopup MethodsC.2.8. CustomTaskPane ObjectC.2.8.1. CustomTaskPane PropertiesC.2.8.2. CustomTaskPane MethodsC.2.8.3. CustomTaskPane EventsC.2.9. CustomXMLNode Object and the CustomXMLNodes Collection ObjectC.2.9.1. CustomXMLNodes Collection PropertiesC.2.9.2. CustomXMLNode PropertiesC.2.9.3. CustomXMLNode MethodsC.2.9.3.1. CustomXMLNode, CustomXMLNodes, and CustomXMLPart ExampleC.2.10. CustomXMLPart Object and the CustomXMLParts Collection ObjectC.2.10.1. CustomXMLParts Collection PropertiesC.2.10.2. CustomXMLParts Collection MethodsC.2.10.3. CustomXMLParts Collection EventsC.2.10.4. CustomXMLPart PropertiesC.2.10.5. CustomXMLPart MethodsC.2.10.6. CustomXMLPart EventsC.2.11. CustomXMLPrefixMapping Object and the CustomXMLPrefixMappings Collection ObjectC.2.11.1. CustomXMLPrefixMappings Collection PropertiesC.2.11.2. CustomXMLPrefixMappings Collection MethodsC.2.11.3. CustomXMLPrefixMapping PropertiesC.2.12. CustomXMLSchema Object and the CustomXMLSchemaCollection ObjectC.2.12.1. CustomXMLSchemaCollection PropertiesC.2.12.2. CustomXMLSchemaCollection MethodsC.2.12.3. CustomXMLSchema PropertiesC.2.12.4. CustomXMLSchema MethodsC.2.13. CustomXMLValidationError Object and the CustomXMLValidationErrors CollectionObjectC.2.13.1. CustomXMLValidationErrors Collection PropertiesC.2.13.2. CustomXMLValidationErrors Collection MethodsC.2.13.3. CustomXMLValidationError PropertiesC.2.13.4. CustomXMLValidationError MethodsC.2.14. DocumentInspector Object and the DocumentInspectors CollectionObjectC.2.14.1. DocumentInspectors Collection PropertiesC.2.14.2. DocumentInspector PropertiesC.2.14.3. DocumentInspector MethodsC.2.14.4. DocumentInspector Object ExampleC.2.15. DocumentLibraryVersion Object and the DocumentLibraryVersions Collection ObjectC.2.15.1. DocumentLibraryVersions Collection PropertiesC.2.15.2. DocumentLibraryVersion PropertiesC.2.15.3. DocumentLibraryVersion MethodsC.2.16. DocumentProperty Object and the DocumentProperties Collection ObjectC.2.16.1. DocumentProperties Collection PropertiesC.2.16.2. DocumentProperties Collection MethodsC.2.16.3. DocumentProperty PropertiesC.2.16.4. DocumentProperty MethodsC.2.17. EncryptionProvider ObjectC.2.17.1. EncryptionProvider MethodsC.2.18. FileDialog ObjectC.2.18.1. FileDialog PropertiesC.2.18.2. FileDialog MethodsC.2.19. FileDialogFilter Object and the FileDialogFilters Collection ObjectC.2.19.1. FileDialogFilters PropertiesC.2.19.2. FileDialogFilters Collection MethodsC.2.19.3. FileDialogFilter PropertiesC.2.20. FileDialogSelectedItems Collection ObjectC.2.20.1. FileDialogSelectedItems Collection PropertiesC.2.20.2. FileDialogSelectedItems Collection MethodsC.2.21. FileTypes ObjectC.2.21.1. FileTypes Collection PropertiesC.2.21.2. FileTypes Collection MethodsC.2.22. Font2 ObjectC.2.22.1. Font2 PropertiesC.2.23. GlowFormat ObjectC.2.23.1. GlowFormat PropertiesC.2.24. GradientStop Object and the GradientStops Collection ObjectC.2.24.1. GradientStops Collection PropertiesC.2.24.2. GradientStops Collection MethodsC.2.24.3. GradientStop PropertiesC.2.25. IAssistance ObjectC.2.25.1. IAssistance MethodsC.2.26. IBlogExtensiblity and IBlogPictureExtensibility ObjectsC.2.26.1. IBlogExtensibility MethodsC.2.26.2. IBlogPictureExtensibility MethodsC.2.27. ICTPFactory ObjectC.2.27.1. ICTPFactory MethodsC.2.28. ICustomTaskPaneConsumer ObjectC.2.28.1. ICustomTaskPaneConsumer MethodsC.2.29. IDocumentInspector ObjectC.2.29.1. IDocumentInspector MethodsC.2.30. IRibbonControl ObjectC.2.30.1. IRibbonControl PropertiesC.2.31. IRibbonExtensiblity ObjectC.2.31.1. IRibbonExtensibility MethodsC.2.32. IRibbonUI ObjectC.2.32.1. IRibbonUI MethodsC.2.33. LanguageSettings ObjectC.2.33.1. LanguageSettings PropertiesC.2.34. MetaProperty Object and the MetaProperties Collection ObjectC.2.34.1. MetaProperties Collection PropertiesC.2.34.2. MetaProperties Collection MethodsC.2.34.3. MetaProperty PropertiesC.2.34.4. MetaProperty MethodsC.2.35. MsoEnvelope ObjectC.2.35.1. MsoEnvelope PropertiesC.2.35.2. MsoEnvelope EventsC.2.36. NewFile ObjectC.2.36.1. NewFile PropertiesC.2.36.2. NewFile MethodsC.2.37. The ODSOColumn Object and the ODSOColumns Collection ObjectC.2.38. The ODSOFilter Object and the ODSOFilters Collection ObjectC.2.39. OfficeDataSourceObject ObjectC.2.40. OfficeTheme ObjectC.2.40.1. OfficeTheme PropertiesC.2.41. ParagraphFormat2 ObjectC.2.41.1. ParagraphFormat2 PropertiesC.2.42. Permission ObjectC.2.42.1. Permission PropertiesC.2.42.2. Permission MethodsC.2.43. PolicyItem Object and the ServerPolicy Collection ObjectC.2.43.1. ServerPolicy PropertiesC.2.43.2. PolicyItem PropertiesC.2.44. ReflectionFormat ObjectC.2.44.1. ReflectionFormat PropertiesC.2.45. Ruler2 ObjectC.2.45.1. Ruler2 PropertiesC.2.46. RulerLevel2 Object and the RulerLevels2 Collection ObjectC.2.46.1. RulerLevels2 Collection PropertiesC.2.46.2. RulerLevels2 Collection MethodsC.2.46.3. RulerLevel2 PropertiesC.2.47. ScopeFolder Object and the ScopeFolders Collection ObjectC.2.47.1. ScopeFolders Collection PropertiesC.2.47.2. ScopeFolder PropertiesC.2.47.3. ScopeFolder MethodsC.2.48. SearchFolders Collection ObjectC.2.48.1. SearchFolders Collection Common PropertiesC.2.48.2. SearchFolders Collection PropertiesC.2.48.3. SearchFolders Collection MethodsC.2.49. SearchScope Object and the SearchScopes Collection ObjectC.2.49.1. SearchScopes Collection PropertiesC.2.49.2. SearchScope PropertiesC.2.50. SharedWorkspace ObjectC.2.50.1. SharedWorkspace PropertiesC.2.50.2. SharedWorkspace MethodsC.2.51. SharedWorkspaceFile Object and the SharedWorkspaceFiles Collection ObjectC.2.51.1. SharedWorkspaceFiles Collection PropertiesC.2.51.2. SharedWorkspaceFiles Collection MethodsC.2.51.3. SharedWorkspaceFile PropertiesC.2.51.4. SharedWorkspaceFile MethodsC.2.52. SharedWorkspaceFolder Object and the SharedWorkspaceFolders Collection ObjectC.2.52.1. SharedWorkspaceFolders Collection PropertiesC.2.52.2. SharedWorkspaceFolders Collection MethodsC.2.52.3. SharedWorkspaceFolder PropertiesC.2.52.4. SharedWorkspaceFolder MethodsC.2.53. SharedWorkspaceLink Object and the SharedWorkspaceLinks Collection ObjectC.2.53.1. SharedWorkspaceLinks Collection PropertiesC.2.53.2. SharedWorkspaceLinks Collection MethodsC.2.53.3. SharedWorkspaceLink PropertiesC.2.53.4. SharedWorkspaceLink MethodsC.2.54. SharedWorkspaceMember Object and the SharedWorkspaceMembers Collection ObjectC.2.54.1. SharedWorkspaceMembers Collection PropertiesC.2.54.2. SharedWorkspaceMembers Collection MethodsC.2.54.3. SharedWorkspaceMember PropertiesC.2.54.4. SharedWorkspaceMember MethodsC.2.55. SharedWorkspaceTask Object and the SharedWorkspaceTasks Collection ObjectC.2.55.1. SharedWorkspaceTasks Collection PropertiesC.2.55.2. SharedWorkspaceTasks Collection MethodsC.2.55.3. SharedWorkspaceTask PropertiesC.2.55.4. SharedWorkspaceTask MethodsC.2.56. Signature Object and the SignatureSet Collection ObjectC.2.56.1. SignatureSet Collection PropertiesC.2.56.2. SignatureSet Collection MethodsC.2.56.3. Signature PropertiesC.2.56.4. Signature MethodsC.2.57. SignatureInfo ObjectC.2.57.1. SignatureInfo PropertiesC.2.57.2. SignatureInfo MethodsC.2.58. SignatureProvider ObjectC.2.58.1. SignatureProvider MethodsC.2.59. SignatureSetup ObjectC.2.59.1. SignatureSetup PropertiesC.2.60. SmartDocumentC.2.60.1. SmartDocument PropertiesC.2.60.2. SmartDocument MethodsC.2.61. SoftEdgeFormat ObjectC.2.61.1. ReflectionFormat PropertiesC.2.62. Sync ObjectC.2.62.1. Sync PropertiesC.2.62.2. Sync MethodsC.2.63. TabStop2 Object and the TabStops2 Collection ObjectC.2.63.1. TabStops2 Collection PropertiesC.2.63.2. TabStops2 Collection MethodsC.2.63.3. TabStop2 PropertiesC.2.63.4. TabStop2 MethodsC.2.64. TextColumn2 Object and the TextColumns2 Collection ObjectC.2.64.1. TextColumn2 PropertiesC.2.65. TextRange2 ObjectC.2.65.1. TextRange2 PropertiesC.2.65.2. TextRange2 MethodsC.2.66. ThemeColor ObjectC.2.66.1. ThemeColor PropertiesC.2.67. ThemeColorsScheme ObjectC.2.67.1. ThemeColorsScheme PropertiesC.2.67.2. ThemeColorsScheme MethodsC.2.68. ThemeEffectScheme ObjectC.2.68.1. ThemeEffectScheme PropertiesC.2.68.2. ThemeEffectScheme MethodsC.2.69. ThemeFont Object and the ThemeFonts Collection ObjectC.2.69.1. ThemeFonts Collection PropertiesC.2.69.2. ThemeFonts Collection MethodsC.2.69.3. ThemeFont PropertiesC.2.70. ThemeFontScheme ObjectC.2.70.1. ThemeFontScheme PropertiesC.2.70.2. ThemeFontScheme MethodsC.2.71. UserPermissionC.2.71.1. UserPermission PropertiesC.2.71.2. UserPermission MethodsC.2.72. WebPageFont Object and the WebPageFonts Collection ObjectC.2.72.1. WebPageFonts Collection PropertiesC.2.72.2. WebPageFont PropertiesC.2.73. WorkflowTask Object and the WorkflowTasks Collection ObjectC.2.73.1. WorkflowTasks Collection PropertiesC.2.73.2. WorkflowTask PropertiesC.2.73.3. WorkflowTask MethodsC.2.74. WorkflowTemplate Object and the WorkflowTemplates Collection ObjectC.2.74.1. WorkflowTemplates Collection PropertiesC.2.74.2. WorkflowTemplate PropertiesC.2.74.3. WorkflowTemplate Methods

Content preview from Excel® 2007 VBA Programmer's Reference

Chapter 3. Workbooks and Worksheets

In this chapter, you learn how to create new Workbook objects and how to interact with the files that you use to store those workbooks. To do this, some basic utility functions are presented. You also see how to handle the Sheet objects within the workbook, and how some important features must be handled through the Window object. Finally, you learn how to synchronize your worksheets as you move from one worksheet to another.

The Workbooks Collection

The Workbooks collection consists of all the currently open Workbook objects in memory. Members can be added to the Workbooks collection in a number of ways. You can create a new empty workbook based on the default properties of the Workbook object, or you can create a new workbook based on a template file. Finally, you can open an existing workbook file.

To create a new empty workbook based on the default workbook, use the Add method of the Workbooks collection:

Workbooks.Add

The new workbook will be the active workbook, so you can refer to it in the following code as ActiveWorkbook. If you immediately save the workbook, using the SaveAs method, you can give it a filename that can be used to refer to the workbook in later code, even if it is no longer active. Before you try the following code, make sure you have a C:\Data directory or change the directory name used in the code:

Workbooks.Add ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData1.xlsx" Workbooks.Add ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData2.xlsx" ...

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: 9780470046432Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel® 2007 VBA Programmer's Reference

by John Green, Stephen Bullen, Rob Bovey, Michael Alexander

Chapter 3. Workbooks and Worksheets

The Workbooks Collection

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.