Programming Excel with VBA and .NET
暫譯: 使用 VBA 和 .NET 編程 Excel

Preface

Part I. Learning VBA

1. Becoming an Excel Programmer

     Why Program?

     Record and Read Code

     Change Recorded Code

     Fix Misteakes

     Start and Stop

     View Results

     Where's My Code?

     Macros and Security

     Write Bug-Free Code

     Navigate Samples and Help

     What You've Learned

2. Knowing the Basics

     Parts of a Program

     Classes and Modules

     Procedures

     Variables

     Conditional Statements

     Loops

     Expressions

     Exceptions

     What You've Learned

3. Tasks in Visual Basic

     Types of Tasks

     Interact with Users

     Do Math

     Work with Text

     Get Dates and Times

     Read and Write Files

     Check Results

     Find Truth

     Compare Bits

     Run Other Applications

     Control the Compiler

     Not Covered Here

     What You've Learned

4. Using Excel Objects

     Objects and Their Members

     Get Excel Objects

     Get Objects from Collections

     About Me and the Active Object

     Find the Right Object

     Common Members

     Respond to Events in Excel

     The Global Object

     The WorksheetFunction Object

     What You've Learned

5. Creating Your Own Objects

     Modules Versus Classes

     Add Methods

     Create Properties

     Define Enumerations

     Raise Events

     Collect Objects

     Expose Objects

     Destroy Objects

     Things You Can't Do

     What You've Learned

6. Writing Code for Use by Others

     Types of Applications

     The Development Process

     Determine Requirements

     Design

     Implement and Test

     Integrate

     Test Platforms

     Document

     Deploy

     What You've Learned

     Resources

Part II. Excel Objects

7. Controlling Excel

     Perform Tasks

     Control Excel Options

     Get References

     Application Members

     AutoCorrect Members

     AutoRecover Members

     ErrorChecking Members

     SpellingOptions Members

     Window and Windows Members

     Pane and Panes Members

8. Opening, Saving, and Sharing Workbooks

     Add, Open, Save, and Close

     Share Workbooks

     Program with Shared Workbooks

     Program with Shared Workspaces

     Respond to Actions

     Workbook and Workbooks Members

     RecentFile and RecentFiles Members

9. Working with Worksheets and Ranges

     Work with Worksheet Objects

     Worksheets and Worksheet Members

     Sheets Members

     Work with Outlines

     Outline Members

     Work with Ranges

     Range Members

     Work with Scenario Objects

     Scenario and Scenarios Members

     Resources

10. Linking and Embedding

     Add Comments

     Use Hyperlinks

     Link and Embed Objects

     Speak

     Comment and Comments Members

     Hyperlink and Hyperlinks Members

     OleObject and OleObjects Members

     OLEFormat Members

     Speech Members

     UsedObjects Members

11. Printing and Publishing

     Print and Preview

     Control Paging

     Change Printer Settings

     Filter Ranges

     Save and Display Views

     Publish to the Web

     AutoFilter Members

     Filter and Filters Members

     CustomView and CustomViews Members

     HPageBreak, HPageBreaks, VPageBreak, VPageBreaks Members

     PageSetup Members

     Graphic Members

     PublishObject and PublishObjects Members

     WebOptions and DefaultWebOptions Members

12. Loading and Manipulating Data

     Working with QueryTable Objects

     QueryTable and QueryTables Members

     Working with Parameter Objects

     Parameter Members

     Working with ADO and DAO

     ADO Objects and Members

     DAO Objects and Members

     DAO.Database and DAO.Databases Members

     DAO.Document and DAO.Documents Members

     DAO.QueryDef and DAO.QueryDefs Members

     DAO.Recordset and DAO.Recordsets Members

13. Analyzing Data with Pivot Tables

     Quick Guide to Pivot Tables

     Program Pivot Tables

     PivotTable and PivotTables Members

     PivotCache and PivotCaches Members

     PivotField and PivotFields Members

     CalculatedFields Members

     CalculatedItems Members

     PivotCell Members

     PivotFormula and PivotFormulas Members

     PivotItem and PivotItems Members

     PivotItemList Members

     PivotLayout Members

     CubeField and CubeFields Members

     CalculatedMember and CalculatedMembers Members

14. Sharing Data Using Lists

     Use Lists

     ListObject and ListObjects Members

     ListRow and ListRows Members

     ListColumn and ListColumns Members

     ListDataFormat Members

     Use the Lists Web Service

     Lists Web Service Members

     Resources

15. Working with XML

     Understand XML

     Save Workbooks as XML

     Use XML Maps

     Program with XML Maps

     XmlMap and XmlMaps Members

     XmlDataBinding Members

     XmlNamespace and XmlNamespaces Members

     XmlSchema and XmlSchemas Members

     Get an XML Map from a List or Range

     XPath Members

     Resources

16. Charting

     Navigate Chart Objects

     Create Charts Quickly

     Embed Charts

     Create More Complex Charts

     Choose Chart Type

     Create Combo Charts

     Add Titles and Labels

     Plot a Series

     Respond to Chart Events

     Chart and Charts Members

     ChartObject and ChartObjects Members

     ChartGroup and ChartGroups Members

     SeriesLines Members

     Axes and Axis Members

     DataTable Members

     Series and SeriesCollection Members

     Point and Points Members

17. Formatting Charts

     Format Titles and Labels

     Change Backgrounds and Fonts

     Add Trendlines

     Add Series Lines and Bars

     ChartTitle, AxisTitle, and DisplayUnitLabel Members

     DataLabel and DataLabels Members

     LeaderLines Members

     ChartArea Members

     ChartFillFormat Members

     ChartColorFormat Members

     DropLines and HiLoLines Members

     DownBars and UpBars Members

     ErrorBars Members

     Legend Members

     LegendEntry and LegendEntries Members

     LegendKey Members

     Gridlines Members

     TickLabels Members

     Trendline and Trendlines Members

     PlotArea Members

     Floor Members

     Walls Members

     Corners Members

18. Drawing Graphics

     Draw in Excel

     Create Diagrams

     Program with Drawing Objects

     Program Diagrams

     Shape, ShapeRange, and Shapes Members

     Adjustments Members

     CalloutFormat Members

     ColorFormat Members

     ConnectorFormat Members

     ControlFormat Members

     FillFormat Members

     FreeFormBuilder

     GroupShapes Members

     LineFormat Members

     LinkFormat Members

     PictureFormat Members

     ShadowFormat

     ShapeNode and ShapeNodes Members

     TextFrame

     TextEffectFormat

     ThreeDFormat

19. Adding Menus and Toolbars

     About Excel Menus

     Build a Top-Level Menu

     Create a Menu in Code

     Build Context Menus

     Build a Toolbar

     Create Toolbars in Code

     CommandBar and CommandBars Members

     CommandBarControl and CommandBarControls Members

     CommandBarButton Members

     CommandBarComboBox Members

     CommandBarPopup Members

20. Building Dialog Boxes

     Types of Dialogs

     Create Data-Entry Forms

     Design Your Own Forms

     Use Controls on Worksheets

     UserForm and Frame Members

     Control and Controls Members

     Font Members

     CheckBox, OptionButton, ToggleButton Members

     ComboBox Members

     CommandButton Members

     Image Members

     Label Members

     ListBox Members

     MultiPage Members

     Page Members

     ScrollBar and SpinButton Members

     TabStrip Members

     TextBox and RefEdit Members

21. Sending and Receiving Workbooks

     Send Mail

     Work with Mail Items

     Collect Review Comments

     Route Workbooks

     Read Mail

     MsoEnvelope Members

     MailItem Members

     RoutingSlip Members

Part III. Extending Excel

22. Building Add-ins

     Types of Add-ins

     Code-Only Add-ins

     Visual Add-ins

     Set Add-in Properties

     Sign the Add-in

     Distribute the Add-in

     Work with Add-ins in Code

     AddIn and AddIns Members

23. Integrating DLLs and COM

     Use DLLs

     Use COM Applications

24. Getting Data from the Web

     Perform Web Queries

     QueryTable and QueryTables Web Query Members

     Use Web Services

     Resources

25. Programming Excel with .NET

     Approaches to Working with .NET

     Create .NET Components for Excel

     Use .NET Components in Excel

     Use Excel as a Component in .NET

     Create Excel Applications in .NET

     Resources

26. Exploring Security in Depth

     Security Layers

     Understand Windows Security

     Password-Protect and Encrypt Workbooks

     Program with Passwords and Encryption

     Workbook Password and Encryption Members

     Excel Password Security

     Protect Items in a Workbook

     Program with Protection

     Workbook Protection Members

     Worksheet Protection Members

     Chart Protection Members

     Protection Members

     AllowEditRange and AllowEditRanges Members

     UserAccess and UserAccessList Members

     Set Workbook Permissions

     Program with Permissions

     Permission and UserPermission Members

     Add Digital Signatures

     Set Macro Security

     Set ActiveX Control Security

     Distribute Security Settings

     Using the Anti-Virus API

     Common Tasks

     Resources

Part IV. Appendixes

A. Reference Tables

B. Version Compatibility

Index