Programming Excel with VBA and .NET

Find the Right Object

The hardest part about Excel programming is finding the right object for the job. Excel’s object library is huge and not always easy to understand. One way to tackle that problem is to categorize the objects by task. The chapters later in this book take that approach, as shown by Table 4-4.

Table 4-4. How this book organizes Excel objects by task

Chapter	Description	Covers these objects
7, Controlling Excel	Control Excel’s general options and display and respond to application-level events	Application, AutoCorrect, AutoRecover, ErrorChecking, Windows, and Panes
8, Opening, Saving, and Sharing Workbooks	Access workbooks and their properties and respond to workbook events	Workbook and RecentFile
9, Working with Worksheets and Ranges	Perform general tasks on ranges of cells including inserting values, search and replace, and formatting and respond to worksheet events	Worksheet and Range
10, Linking and Embedding	Add comments, hyperlinks, and various OLE objects to worksheets	Comment, Hyperlink, OLEObject, Speech, and UsedObjects
11, Printing and Publishing	Create hardcopy and online output from workbooks	AutoFilter, Filter, HPageBreak, VPageBreak, PageSetup, Graphic, PublishObject, DefaultWebOptions, and WebOptions
12, Loading and Manipulating Data	Bring data into a workbook from a database or other data source	Parameter and QueryTable ADO objects: Command, Connection, Field, Parameter, and RecordSet DAO objects: Database, DbEngine, Document, QueryDef, and Recordset
13, Analyzing Data with Pivot Tables	Organize, sort, and filter data through pivot tables	CalculatedField, CalculatedMember, CubeField, PivotCache, PivotCell, PivotField, PivotFormula, PivotItem, PivotItemList, PivotLayout, and PivotTable
14, Sharing Data Using Lists	Use lists for data entry, filtering, sorting, and sharing data	ListObject, ListRow, ListColumn, and ListDataFormat
15, Working with XML	Import XML data into Excel and export data from workbooks in XML format	XmlMap, XmlDataBinding, XmlNamespace, XmlSchema, and XPath
16, Charting	Display numeric data graphically	Axis, Chart, ChartGroup, ChartObject, DataTable, Point, Series, and SeriesLines
17, Formatting Charts	Change low-level aspects of the chart	ChartArea, ChartColorFormat, ChartFillFormat, Corners, DataLabel, DownBars, DropLines, ErrorBars, Floor, Gridlines, HiLoLines, LeaderLines, Legend, LegendEntry, LegendKey, PlotArea, TickLabels, Trendline, Trendlines, UpBars, and Walls
18, Drawing Graphics	Create graphics on Excel worksheets	Adjustments, CalloutFormat, ColorFormat, ConnectorFormat, ControlFormat, FillFormat, FreeFormBuilder, GroupShapes, LineFormat, LinkFormat, PictureFormat, ShadowFormat, Shape, ShapeNode, ShapeRange, TextEffectFormat, TextFrame, and ThreeDFormat
19, Adding Menus and Toolbars	Add items to the Excel user interface	CommandBar, CommandBarButton, CommandBarComboBox, and CommandBarPopup
20, Building Dialog Boxes	Create forms and use controls in Excel	Forms 2.0 objects: UserForm, CheckBox, ComboBox, CommandButton, Control, Frame, Image, Label, ListBox, MultiPage, OptionButton, RefEdit, ScrollBar, SpinButton, TabStrip, and ToggleButton
21, Sending and Receiving Workbooks	Send mail from Excel	MsoEnvelope, MailItem, and RoutingSlip
22, Building Add-ins	Load and use add-ins as well as create and distribute new ones	AddIn
26, Exploring Security in Depth	Limit edits to sheets and ranges of cells	AllowEditRange, Protection, Permission, UserAccess, UserAccessList

To give you an overview of how the Excel object library is organized, the following sections break the Excel object library into parts and illustrate how the objects are organized graphically. These illustrations are similar to those found in online Help as shown in Figure 4-10 earlier but are a little more complete (and I think more accurate) than Excel’s Help.