Programming Excel with VBA and .NET

Programming Excel with VBA and .NET

by Jeff Webb, Steve Saunders
Released April 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596007669

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Why program Excel? For solving complex calculations and presenting results, Excel is amazingly complete with every imaginable feature already in place. But programming Excel isn't about adding new features as much as it's about combining existing features to solve particular problems. With a few modifications, you can transform Excel into a task-specific piece of software that will quickly and precisely serve your needs. In other words, Excel is an ideal platform for probably millions of small spreadsheet-based software solutions.

The best part is, you can program Excel with no additional tools. A variant of the Visual Basic programming language, VB for Applications (VBA) is built into Excel to facilitate its use as a platform. With VBA, you can create macros and templates, manipulate user interface features such as menus and toolbars, and work with custom user forms or dialog boxes. VBA is relatively easy to use, but if you've never programmed before, Programming Excel with VBA and .NET is a great way to learn a lot very quickly. If you're an experienced Excel user or a Visual Basic programmer, you'll pick up a lot of valuable new tricks. Developers looking forward to .NET development will also find discussion of how the Excel object model works with .NET tools, including Visual Studio Tools for Office (VSTO).

This book teaches you how to use Excel VBA by explaining concepts clearly and concisely in plain English, and provides plenty of downloadable samples so you can learn by doing. You'll be exposed to a wide range of tasks most commonly performed with Excel, arranged into chapters according to subject, with those subjects corresponding to one or more Excel objects. With both the samples and important reference information for each object included right in the chapters, instead of tucked away in separate sections, Programming Excel with VBA and .NET covers the entire Excel object library. For those just starting out, it also lays down the basic rules common to all programming languages.

With this single-source reference and how-to guide, you'll learn to use the complete range of Excel programming tasks to solve problems, no matter what you're experience level.

Publisher resources

View/Submit Errata

Table of contents

  1. Programming Excel with VBA and .NET
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Learn by Doing
    2. Don’t Force It
    3. Excel Versions
    4. Get the Samples
    5. What’s in This Book
    6. Font Conventions
    7. Syntax Conventions
    8. Using Code Examples
    9. Safari® Enabled
    10. How to Contact Us
    11. Acknowledgments
  4. I. Learning VBA
    1. 1. Becoming an Excel Programmer
      1. 1.1. Why Program?
      2. 1.2. Record and Read Code
      3. 1.3. Change Recorded Code
      4. 1.4. Fix Misteakes
        1. 1.4.1. Fix Syntax Errors
        2. 1.4.2. Fix Compile-Time Errors
        3. 1.4.3. Fix Runtime Errors
      5. 1.5. Start and Stop
      6. 1.6. View Results
      7. 1.7. Where’s My Code?
      8. 1.8. Macros and Security
      9. 1.9. Write Bug-Free Code
      10. 1.10. Navigate Samples and Help
      11. 1.11. What You’ve Learned
    2. 2. Knowing the Basics
      1. 2.1. Parts of a Program
      2. 2.2. Classes and Modules
      3. 2.3. Procedures
        1. 2.3.1. Arguments and Results
        2. 2.3.2. Optional Arguments
        3. 2.3.3. Named Arguments
        4. 2.3.4. Properties
        5. 2.3.5. Events
      4. 2.4. Variables
        1. 2.4.1. Names
        2. 2.4.2. Declarations
        3. 2.4.3. Conversions
        4. 2.4.4. Scope and Lifetime
        5. 2.4.5. Scope for Procedures
        6. 2.4.6. Constants and Enumerations
        7. 2.4.7. Arrays
        8. 2.4.8. User-Defined Types
        9. 2.4.9. Objects
      5. 2.5. Conditional Statements
      6. 2.6. Loops
      7. 2.7. Expressions
      8. 2.8. Exceptions
      9. 2.9. What You’ve Learned
    3. 3. Tasks in Visual Basic
      1. 3.1. Types of Tasks
      2. 3.2. Interact with Users
      3. 3.3. Do Math
      4. 3.4. Work with Text
        1. 3.4.1. Compare Strings
        2. 3.4.2. Convert Strings
        3. 3.4.3. Change Strings
        4. 3.4.4. Repeat Characters
      5. 3.5. Get Dates and Times
      6. 3.6. Read and Write Files
        1. 3.6.1. Sequential Access
        2. 3.6.2. Random Access
        3. 3.6.3. Common Tasks
      7. 3.7. Check Results
      8. 3.8. Find Truth
      9. 3.9. Compare Bits
      10. 3.10. Run Other Applications
      11. 3.11. Control the Compiler
      12. 3.12. Not Covered Here
      13. 3.13. What You’ve Learned
    4. 4. Using Excel Objects
      1. 4.1. Objects and Their Members
      2. 4.2. Get Excel Objects
      3. 4.3. Get Objects from Collections
      4. 4.4. About Me and the Active Object
      5. 4.5. Find the Right Object
        1. 4.5.1. Top-Level Objects
        2. 4.5.2. Workbook Objects
        3. 4.5.3. Worksheet and Range Objects
        4. 4.5.4. Chart Objects
        5. 4.5.5. Data List and XML Objects
        6. 4.5.6. Database Objects
        7. 4.5.7. Dialog Box and Form Objects
      6. 4.6. Common Members
        1. 4.6.1. Activate Objects: Get Names and Values
        2. 4.6.2. Add or Delete Objects Through Collections
        3. 4.6.3. Change Size and Position of Objects
        4. 4.6.4. Print Objects
      7. 4.7. Respond to Events in Excel
      8. 4.8. The Global Object
      9. 4.9. The WorksheetFunction Object
      10. 4.10. What You’ve Learned
    5. 5. Creating Your Own Objects
      1. 5.1. Modules Versus Classes
        1. 5.1.1. Modules
        2. 5.1.2. Classes
      2. 5.2. Add Methods
      3. 5.3. Create Properties
        1. 5.3.1. Read-Only Properties
        2. 5.3.2. Write-Once/Write-Only Properties
      4. 5.4. Define Enumerations
      5. 5.5. Raise Events
      6. 5.6. Collect Objects
      7. 5.7. Expose Objects
      8. 5.8. Destroy Objects
      9. 5.9. Things You Can’t Do
      10. 5.10. What You’ve Learned
    6. 6. Writing Code for Use by Others
      1. 6.1. Types of Applications
      2. 6.2. The Development Process
      3. 6.3. Determine Requirements
      4. 6.4. Design
      5. 6.5. Implement and Test
      6. 6.6. Integrate
      7. 6.7. Test Platforms
      8. 6.8. Document
      9. 6.9. Deploy
        1. 6.9.1. Protect Code
        2. 6.9.2. Sign Files
        3. 6.9.3. When Signatures Expire
        4. 6.9.4. Install Workbooks
        5. 6.9.5. Install Templates and Add-ins
      10. 6.10. What You’ve Learned
      11. 6.11. Resources
  5. II. Excel Objects
    1. 7. Controlling Excel
      1. 7.1. Perform Tasks
        1. 7.1.1. Quit Excel
        2. 7.1.2. Lock Out User Actions
        3. 7.1.3. Open and Close Excel Windows
        4. 7.1.4. Display Dialogs
      2. 7.2. Control Excel Options
        1. 7.2.1. Set Startup Paths
        2. 7.2.2. View System Settings
      3. 7.3. Get References
      4. 7.4. Application Members
        1. [Application.]ActivateMicrosoftApp(XlMSApplication)
        2. [Application.]ActivePrinter [= setting]
        3. Application.AddChartAutoFormat(Chart, Name, [Description])
        4. Application.AddCustomList(ListArray, [ByRow])
        5. Application.AlertBeforeOverwriting [= setting]
        6. Application.AltStartupPath
        7. Application.ArbitraryXMLSupportAvailable
        8. Application.AskToUpdateLinks [= setting]
        9. Application.Assistant
        10. Application.AutoCorrect
        11. Application.AutoFormatAsYouTypeReplaceHyperlinks [= setting]
        12. Application.AutomationSecurity [=MsoAutomationSecurity]
        13. Application.AutoPercentEntry [= setting]
        14. Application.AutoRecover
        15. Application.Build
        16. [Application.]Calculate( )
        17. Application.CalculateBeforeSave [= setting]
        18. Application.CalculateFull( )
        19. Application.CalculateFullRebuild( )
        20. Application.Calculation [= XlCalculation]
        21. Application.CalculationInterruptKey [= XlCalculationInterruptKey]
        22. Application.CalculationState
        23. Application.CalculationVersion
        24. Application.Caller
        25. Application.Caption [= setting]
        26. Application.CellDragAndDrop [= setting]
        27. [Application.]Cells[(row, column)]
        28. Application.CentimetersToPoints(Centimeters)
        29. [Application.]Charts([index])
        30. Application.CheckAbort([KeepAbort])
        31. Application.CheckSpelling(Word, [CustomDictionary], [IgnoreUppercase])
        32. Application.ClipboardFormats
        33. [Application].Columns([index])
        34. Application.COMAddIns([index])
        35. Application.CommandBars([index])
        36. Application.CommandUnderlines [= xlCommandUnderlines]
        37. Application.ConstrainNumeric [= setting]
        38. Application.ControlCharacters [= setting]
        39. Application.ConvertFormula(Formula, FromReferenceStyle, [ToReferenceStyle], [ToAbsolute], [RelativeTo])
        40. Application.CopyObjectsWithCells [= setting]
        41. Application.Cursor [= XlMousePointer]
        42. Application.CursorMovement [= setting]
        43. Application.CustomListCount
        44. Application.CutCopyMode [= setting]
        45. Application.DataEntryMode [= setting]
        46. Application.DecimalSeparator [= setting]
        47. Application.DefaultFilePath [= setting]
        48. Application.DefaultSaveFormat [= XlFileFormat]
        49. Application.DefaultSheetDirection [= setting]
        50. Application.DefaultWebOptions
        51. Application.DeleteChartAutoFormat(Name)
        52. [Application.]DeleteCustomList(ListNum)
        53. Application.Dialogs(XlBuiltInDialog)
        54. Application.DisplayAlerts [= setting]
        55. Application.DisplayClipboardWindow [= setting]
        56. Application.DisplayCommentIndicator [=XlCommentDisplayMode]
        57. Application.DisplayDocumentActionTaskPane [= setting]
        58. Application.DisplayExcel4Menus [= setting]
        59. Application.DisplayFormulaBar [= setting]
        60. Application.DisplayFullScreen [= setting]
        61. Application.DisplayFunctionToolTips [= setting]
        62. Application.DisplayInsertOptions [= setting]
        63. Application.DisplayNoteIndicator [= setting]
        64. Application.DisplayPasteOptions [= setting]
        65. Application.DisplayRecentFiles [= setting]
        66. Application.DisplayScrollBars [= setting]
        67. Application.DisplayStatusBar [= setting]
        68. Application.DisplayXMLSourcePane ([XmlMap])
        69. Application.DoubleClick( )
        70. Application.EditDirectlyInCell [= setting]
        71. Application.EnableAnimations [= setting]
        72. [Application.]EnableAutoComplete [= setting]
        73. Application.EnableCancelKey [= XlEnableCancelKey]
        74. Application.EnableEvents [= setting]
        75. Application.EnableSound [= setting]
        76. Application.ErrorCheckingOptions
        77. [Application.]Evaluate(Name)
        78. Application.ExtendList [= setting]
        79. Application.FeatureInstall [= MsoFeatureInstall]
        80. Application.FileConverters[(Index1, Index2)]
        81. Application.FileDialog (MsoFileDialogType)
        82. Application.FileFind
        83. Application.FileSearch
        84. Application.FindFile( )
        85. Application.FindFormat
        86. Application.FixedDecimal [= setting]
        87. Application.FixedDecimalPlaces [= setting]
        88. Application.GenerateGetPivotData [= setting]
        89. Application.GetCustomListContents
        90. Application.GetCustomListNum(ListArray)
        91. Application.GetOpenFilename([FileFilter], [FilterIndex], [Title], [ButtonText], [MultiSelect])
        92. Application.GetPhonetic([Text])
        93. Application.GetSaveAsFilename([InitialFilename], [FileFilter], [FilterIndex], [Title], [ButtonText])
        94. Application.Goto([Reference], [Scroll])
        95. Application.Height
        96. Application.Help([HelpFile], [HelpContextID])
        97. Application.Hinstance
        98. Application.Hwnd
        99. Application.InchesToPoints(Inches)
        100. [Application.]InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID], [Type])
        101. Application.Interactive [= setting]
        102. Application.International(XlApplicationInternational)
        103. [Application.]Intersect(Arg1, Arg2, [Argn], ...)
        104. Application.Iteration [= setting]
        105. Application.LanguageSettings
        106. Application.LargeButtons [= setting]
        107. Application.Left [= setting]
        108. Application.LibraryPath
        109. Application.MacroOptions([Macro], [Description], [HasMenu], [MenuText], [HasShortcutKey], [ShortcutKey], [Category], [StatusBar], [HelpContextId], [HelpFile])
        110. Application.MailLogoff( )
        111. Application.MailLogon([Name], [Password], [DownloadNewMail])
        112. Application.MailSession
        113. Application.MailSystem
        114. Application.MapPaperSize [= setting]
        115. Application.MaxChange [= setting]
        116. Application.MaxIterations [= setting]
        117. Application.MoveAfterReturn [= setting]
        118. Application.MoveAfterReturnDirection [=XlDirection]
        119. Application.Names([index])
        120. Application.NetworkTemplatesPath
        121. Application.NewWorkbook
        122. Application.NextLetter( )
        123. Application.ODBCErrors
        124. Application.ODBCTimeout [= setting]
        125. Application.OLEDBErrors
        126. Application.OnKey(Key, [Procedure])
        127. Application.OnRepeat(Text, Procedure)
        128. Application.OnTime(EarliestTime, Procedure, [LatestTime], [Schedule])
        129. Application.OnUndo(Text, Procedure)
        130. Application.OnWindow [= setting]
        131. Application.OperatingSystem
        132. Application.OrganizationName
        133. Application.Path
        134. Application.PathSeparator
        135. Application.PivotTableSelection [= setting]
        136. Application.PreviousSelections([index])
        137. Application.ProductCode
        138. Application.PromptForSummaryInfo [= setting]
        139. Application.Quit( )
        140. [Application.]Range([cell1],[cell2])
        141. Application.Ready
        142. Application.RecentFiles([index])
        143. Application.RecordMacro([BasicCode], [XlmCode])
        144. Application.RecordRelative [= setting]
        145. Application.ReferenceStyle [=XlReferenceStyle]
        146. Application.RegisteredFunctions
        147. Application.RegisterXLL(Filename)
        148. Application.Repeat( )
        149. Application.ReplaceFormat [= setting]
        150. Application.RollZoom [= setting]
        151. [Application.]Rows([index])
        152. Application.RTD
        153. Application.Run([Macro], [Args])
        154. Application.SaveWorkspace([Filename])
        155. Application.ScreenUpdating [= setting]
        156. Application.Selection
        157. Application.SendKeys(Keys, [Wait])
        158. Application.SetDefaultChart([FormatName], [Gallery])
        159. [Application.]Sheets([index])
        160. Application.SheetsInNewWorkbook [= setting]
        161. Application.ShowChartTipNames [= setting]
        162. Application.ShowChartTipValues [= setting]
        163. Application.ShowStartupDialog [= setting]
        164. Application.ShowToolTips [= setting]
        165. Application.ShowWindowsInTaskbar [= setting]
        166. Application.SmartTagRecognizers
        167. Application.Speech
        168. Application.SpellingOptions
        169. Application.StandardFont [= setting]
        170. Application.StandardFontSize [= setting]
        171. Application.StartupPath
        172. Application.StatusBar [= setting]
        173. Application.TemplatesPath
        174. Application.ThisCell
        175. Application.ThisWorkbook
        176. Application.ThousandsSeparator [= setting]
        177. Application.Top [= setting]
        178. Application.Undo( )
        179. [Application.]Union(Arg1, Arg2, [Argn])
        180. Application.UsableHeight
        181. Application.UsableWidth
        182. Application.UsedObjects
        183. Application.UserControl
        184. Application.UserLibraryPath
        185. Application.UserName [= setting]
        186. Application.UseSystemSeparators [= setting]
        187. Application.VBE
        188. Application.Version
        189. Application.Visible [= setting]
        190. Application.Volatile([Volatile])
        191. Application.Wait(Time)
        192. Application.Watches([index])
        193. Application.Width [= setting]
        194. Application.Windows([index])
        195. Application.WindowsForPens
        196. Application.WindowState [= XlWindowState]
        197. [Application.]Workbooks([index])
        198. [Application.]WorksheetFunction
        199. [Application.]Worksheets([index])
      5. 7.5. AutoCorrect Members
        1. AutoCorrect.AddReplacement(What, Replacement)
        2. AutoCorrect.DeleteReplacement(What)
        3. AutoCorrect.ReplacementList
      6. 7.6. AutoRecover Members
        1. AutoRecover.Enabled [ = setting ]
        2. AutoRecover.Path [ = setting ]
        3. AutoRecover.Time [ = setting ]
      7. 7.7. ErrorChecking Members
      8. 7.8. SpellingOptions Members
      9. 7.9. Window and Windows Members
        1. window.Activate( )
        2. window.ActivateNext( )
        3. window.ActivatePrevious( )
        4. windows.Arrange([ArrangeStyle], [ActiveWorkbook], [SyncHorizontal], [SyncVertical])
        5. windows.BreakSideBySide( )
        6. window.Close([SaveChanges], [Filename], [RouteWorkbook])
        7. windows.CompareSideBySideWith(WindowName)
        8. window.DisplayFormulas [= setting]
        9. window.DisplayGridlines [= setting]
        10. window.DisplayHeadings [= setting]
        11. window.DisplayHorizontalScrollBar [= setting]
        12. window.DisplayOutline [= setting]
        13. window.DisplayRightToLeft [= setting]
        14. window.DisplayVerticalScrollBar [= setting]
        15. window.DisplayWorkbookTabs [= setting]
        16. window.DisplayZeros [= setting]
        17. window.EnableResize [= setting]
        18. window.FreezePanes [= setting]
        19. window.GridlineColor [= setting]
        20. window.GridlineColorIndex [=xlColorIndexAutomatic]
        21. window.LargeScroll([Down], [Up], [ToRight], [ToLeft])
        22. window.Panes
        23. window.PointsToScreenPixelsX(Points)
        24. window.PointsToScreenPixelsY(Points)
        25. window.RangeFromPoint(x, y)
        26. window.RangeSelection
        27. windows.ResetPositionsSideBySide( )
        28. window.ScrollColumn [= setting]
        29. window.ScrollIntoView(Left, Top, Width, Height, [Start])
        30. window.ScrollRow [= setting]
        31. window.ScrollWorkbookTabs([Sheets], [Position])
        32. window.SelectedSheets
        33. window.Selection
        34. window.SmallScroll([Down], [Up], [ToRight], [ToLeft])
        35. window.Split [= setting]
        36. window.SplitColumn [= setting]
        37. window.SplitHorizontal [= setting]
        38. window.SplitRow [= setting]
        39. window.SplitVertical [= setting]
        40. windows.SyncScrollingSideBySide [= setting]
        41. window.TabRatio [= setting]
        42. window.View [= XlWindowView]
        43. window.VisibleRange
        44. window.WindowNumber [= setting]
        45. window.WindowState [= XlWindowState]
        46. window.Zoom [= setting]
      10. 7.10. Pane and Panes Members
    2. 8. Opening, Saving, and Sharing Workbooks
      1. 8.1. Add, Open, Save, and Close
        1. 8.1.1. Templates
        2. 8.1.2. Open as Read-Only or with Passwords
        3. 8.1.3. Open Text Files
        4. 8.1.4. Open XML Files
        5. 8.1.5. Close Workbooks
      2. 8.2. Share Workbooks
      3. 8.3. Program with Shared Workbooks
      4. 8.4. Program with Shared Workspaces
        1. 8.4.1. Open Workbooks from a Shared Workspace
        2. 8.4.2. Link a Workbook to a Workspace
        3. 8.4.3. Remove Sharing
      5. 8.5. Respond to Actions
      6. 8.6. Workbook and Workbooks Members
        1. workbook.AcceptAllChanges([When], [Who], [Where])
        2. workbook.AcceptLabelsInFormulas [= setting]
        3. workbook.Activate( )
        4. workbook.ActiveChart
        5. workbook.ActiveSheet
        6. workbooks.Add([Template])
        7. workbook.AddToFavorites
        8. workbook.Author [= setting]
        9. workbook.AutoUpdateFrequency [= setting]
        10. workbook.AutoUpdateSaveChanges [= setting]
        11. workbook.BreakLink(Name, Type)
        12. workbook.BuiltinDocumentProperties
        13. workbook.CalculationVersion
        14. workbook.CanCheckIn
        15. workbooks.CanCheckOut(Filename)
        16. workbook.ChangeFileAccess(Mode, [WritePassword], [Notify])
        17. workbook.ChangeHistoryDuration [= setting]
        18. workbook.ChangeLink(Name, NewName, [Type])
        19. workbook.Charts
        20. workbook.CheckIn([SaveChanges], [Comments], [MakePublic])
        21. workbooks.CheckOut(Filename)
        22. workbook.Close([SaveChanges], [Filename], [RouteWorkbook])
        23. workbook.CodeName
        24. workbook.Colors [= setting]
        25. workbook.CommandBars
        26. workbook.Comments [= setting]
        27. workbook.ConflictResolution [= setting]
        28. workbook.Container
        29. workbook.CreateBackup [= setting]
        30. workbook.CustomDocumentProperties
        31. workbook.CustomViews
        32. workbook.DeleteNumberFormat(NumberFormat)
        33. workbook.DisplayDrawingObjects [= setting]
        34. workbook.DisplayInkComments [= setting]
        35. workbook.DocumentLibraryVersions
        36. workbook.EnableAutoRecover [= setting]
        37. workbook.EndReview
        38. workbook.EnvelopeVisible [= setting]
        39. workbook.ExclusiveAccess
        40. workbook.FileFormat
        41. workbook.FollowHyperlink(Address, [SubAddress], [NewWindow], [AddHistory], [ExtraInfo], [Method], [HeaderInfo])
        42. workbook.ForwardMailer( )
        43. workbook.FullName
        44. workbook.FullNameURLEncoded
        45. workbook.HasMailer
        46. workbook.HasPassword
        47. workbook.HasRoutingSlip [= setting]
        48. workbook.HighlightChangesOnScreen [= setting]
        49. workbook.HighlightChangesOptions([When], [Who], [Where])
        50. workbook.HTMLProject
        51. workbook.InactiveListBorderVisible [= setting]
        52. workbook.IsAddin [= setting]
        53. workbook.IsInplace
        54. workbook.KeepChangeHistory [= setting]
        55. workbook.Keywords [= setting]
        56. workbook.LinkInfo(Name, LinkInfo, [Type], [EditionRef])
        57. workbook.LinkSources([Type])
        58. workbook.ListChangesOnNewSheet [= setting]
        59. workbook.Mailer
        60. workbook.MergeWorkbook(Filename)
        61. workbook.MultiUserEditing
        62. workbook.Names
        63. workbook.NewWindow
        64. workbooks.Open(Filename, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad])
        65. workbooks.OpenDatabase(Filename, [CommandText], [CommandType], [BackgroundQuery], [ImportDataAs])
        66. workbook.OpenLinks(Name, [ReadOnly], [Type])
        67. workbooks.OpenText(Filename, [Origin], [StartRow], [DataType], [TextQualifier], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [TextVisualLayout], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers], [Local])
        68. workbooks.OpenXML(Filename, [Stylesheets], [LoadOption])
        69. workbook.Path
        70. workbook.PersonalViewListSettings [= setting]
        71. workbook.PersonalViewPrintSettings [= setting]
        72. workbook.PivotCaches
        73. workbook.PivotTableWizard([SourceType], [SourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [SaveData], [HasAutoFormat], [AutoPage], [Reserved], [BackgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount], [ReadData], [Connection])
        74. workbook.Post([DestName])
        75. workbook.PrecisionAsDisplayed [= setting]
        76. workbook.PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName])
        77. workbook.PrintPreview([EnableChanges])
        78. workbook.PublishObjects
        79. workbook.PurgeChangeHistoryNow(Days, [SharingPassword])
        80. workbook.ReadOnly
        81. workbook.ReadOnlyRecommended
        82. workbook.RecheckSmartTags
        83. workbook.RefreshAll
        84. workbook.RejectAllChanges([When], [Who], [Where])
        85. workbook.ReloadAs(Encoding)
        86. workbook.RemovePersonalInformation [= setting]
        87. workbook.RemoveUser(Index)
        88. workbook.Reply
        89. workbook.ReplyAll
        90. workbook.ReplyWithChanges([ShowMessage])
        91. workbook.ResetColors
        92. workbook.RevisionNumber
        93. workbook.Route
        94. workbook.Routed
        95. workbook.RoutingSlip
        96. workbook.RunAutoMacros(Which)
        97. workbook.Save
        98. workbook.SaveAs([Filename], [FileFormat], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AccessMode], [ConflictResolution], [AddToMru], [TextCodepage], [TextVisualLayout], [Local])
        99. workbook.SaveAsXMLData(Filename, Map)
        100. workbook.SaveCopyAs([Filename])
        101. workbook.Saved [= setting]
        102. workbook.SaveLinkValues [= setting]
        103. workbook.SendFaxOverInternet([Recipients], [Subject], [ShowMessage])
        104. workbook.SendForReview([Recipients], [Subject], [ShowMessage], [IncludeAttachment])
        105. workbook.SendMail(Recipients, [Subject], [ReturnReceipt])
        106. workbook.SendMailer([FileFormat], [Priority])
        107. workbook.SetLinkOnData(Name, [Procedure])
        108. workbook.SharedWorkspace
        109. workbook.Sheets
        110. workbook.ShowConflictHistory [= setting]
        111. workbook.ShowPivotTableFieldList [= setting]
        112. workbook.SmartDocument
        113. workbook.SmartTagOptions
        114. workbook.Styles [= setting]
        115. workbook.Subject [= setting]
        116. workbook.TemplateRemoveExtData [= setting]
        117. workbook.Title [= setting]
        118. workbook.ToggleFormsDesign [= setting]
        119. workbook.UpdateFromFile
        120. workbook.UpdateLink([Name], [Type])
        121. workbook.UpdateLinks [= setting]
        122. workbook.UpdateRemoteReferences [= setting]
        123. workbook.UserStatus
        124. workbook.VBASigned
        125. workbook.VBProject
        126. workbook.WebOptions
        127. workbook.WebPagePreview
        128. workbook.Windows [= setting]
        129. workbook.Worksheets
        130. workbook.XmlImport(Url, ImportMap, [Overwrite], [Destination])
        131. workbook.XmlImportXml(Data, ImportMap, [Overwrite], [Destination])
        132. workbook.XmlMaps
        133. workbook.XmlNamespaces
      7. 8.7. RecentFile and RecentFiles Members
        1. recentfiles.Add(Name)
        2. recentfile.Delete( )
        3. recentfiles.Maximum [= setting]
        4. recentfile.Name
        5. recentfile.Open
        6. recentfile.Path
    3. 9. Working with Worksheets and Ranges
      1. 9.1. Work with Worksheet Objects
        1. 9.1.1. Get Cells in a Worksheet
      2. 9.2. Worksheets and Worksheet Members
        1. worksheet.Activate( )
        2. worksheets.Add(Before, After, Count, Type)
        3. worksheet.Calculate( )
        4. worksheet.Cells
        5. worksheet.CheckSpelling(CustomDictionary, IgnoreUppercase, AlwaysSuggest, SpellLang)
        6. worksheet.Columns([Index])
        7. worksheet.Comments
        8. worksheet.Copy(Before, After)
        9. worksheet.DisplayPageBreaks
        10. worksheet.EnableCalculation [= setting]
        11. worksheet.EnableOutlining [= setting]
        12. worksheet.EnablePivotTable [= setting]
        13. worksheet.EnableSelection [= setting]
        14. worksheet.Hyperlinks
        15. worksheet.Move(Before, After)
        16. worksheet.Outline
        17. worksheet.PageSetup
        18. worksheet.Paste([Destination], [Link])
        19. worksheet.PasteSpecial([Format], [Link], [DisplayAsIcon], [IconFileName], [IconIndex], [IconLabel], [NoHTMLFormatting])
        20. worksheet.Protect([Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [ AllowInsertingColumns], [ AllowInsertingRows], [AllowInsertingHyperlinks], [ AllowDeletingColumns], [ AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables])
        21. worksheet.ProtectContents
        22. worksheet.ProtectDrawingObjects
        23. worksheet.Protection
        24. worksheet.ProtectionMode
        25. worksheet.ProtectScenarios
        26. worksheet.QueryTables
        27. worksheet.Range([Cell1], [Cell2])
        28. worksheet.Rows([Index])
        29. worksheet.Scenarios([Index])
        30. worksheet.ScrollArea
        31. worksheet.SetBackgroundPicture([Filename])
        32. worksheet.Shapes
        33. worksheet.StandardHeight
        34. worksheet.StandardWidth
        35. worksheet.Type [= setting]
        36. worksheet.Unprotect([Password])
        37. worksheet.UsedRange
      3. 9.3. Sheets Members
        1. Sheets.Copy(Before, After)
        2. Sheets.FillAcrossSheets(Range, Type)
        3. Sheets.Move(Before, After)
      4. 9.4. Work with Outlines
      5. 9.5. Outline Members
        1. outline.AutomaticStyles
        2. outline.ShowLevels(RowLevels, ColumnLevels)
        3. outline.SummaryColumn
        4. outline.SummaryRow
      6. 9.6. Work with Ranges
        1. 9.6.1. Find and Replace Text in a Range
        2. 9.6.2. Use Named Ranges
        3. 9.6.3. Format and Change Text
      7. 9.7. Range Members
        1. range.Activate( )
        2. range.AddComment( )
        3. range.AddIndent[= setting]
        4. range.Address([RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo])
        5. range.AllowEdit
        6. range.Areas([Index])
        7. range.AutoFill(Destination, [Type])
        8. range.AutoFit
        9. range.BorderAround([LineStyle], [Weight], [ColorIndex], [Color])
        10. range.Borders([Index])
        11. range.Calculate( )
        12. range.Cells([RowIndex], [ColumnIndex])
        13. range.Characters([Start], [Length])
        14. range.CheckSpelling([CustomDictionary], [IgnoreUppercase], [AlwaysSuggest], [SpellLang])
        15. range.Clear( )
        16. range.ClearContents( )
        17. range.ClearFormats( )
        18. range.Column
        19. range.Columns([Index])
        20. range.ColumnWidth
        21. range.Copy([Destination])
        22. range.CopyFromRecordset([Data, MaxRows, MaxColumns])
        23. range.Cut([Destination])
        24. range.Delete([Shift])
        25. range.Dependents
        26. range.DirectDependents
        27. range.DirectPrecedents
        28. range.End([Direction])
        29. range.EntireColumn
        30. range.EntireRow
        31. range.FillDown
        32. range.FillLeft
        33. range.FillRight
        34. range.FillUp
        35. range.Find(What, [After], [LookIn], [LookAt]), [SearchOrder], [SearchDirection], [MatchCase], [MatchByte], [SearchFormat])
        36. range.FindNext([After])
        37. range.FindPrevious([After])
        38. range.Font
        39. range.Formula
        40. range.FormulaR1C1
        41. range.Hidden
        42. range.HorizontalAlignment
        43. range.Hyperlinks
        44. range.Insert([Shift])
        45. range.Interior
        46. range.Item(RowIndex, [ColumnIndex])
        47. range.Justify
        48. range.Locked
        49. range.Merge([Across])
        50. range.MergeArea
        51. range.MergeCells
        52. range.Next
        53. range.NoteText([Text], [Start], [Length])
        54. range.NumberFormat
        55. range.NumberFormatLocal
        56. range.Offset([RowOffset], [ColumnOffset])
        57. range.PageBreak
        58. range.PasteSpecial([Paste], [Operation], [SkipBlanks], [Transpose])
        59. range.Precedents
        60. range.Previous
        61. range.PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName])
        62. range.PrintPreview
        63. range.Replace(What, Replacement, [LookAt]), [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat])
        64. range.Resize([RowSize]), [ColumnSize])
        65. range.Row
        66. range.RowDifferences(Comparison)
        67. range.RowHeight
        68. range.Rows([Index])
        69. range.Select
        70. range.Show
        71. range.ShowDependents([Remove])
        72. range.ShowDetail [= setting]
        73. range.ShowErrors( )
        74. range.ShowPrecedents([Remove])
        75. range.ShrinkToFit [= setting]
        76. range.Sort([Key1]), [Order1], [Key2], [Type], [Order2], [Key3], [Order3], [Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod], [DataOption1], [DataOption2], [DataOption3])
        77. range.SpecialCells(Type, [Value])
        78. range.Style
        79. range.Table([RowInput], [ColumnInput])
        80. range.Text
        81. range.TextToColumns([Destination]), [DataType], [TextQualifier], [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space], [Other], [OtherChar], [FieldInfo], [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers])
        82. range.UnMerge
        83. range.UseStandardHeight [= setting]
        84. range.UseStandardWidth [= setting]
        85. range.Value([RangeValueDataType]) [= setting]
        86. range.VerticalAlignment
        87. range.Worksheet
        88. range.WrapText[= setting]
      8. 9.8. Work with Scenario Objects
      9. 9.9. Scenario and Scenarios Members
        1. scenario.ChangeScenario(ChangingCells, [Values])
        2. scenario.ChangingCells
        3. scenario.Comment [= setting]
        4. scenario.Hidden [= setting]
        5. scenario.Locked [= setting]
        6. scenario.Show
        7. scenario.Values
      10. 9.10. Resources
    4. 10. Linking and Embedding
      1. 10.1. Add Comments
      2. 10.2. Use Hyperlinks
      3. 10.3. Link and Embed Objects
        1. 10.3.1. Embed Controls
        2. 10.3.2. Use OleObjects in Code
      4. 10.4. Speak
      5. 10.5. Comment and Comments Members
        1. comment.Author
        2. comment.Delete
        3. comment.Shape
        4. comment.Text([Text], [Start], [Overwrite])
      6. 10.6. Hyperlink and Hyperlinks Members
        1. hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])
        2. hyperlink.Address [= setting]
        3. hyperlink.AddToFavorites( )
        4. hyperlink.CreateNewDocument(Filename, EditNow, Overwrite)
        5. hyperlink.EmailSubject [= setting]
        6. hyperlink.Follow([NewWindow], [AddHistory], [ExtraInfo], [Method], [HeaderInfo])
        7. hyperlink.Range
        8. hyperlink.ScreenTip [= setting]
        9. hyperlink.Shape
        10. hyperlink.SubAddress [= setting]
        11. hyperlink.TextToDisplay [= setting]
        12. hyperlink.Type
      7. 10.7. OleObject and OleObjects Members
        1. oleobject.Add([ClassType], [Filename], [Link], [DisplayAsIcon], [IconFileName], [IconIndex], [IconLabel], [Left], [Top], [Width], [Height])
        2. oleobject.AutoLoad [= setting]
        3. oleobject.AutoUpdate [= setting]
        4. oleobject.BottomRightCell
        5. oleobject.BringToFront( )
        6. oleobject.Copy( )
        7. oleobject.CopyPicture([Appearance], [Format])
        8. oleobject.Duplicate( )
        9. oleobjects.Group( )
        10. oleobject.LinkedCell
        11. oleobject.ListFillRange [= setting]
        12. oleObject.Object
        13. oleobject.OLEType
        14. oleobject.OnAction [= setting]
        15. oleobject.Placement [= xlPlacement]
        16. oleobject.progID [= setting]
        17. oleobject.Shadow [= setting]
        18. oleobject.ShapeRange
        19. oleobject.SourceName
        20. oleobject.Update( )
        21. oleobject.Verb([Verb])
        22. oleobject.ZOrder
      8. 10.8. OLEFormat Members
      9. 10.9. Speech Members
        1. speech.Direction [= XlSpeakDirection]
        2. speech.Speak(Text, [SpeakAsync], [SpeakXML], [Purge])
        3. speech.SpeakCellOnEnter [= setting]
      10. 10.10. UsedObjects Members
    5. 11. Printing and Publishing
      1. 11.1. Print and Preview
      2. 11.2. Control Paging
      3. 11.3. Change Printer Settings
      4. 11.4. Filter Ranges
      5. 11.5. Save and Display Views
      6. 11.6. Publish to the Web
      7. 11.7. AutoFilter Members
        1. autofilter.Filters(index)
      8. 11.8. Filter and Filters Members
        1. filter.Criteria1
        2. filter.Criteria2
        3. filter.On
        4. filter.Operator
      9. 11.9. CustomView and CustomViews Members
        1. customviews.Add(ViewName, [PrintSettings], [RowColSettings])
        2. customview.PrintSettings
        3. customview.RowColSettings
        4. customview.Show
      10. 11.10. HPageBreak, HPageBreaks, VPageBreak, VPageBreaks Members
        1. pagebreaks.Add(Before)
        2. pagebreak.DragOff(Direction, RegionIndex)
        3. pagebreak.Extent
        4. pagebreak.Location
        5. pagebreak.Type
      11. 11.11. PageSetup Members
        1. pagesetup.BlackAndWhite [= setting]
        2. pagesetup.BottomMargin [= setting]
        3. pagesetup.CenterFooter [= setting]
        4. pagesetup.CenterFooterPicture
        5. pagesetup.CenterHeader [= setting]
        6. pagesetup.CenterHeaderPicture
        7. pagesetup.CenterHorizontally [= setting]
        8. pagesetup.CenterVertically [= setting]
        9. pagesetup.ChartSize [= setting]
        10. pagesetup.Draft [= setting]
        11. pagesetup.FirstPageNumber [= setting]
        12. pagesetup.FitToPagesTall [= setting]
        13. pagesetup.FitToPagesWide [= setting]
        14. pagesetup.FooterMargin [= setting]
        15. pagesetup.HeaderMargin [= setting]
        16. pagesetup.LeftFooter [= setting]
        17. pagesetup.LeftFooterPicture
        18. pagesetup.LeftHeader [= setting]
        19. pagesetup.LeftHeaderPicture
        20. pagesetup.LeftMargin [= setting]
        21. pagesetup.Order [= setting]
        22. pagesetup.Orientation [= setting]
        23. pagesetup.PaperSize [= setting]
        24. pagesetup.PrintArea [= setting]
        25. pagesetup.PrintComments [= setting]
        26. pagesetup.PrintErrors [= setting]
        27. pagesetup.PrintGridlines [= setting]
        28. pagesetup.PrintHeadings [= setting]
        29. pagesetup.PrintNotes [= setting]
        30. pagesetup.PrintQuality(index) [= setting]
        31. pagesetup.PrintTitleColumns [= setting]
        32. pagesetup.PrintTitleRows [= setting]
        33. pagesetup.RightFooter [= setting]
        34. pagesetup.RightFooterPicture
        35. pagesetup.RightHeader [= setting]
        36. pagesetup.RightHeaderPicture
        37. pagesetup.RightMargin [= setting]
        38. pagesetup.TopMargin [= setting]
        39. pagesetup.Zoom [= setting]
      12. 11.12. Graphic Members
        1. graphic.Filename [= setting]
        2. graphic.LockAspectRatio [= setting]
      13. 11.13. PublishObject and PublishObjects Members
        1. publishobjects.Add(SourceType, Filename, [Sheet], [Source], [HtmlType], [DivID], [Title])
        2. publishobject.AutoRepublish [= setting]
        3. publishobject.DivID
        4. publishobject.Filename [= setting]
        5. publishobject.HtmlType [= setting]
        6. publishobjects.Publish([Create])
        7. publishobject.Sheet
        8. publishobject.Source
        9. publishobject.SourceType
        10. publishobject.Title [= setting]
      14. 11.14. WebOptions and DefaultWebOptions Members
        1. options.AllowPNG [= setting]
        2. defaultweboptions.AlwaysSaveInDefaultEncoding [= setting]
        3. defaultweboptions.CheckIfOfficeIsHTMLEditor [= setting]
        4. options.DownloadComponents [= setting]
        5. options.Encoding [= msoEncoding]
        6. options.FolderSuffix
        7. defaultweboptions.Fonts
        8. defaultweboptions.LoadPictures [= setting]
        9. options.LocationOfComponents [= setting]
        10. options.OrganizeInFolder [= setting]
        11. options.RelyOnCSS [= setting]
        12. options.RelyOnVML [= setting]
        13. defaultweboptions.SaveHiddenData [= setting]
        14. defaultweboptions.SaveNewWebPagesAsWebArchives [= setting]
        15. options.ScreenSize [= msoScreenSize]
        16. options.TargetBrowser [= msoTargetBrowser]
        17. defaultweboptions.UpdateLinksOnSave [= setting]
        18. options.UseLongFileNames [= setting]
    6. 12. Loading and Manipulating Data
      1. 12.1. Working with QueryTable Objects
      2. 12.2. QueryTable and QueryTables Members
        1. querytables.Add(Connection, Destination, [Sql])
        2. querytable.AdjustColumnWidth [= setting]
        3. querytable.BackgroundQuery[= setting]
        4. querytable.CancelRefresh
        5. querytable.CommandText[= setting]
        6. querytable.CommandType[= setting]
        7. querytable.Connection[= setting]
        8. querytable.Delete
        9. querytable.Destination
        10. querytable.EnableEditing[= setting]
        11. querytable.EnableRefresh[= setting]
        12. querytable.FetchedRowOverflow[= setting]
        13. querytable.FieldNames[= setting]
        14. querytable.FillAdjacentFormulas[= setting]
        15. querytable.MaintainConnection[= setting]
        16. querytable.Parameters
        17. querytable.PreserveColumnInfo[= setting]
        18. querytable.PreserveFormatting[= setting]
        19. querytable.QueryType[= setting]
        20. querytable.Recordset[= setting]
        21. querytable.Refresh([BackgroundQuery])
        22. querytable.Refreshing
        23. querytable.RefreshOnFileOpen[= setting]
        24. querytable.RefreshPeriod[= setting]
        25. querytable.RefreshStyle[= setting]
        26. querytable.ResetTimer
        27. querytable.ResultRange
        28. querytable.RowNumbers[= setting]
        29. querytable.SavePassword[= setting]
        30. querytable.TextFileColumnDataTypes[= setting]
        31. querytable.TextFileCommaDelimiter[= setting]
        32. querytable.TextFileConsecutiveDelimiter[= setting]
        33. querytable.TextFileDecimalSeparator[= setting]
        34. querytable.TextFileFixedColumnWidths[= setting]
        35. querytable.TextFileOtherDelimiter[= setting]
        36. querytable.TextFileParseType[= setting]
        37. querytable.TextFilePlatform[= setting]
        38. querytable.TextFilePromptOnRefresh[= setting]
        39. querytable.TextFileSemicolonDelimiter[= setting]
        40. querytable.TextFileSpaceDelimiter[= setting]
        41. querytable.TextFileTabDelimiter[= setting]
        42. querytable.TextFileTextQualifier[= setting]
        43. querytable.TextFileThousandsSeparator[= setting]
        44. querytable.TextFileTrailingMinusNumbers[= setting]
        45. querytable.TextFileVisualLayout[= setting]
      3. 12.3. Working with Parameter Objects
      4. 12.4. Parameter Members
        1. parameters.Add(Name, [iDataType])
        2. parameter.DataType [= setting]
        3. parameter.Delete
        4. parameter.PromptString[= setting]
        5. parameter.RefreshOnChange[= setting]
        6. parameter.SetParam[= setting]
        7. parameter.SourceRange[= setting]
        8. parameter.Type[= setting]
        9. parameter.Value[= setting]
      5. 12.5. Working with ADO and DAO
      6. 12.6. ADO Objects and Members
        1. 12.6.1. ADO.Command Members
          1. command.ActiveConnection[= setting]
          2. command.CommandText[= setting]
          3. command.CommandType[= setting]
          4. command.CreateParameter
          5. command.Execute
          6. command.Name[= setting]
        2. 12.6.2. ADO.Connection Members
          1. connection.BeginTrans
          2. connection.Cancel
          3. connection.CommandTimeout[= setting]
          4. connection.CommitTrans
          5. connection.ConnectionString[= setting]
          6. connection.ConnectionTimeout[= setting]
          7. connection.Open
          8. connection.RollbackTrans
          9. connection.Version[= setting]
        3. 12.6.3. ADO.Field and ADO.Fields Members
          1. field.ActualSize[= setting]
          2. field.AppendChunk
          3. fields.CancelUpdate
          4. field.DefinedSize[= setting]
          5. field.GetChunk(Size)
          6. field.NumericScale[= setting]
          7. field.OriginalValue[= setting]
          8. field.UnderlyingValue[= setting]
          9. field.Value[= setting]
        4. 12.6.4. ADO.Parameter and ADO.Parameters Members
          1. Parameter.AppendChunk
          2. Parameter.Name[= setting]
          3. Parameter.NumericScale[= setting]
          4. Parameter.Precision[= setting]
          5. parameter.Size[= setting]
          6. parameter.Value[= setting]
        5. 12.6.5. ADO.Record Members
          1. record.ActiveConnection[= setting]
          2. record.Cancel
          3. record.GetChildren
          4. record.Open([Source], [ActiveConnection], [Mode]), [CreateOptions], [Options], [UserName], [Password])
          5. record.RecordType
          6. record.Source[= setting]
          7. record.State
        6. 12.6.6. ADO.Recordset Members
          1. recordset.AbsolutePosition[= setting]
          2. recordset.ActiveCommand[= setting]
          3. recordset.ActiveConnection [= setting]
          4. recordset.AddNew([FieldList], [Values])
          5. recordset.BOF[= setting]
          6. recordset.Cancel
          7. recordset.CancelUpdate
          8. recordset.Delete([AffectRecords])
          9. recordset.EOF[= setting]
          10. recordset.Filter[= setting]
          11. recordset.MoveFirst
          12. recordset.MoveLast
          13. recordset.MoveNext
          14. recordset.MovePrevious
          15. recordset.Open([Source], [ActiveConnection], [CursorType] , [LockType] , [Options])
          16. recordset.RecordCount[= setting]
          17. recordset.Requery
          18. recordset.Source[= setting]
          19. recordset.Update([Fields], [Value])
      7. 12.7. DAO Objects and Members
      8. 12.8. DAO.Database and DAO.Databases Members
        1. Reference Section
          1. database.Connection
        2. Reference Section
          1. database.Execute(Source, [Options])
        3. Reference Section
          1. database.OpenRecordset(Source, [Type], [Options]), [LockEdits])
        4. 12.8.1. DAO.DbEngine Members
          1. dbengine.CompactDatabase(olddb, newdb, [locale]), [options] , [password])
          2. dbengine.OpenDatabase(dbname, [options], [read-only], [connect])
      9. 12.9. DAO.Document and DAO.Documents Members
        1. Document.Container
        2. Document.Name
      10. 12.10. DAO.QueryDef and DAO.QueryDefs Members
        1. querydef.Execute([Options])
        2. querydef.MaxRecords[= setting]
        3. querydef.OpenRecordset([Type], [Options]), [LockEdits])
        4. querydef.SQL[= setting]
      11. 12.11. DAO.Recordset and DAO.Recordsets Members
        1. recordset.AddNew
        2. recordset.BOF[= setting]
        3. recordset.EOF[= setting]
        4. recordset.MoveFirst
        5. recordset.MoveLast
        6. recordset.MoveNext
        7. recordset.MovePrevious
    7. 13. Analyzing Data with Pivot Tables
      1. 13.1. Quick Guide to Pivot Tables
        1. 13.1.1. Create a Pivot Table
        2. 13.1.2. Apply Formatting
        3. 13.1.3. Change Totals
        4. 13.1.4. Chart the Data
        5. 13.1.5. Change the Layout
        6. 13.1.6. Connect to an External Data Source
        7. 13.1.7. Create OLAP Cubes
      2. 13.2. Program Pivot Tables
        1. 13.2.1. Create Pivot Tables
        2. 13.2.2. Refresh Pivot Tables and Charts
        3. 13.2.3. Connect to External Data
        4. 13.2.4. OLAP Data Cubes
      3. 13.3. PivotTable and PivotTables Members
        1. pivottables.Add(PivotCache, TableDestination, [TableName], [ReadData], [DefaultVersion])
        2. pivottable.AddDataField(Field, [Caption], [Function])
        3. pivottable.AddFields([RowFields], [ColumnFields], [PageFields], [AddToTable])
        4. pivottable.CacheIndex [= setting]
        5. pivottable.CalculatedFields( )
        6. pivottable.CalculatedMembers
        7. pivottable.ColumnFields
        8. pivottable.ColumnGrand [= setting]
        9. pivottable.ColumnRange
        10. pivottable.CreateCubeFile(File, [Measures], [Levels], [Members], [Properties])
        11. pivottable.CubeFields
        12. pivottable.DataBodyRange
        13. pivottable.DataFields
        14. pivottable.DataLabelRange
        15. pivottable.DataPivotField
        16. pivottable.DisplayEmptyColumn [= setting]
        17. pivottable.DisplayEmptyRow [= setting]
        18. pivottable.DisplayErrorString [= setting]
        19. pivottable.DisplayImmediateItems [= setting]
        20. pivottable.DisplayNullString [= setting]
        21. pivottable.EnableDataValueEditing [= setting]
        22. pivottable.EnableDrilldown [= setting]
        23. pivottable.EnableFieldDialog [= setting]
        24. pivottable.EnableFieldList [= setting]
        25. pivottable.EnableWizard [= setting]
        26. pivottable.ErrorString [= setting]
        27. pivottable.Format(Format)
        28. pivottable.GetData(Name)
        29. pivottable.GetPivotData([DataField], [Field1], [Item1], [Fieldn], [Itemn])
        30. pivottable.GrandTotalName [= setting]
        31. pivottable.HasAutoFormat [= setting]
        32. pivottable.HiddenFields
        33. pivottable.InnerDetail [= setting]
        34. pivottable.ListFormulas( )
        35. pivottable.ManualUpdate [= setting]
        36. pivottable.MDX
        37. pivottable.MergeLabels [= setting]
        38. pivottable.NullString [= setting]
        39. pivottable.PageFieldOrder [= xlOrder]
        40. pivottable.PageFields
        41. pivottable.PageFieldWrapCount [= setting]
        42. pivottable.PageRange
        43. pivottable.PageRangeCells
        44. pivottable.PivotCache( )
        45. pivottable.PivotFields
        46. pivottable.PivotFormulas
        47. pivottable.PivotSelect(Name, [Mode], [UseStandardName])
        48. pivottable.PivotSelection [= setting]
        49. pivottable.PivotSelectionStandard [= setting]
        50. pivottable.PivotTableWizard([SourceType], [SourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [SaveData], [HasAutoFormat], [AutoPage], [Reserved], [BackgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount], [ReadData], [Connection])
        51. pivottable.PreserveFormatting [= setting]
        52. pivottable.PrintTitles [= setting]
        53. pivottable.RefreshDate
        54. pivottable.RefreshName
        55. pivottable.RefreshTable( )
        56. pivottable.RepeatItemsOnEachPrintedPage [= setting]
        57. pivottable.RowFields
        58. pivottable.RowGrand [= setting]
        59. pivottable.RowRange
        60. pivottable.SaveData [= setting]
        61. pivottable.SelectionMode [= xlPTSelectionMode]
        62. pivottable.ShowCellBackgroundFromOLAP [= setting]
        63. pivottable.ShowPageMultipleItemLabel [= setting]
        64. pivottable.ShowPages([PageField])
        65. pivottable.SourceData
        66. pivottable.SubtotalHiddenPageItems [= setting]
        67. pivottable.TableRange1
        68. pivottable.TableRange2
        69. pivottable.TableStyle [= setting]
        70. pivottable.TotalsAnnotation [= setting]
        71. pivottable.Update( )
        72. pivottable.VacatedStyle [= setting]
        73. pivottable.Value [= setting]
        74. pivottable.Version
        75. pivottable.ViewCalculatedMembers [= setting]
        76. pivottable.VisibleFields
        77. pivottable.VisualTotals [= setting]
      4. 13.4. PivotCache and PivotCaches Members
        1. pivotcaches.Add(SourceType, [SourceData])
        2. pivotcache.ADOConnection
        3. pivotcache.BackgroundQuery [= setting]
        4. pivotcache.CommandText [= setting]
        5. pivotcache.CommandType [= xlCmdType]
        6. pivotcache.Connection [= setting]
        7. pivotcache.CreatePivotTable(TableDestination, [TableName], [ReadData], [DefaultVersion])
        8. pivotcache.EnableRefresh [= setting]
        9. pivotcache.IsConnected
        10. pivotcache.LocalConnection [= setting]
        11. pivotcache.MaintainConnection [= setting]
        12. pivotcache.MakeConnection( )
        13. pivotcache.MemoryUsed
        14. pivotcache.MissingItemsLimit [= setting]
        15. pivotcache.OLAP
        16. pivotcache.OptimizeCache [= setting]
        17. pivotcache.QueryType
        18. pivotcache.RecordCount
        19. pivotcache.Recordset [= setting]
        20. pivotcache.Refresh( )
        21. pivotcache.RefreshOnFileOpen [= setting]
        22. pivotcache.RefreshPeriod [= setting]
        23. pivotcache.ResetTimer( )
        24. pivotcache.RobustConnect [= xlRobustConnect]
        25. pivotcache.SaveAsODC(ODCFileName, [Description], [Keywords])
        26. pivotcache.SavePassword [= setting]
        27. pivotcache.SourceConnectionFile [= setting]
        28. pivotcache.SourceDataFile
        29. pivotcache.SourceType
        30. pivotcache.Sql [= setting]
        31. pivotcache.UseLocalConnection [= setting]
      5. 13.5. PivotField and PivotFields Members
        1. pivotfield.AddPageItem(Item, [ClearList])
        2. pivotfield.AutoShow(Type, Range, Count, Field)
        3. pivotfield.AutoShowCount
        4. pivotfield.AutoShowField
        5. pivotfield.AutoShowRange
        6. pivotfield.AutoShowType
        7. pivotfield.AutoSort(Order, Field)
        8. pivotfield.AutoSortField
        9. pivotfield.AutoSortOrder
        10. pivotfield.BaseField [= setting]
        11. pivotfield.BaseItem [= setting]
        12. pivotfield.CalculatedItems( )
        13. pivotfield.Calculation [= xlPivotFieldCalculation]
        14. pivotfield.Caption [= setting]
        15. pivotfield.ChildField
        16. pivotfield.ChildItems
        17. pivotfield.CubeField
        18. pivotfield.CurrentPage [= setting]
        19. pivotfield.CurrentPageList [= setting]
        20. pivotfield.CurrentPageName [= setting]
        21. pivotfield.DatabaseSort [= setting]
        22. pivotfield.DataRange
        23. pivotfield.DataType
        24. pivotfield.Delete( )
        25. pivotfield.DragToColumn [= setting]
        26. pivotfield.DragToData [= setting]
        27. pivotfield.DragToHide [= setting]
        28. pivotfield.DragToPage [= setting]
        29. pivotfield.DragToRow [= setting]
        30. pivotfield.DrilledDown [= setting]
        31. pivotfield.EnableItemSelection [= setting]
        32. pivotfield.Formula [= setting]
        33. pivotfield.Function [= xlConsolidationFunction]
        34. pivotfield.GroupLevel
        35. pivotfield.HiddenItems
        36. pivotfield.HiddenItemsList [= setting]
        37. pivotfield.IsCalculated
        38. pivotfield.IsMemberProperty
        39. pivotfield.LabelRange
        40. pivotfield.LayoutBlankLine [= setting]
        41. pivotfield.LayoutForm [= xlLayoutFormType]
        42. pivotfield.LayoutPageBreak [= setting]
        43. pivotfield.LayoutSubtotalLocation [= XlSubtototalLocationType]
        44. pivotfield.NumberFormat [= setting]
        45. pivotfield.Orientation [= xlPivotFieldOrientation]
        46. pivotfield.ParentField
        47. pivotfield.PivotItems([Index])
        48. pivotfield.Position [= setting]
        49. pivotfield.PropertyOrder [= setting]
        50. pivotfield.PropertyParentField
        51. pivotfield.ServerBased [= setting]
        52. pivotfield.ShowAllItems [= setting]
        53. pivotfield.SourceName
        54. pivotfield.StandardFormula [= setting]
        55. pivotfield.SubtotalName [= setting]
        56. pivotfield.Subtotals [= setting]
        57. pivotfield.TotalLevels
        58. pivotfield.VisibleItems
      6. 13.6. CalculatedFields Members
        1. calculatedfields.Add(Name, Formula, [UseStandardFormula])
      7. 13.7. CalculatedItems Members
        1. pivotitem.Add(Name, Formula, [UseStandardFormula])
      8. 13.8. PivotCell Members
      9. 13.9. PivotFormula and PivotFormulas Members
      10. 13.10. PivotItem and PivotItems Members
      11. 13.11. PivotItemList Members
      12. 13.12. PivotLayout Members
      13. 13.13. CubeField and CubeFields Members
        1. cubefield.AddMemberPropertyField(Property, [PropertyOrder])
        2. cubefields.AddSet(Name, Caption)
        3. cubefield.CubeFieldType
        4. cubefield.EnableMultiplePageItems [= setting]
        5. cubefield.HasMemberProperties
        6. cubefield.HiddenLevels [= setting]
        7. cubefield.ShowInFieldList [= setting]
        8. cubefield.TreeviewControl
      14. 13.14. CalculatedMember and CalculatedMembers Members
        1. calculatedmembers.Add(Name, Formula, [SolveOrder], [Type])
    8. 14. Sharing Data Using Lists
      1. 14.1. Use Lists
        1. 14.1.1. Supported Data Types
        2. 14.1.2. Resolve Conflicts
        3. 14.1.3. Authorization and Authentication in Shared Lists
        4. 14.1.4. Create a List in Code
        5. 14.1.5. Share a List
        6. 14.1.6. Insert a Shared List
        7. 14.1.7. Refresh and Update
        8. 14.1.8. Unlink, Unlist, and Delete
      2. 14.2. ListObject and ListObjects Members
        1. Reference Section
          1. listobjects.Add([SourceType], [Source], [LinkSource], [XlListObjectHasHeaders], [Destination])
        2. 14.2.1.
          1. 14.2.1.1. Create a list from a range
          2. 14.2.1.2. Insert a shared list
            1. listobject.DataBodyRange
            2. listobject.Delete( )
            3. listobject.Publish(Target, LinkSource)
            4. listobject.SharePointURL
            5. listobject.ShowTotals [= setting]
            6. listobject.Unlink( )
            7. listobject.Unlist( )
            8. listobject.UpdateChanges([iConflictType])
            9. listobject.XMLMap
      3. 14.3. ListRow and ListRows Members
        1. listrows.Add([Position])
        2. listrow.Delete
        3. listrow.InvalidData
        4. listrow.Range
      4. 14.4. ListColumn and ListColumns Members
        1. listcolumn.ListDataFormat
        2. listcolumn.SharePointFormula
        3. listcolumn.TotalsCalculation [= setting]
        4. listcolumn.XPath
      5. 14.5. ListDataFormat Members
        1. listdataformat.AllowFillIn
        2. listdataformat.Choices
        3. listdataformat.DecimalPlaces
        4. listdataformat.DefaultValue
        5. listdataformat.IsPercent
        6. listdataformat.lcid
        7. listdataformat.MaxCharacters
        8. listdataformat.MaxNumber
        9. listdataformat.MinNumber
        10. listdataformat.ReadOnly
        11. listdataformat.Required
        12. listdataformat.Type
      6. 14.6. Use the Lists Web Service
        1. 14.6.1. Authentication and Authorization
        2. 14.6.2. Debugging Tip
        3. 14.6.3. Add Attachments to a List
        4. 14.6.4. Retrieve Attachments
        5. 14.6.5. Delete Attachments
        6. 14.6.6. Delete a SharePoint List
        7. 14.6.7. Look Up a List GUID
        8. 14.6.8. Perform Queries
      7. 14.7. Lists Web Service Members
        1. wslists.AddAttachment (listName, listItemID, fileName, attachment)
        2. wslists.AddList (listName, description, templateID)
        3. wslists.DeleteAttachment (listName, listItemID, url)
        4. wslists.DeleteList (listName)
        5. wslists.GetAttachmentCollection (listName, listItemID)
        6. wslists.GetList (listName)
        7. wslists.GetListAndView (listName, viewName)
        8. wslists.GetListCollection ( )
        9. wslists.GetListItemChanges (listName, viewFields, since, contains)
        10. wslists.GetListItems (listName, viewName, query, viewFields, rowLimit, queryOptions)
        11. wslists.UpdateList (listName, listProperties, newFields, updateFields, deleteFields, listVersion)
        12. wslists.UpdateListItems (listName, updates)
      8. 14.8. Resources
    9. 15. Working with XML
      1. 15.1. Understand XML
      2. 15.2. Save Workbooks as XML
        1. 15.2.1. Data Excel Omits from XML
        2. 15.2.2. Transform XML Spreadsheets
        3. 15.2.3. Create XSLT for an XML Spreadsheet
        4. 15.2.4. Transform in Code
        5. 15.2.5. Transform from the Command Line
        6. 15.2.6. Transform with Processing Instructions
        7. 15.2.7. Transform XML into an XML Spreadsheet
      3. 15.3. Use XML Maps
        1. 15.3.1. Limitations of XML Maps
        2. 15.3.2. Use Schemas with XML Maps
        3. 15.3.3. Export XML Data Through XML Maps
        4. 15.3.4. Approaches to Using XML Maps
          1. 15.3.4.1. Avoid lists of lists
          2. 15.3.4.2. Avoid denormalized data
          3. 15.3.4.3. Create an XML schema
          4. 15.3.4.4. Include all nodes if exporting
          5. 15.3.4.5. Other things to avoid
        5. 15.3.5. Respond to XML Events
      4. 15.4. Program with XML Maps
        1. 15.4.1. Add or Delete XML Maps
        2. 15.4.2. Export and Import XML
        3. 15.4.3. Refresh, Change, or Clear the Data Binding
        4. 15.4.4. View the Schema
      5. 15.5. XmlMap and XmlMaps Members
        1. xmlmaps.Add(Schema, [RootElementName])
        2. xmlmap.AdjustColumnWidth [= setting]
        3. xmlmap.AppendOnImport [= setting]
        4. xmlmap.DataBinding
        5. xmlmap.Delete
        6. xmlmap.Export(Url, [Overwrite])
        7. xmlmap.ExportXml(Data)
        8. xmlmap.Import(Url, [Overwrite])
        9. xmlmap.ImportXml(Data, [Overwrite])
        10. xmlmap.IsExportable
        11. xmlmap.PreserveColumnFilter [= setting]
        12. xmlmap.PreserveNumberFormatting [= setting]
        13. xmlmap.RootElementName
        14. xmlmap.RootElementNamespace
        15. xmlmap.SaveDataSourceDefinition [= setting]
        16. xmlmap.Schemas
        17. xmlmap.ShowImportExportValidationErrors [= setting]
      6. 15.6. XmlDataBinding Members
        1. xmldatabinding.ClearSettings
        2. xmldatabinding.LoadSettings(Url)
        3. xmldatabinding.Refresh
        4. xmldatabinding.SourceUrl
      7. 15.7. XmlNamespace and XmlNamespaces Members
        1. xmlnamespaces.InstallManifest(Path, [InstallForAllUsers])
        2. xmlnamespace.Prefix
        3. xmlnamespace.Uri
        4. xmlnamespaces.Value
      8. 15.8. XmlSchema and XmlSchemas Members
        1. xmlschema.Namespace
        2. xmlschema.XML
      9. 15.9. Get an XML Map from a List or Range
        1. 15.9.1. Map XML to a List Column
        2. 15.9.2. Remove a Mapping
      10. 15.10. XPath Members
        1. xpath.Clear
        2. xpath.Map
        3. xpath.Repeating
        4. xpath.SetValue(Map, XPath, [SelectionNamespace], [Repeating])
        5. xpath.Value
      11. 15.11. Resources
    10. 16. Charting
      1. 16.1. Navigate Chart Objects
      2. 16.2. Create Charts Quickly
      3. 16.3. Embed Charts
      4. 16.4. Create More Complex Charts
      5. 16.5. Choose Chart Type
      6. 16.6. Create Combo Charts
      7. 16.7. Add Titles and Labels
      8. 16.8. Plot a Series
      9. 16.9. Respond to Chart Events
      10. 16.10. Chart and Charts Members
        1. chart.Add([Before], [After], [Count])
        2. chart.ApplyCustomType(ChartType, [TypeName])
        3. chart.ApplyDataLabels([Type], [LegendKey], [AutoText], [HasLeaderLines], [ShowSeriesName], [ShowCategoryName], [ShowValues], [ShowPercentage], [ShowBubbleSize], [Separator])
        4. chart.Area3DGroup
        5. chart.AreaGroups([Index])
        6. chart.AutoFormat(Gallery, [Format])
        7. chart.AutoScaling [= setting]
        8. chart.Axes([Type], [AxisGroup])
        9. chart.Bar3DGroup
        10. chart.BarGroups([Index])
        11. chart.BarShape [= xlBarShape]
        12. chart.ChartArea
        13. chart.ChartGroups([Index])
        14. chart.ChartObjects([Index])
        15. chart.ChartTitle
        16. chart.ChartType [= xlChartType]
        17. chart.ChartWizard([Source], [Gallery], [Format], [PlotBy], [CategoryLabels], [SeriesLabels], [HasLegend], [Title], [CategoryTitle], [ValueTitle], [ExtraTitle])
        18. chart.CodeName
        19. chart.Column3DGroup
        20. chart.ColumnGroups([Index])
        21. chart.CopyPicture([Appearance], [Format], [Size])
        22. chart.Corners
        23. chart.CreatePublisher([Edition], [Appearance], [Size], [ContainsPICT], [ContainsBIFF], [ContainsRTF], [ContainsVALU])
        24. chart.DataTable
        25. chart.DepthPercent [= setting]
        26. chart.Deselect( )
        27. chart.DisplayBlanksAs [= xlDisplayBlanksAs]
        28. chart.DoughnutGroups([Index])
        29. chart.Elevation [= setting]
        30. chart.Floor
        31. chart.GapDepth [= setting]
        32. chart.GetChartElement(x, y, ElementID, Arg1, Arg2)
        33. chart.HasAxis(xlAxisGroup, xlAxisType) [= setting]
        34. chart.HasDataTable [= setting]
        35. chart.HasLegend [= setting]
        36. chart.HasPivotFields [= setting]
        37. chart.HasTitle [= setting]
        38. chart.HeightPercent [= setting]
        39. chart.Legend
        40. chart.Line3DGroup
        41. chart.LineGroups([Index])
        42. chart.Location(Where, [Name])
        43. chart.Perspective [= setting]
        44. chart.Pie3DGroup
        45. chart.PieGroups([Index])
        46. chart.PivotLayout
        47. chart.PlotArea
        48. chart.PlotBy [= xlRowCol]
        49. chart.PlotVisibleOnly [= setting]
        50. chart.Refresh( )
        51. chart.RightAngleAxes [= setting]
        52. chart.Rotation [= setting]
        53. chart.Select([Replace])
        54. chart.SeriesCollection([Index])
        55. chart.SetBackgroundPicture(Filename)
        56. chart.SetSourceData(Source, [PlotBy])
        57. chart.ShowWindow [= setting]
        58. chart.SizeWithWindow [= setting]
        59. chart.SurfaceGroup
        60. chart.Walls
        61. chart.WallsAndGridlines2D [= setting]
        62. chart.XYGroups([Index])
      11. 16.11. ChartObject and ChartObjects Members
        1. chartobjects.Add(Left, Top, Width, Height)
        2. chartobject.BottomRightCell
        3. chartobject.Chart
        4. chartobjects.RoundedCorners [= setting]
        5. chartobject.TopLeftCell
      12. 16.12. ChartGroup and ChartGroups Members
        1. chartgroup.BubbleScale [= setting]
        2. chartgroup.DoughnutHoleSize [= setting]
        3. chartgroup.DownBars
        4. chartgroup.DropLines
        5. chartgroup.FirstSliceAngle [= setting]
        6. chartgroup.GapWidth [= setting]
        7. chartgroup.Has3DShading [= setting]
        8. chartgroup.HasDropLines [= setting]
        9. chartgroup.HasHiLoLines [= setting]
        10. chartgroup.HasRadarAxisLabels [= setting]
        11. chartgroup.HasSeriesLines [= setting]
        12. chartgroup.HasUpDownBars [= setting]
        13. chartgroup.HiLoLines
        14. chartgroup.Overlap [= setting]
        15. chartgroup.RadarAxisLabels
        16. chartgroup.SecondPlotSize [= setting]
        17. chartgroup.SeriesLines
        18. chartgroup.ShowNegativeBubbles [= setting]
        19. chartgroup.SizeRepresents [= xlSizeRepresents]
        20. chartgroup.SplitType [= xlSplitType]
        21. chartgroup.SplitValue [= setting]
        22. chartgroup.UpBars
        23. chartgroup.VaryByCategories [= setting]
      13. 16.13. SeriesLines Members
      14. 16.14. Axes and Axis Members
        1. axis.AxisBetweenCategories [= setting]
        2. axis.AxisGroup
        3. axis.AxisTitle
        4. axis.BaseUnit [= setting]
        5. axis.BaseUnitIsAuto [= setting]
        6. axis.CategoryNames [= setting]
        7. axis.CategoryType [= xlCategoryType]
        8. axis.Crosses [= xlAxisCrosses]
        9. axis.CrossesAt [= setting]
        10. axis.DisplayUnit [= xlDisplayUnit]
        11. axis.DisplayUnitCustom [= setting]
        12. axis.DisplayUnitLabel
        13. axis.HasDisplayUnitLabel [= setting]
        14. axis.HasMajorGridlines [= setting]
        15. axis.HasMinorGridlines [= setting]
        16. axis.HasTitle [= setting]
        17. axes.[Item](Type, [AxisGroup])
        18. axis.MajorGridlines
        19. axis.MajorTickMark [= xlTickMark]
        20. axis.MajorUnit [= setting]
        21. axis.MajorUnitIsAuto [= setting]
        22. axis.MajorUnitScale [= xlTimeUnit]
        23. axis.MaximumScale [= setting]
        24. axis.MaximumScaleIsAuto [= setting]
        25. axis.MinimumScale [= setting]
        26. axis.MinimumScaleIsAuto [= setting]
        27. axis.MinorGridlines
        28. axis.MinorTickMark [= xlTickMark]
        29. axis.MinorUnit [= setting]
        30. axis.MinorUnitIsAuto [= setting]
        31. axis.MinorUnitScale [= xlTimeUnit]
        32. axis.ReversePlotOrder [= setting]
        33. axis.ScaleType [= xlScaleType]
        34. axis.TickLabelPosition [= xlTickLabelPosition]
        35. axis.TickLabels
        36. axis.TickLabelSpacing [= setting]
        37. axis.TickMarkSpacing [= setting]
        38. axis.Type
      15. 16.15. DataTable Members
      16. 16.16. Series and SeriesCollection Members
        1. seriescollection.Add(Source, [Rowcol], [SeriesLabels], [CategoryLabels], [Replace])
        2. series.ApplyCustomType(ChartType)
        3. series.ApplyDataLabels([Type], [LegendKey], [AutoText], [HasLeaderLines], [ShowSeriesName], [ShowCategoryName], [ShowValues], [ShowPercentage], [ShowBubbleSize], [Separator])
        4. series.ApplyPictToEnd [= setting]
        5. series.ApplyPictToFront [= setting]
        6. series.ApplyPictToSides [= setting]
        7. series.AxisGroup [= xlAxisGroup]
        8. series.ChartType [= xlChartType]
        9. series.ClearFormats( )
        10. series.DataLabels([Index])
        11. series.ErrorBar(Direction, Include, Type, [Amount], [MinusValues])
        12. series.ErrorBars
        13. series.Explosion [= setting]
        14. seriescollection.Extend(Source, [Rowcol], [CategoryLabels])
        15. series.Fill
        16. series.Formula [= setting]
        17. End Function series.FormulaLocal [= setting]
        18. series.FormulaR1C1 [= setting]
        19. series.FormulaR1C1Local [= setting]
        20. series.Has3DEffect [= setting]
        21. series.HasDataLabels [= setting]
        22. series.HasErrorBars [= setting]
        23. series.HasLeaderLines [= setting]
        24. series.InvertIfNegative [= setting]
        25. series.LeaderLines
        26. series.MarkerBackgroundColor [= setting]
        27. series.MarkerBackgroundColorIndex [= setting]
        28. series.MarkerForegroundColor [= setting]
        29. series.MarkerForegroundColorIndex [= setting]
        30. series.MarkerSize [= setting]
        31. series.MarkerStyle [= xlMarkerStyle]
        32. seriescollection.NewSeries( )
        33. seriescollection.Paste([Rowcol], [SeriesLabels], [CategoryLabels], [Replace], [NewSeries])
        34. series.PictureType [= xlChartPictureType]
        35. series.PictureUnit [= setting]
        36. series.PlotOrder [= setting]
        37. series.Points([Index])
        38. series.Smooth [= setting]
        39. series.Trendlines([Index])
        40. series.Values [= setting]
        41. series.XValues [= setting]
      17. 16.17. Point and Points Members
        1. point.SecondaryPlot [= setting]
    11. 17. Formatting Charts
      1. 17.1. Format Titles and Labels
      2. 17.2. Change Backgrounds and Fonts
      3. 17.3. Add Trendlines
      4. 17.4. Add Series Lines and Bars
      5. 17.5. ChartTitle, AxisTitle, and DisplayUnitLabel Members
        1. title.Caption [= setting]
        2. title.Characters
        3. title.Fill
        4. title.Font
        5. title.HorizontalAlignment [= setting]
        6. title.Orientation [= setting]
        7. title.ReadingOrder [= setting]
        8. title.VerticalAlignment [= setting]
      6. 17.6. DataLabel and DataLabels Members
        1. datalabel AutoText [= setting]
        2. datalabels.NumberFormat [= setting]
        3. datalabel.NumberFormatLinked [= setting]
        4. datalabel.NumberFormatLocal [= setting]
        5. datalabel.Position [= xlDataLabelPosition]
        6. datalabel.Separator [= setting]
        7. datalabel.ShowBubbleSize [= setting]
        8. datalabel.ShowCategoryName [= setting]
        9. datalabel.ShowLegendKey [= setting]
        10. datalabel.ShowPercentage [= setting]
        11. datalabel.ShowSeriesName [= setting]
        12. datalabel.ShowValue [= setting]
      7. 17.7. LeaderLines Members
      8. 17.8. ChartArea Members
        1. chartarea.AutoScaleFont [= setting]
        2. chartarea.Clear( )
        3. chartarea.ClearContents( )
        4. chartarea.ClearFormats( )
        5. chartarea.Fill
        6. chartarea.Font
        7. chartarea.Interior
        8. chartarea.Shadow [= setting]
      9. 17.9. ChartFillFormat Members
        1. chartfillformat.BackColor
        2. chartfillformat.ForeColor
        3. chartfillformat.GradientColorType
        4. chartfillformat.GradientDegree
        5. chartfillformat.GradientStyle
        6. chartfillformat.GradientVariant
        7. chartfillformat.OneColorGradient(Style, Variant, Degree)
        8. chartfillformat.Pattern
        9. chartfillformat.Patterned(Pattern)
        10. chartfillformat.PresetGradient(Style, Variant, PresetGradientType)
        11. chartfillformat.PresetGradientType
        12. chartfillformat.PresetTexture
        13. chartfillformat.PresetTextured(PresetTexture)
        14. chartfillformat.Solid( )
        15. chartfillformat.TextureName
        16. chartfillformat.TextureType
        17. chartfillformat.TwoColorGradient(Style, Variant)
        18. chartfillformat.UserPicture(PictureFile)
        19. chartfillformat.UserTextured(TextureFile)
      10. 17.10. ChartColorFormat Members
      11. 17.11. DropLines and HiLoLines Members
      12. 17.12. DownBars and UpBars Members
      13. 17.13. ErrorBars Members
        1. errorbars.ClearFormats( )
        2. errorbars.EndStyle [=xlEndStyleCap]
      14. 17.14. Legend Members
        1. legend.LegendEntries([Index])
        2. legend.Position [= xlLegendPosition]
      15. 17.15. LegendEntry and LegendEntries Members
        1. legendentry.LegendKey
      16. 17.16. LegendKey Members
      17. 17.17. Gridlines Members
      18. 17.18. TickLabels Members
        1. ticklabels.Alignment [= setting]
        2. ticklabels.Depth
        3. ticklabels.Font
        4. ticklabels.NumberFormat [= setting]
        5. ticklabels.NumberFormatLinked [= setting]
        6. ticklabels.NumberFormatLocal [= setting]
        7. ticklabels.Offset [= setting]
        8. ticklabels.Orientation [= xlTickLabelOrientation]
        9. ticklabels.ReadingOrder [= setting]
      19. 17.19. Trendline and Trendlines Members
        1. trendlines.Add([Type], [Order], [Period], [Forward], [Backward], [Intercept], [DisplayEquation], [DisplayRSquared], [Name])
        2. trendline.Backward [= setting]
        3. trendline.ClearFormats( )
        4. trendline.DataLabel
        5. trendline.DisplayEquation [= setting]
        6. trendline.DisplayRSquared [= setting]
        7. trendline.Forward [= setting]
        8. trendline.Intercept [= setting]
        9. trendline.InterceptIsAuto [= setting]
        10. trendline.Name [= setting]
        11. trendline.NameIsAuto [= setting]
        12. trendline.Order [= setting]
        13. trendline.Period [= setting]
      20. 17.20. PlotArea Members
        1. plotarea.ClearFormats( )
        2. plotarea.Fill
        3. plotarea.InsideHeight
        4. plotarea.InsideLeft
        5. plotarea.InsideTop
        6. plotarea.InsideWidth
      21. 17.21. Floor Members
      22. 17.22. Walls Members
      23. 17.23. Corners Members
    12. 18. Drawing Graphics
      1. 18.1. Draw in Excel
      2. 18.2. Create Diagrams
      3. 18.3. Program with Drawing Objects
        1. 18.3.1. Draw Simple Shapes
        2. 18.3.2. Add Text
        3. 18.3.3. Connect Shapes
        4. 18.3.4. Insert Pictures
        5. 18.3.5. Insert Other Objects
        6. 18.3.6. Group Shapes
      4. 18.4. Program Diagrams
      5. 18.5. Shape, ShapeRange, and Shapes Members
        1. shapes.AddCallout(Type, Left, Top, Width, Height)
        2. shapes.AddConnector(Type, BeginX, BeginY, EndX, EndY)
        3. shapes.AddCurve(SafeArrayOfPoints)
        4. shapes.AddLabel(Orientation, Left, Top, Width, Height)
        5. shapes.AddLine(BeginX, BeginY, EndX, EndY)
        6. shapes.AddPicture(Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height)
        7. shapes.AddPolyline(SafeArrayOfPoints)
        8. shapes.AddShape(Type, Left, Top, Width, Height)
        9. shapes.AddTextbox(Orientation, Left, Top, Width, Height)
        10. shapes.AddTextEffect(PresetTextEffect, Text, FontName, FontSize, FontBold, FontItalic, Left, Top)
        11. shape.Adjustments
        12. shaperange.Align(AlignCmd, RelativeTo)
        13. shape.AlternativeText [= setting]
        14. shape.Apply( )
        15. shape.AutoShapeType [= msoAutoShapeType]
        16. shape.BlackWhiteMode [= msoBlackWhiteMode]
        17. shapes.BuildFreeform(EditingType, X1, Y1)
        18. shape.Callout
        19. shape.ConnectionSiteCount
        20. shape.Connector
        21. shape.ConnectorFormat
        22. shape.ControlFormat
        23. shaperange.Distribute(DistributeCmd, RelativeTo)
        24. shape.Duplicate( )
        25. shape.Fill
        26. shape.Flip(FlipCmd)
        27. shape.FormControlType
        28. shaperange.Group( )
        29. shape.GroupItems
        30. shape.HorizontalFlip
        31. shape.Hyperlink
        32. shape.ID
        33. shape.IncrementLeft(Increment)
        34. shape.IncrementRotation(Increment)
        35. shape.IncrementTop(Increment)
        36. shape.Line
        37. shape.LinkFormat
        38. shape.LockAspectRatio [= setting]
        39. shape.Locked [= setting]
        40. shape.ParentGroup
        41. shape.PickUp( )
        42. shape.PictureFormat
        43. shape.Placement [= xlPlacement]
        44. shapes.Range(Index)
        45. shaperange.Regroup( )
        46. shape.RerouteConnections( )
        47. shape.Rotation [= setting]
        48. shapes.SelectAll( )
        49. shape.SetShapesDefaultProperties( )
        50. shape.Shadow
        51. shape.TextEffect
        52. shape.TextFrame
        53. shape.ThreeD
        54. shape.Type
        55. shape.Ungroup( )
        56. shape.VerticalFlip
        57. shape.Vertices
      6. 18.6. Adjustments Members
      7. 18.7. CalloutFormat Members
        1. callout.Accent [= setting]
        2. callout.Angle [= msoCalloutAngleType]
        3. callout.AutoAttach [= setting]
        4. callout.AutoLength
        5. callout.AutomaticLength( )
        6. callout.Border [= setting]
        7. callout.CustomDrop(Drop)
        8. callout.CustomLength(Length)
        9. callout.Drop
        10. callout.DropType
        11. callout.Gap [= setting]
        12. callout.Length
        13. callout.PresetDrop(DropType)
        14. callout.Type [= msoCalloutType]
      8. 18.8. ColorFormat Members
        1. colorformat.TintAndShade [= setting]
      9. 18.9. ConnectorFormat Members
        1. connectorformat.BeginConnect(ConnectedShape, ConnectionSite)
        2. connectorformat.BeginConnected
        3. connectorformat.BeginConnectedShape
        4. connectorformat.BeginConnectionSite
        5. connectorformat.BeginDisconnect( )
        6. connectorformat.EndConnect(ConnectedShape, ConnectionSite)
        7. connectorformat.EndConnected
        8. connectorformat.EndConnectedShape
        9. connectorformat.EndConnectionSite
        10. connectorformat.EndDisconnect( )
        11. connectorformat.Type [= msoConnectorType]
      10. 18.10. ControlFormat Members
      11. 18.11. FillFormat Members
        1. fillformat.BackColor
        2. fillformat.ForeColor
        3. fillformat.Transparency [= setting]
      12. 18.12. FreeFormBuilder
        1. freeformbuilder.AddNodes(SegmentType, EditingType, X1, Y1, [X2], [Y2], [X3], [Y3])
        2. freeformbuilder.ConvertToShape( )
      13. 18.13. GroupShapes Members
      14. 18.14. LineFormat Members
      15. 18.15. LinkFormat Members
      16. 18.16. PictureFormat Members
        1. pictureformat.Brightness [= setting]
        2. pictureformat.ColorType [= msoPictureColorType]
        3. pictureformat.Contrast [= setting]
        4. pictureformat.CropBottom [= setting]
        5. pictureformat.CropLeft [= setting]
        6. pictureformat.CropRight [= setting]
        7. pictureformat.CropTop [= setting]
        8. pictureformat.IncrementBrightness(Increment)
        9. pictureformat.IncrementContrast(Increment)
        10. pictureformat.TransparencyColor [= setting]
        11. pictureformat.TransparentBackground [= setting]
      17. 18.17. ShadowFormat
      18. 18.18. ShapeNode and ShapeNodes Members
      19. 18.19. TextFrame
        1. textframe.AutoMargins [= setting]
        2. textframe.AutoSize [= setting]
        3. textframe.Characters([Start], [Length])
        4. textframe.HorizontalAlignment [= xlHAlign]
        5. textframe.MarginBottom [= setting]
        6. textframe.MarginLeft [= setting]
        7. textframe.MarginRight [= setting]
        8. textframe.MarginTop [= setting]
        9. textframe.Orientation [= msoTextOrientation]
        10. textframe.VerticalAlignment [= xlVAlign]
      20. 18.20. TextEffectFormat
        1. shape.Alignment [= msoTextEffectAlignment]
        2. shape.FontBold [= setting]
        3. shape.FontItalic [= setting]
        4. shape.FontName [= setting]
        5. shape.FontSize [= setting]
        6. shape.KernedPairs [= setting]
        7. shape.NormalizedHeight [= setting]
        8. shape.PresetShape [= msoPresetTextEffectShape]
        9. shape.PresetTextEffect [= msoPresetTextEffect]
        10. shape.RotatedChars [= setting]
        11. shape.Text [= setting]
        12. shape.ToggleVerticalText( )
        13. shape.Tracking [= setting]
      21. 18.21. ThreeDFormat
    13. 19. Adding Menus and Toolbars
      1. 19.1. About Excel Menus
      2. 19.2. Build a Top-Level Menu
        1. 19.2.1. Change Existing Menus
        2. 19.2.2. Assign Accelerator and Shortcut Keys
        3. 19.2.3. Save and Distribute Menus
      3. 19.3. Create a Menu in Code
        1. 19.3.1. Remove the Menu on Close
        2. 19.3.2. Change an Existing Menu
        3. 19.3.3. Reset an Existing Menu
      4. 19.4. Build Context Menus
        1. 19.4.1. Change Context Menu Items
        2. 19.4.2. Restore Context Menus
        3. 19.4.3. Create New Context Menus
      5. 19.5. Build a Toolbar
        1. 19.5.1. Create Menus Using Toolbars
        2. 19.5.2. Save and Distribute Toolbars
      6. 19.6. Create Toolbars in Code
        1. 19.6.1. Add Edit Controls to Toolbars
        2. 19.6.2. Delete Toolbars
      7. 19.7. CommandBar and CommandBars Members
        1. commandbars.ActionControl
        2. commandbars.ActiveMenuBar
        3. commandbars.AdaptiveMenus [= setting]
        4. commandbars.Add([Name], [Position], [MenuBar], [Temporary])
        5. commandbar.BuiltIn
        6. commandbar.Controls
        7. commandbar.Delete( )
        8. commandbars.DisableAskAQuestionDropdown [= setting]
        9. commandbars.DisableCustomize [= setting]
        10. commandbars.DisplayFonts [= setting]
        11. commandbar.DisplayKeysInTooltips [= setting]
        12. commandbars.DisplayTooltips [= setting]
        13. commandbar.Enabled [= setting]
        14. commandbar.FindControl([Type], [Id], [Tag], [Visible], [Recursive])
        15. commandbars.FindControls([Type], [Id], [Tag], [Visible])
        16. commandbar.Id
        17. commandbars.LargeButtons [= setting]
        18. commandbars.MenuAnimationStyle [= msoMenuAnimation]
        19. commandbar.Name [= setting]
        20. commandbar.NameLocal [= setting]
        21. commandbar.Position [= msoBarPosition]
        22. commandbar.Protection [= msoBarProtection]
        23. CommandBars.ReleaseFocus( )
        24. commandbar.Reset( )
        25. commandbar.RowIndex [= msoBarRow]
        26. commandbar.ShowPopup([x], [y])
        27. commandbar.Type
      8. 19.8. CommandBarControl and CommandBarControls Members
        1. commandbarcontrols.Add([Type], [Id], [Parameter], [Before], [Temporary])
        2. commandbarcontrol.BeginGroup [= setting]
        3. commandbarcontrol.BuiltIn
        4. commandbarcontrol.Caption [= setting]
        5. commandbarcontrol.Copy([Bar], [Before])
        6. commandbarcontrol.Delete([Temporary])
        7. commandbarcontrol.DescriptionText [= setting]
        8. commandbarcontrol.Enabled [= setting]
        9. commandbarcontrol.Execute( )
        10. commandbarcontrol.HelpContextId [= setting]
        11. commandbarcontrol.HelpFile [= setting]
        12. commandbarcontrol.Id
        13. commandbarcontrol.IsPriorityDropped
        14. commandbarcontrol.Move([Bar], [Before])
        15. commandbarcontrol.OLEUsage [= msoControlOLEUsage]
        16. commandbarcontrol.OnAction [= setting]
        17. commandbarcontrol.Parameter [= setting]
        18. commandbarcontrol.Priority [= setting]
        19. commandbarcontrol.Reset( )
        20. commandbarcontrol.SetFocus( )
        21. commandbarcontrol.Tag [= setting]
        22. commandbarcontrol.TooltipText [= setting]
        23. commandbarcontrol.Type
      9. 19.9. CommandBarButton Members
        1. commandbarbutton.CopyFace( )
        2. commandbarbutton.FaceId [= setting]
        3. commandbarbutton.HyperlinkType [= msoCommandBarButtonHyperlinkType]
        4. commandbarbutton.Mask
        5. commandbarbutton.PasteFace( )
        6. commandbarbutton.Picture
        7. commandbarbutton.ShortcutText [= setting]
        8. commandbarbutton.State [= msoButtonState]
        9. commandbarbutton.Style [= msoButtonStyle]
      10. 19.10. CommandBarComboBox Members
        1. commandbarcombobox.AddItem(Text, [Index])
        2. commandbarcombobox.Clear( )
        3. commandbarcombobox.DropDownLines [= setting]
        4. commandbarcombobox.DropDownWidth [= setting]
        5. commandbarcombobox.List(Index)
        6. commandbarcombobox.ListCount
        7. commandbarcombobox.ListHeaderCount [= setting]
        8. commandbarcombobox.ListIndex [= setting]
        9. commandbarcombobox.RemoveItem(Index)
        10. commandbarcombobox.Style [= msoComboStyle]
        11. commandbarcombobox.Text [= setting]
      11. 19.11. CommandBarPopup Members
        1. commandbarpopup.Controls
        2. commandbarpopup.OLEMenuGroup [= msoOLEMenuGroup]
    14. 20. Building Dialog Boxes
      1. 20.1. Types of Dialogs
      2. 20.2. Create Data-Entry Forms
        1. 20.2.1. Advanced Validation
        2. 20.2.2. Data Forms from Code
      3. 20.3. Design Your Own Forms
        1. 20.3.1. Respond to Form Events
        2. 20.3.2. Show a Form
        3. 20.3.3. Separate Work Code from UI Code
        4. 20.3.4. Enable and Disable Controls
        5. 20.3.5. Create Tabbed Dialogs
        6. 20.3.6. Provide Keyboard Access to Controls
        7. 20.3.7. Choose the Right Control
      4. 20.4. Use Controls on Worksheets
        1. 20.4.1. Add a Simple Button
        2. 20.4.2. Use Controls from the Worksheet Class
        3. 20.4.3. Controls on a Worksheet Versus Controls on a Form
      5. 20.5. UserForm and Frame Members
        1. form.ActiveControl
        2. form.BackColor [= rgb]
        3. form.BorderColor [= rgb]
        4. form.BorderStyle [= fmBorderStyle]
        5. form.CanPaste
        6. form.CanRedo
        7. form.CanUndo
        8. form.Caption [= setting]
        9. form.Controls
        10. form.Copy( )
        11. form.Cut( )
        12. form.Cycle [= fmCycle]
        13. form.DrawBuffer [= setting]
        14. form.Enabled [= setting]
        15. form.Font [= setting]
        16. form.ForeColor [= rgb]
        17. form.InsideHeight
        18. form.InsideWidth
        19. form.KeepScrollBarsVisible [= fmScrollBars]
        20. form.MouseIcon [= setting]
        21. form.MousePointer [= fmMousePointer]
        22. form.Paste( )
        23. form.Picture [= setting]
        24. form.PictureAlignment [= fmPictureAlignment]
        25. form.PictureSizeMode [= fmPictureSizeMode]
        26. form.PictureTiling [= setting]
        27. form.PrintForm
        28. form.RedoAction( )
        29. form.Repaint( )
        30. form.Scroll([ActionX] [, ActionY])
        31. form.ScrollBars [= fmScrollBars]
        32. form.ScrollHeight [= setting]
        33. form.ScrollLeft [= setting]
        34. form.ScrollTop [= setting]
        35. form.ScrollWidth [= setting]
        36. form.SetDefaultTabOrder( )
        37. form.SpecialEffect [= fmButtonEffect]
        38. form.UndoAction( )
        39. form.VerticalScrollBarSide [= fmVerticalScrollbarSide]
        40. form.Zoom [= setting]
      6. 20.6. Control and Controls Members
        1. controls.Add(ProgID [, Name] [, Visible])
        2. control.Cancel [= setting]
        3. controls .Clear( )
        4. control.ControlSource [= setting]
        5. control.ControlTipText [= setting]
        6. control.Default [= setting]
        7. control.LayoutEffect
        8. controls.Move ([Left ][, Top ][, Width ][, Height ][, Layout])
        9. control.Object
        10. control.OldHeight
        11. control.OldLeft
        12. control.OldTop
        13. control.OldWidth
        14. control.Remove(Index)
        15. control.RowSource [= setting]
        16. control.SetFocus( )
        17. control.TabIndex [= setting]
        18. control.TabStop [= setting]
        19. control.Tag [= setting]
        20. control.ZOrder([zPosition])
      7. 20.7. Font Members
      8. 20.8. CheckBox, OptionButton, ToggleButton Members
        1. control.AutoSize [= setting]
        2. control.BackStyle [= fmBackStyle]
        3. control.GroupName [= setting]
        4. control.Locked [= setting]
        5. control.TripleState [= setting]
      9. 20.9. ComboBox Members
        1. control.AddItem(Item[, Index])
        2. control.AutoTab [= setting]
        3. control.AutoWordSelect [= setting]
        4. control.BoundColumn [= setting]
        5. control.Clear( )
        6. control.Column([Column][, Row])
        7. control.ColumnCount [= setting]
        8. control.ColumnHeads [= setting]
        9. control.ColumnWidths [= setting]
        10. control.CurTargetX
        11. control.CurX [= setting]
        12. control.DragBehavior [= fmDragBehavior]
        13. control.DropButtonStyle [= fmDropButtonStyle]
        14. control.DropDown( )
        15. control.EnterFieldBehavior [= fmEnterFieldBehavior]
        16. control.HideSelection [= setting]
        17. control.IMEMode [= fmIMEMode]
        18. control.LineCount
        19. control.List([Row, Column]) [= setting]
        20. control.ListCount
        21. control.ListIndex [= setting]
        22. control.ListRows [= setting]
        23. control.ListStyle [= fmListStyle]
        24. control.ListWidth [= setting]
        25. control.MatchEntry [= fmMatchEntry]
        26. control.MatchFound
        27. control.MatchRequired [= setting]
        28. control.MaxLength [= setting]
        29. control.RemoveItem(Index)
        30. control.SelectionMargin [= setting]
        31. control.SelLength [= setting]
        32. control.SelStart [= setting]
        33. control.SelText [= setting]
        34. control.ShowDropButtonWhen [= fmShowDropButtonWhen]
        35. control.Style [= fmStyle]
        36. control.Text [= setting]
        37. control.TextAlign [= fmTextAlign]
        38. control.TextColumn [= setting]
        39. control.TextLength
        40. control.TopIndex [= setting]
      10. 20.10. CommandButton Members
        1. control.Picture [= setting]
        2. control.PicturePosition [= fmPicturePosition]
        3. control.TakeFocusOnClick [= setting]
      11. 20.11. Image Members
        1. control.Picture [= setting]
        2. control.PictureAlignment [= fmPictureAlignment]
        3. control.PictureSizeMode [= fmPictureSizeMode]
        4. control.PictureTiling [= setting]
      12. 20.12. Label Members
      13. 20.13. ListBox Members
        1. control.IntegralHeight [= setting]
        2. control.MultiSelect [= fmMultiSelect]
      14. 20.14. MultiPage Members
        1. control.MultiRow [= setting]
        2. control.Pages
        3. control.SelectedItem
        4. control.Style [= fmTabStyle]
        5. control.TabFixedHeight [= setting]
        6. control.TabFixedWidth [= setting]
        7. control.TabOrientation [= fmTabOrientation]
      15. 20.15. Page Members
        1. control.TransitionEffect [= fmTransitionEffect]
        2. control.TransitionPeriod [= setting]
      16. 20.16. ScrollBar and SpinButton Members
        1. control.LargeChange [= setting]
        2. control.Max [= setting]
        3. control.Min [= setting]
        4. control.Orientation [= fmOrientation]
        5. control.ProportionalThumb [= setting]
        6. control.SmallChange [= setting]
      17. 20.17. TabStrip Members
        1. control.ClientHeight
        2. control.ClientLeft
        3. control.ClientTop
        4. control.ClientWidth
        5. control.Tabs
      18. 20.18. TextBox and RefEdit Members
        1. control.CurLine [= setting]
        2. control.EnterKeyBehavior [= fmEnterFieldBehavior]
        3. control.IntegralHeight [= setting]
        4. control.LineCount
        5. control.MultiLine [= setting]
        6. control.PasswordChar [= setting]
        7. control.ScrollBars [= fmScrollBars]
        8. control.TabKeyBehavior [= setting]
        9. control.WordWrap [= setting]
    15. 21. Sending and Receiving Workbooks
      1. 21.1. Send Mail
      2. 21.2. Work with Mail Items
      3. 21.3. Collect Review Comments
      4. 21.4. Route Workbooks
      5. 21.5. Read Mail
      6. 21.6. MsoEnvelope Members
        1. mailenvelope.Introduction [= setting]
        2. mailenvelope.Item
      7. 21.7. MailItem Members
        1. mailitem.Attachments
        2. mailitem.BCC [= setting]
        3. mailitem.Body [= setting]
        4. mailitem.CC [= setting]
        5. mailitem.Close(SaveMode)
        6. mailitem.DeferredDeliveryTime [= setting]
        7. mailitem.DeleteAfterSubmit [= setting]
        8. mailitem.Display( )
        9. mailitem.ExpiryTime [= setting]
        10. mailitem.HTMLBody [= setting]
        11. mailitem.Importance [= setting]
        12. mailitem.PrintOut( )
        13. mailitem.ReadReceiptRequested [= setting]
        14. mailitem.Recipients
        15. mailitem.Save( )
        16. mailitem.SaveAs(Path, Type)
        17. mailitem.SaveSentMessageFolder [= setting]
        18. mailitem.Send( )
        19. mailitem.SenderEmailAddress
        20. mailitem.SenderName
        21. mailitem.Sensitivity [= olSensitivity]
        22. mailitem.Subject [= setting]
        23. mailitem.To [= setting]
      8. 21.8. RoutingSlip Members
        1. routingslip.Delivery [= xlRoutingSlipDelivery]
        2. routingslip.Message [= setting]
        3. routingslip.Recipients [= setting]
        4. routingslip.Reset( )
        5. routingslip.ReturnWhenDone [= setting]
        6. routingslip.Status
        7. routingslip.Subject [= setting]
        8. routingslip.TrackStatus [= setting]
  6. III. Extending Excel
    1. 22. Building Add-ins
      1. 22.1. Types of Add-ins
      2. 22.2. Code-Only Add-ins
        1. 22.2.1. Save Add-ins
        2. 22.2.2. Create a Test Workbook
        3. 22.2.3. Use the Add-in from Code
        4. 22.2.4. Change the Add-in
        5. 22.2.5. Programming Tips
      3. 22.3. Visual Add-ins
        1. 22.3.1. Add a Menu Item
        2. 22.3.2. Add a Toolbar
        3. 22.3.3. Respond to Application Events
      4. 22.4. Set Add-in Properties
      5. 22.5. Sign the Add-in
      6. 22.6. Distribute the Add-in
      7. 22.7. Work with Add-ins in Code
      8. 22.8. AddIn and AddIns Members
        1. addins.Add(Filename, [CopyFile])
        2. addin.Author
        3. addin.Comments
        4. addin.FullName
        5. addin.Installed [= setting]
        6. addin.Keywords
        7. addin.Path
        8. addin.Subject
        9. addin.Title
    2. 23. Integrating DLLs and COM
      1. 23.1. Use DLLs
        1. 23.1.1. Find the Right Function
        2. 23.1.2. Declare and Use DLL Functions
        3. 23.1.3. Use Flags and Constants
        4. 23.1.4. Work with Strings
        5. 23.1.5. Handle Exceptions
      2. 23.2. Use COM Applications
        1. 23.2.1. Program Other Office Applications
        2. 23.2.2. Integrate Word
        3. 23.2.3. Automate PowerPoint
        4. 23.2.4. Handle Exceptions
        5. 23.2.5. Get Help on Objects
    3. 24. Getting Data from the Web
      1. 24.1. Perform Web Queries
        1. 24.1.1. Modify a Web Query
        2. 24.1.2. Perform Periodic Updates
        3. 24.1.3. Trap QueryTable Events
        4. 24.1.4. Manage Web Queries
        5. 24.1.5. Limitations of Web Queries
      2. 24.2. QueryTable and QueryTables Web Query Members
        1. querytables.Add(Connection, Destination, [Sql])
        2. querytable.AdjustColumnWidth [= setting]
        3. querytable.BackgroundQuery [= setting]
        4. querytable.CancelRefresh
        5. querytable.Connection [= setting]
        6. querytable.Delete
        7. querytable.Destination
        8. querytable.EditWebPage [= setting]
        9. querytable.EnableEditing [= setting]
        10. querytable.EnableRefresh [= setting]
        11. querytable.FetchedRowOverflow
        12. querytable.FillAdjacentFormulas [= setting]
        13. querytable.PostText [= setting]
        14. querytable.PreserveFormatting [= setting]
        15. querytable.QueryType [= xlQueryType]
        16. querytable.Refresh([BackgroundQuery])
        17. querytable.Refreshing
        18. querytable.RefreshOnFileOpen [= setting]
        19. querytable.RefreshPeriod [= setting]
        20. querytable.RefreshStyle [= xlCellInsertionMode]
        21. querytable.ResetTimer
        22. querytable.ResultRange
        23. querytable.TablesOnlyFromHTML [= setting]
        24. querytable.WebConsecutiveDelimitersAsOne [= setting]
        25. querytable.WebDisableDateRecognition [= setting]
        26. querytable.WebDisableRedirections [= setting]
        27. querytable.WebFormatting [= setting]
        28. querytable.WebPreFormattedTextToColumns [= setting]
        29. querytable.WebSelectionType [= xlWebSelectionType]
        30. querytable.WebSingleBlockTextImport [= setting]
        31. querytable.WebTables [= setting]
      3. 24.3. Use Web Services
        1. 24.3.1. Use the Web Services Toolkit
        2. 24.3.2. Use Web Services Through XML
        3. 24.3.3. Call a Web Service Asynchronously
        4. 24.3.4. Reformat XML Results for Excel
      4. 24.4. Resources
    4. 25. Programming Excel with .NET
      1. 25.1. Approaches to Working with .NET
      2. 25.2. Create .NET Components for Excel
      3. 25.3. Use .NET Components in Excel
        1. 25.3.1. Respond to Errors and Events from .NET Objects
        2. 25.3.2. Debug .NET Components
        3. 25.3.3. Distribute .NET Components
      4. 25.4. Use Excel as a Component in .NET
        1. 25.4.1. Work with Excel Objects in .NET
        2. 25.4.2. Respond to Excel Events in .NET
        3. 25.4.3. Respond to Excel Exceptions in .NET
        4. 25.4.4. Distribute .NET Applications That Use Excel
      5. 25.5. Create Excel Applications in .NET
        1. 25.5.1. Set .NET Security Policies
        2. 25.5.2. Respond to Events in .NET Applications
        3. 25.5.3. Debug Excel .NET Applications
        4. 25.5.4. Display Forms
        5. 25.5.5. Distribute Excel .NET Applications
        6. 25.5.6. Migrate to .NET
          1. 25.5.6.1. Be explicit
          2. 25.5.6.2. Pass arguments by value
          3. 25.5.6.3. Collections start at zero
          4. 25.5.6.4. Data access is through ADO.NET
      6. 25.6. Resources
    5. 26. Exploring Security in Depth
      1. 26.1. Security Layers
      2. 26.2. Understand Windows Security
        1. 26.2.1. Set File Permissions in Windows XP
        2. 26.2.2. View Users and Groups in XP
      3. 26.3. Password-Protect and Encrypt Workbooks
      4. 26.4. Program with Passwords and Encryption
      5. 26.5. Workbook Password and Encryption Members
        1. workbook.HasPassword
        2. workbook.Password [= setting]
        3. workbook.PasswordEncryptionAlgorithm
        4. workbook.PasswordEncryptionFileProperties
        5. workbook.PasswordEncryptionKeyLength
        6. workbook.PasswordEncryptionProvider
        7. workbook.SetPasswordEncryptionOptions(PasswordEncryptionProvider, PasswordEncryptionAlgorithm, PasswordEncryptionKeyLength, PasswordEncryptionFileProperties)
        8. workbook.WritePassword [= setting]
        9. workbook.WriteReserved
        10. workbook.WriteReservedBy
      6. 26.6. Excel Password Security
      7. 26.7. Protect Items in a Workbook
      8. 26.8. Program with Protection
      9. 26.9. Workbook Protection Members
        1. workbook.Protect([Password], [Structure], [Windows])
        2. workbook.ProtectSharing([Filename], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [SharingPassword])
        3. workbook.ProtectStructure
        4. workbook.ProtectWindows
        5. workbook.Unprotect([Password])
        6. workbook.UnprotectSharing([SharingPassword])
      10. 26.10. Worksheet Protection Members
        1. worksheet.Protect([Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [AllowInsertingColumns], [AllowInsertingRows], [AllowInsertingHyperlinks], [AllowDeletingColumns], [AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables])
        2. worksheet.ProtectContents
        3. worksheet.ProtectDrawingObjects
        4. worksheet.Protection
        5. worksheet.ProtectionMode
        6. worksheet.ProtectScenarios
        7. worksheet.Unprotect([Password])
      11. 26.11. Chart Protection Members
        1. chart.Protect([Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly])
        2. chart.ProtectData [= setting]
        3. chart.ProtectGoalSeek [= setting]
        4. chart.ProtectSelection [= setting]
        5. chart.UnProtect([Password])
      12. 26.12. Protection Members
        1. protection.AllowDeletingColumns
        2. protection.AllowDeletingRows
        3. protection.AllowEditRanges
        4. protection.AllowFiltering
        5. protection.AllowFormattingCells
        6. protection.AllowFormattingColumns
        7. protection.AllowFormattingRows
        8. protection.AllowInsertingColumns
        9. protection.AllowInsertingHyperlinks
        10. protection.AllowInsertingRows
        11. protection.AllowSorting
        12. protection.AllowUsingPivotTables
      13. 26.13. AllowEditRange and AllowEditRanges Members
        1. alloweditranges.Add(Title, Range, [Password])
        2. alloweditrange.ChangePassword(Password)
        3. alloweditrange.Delete( )
        4. alloweditrange.Range
        5. alloweditrange.Title
        6. alloweditrange.Unprotect([Password])
        7. alloweditrange.Users
      14. 26.14. UserAccess and UserAccessList Members
        1. useraccesslist.Add(Name, AllowEdit)
        2. useraccess.AllowEdit [= setting]
        3. useraccess.Delete( )
        4. useraccesslist.DeleteAll( )
      15. 26.15. Set Workbook Permissions
      16. 26.16. Program with Permissions
      17. 26.17. Permission and UserPermission Members
        1. permission.Add(UserId, [Permission], [ExpirationDate])
        2. permission.ApplyPolicy(FileName)
        3. permission.DocumentAuthor [= setting]
        4. permission.Enabled [= setting]
        5. permission.EnableTrustedBrowser [= setting]
        6. userpermission.ExpirationDate [= setting]
        7. userpermission.Permission [= setting]
        8. permission.PermissionFromPolicy
        9. permission.PolicyDescription
        10. permission.PolicyName
        11. userpermission.Remove( )
        12. permission.RemoveAll( )
        13. permission.RequestPermissionURL [= setting]
        14. permission.StoreLicenses [= setting]
        15. userpermission.UserId
      18. 26.18. Add Digital Signatures
      19. 26.19. Set Macro Security
      20. 26.20. Set ActiveX Control Security
      21. 26.21. Distribute Security Settings
        1. 26.21.1. Change Security Settings
        2. 26.21.2. Distribute Certificates
      22. 26.22. Using the Anti-Virus API
      23. 26.23. Common Tasks
        1. 26.23.1. Get Rid of the Macro Security Warning
        2. 26.23.2. Prevent Someone from Running Any Macros
        3. 26.23.3. Make a File Truly Secure
        4. 26.23.4. Add a Trusted Publisher for a Group of Users
      24. 26.24. Resources
  7. IV. Appendixes
    1. A. Reference Tables
      1. A.1. Dialogs Collection Constants
      2. A.2. Common Programmatic IDs
    2. B. Version Compatibility
      1. B.1. Summary of Version Changes
      2. B.2. Macintosh Compatibility
  8. About the Authors
  9. Colophon
  10. Copyright

Product information

  • Title: Programming Excel with VBA and .NET
  • Author(s): Jeff Webb, Steve Saunders
  • Release date: April 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596007669