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.
Sprache
Verlagsort
Zielgruppe
Produkt-Hinweis
Broschur/Paperback
Klebebindung
Maße
Höhe: 238 mm
Breite: 179 mm
Dicke: 65 mm
Gewicht
ISBN-13
978-0-596-00766-9 (9780596007669)
Schweitzer Klassifikation
Jeff Webb is the author of Excel 2003 Programming: A Developer's Notebook. Jeff has written about computers and technology for 20 years. His books include Using Excel Visual Basic for Applications; Visual Basic Developer's Workshop; and Developing Web Applications with Visual Basic .NET. He has also written programming guides, articles, and sample applications for Microsoft and Digital Equipment Corporation.
Inhaltsverzeichnis
Preface
Part I. Learning VBA
1. Becoming an Excel Programmer
Why Program?
Record and Read Code
Change Recorded Code
Fix Misteakes
Start and Stop
View Results
Where's My Code?
Macros and Security
Write Bug-Free Code
Navigate Samples and Help
What You've Learned
2. Knowing the Basics
Parts of a Program
Classes and Modules
Procedures
Variables
Conditional Statements
Loops
Expressions
Exceptions
What You've Learned
3. Tasks in Visual Basic
Types of Tasks
Interact with Users
Do Math
Work with Text
Get Dates and Times
Read and Write Files
Check Results
Find Truth
Compare Bits
Run Other Applications
Control the Compiler
Not Covered Here
What You've Learned
4. Using Excel Objects
Objects and Their Members
Get Excel Objects
Get Objects from Collections
About Me and the Active Object
Find the Right Object
Common Members
Respond to Events in Excel
The Global Object
The WorksheetFunction Object
What You've Learned
5. Creating Your Own Objects
Modules Versus Classes
Add Methods
Create Properties
Define Enumerations
Raise Events
Collect Objects
Expose Objects
Destroy Objects
Things You Can't Do
What You've Learned
6. Writing Code for Use by Others
Types of Applications
The Development Process
Determine Requirements
Design
Implement and Test
Integrate
Test Platforms
Document
Deploy
What You've Learned
Resources
Part II. Excel Objects
7. Controlling Excel
Perform Tasks
Control Excel Options
Get References
Application Members
AutoCorrect Members
AutoRecover Members
ErrorChecking Members
SpellingOptions Members
Window and Windows Members
Pane and Panes Members
8. Opening, Saving, and Sharing Workbooks
Add, Open, Save, and Close
Share Workbooks
Program with Shared Workbooks
Program with Shared Workspaces
Respond to Actions
Workbook and Workbooks Members
RecentFile and RecentFiles Members
9. Working with Worksheets and Ranges
Work with Worksheet Objects
Worksheets and Worksheet Members
Sheets Members
Work with Outlines
Outline Members
Work with Ranges
Range Members
Work with Scenario Objects
Scenario and Scenarios Members
Resources
10. Linking and Embedding
Add Comments
Use Hyperlinks
Link and Embed Objects
Speak
Comment and Comments Members
Hyperlink and Hyperlinks Members
OleObject and OleObjects Members
OLEFormat Members
Speech Members
UsedObjects Members
11. Printing and Publishing
Print and Preview
Control Paging
Change Printer Settings
Filter Ranges
Save and Display Views
Publish to the Web
AutoFilter Members
Filter and Filters Members
CustomView and CustomViews Members
HPageBreak, HPageBreaks, VPageBreak, VPageBreaks Members
PageSetup Members
Graphic Members
PublishObject and PublishObjects Members
WebOptions and DefaultWebOptions Members
12. Loading and Manipulating Data
Working with QueryTable Objects
QueryTable and QueryTables Members
Working with Parameter Objects
Parameter Members
Working with ADO and DAO
ADO Objects and Members
DAO Objects and Members
DAO.Database and DAO.Databases Members
DAO.Document and DAO.Documents Members
DAO.QueryDef and DAO.QueryDefs Members
DAO.Recordset and DAO.Recordsets Members
13. Analyzing Data with Pivot Tables
Quick Guide to Pivot Tables
Program Pivot Tables
PivotTable and PivotTables Members
PivotCache and PivotCaches Members
PivotField and PivotFields Members
CalculatedFields Members
CalculatedItems Members
PivotCell Members
PivotFormula and PivotFormulas Members
PivotItem and PivotItems Members
PivotItemList Members
PivotLayout Members
CubeField and CubeFields Members
CalculatedMember and CalculatedMembers Members
14. Sharing Data Using Lists
Use Lists
ListObject and ListObjects Members
ListRow and ListRows Members
ListColumn and ListColumns Members
ListDataFormat Members
Use the Lists Web Service
Lists Web Service Members
Resources
15. Working with XML
Understand XML
Save Workbooks as XML
Use XML Maps
Program with XML Maps
XmlMap and XmlMaps Members
XmlDataBinding Members
XmlNamespace and XmlNamespaces Members
XmlSchema and XmlSchemas Members
Get an XML Map from a List or Range
XPath Members
Resources
16. Charting
Navigate Chart Objects
Create Charts Quickly
Embed Charts
Create More Complex Charts
Choose Chart Type
Create Combo Charts
Add Titles and Labels
Plot a Series
Respond to Chart Events
Chart and Charts Members
ChartObject and ChartObjects Members
ChartGroup and ChartGroups Members
SeriesLines Members
Axes and Axis Members
DataTable Members
Series and SeriesCollection Members
Point and Points Members
17. Formatting Charts
Format Titles and Labels
Change Backgrounds and Fonts
Add Trendlines
Add Series Lines and Bars
ChartTitle, AxisTitle, and DisplayUnitLabel Members
DataLabel and DataLabels Members
LeaderLines Members
ChartArea Members
ChartFillFormat Members
ChartColorFormat Members
DropLines and HiLoLines Members
DownBars and UpBars Members
ErrorBars Members
Legend Members
LegendEntry and LegendEntries Members
LegendKey Members
Gridlines Members
TickLabels Members
Trendline and Trendlines Members
PlotArea Members
Floor Members
Walls Members
Corners Members
18. Drawing Graphics
Draw in Excel
Create Diagrams
Program with Drawing Objects
Program Diagrams
Shape, ShapeRange, and Shapes Members
Adjustments Members
CalloutFormat Members
ColorFormat Members
ConnectorFormat Members
ControlFormat Members
FillFormat Members
FreeFormBuilder
GroupShapes Members
LineFormat Members
LinkFormat Members
PictureFormat Members
ShadowFormat
ShapeNode and ShapeNodes Members
TextFrame
TextEffectFormat
ThreeDFormat
19. Adding Menus and Toolbars
About Excel Menus
Build a Top-Level Menu
Create a Menu in Code
Build Context Menus
Build a Toolbar
Create Toolbars in Code
CommandBar and CommandBars Members
CommandBarControl and CommandBarControls Members
CommandBarButton Members
CommandBarComboBox Members
CommandBarPopup Members
20. Building Dialog Boxes
Types of Dialogs
Create Data-Entry Forms
Design Your Own Forms
Use Controls on Worksheets
UserForm and Frame Members
Control and Controls Members
Font Members
CheckBox, OptionButton, ToggleButton Members
ComboBox Members
CommandButton Members
Image Members
Label Members
ListBox Members
MultiPage Members
Page Members
ScrollBar and SpinButton Members
TabStrip Members
TextBox and RefEdit Members
21. Sending and Receiving Workbooks
Send Mail
Work with Mail Items
Collect Review Comments
Route Workbooks
Read Mail
MsoEnvelope Members
MailItem Members
RoutingSlip Members
Part III. Extending Excel
22. Building Add-ins
Types of Add-ins
Code-Only Add-ins
Visual Add-ins
Set Add-in Properties
Sign the Add-in
Distribute the Add-in
Work with Add-ins in Code
AddIn and AddIns Members
23. Integrating DLLs and COM
Use DLLs
Use COM Applications
24. Getting Data from the Web
Perform Web Queries
QueryTable and QueryTables Web Query Members
Use Web Services
Resources
25. Programming Excel with.NET
Approaches to Working with.NET
Create.NET Components for Excel
Use.NET Components in Excel
Use Excel as a Component in.NET
Create Excel Applications in.NET
Resources
26. Exploring Security in Depth
Security Layers
Understand Windows Security
Password-Protect and Encrypt Workbooks
Program with Passwords and Encryption
Workbook Password and Encryption Members
Excel Password Security
Protect Items in a Workbook
Program with Protection
Workbook Protection Members
Worksheet Protection Members
Chart Protection Members
Protection Members
AllowEditRange and AllowEditRanges Members
UserAccess and UserAccessList Members
Set Workbook Permissions
Program with Permissions
Permission and UserPermission Members
Add Digital Signatures
Set Macro Security
Set ActiveX Control Security
Distribute Security Settings
Using the Anti-Virus API
Common Tasks
Resources
Part IV. Appendixes
A. Reference Tables
B. Version Compatibility
Index
