Programming Excel With Vba And .net

by Webb, Jeff

• ISBN13: 9780596007669
• ISBN10: 0596007663
• Format: Paperback
• Copyright: 2006-04-30
• Publisher: Oreilly & Associates Inc

Summary

Built into all Microsoft Office applications, Visual Basic for Applications (VBA) enables power users to control almost all aspects of Excel. With VBA, they can manipulate user interface features, such as menus and toolbars, and work with custom user forms or dialog boxes. "Programming Excel" teaches everyone from beginners to IT professionals how to use Excel VBA by explaining basic concepts clearly and concisely in plain English, and it also provides a complete language reference organised by task. The book outlines programming practices, provides useful examples that demonstrate the most commonly performed tasks, and completely covers the entire Excel object library.

Author Biography

Jeff Webb is one of the original Visual Basic team members. He was intensely involved with Excel VBA and conceived the first Office Developer's Kit. Jeff also wrote the first book on Excel VBA, Using Excel Visual Basic for Applications, which has remained in print for an amazing 12 years. Now, he returns to his favorite subject with a completely new, comprehensive guide: Programming Excel with VBA and VB .NET.

Steve Saunders is a Microsoft veteren who helped pioneer the design of hypertext help systems that paved the way for the design of today's Web sites. He was lead designer for the award-winning Microsoft Access and Basic help systems, and was a charter member of the Visual Basic product team. Later, Steve wrote documentation for Access programmers and became an Access programmer himself, creating applications for his group at Microsoft and for a growing list of clients outside the company.

Businesses and non-profit organizations use Steve's affordable custom applications to track their critical information, including customers and orders, lodging reservations, real estate listings, donors and donations. Since 1996, the Methow Valley Sport Trails Association (MVSTA) has used his race management application, Racetracker, to produce accurate and timely results for its popular cross-country ski and running races.

Before his years at Microsoft, Steve was a reporter and editor for newspapers in Utah, Maine, and Massachusetts, and a technical writer in the database group at Digital Equipment Corp. He holds a master's degree in journalism from Boston University, a graduate certificate in technical writing from Northeastern University, and a bachelor's degree in technical writing and creative writing from Carnegie-Mellon University.

Currently a principal of Smiling Country, Steve enjoys combining his technical, design, and editorial expertise to help businesses succeed.

Table of Contents

Preface	xv
Part I. Learning VBA
Becoming an Excel Programmer	3	(32)
Why Program?	3	(1)
Record and Read Code	4	(4)
Change Recorded Code	8	(3)
Fix Misteakes	11	(9)
Start and Stop	20	(1)
View Results	21	(5)
Where's My Code?	26	(1)
Macros and Security	27	(3)
Write Bug-Free Code	30	(2)
Navigate Samples and Help	32	(2)
What You've Learned	34	(1)
Knowing the Basics	35	(40)
Parts of a Program	35	(1)
Classes and Modules	36	(4)
Procedures	40	(10)
Variables	50	(15)
Conditional Statements	65	(2)
Loops	67	(4)
Expressions	71	(1)
Exceptions	72	(2)
What You've Learned	74	(1)
Tasks in Visual Basic	75	(39)
Types of Tasks	75	(1)
Interact with Users	76	(3)
Do Math	79	(2)
Work with Text	81	(9)
Get Dates and Times	90	(2)
Read and Write Files	92	(8)
Check Results	100	(3)
Find Truth	103	(1)
Compare Bits	104	(2)
Run Other Applications	106	(6)
Control the Compiler	112	(1)
Not Covered Here	112	(1)
What You've Learned	113	(1)
Using Excel Objects	114	(42)
Objects and Their Members	114	(3)
Get Excel Objects	117	(3)
Get Objects from Collections	120	(3)
About Me and the Active Object	123	(3)
Find the Right Object	126	(10)
Common Members	136	(6)
Respond to Events in Excel	142	(5)
The Global Object	147	(1)
The WorksheetFunction Object	147	(8)
What You've Learned	155	(1)
Creating Your Own Objects	156	(20)
Modules Versus Classes	156	(3)
Add Methods	159	(1)
Create Properties	160	(4)
Define Enumerations	164	(1)
Raise Events	165	(3)
Collect Objects	168	(2)
Expose Objects	170	(2)
Destroy Objects	172	(2)
Things You Can't Do	174	(1)
What You've Learned	175	(1)
Writing Code for Use by Others	176	(23)
Types of Applications	176	(1)
The Development Process	177	(2)
Determine Requirements	179	(1)
Design	180	(1)
Implement and Test	180	(1)
Integrate	181	(2)
Test Platforms	183	(2)
Document	185	(3)
Deploy	188	(7)
What You've Learned	195	(1)
Resources	196	(3)
Part II. Excel Objects
Controlling Excel	199	(67)
Perform Tasks	199	(4)
Control Excel Options	203	(2)
Get References	205	(2)
Application Members	207	(43)
AutoCorrect Members	250	(2)
AutoRecover Members	252	(1)
ErrorChecking Members	253	(1)
SpellingOptions Members	253	(2)
Window and Windows Members	255	(9)
Pane and Panes Members	264	(2)
Opening, Saving, and Sharing Workbooks	266	(64)
Add, Open, Save, and Close	267	(9)
Share Workbooks	276	(3)
Program with Shared Workbooks	279	(3)
Program with Shared Workspaces	282	(6)
Respond to Actions	288	(4)
Workbook and Workbooks Members	292	(36)
RecentFile and RecentFiles Members	328	(2)
Working with Worksheets and Ranges	330	(39)
Work with Worksheet Objects	331	(1)
Worksheets and Worksheet Members	332	(8)
Sheets Members	340	(1)
Work with Outlines	341	(1)
Outline Members	342	(1)
Work with Ranges	343	(4)
Range Members	347	(19)
Work with Scenario Objects	366	(1)
Scenario and Scenarios Members	367	(1)
Resources	368	(1)
Linking and Embedding	369	(22)
Add Comments	369	(1)
Use Hyperlinks	370	(2)
Link and Embed Objects	372	(6)
Speak	378	(1)
Comment and Comments Members	379	(1)
Hyperlink and Hyperlinks Members	380	(3)
OleObject and OleObjects Members	383	(5)
OLEFormat Members	388	(1)
Speech Members	388	(1)
UsedObjects Members	389	(2)
Printing and Publishing	391	(30)
Print and Preview	391	(1)
Control Paging	392	(1)
Change Printer Settings	393	(2)
Filter Ranges	395	(2)
Save and Display Views	397	(2)
Publish to the Web	399	(3)
AutoFilter Members	402	(1)
Filter and Filters Members	403	(1)
CustomView and CustomViews Members	404	(1)
HPageBreak, HPageBreaks, VPageBreak, VPageBreaks Members	405	(1)
PageSetup Members	406	(6)
Graphic Members	412	(2)
PublishObject and PublishObjects Members	414	(2)
WebOptions and DefaultWebOptions Members	416	(5)
Loading and Manipulating Data	421	(37)
Working with Query Table Objects	422	(1)
QueryTable and QueryTables Members	423	(10)
Working with Parameter Objects	433	(1)
Parameter Members	434	(3)
Working with ADO and DAO	437	(1)
ADO Objects and Members	437	(13)
DAO Objects and Members	450	(1)
DAO.Database and DAO.Databases Members	450	(3)
DAO.Document and DAO.Documents Members	453	(1)
DAO.QueryDef and DAO.QueryDefs Members	453	(2)
DAO.Recordset and DAO.Recordsets Members	455	(3)
Analyzing Data with Pivot Tables	458	(62)
Quick Guide to Pivot Tables	458	(13)
Program Pivot Tables	471	(5)
PivotTable and PivotTables Members	476	(15)
PivotCache and PivotCaches Members	491	(8)
PivotField and PivotFields Members	499	(11)
CalculatedFields Members	510	(1)
CalculatedItems Members	511	(2)
PivotCell Members	513	(1)
PivotFormula and PivotFormulas Members	514	(1)
PivotItem and PivotItems Members	514	(1)
PivotItemList Members	515	(1)
PivotLayout Members	516	(1)
CubeField and CubeFields Members	517	(2)
CalculatedMember and CalculatedMembers Members	519	(1)
Sharing Data Using Lists	520	(33)
Use Lists	521	(8)
ListObject and ListObjects Members	529	(5)
ListRow and ListRows Members	534	(2)
ListColumn and ListColumns Members	536	(1)
ListDataFormat Members	537	(3)
Use the Lists Web Service	540	(8)
Lists Web Service Members	548	(4)
Resources	552	(1)
Working with XML	553	(45)
Understand XML	554	(1)
Save Workbooks as XML	555	(13)
Use XML Maps	568	(15)
Program with XML Maps	583	(2)
XmlMap and XmlMaps Members	585	(5)
XmlDataBinding Members	590	(1)
XmlNamespace and XmlNamespaces Members	591	(2)
XmlSchema and XmlSchemas Members	593	(1)
Get an XML Map from a List or Range	594	(1)
XPath Members	595	(2)
Resources	597	(1)
Charting	598	(59)
Navigate Chart Objects	598	(1)
Create Charts Quickly	598	(3)
Embed Charts	601	(1)
Create More Complex Charts	602	(2)
Choose Chart Type	604	(2)
Create Combo Charts	606	(1)
Add Titles and Labels	607	(2)
Plot a Series	609	(1)
Respond to Chart Events	610	(1)
Chart and Charts Members	611	(18)
ChartObject and ChartObjects Members	629	(1)
ChartGroup and ChartGroups Members	630	(4)
SeriesLines Members	634	(1)
Axes and Axis Members	635	(8)
DataTable Members	643	(1)
Series and SeriesCollection Members	644	(11)
Point and Points Members	655	(2)
Formatting Charts	657	(41)
Format Titles and Labels	657	(2)
Change Backgrounds and Fonts	659	(3)
Add Trendlines	662	(1)
Add Series Lines and Bars	663	(1)
ChartTitle, AxisTitle, and DisplayUnitLabel Members	664	(2)
DataLabel and DataLabels Members	666	(5)
LeaderLines Members	671	(1)
ChartArea Members	672	(2)
ChartFillFormat Members	674	(7)
ChartColorFormat Members	681	(1)
DropLines and HiLoLines Members	682	(1)
DownBars and UpBars Members	682	(1)
ErrorBars Members	683	(1)
Legend Members	684	(1)
LegendEntry and LegendEntries Members	685	(1)
LegendKey Members	686	(2)
Gridlines Members	688	(1)
TickLabels Members	688	(2)
Trendline and Trendlines Members	690	(3)
PlotArea Members	693	(3)
Floor Members	696	(1)
Walls Members	696	(1)
Corners Members	697	(1)
Drawing Graphics	698	(51)
Draw in Excel	698	(2)
Create Diagrams	700	(1)
Program with Drawing Objects	700	(7)
Program Diagrams	707	(2)
Shape, ShapeRange, and Shapes Members	709	(20)
Adjustments Members	729	(1)
CalloutFormat Members	729	(3)
ColorFormat Members	732	(1)
ConnectorFormat Members	732	(2)
ControlFormat Members	734	(1)
FillFormat Members	735	(1)
FreeFormBuilder	736	(1)
GroupShapes Members	737	(1)
LineFormat Members	738	(1)
LinkFormat Members	738	(1)
PictureFormat Members	739	(1)
ShadowFormat	740	(1)
ShapeNode and ShapeNodes Members	741	(1)
TextFrame	742	(3)
TextEffectFormat	745	(3)
ThreeDFormat	748	(1)
Adding Menus and Toolbars	749	(44)
About Excel Menus	749	(1)
Build a Top-Level Menu	750	(6)
Create a Menu in Code	756	(4)
Build Context Menus	760	(4)
Build a Toolbar	764	(3)
Create Toolbars in Code	767	(4)
CommandBar and CommandBars Members	771	(8)
CommandBarControl and CommandBarControls Members	779	(5)
CommandBarButton Members	784	(3)
CommandBarComboBox Members	787	(3)
CommandBarPopup Members	790	(3)
Building Dialog Boxes	793	(59)
Types of Dialogs	793	(1)
Create Data-Entry Forms	794	(4)
Design Your Own Forms	798	(15)
Use Controls on Worksheets	813	(5)
UserForm and Frame Members	818	(8)
Control and Controls Members	826	(5)
Font Members	831	(1)
CheckBox, OptionButton, ToggleButton Members	832	(1)
ComboBox Members	833	(7)
CommandButton Members	840	(1)
Image Members	841	(1)
Label Members	842	(1)
ListBox Members	842	(1)
MultiPage Members	843	(2)
Page Members	845	(1)
ScrollBar and SpinButton Members	846	(1)
TabStrip Members	847	(2)
TextBox and RefEdit Members	849	(3)
Sending and Receiving Workbooks	852	(19)
Send Mail	852	(2)
Work with Mail Items	854	(3)
Collect Review Comments	857	(1)
Route Workbooks	858	(2)
Read Mail	860	(1)
MsoEnvelope Members	860	(1)
MailItem Members	861	(5)
RoutingSlip Members	866	(5)
Part III. Extending Excel
Building Add-ins	871	(16)
Types of Add-ins	871	(1)
Code-Only Add-ins	872	(5)
Visual Add-ins	877	(4)
Set Add-in Properties	881	(1)
Sign the Add-in	882	(1)
Distribute the Add-in	883	(1)
Work with Add-ins in Code	884	(1)
AddIn and AddIns Members	884	(3)
Integrating DLLs and COM	887	(15)
Use DLLs	887	(7)
Use COM Applications	894	(8)
Getting Data from the Web	902	(31)
Perform Web Queries	903	(10)
QueryTable and QueryTables Web Query Members	913	(9)
Use Web Services	922	(10)
Resources	932	(1)
Programming Excel with .NET	933	(37)
Approaches to Working with .NET	934	(1)
Create .NET Components for Excel	934	(3)
Use .NET Components in Excel	937	(9)
Use Excel as a Component in .NET	946	(8)
Create Excel Applications in .NET	954	(15)
Resources	969	(1)
Exploring Security in Depth	970	(69)
Security Layers	970	(1)
Understand Windows Security	971	(4)
Password-Protect and Encrypt Workbooks	975	(1)
Program with Passwords and Encryption	976	(2)
Workbook Password and Encryption Members	978	(2)
Excel Password Security	980	(1)
Protect Items in a Workbook	981	(4)
Program with Protection	985	(3)
Workbook Protection Members	988	(2)
Worksheet Protection Members	990	(2)
Chart Protection Members	992	(2)
Protection Members	994	(2)
AllowEditRange and AllowEditRanges Members	996	(2)
UserAccess and UserAccessList Members	998	(1)
Set Workbook Permissions	999	(4)
Program with Permissions	1003	(2)
Permission and UserPermission Members	1005	(3)
Add Digital Signatures	1008	(1)
Set Macro Security	1008	(2)
Set ActiveX Control Security	1010	(1)
Distribute Security Settings	1011	(3)
Using the Anti-Virus API	1014	(1)
Common Tasks	1015	(1)
Resources	1016	(3)
Part IV. Appendixes
A. Reference Tables	1019	(12)
B. Version Compatibility	1031	(8)
Index	1039