| |
| |
| Preface | |
| |
| |
| |
| Learning VBA | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Knowing the Basics | |
| |
| |
| Parts of a Program | |
| |
| |
| Classes and Modules | |
| |
| |
| Procedures | |
| |
| |
| Variables | |
| |
| |
| Conditional Statements | |
| |
| |
| Loops | |
| |
| |
| Expressions | |
| |
| |
| Exceptions | |
| |
| |
| What You've Learned | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Excel Objects | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Sending and Receiving Workbooks | |
| |
| |
| Send Mail | |
| |
| |
| Work with Mail Items | |
| |
| |
| Collect Review Comments | |
| |
| |
| Route Workbooks | |
| |
| |
| Read Mail | |
| |
| |
| MsoEnvelope Members | |
| |
| |
| MailItem Members | |
| |
| |
| RoutingSlip Members | |
| |
| |
| |
| Extending Excel | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Integrating DLLs and COM | |
| |
| |
| Use DLLs | |
| |
| |
| Use COM Applications | |
| |
| |
| |
| Getting Data from the Web | |
| |
| |
| Perform Web Queries | |
| |
| |
| QueryTable and QueryTables Web Query Members | |
| |
| |
| Use Web Services | |
| |
| |
| Resources | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Appendixes | |
| |
| |
| |
| Reference Tables | |
| |
| |
| |
| Version Compatibility | |
| |
| |
| Index | |