book

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Name: Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®
ISBN: 0321262506

by Stephen Bullen, Rob Bovey, John Green

February 2005

Intermediate to advanced

936 pages

19h 42m

English

Addison-Wesley Professional

Read now

Unlock full access

Copyright
Praise for Professional Excel Development
Acknowledgments
About the Authors
Stephen BullenRob BoveyJohn Green
1. Introduction
About This BookThe Excel DeveloperExcel as an Application Development PlatformThe Worksheet as a Presentation Layer for Data Entry and ReportingThe Worksheet as a Simple Data StoreVBA: Excel's Programming LanguageThe Worksheet as a Declarative Programming LanguageThe Excel Object ModelStructureExamplesSupported VersionsTypefacesOn the CDHelp and SupportFeedback
2. Application Architectures
ConceptsCodeless ApplicationsSelf-Automated WorkbooksFunction and General-Purpose Add-insStructure of a Function or General-Purpose Add-inApplication-Specific Add-insStructure of an Application-Specific Add-inDictator ApplicationsRequirements of a Dictator ApplicationStructure of a Dictator ApplicationTechnical ImplementationsConclusion
3. Excel and VBA Development Best Practices
Naming ConventionsWhat Is a Naming Convention and Why Is It ImportantA Sample Naming ConventionThe Scope Specifier (<scope>)The Array Specifier (<array>)The Data Type Specifier (<data type>)Using Descriptive NamesA Few Words About EnumerationsNaming Convention ExamplesVariablesConstantsUser-Defined TypesEnumerationsProceduresModules, Classes and UserformsWorksheets and Chart SheetsThe Visual Basic ProjectExcel UI Naming ConventionsShapesEmbedded ObjectsDefined NamesExceptions—When Not to Apply the Naming ConventionBest Practices for Application Structure and OrganizationApplication StructureThe One-Workbook vs. the N-Workbook ApplicationSeparation of Logical TiersSeparation of Data/UI from CodeApplication Organization for Procedural ProgrammingOrganizing Code into Modules by Function/CategoryFunctional DecompositionBest Practices for Creating ProceduresGeneral Application Development Best PracticesCode CommentingModule-Level CommentsProcedure-Level CommentsInternal CommentsAvoiding the Worst Code-Commenting MistakeCode ReadabilityVBA Programming Best PracticesGeneral VBA Best PracticesUse of Module DirectivesVariables and ConstantsAvoid Reusing VariablesAvoid the Variant Data TypeBeware of Evil Type CoercionAvoid the As New Declaration SyntaxAlways Fully Qualify Object NamesNever Hard Code Array BoundsAlways Specify the Loop Counter After a Next StatementMake Use of ConstantsVariable ScopeEarly Binding vs. Late BindingDefensive CodingWrite Your Application in the Earliest Version of Excel That You Expect It to Run InExplicitly Use ByRef or ByValExplicitly Call the Default Property of an ObjectValidate Arguments Before Using Them in ProceduresUse Guard Counters to Protect Against Infinite LoopsUse Debug > Compile Early and OftenUse CodeNames to Reference Sheet ObjectsValidate the Data Types of SelectionsChange ControlSaving VersionsDocumenting Changes with CommentsConclusion
4. Worksheet Design
Principles of Good Worksheet UI DesignProgram Rows and Columns: The Fundamental UI Design TechniqueDefined NamesNamed ConstantsNamed RangesNamed FormulasScope of Defined NamesStylesAdvantages of StylesCreating and Using StylesModifying StylesAdding the Style Drop-Down to the ToolbarUser Interface Drawing TechniquesUsing Borders to Create Special EffectsCreating Well-Formatted TablesCell Comments for Help TextUsing ShapesData ValidationUnique EntriesCascading ListsConditional FormattingCreating Dynamic TablesCalling Out Error ConditionsUsing Controls on WorksheetsAdvantages of Forms ControlsAdvantages of ActiveX ControlsPractical ExampleHidden Rows and ColumnsDefined NamesStylesUser Interface Drawing TechniquesData ValidationConditional FormattingConclusion
5. Function, General and Application-Specific Add-ins
The Four Stages of an ApplicationDevelopment/MaintenanceStartupRuntimeShutdownFunction Library Add-insAn Example UDFUDF Naming ConventionsMaking Your UDF Appear NativeCreating a Friendly Name and Description for Your Function Library Add-inCritical UDF DetailsVBA UDF ProblemsGeneral Add-insApplication-Specific Add-insA Table-Driven Approach to UI Worksheet ManagementTable-Driven Methodology DefinedTypical Worksheet User Interface SettingsThe Settings TableThe Utility CodeUsing VBA to Dynamically Modify Your Worksheet User InterfacePractical ExampleFeaturesOpen and Initialize the ApplicationBuild a Toolbar That Gives the User Access to Each FeatureOpen and Initialize the Time-Entry WorkbookSave a Copy of the Time-Entry Workbook to a Predefined Consolidation LocationAllow the User to Add More Data-Entry Rows to the Time-Entry WorksheetAllow the User to Clear the Data-Entry Area So the Timesheet Can Be ReusedAllow the User to Close the PETRAS ApplicationAdd a Custom Property to Allow the Consolidation Application to Locate All Instances of Our Time-Entry WorkbookApplication OrganizationConclusion
6. Dictator Applications
Structure of a Dictator ApplicationStartup and ShutdownVersion and Dependency ChecksStoring and Restoring Excel SettingsHandling CrashesConfiguring the Excel EnvironmentSupporting a Debug ModeCustomizing the User InterfacePreparing a Backdrop GraphicSheet-Based vs. Form-Based User InterfacesHandling Cut, Copy and PasteCustom Command BarsProcessing and AnalysisPresenting ResultsPractical ExamplePETRAS TimesheetPETRAS ReportingIdentifying WorkbooksUsing the PETRAS Reporting ApplicationConclusion

7. Using Class Modules to Create Objects
Creating ObjectsClass Module StructureProperty ProceduresMethodsCreating a CollectionCreating a Collection ObjectAddressing Class Collection ShortcomingsUsing Visual BasicUsing a Text EditorTrapping EventsRaising EventsA Family Relationship ProblemCreating a Trigger ClassPractical ExamplePETRAS TimesheetThe TemplateThe Application-Level Event HandlerPETRAS ReportingConclusion
8. Advanced Command Bar Handling
Command Bar DesignTable-Driven Command BarsIntroducing the Table-Driven Command Bar BuilderThe Command Bar Definition TableCommand Bar NameControl CaptionPositionIsMenubarVisibleWidthProtectionIsTemporaryIsEnabledOnActionControl IDControl TypeControl StyleFace IDBegin GroupBeforeTooltipShortcut TextTagParameterStateListRange and ListsPost MortemPutting It All TogetherAdding a Custom Menu with Submenus to the Worksheet Menu BarAdding a Custom ToolbarAdding a Custom Right-Click Command BarLoading Custom Icons from FilesCreating Bitmap Files for Icons and MasksUsing Bitmap Files as CommandBarButton IconsHooking Command Bar Control EventsWhy Use an Event HookWhat Can an Event Hook DoThe Importance of the Tag PropertyThe Paste Special Command BarThe Paste Special Toolbar DefinitionPractical ExamplePETRAS TimesheetPETRAS ReportingApplication ContextsConclusion
9. Understanding and Using Windows API Calls
OverviewFinding DocumentationFinding DeclarationsFinding the Values of ConstantsUnderstanding HandlesEncapsulating API CallsWorking with the ScreenReading the Screen ResolutionFinding the Size of a PixelDevice ContextsWorking with WindowsWindow ClassesFinding WindowsANSI vs. Unicode and the Alias ClauseFinding Related WindowsWindows MessagesChanging the Window IconChanging Windows StylesWorking with the KeyboardChecking for Shift, Ctrl, Alt, Caps Lock, Num Lock and Scroll LockBit MasksTesting for a Key PressStructuresWorking with the File System and NetworkFinding the User IDBuffersChanging to a UNC PathLocating Special FoldersDeleting a File to the Recycle BinBrowsing for a FolderCallbacksPractical ExamplesPETRAS TimesheetPETRAS ReportingConclusion
10. Userform Design and Best Practices
PrinciplesKeep It SimpleDisplay Canvas, Not Business RulesUse Classes, Not the Default InstanceExpose Properties and Methods, Not ControlsControl FundamentalsNamingLayeringPositioningTab Orders and Accelerator KeysData BindingEvent HandlingValidationVisual EffectsUserform Window StylesDisabling the Close ButtonDisplaying Graphics, Charts, WordArt and So Forth on UserformsLocking vs. Disabling ControlsPopup MenusUserform Positioning and SizingPositioning Next to a CellResponding to Different ResolutionsResizable UserformsSplitter BarsWizardsDesign Rules for Wizard DialogsCreating a Wizard DialogDynamic UserformsSubset UserformsCode-Created and Table-Driven UserformsScroll RegionsDynamic Control Event Handling and Control ArraysModeless UserformsSplash ScreensProgress BarsCombining with Menu ItemsControl SpecificsComboBoxDrop-Down List/ComboText BoxFilename BoxDrop-Down PanesWindows Common ControlsDrag and DropPractical ExamplesPETRAS TimesheetPETRAS ReportingConclusion
11. Interfaces
What Is an Interface?Code ReuseDefining a Custom InterfaceImplementing a Custom InterfaceUsing a Custom InterfacePolymorphic ClassesImproving RobustnessSimplifying DevelopmentA Progress BarThe IProgressBar InterfaceThe FProgressBar FormThe CProgressBar ClassA Plug-in ArchitecturePractical ExamplePETRAS TimesheetPETRAS ReportingConclusion
12. VBA Error Handling
Error-Handling ConceptsUnhandled vs. Handled ErrorsThe Err ObjectWhat Is an Error HandlerError Handler ScopeThe On Error StatementOn Error GoTo <Label>On Error Resume NextOn Error GoTo 0The Resume StatementResumeResume NextResume <Label>Raising Custom ErrorsThe Single Exit Point PrincipleSimple Error HandlingComplex Project Error Handler OrganizationProcedure Error HandlersTrivial ProceduresThe Central Error HandlerError Handling in Classes and UserformsInitialize and Activate EventsTerminate EventsPutting It All TogetherPractical ExamplePETRAS TimesheetPETRAS ReportingConclusion
13. Programming with Databases
An Introduction to DatabasesWhy Use a DatabaseRelational DatabasesFile-Based Databases vs. Client-Server DatabasesNormalizationFirst Normal FormSecond Normal FormThird Normal FormWhen Not to NormalizeRelationships and Referential IntegrityForeign KeysTypes of RelationshipsOne to OneOne to ManyMany to ManyReferential IntegrityNatural vs. Artificial Primary KeysDesigning the Data Access TierData Access with SQL and ADOAn Introduction to ActiveX Data Objects (ADO)Data Access Technology DefinedADO ObjectsTop-Level ObjectsCollectionsConnecting to Data SourcesAccessSQL ServerExcelUsing the Connection StringError Handling ConnectionsData Access TechniquesRetrieving DataInserting DataUpdating DataDeleting DataFurther ReadingProfessional SQL Server 2000 Database DesignADO 2.6 Programmer's ReferenceProfessional ADO 2.5 ProgrammingProfessional SQL Server 2000 ProgrammingAccess 2002 Developer's Handbook SetPractical ExamplePETRAS TimesheetModifying the Application to Load Data-Validation Lists from the DatabaseModifying the Application to Save Time Entries to the DatabasePETRAS ReportingConclusion
14. Data Manipulation Techniques
Excel's Data StructuresUnstructured RangesStructured RangesExcel 2003's ListsQuery TablesData Processing FeaturesIt Doesn't Have to Be DataPivot CachesPivot TablesCalculated Pivot FieldsData ConsolidationAdvanced FilteringCriteria RangesAdvanced FunctionsThe Database FunctionsArray FormulasCircular ReferencesConclusion
15. Advanced Charting Techniques
Fundamental TechniquesCombining Chart TypesUsing Multiple AxesUsing Defined Names to Link Charts to DataSetting Up the Defined Name LinksAuto-Expanding ChartsScrolling and Zooming a Time SeriesTransforming Coordinate SystemsCharting a FunctionFaking ItError BarsDummy XY SeriesVBA TechniquesConverting Between Chart Coordinate SystemsLocating Chart ItemsCalculating Reasonable Axis ScalesConclusion
16. VBA Debugging
Basic VBA Debugging TechniquesRun Mode vs. Break ModeBreak on All ErrorsBreak in Class ModuleBreak on Unhandled ErrorsDebug ModeUser-Defined Debug ModeThe Stop StatementConditional Compilation ConstantsUsing Break Points (F9)Stepping Through CodeStep Into (F8)Step Over (Shift+F8)Step Out (Ctrl+Shift+F8)Step to Cursor (Ctrl+F8)Changing the Execution Point, or Set Next Statement (Ctrl+F9)The Immediate Window (Ctrl+G)Debug.PrintMaking the Best Use of the Immediate WindowThe Call Stack (Ctrl+L)The Watch WindowSetting a Basic WatchUsing a Basic WatchWatch TypesWatch ContextModuleProcedureWatch TypeWatch ExpressionBreak When Value Is TrueBreak When Value ChangesArrays, UDTs and Classes in the Watch WindowQuick Watch (Shift+F9)The Locals WindowThe Object Browser (F2)Basic FeaturesAdvanced FeaturesCreating and Running a Test HarnessUsing AssertionsDebugging Shortcut Keys that Every Developer Should KnowGeneralDebug Mode Code ExecutionNavigationInformationConclusion
17. Optimizing VBA Performance
Measuring PerformanceThe PerfMon UtilityCreative ThinkingDo a JigsawIdentify the StepsThink Outside the BoxBreak the Rules!Know the DataAsk QuestionsKnow the ToolMacro-OptimizationPreprocessCheck the OrderTighten the LoopFast VBA AlgorithmsQuickSortBinary SearchSort and ScanThe SORTSEARCH_INDEX udtMicro-OptimizationVBAUse Matching Data TypesPerform Explicit Conversions Instead of Implicit OnesUse Len(string)=0 Instead of string=””Use Left$, Right$, Mid$ and So Forth Instead of Left, Right and MidPass Strings and Variant Arrays ByRef Instead of ByValDon't Use Option Compare TextUse Early Binding Wherever PossibleUse Integer Arithmetic Where PossibleUse For Each to Iterate Collections (Not by Index)Use For … Next to Iterate Arrays (Not For Each)Use Dictionaries Instead of Collections (If Order Isn't Important)Don't Use If bVariable = True Then, Just Use If bVariable ThenDon't Use IIf()Use Multiple If…ElseIf…End If Instead of Select CaseUse With blocks and Object Variables to Reduce the DotsExcelTurn Off ScreenUpdating and Automatic CalculationDon't Select ThingsUse Variant ArraysDon't Use ActiveSheet, Selection or Worksheets() RepeatedlyTest a Property Before Setting ItUse Doubles to Talk to ExcelUse the PAGE.SETUP XLM Function Instead of the PageSetup ObjectConclusion
18. Controlling Other Office Applications
FundamentalsAutomationReferencingDevelopment Best PracticesAlways Include the Object Library in Variable DeclarationsAlways Fully Qualify Property and Method CallsDevelop Using the Earliest Version You'll SupportGroup Routines in Application-Specific ModulesThe vTable and Early vs. Late BindingHandling InstancesCreate a New InstanceProperly Tidying UpReference an Existing InstanceMultiversion SupportDetermining the Availability of an ApplicationPerformanceThe Primary Office Application Object ModelsAccess and Data Access ObjectsApplicationDAO.DatabaseDoCmdExampleWordApplicationDocumentBookmarkRangeExamplePowerPoint and MSGraphApplicationPresentationSlideShapeChartsExampleOutlookApplicationNamespaceMAPIFolderAppointmentItem, ContactItem, DistributionListItem, JournalItem, MailItem, NoteItem, PostItItem and TaskItemExampleFurther ReadingPractical ExampleConclusion
19. XLLs and the C API
Why Create an XLL-Based Worksheet FunctionCreating an XLL Project in Visual StudioThe Structure of an XLLThe Function TableThe DLLMain FunctionStandard XLL Callback FunctionsxlAutoOpenxlAutoClosexlAddInManagerInfoAdditional XLL Callback FunctionsxlAutoRegisterxlAutoAddxlAutoRemovexlAutoFreeThe XLOPER and OPER Data TypesThe Excel4 FunctionCommonly Used C API FunctionsxlFreexlCoercexlGetNameXLOPERs and Memory ManagementRegistering and Unregistering Custom Worksheet FunctionsSample Application FunctionDebugging the Worksheet FunctionsMiscellaneous TopicsA Caution for Users of COM AutomationC++ Keyword Clash with the XLOPER DefinitionAdditional ResourcesThe Excel 97 SDK on MSDNExcel Add-in Development in C/C++: Applications in FinanceWilliam Hooper's Web SiteLaurent Longre's Web Site (French Only)The Microsoft Excel Public NewsgroupsPlanatech XLL+Keith Lewis' Freeware Object-Oriented C++ Wrapper for the Excel C APIManagedXLLConclusion
20. Combining Excel and Visual Basic 6
A Hello World ActiveX DLLCreating an ActiveX DLL ProjectThe Simplest Case—One-Way CommunicationThe More Complex Case—Two-Way CommunicationDisplaying a VB6 Form in ExcelWhy Use VB6 ActiveX DLLs in Excel VBA ProjectsCode ProtectionTaking Advantage of VB6 FormsBetter ActiveX Control SupportControl ArraysBetter Support for Object-Oriented ProgrammingMore Class Instancing TypesMultiUseGlobalMultiUseBetter Support for Custom Collections through Direct Support of NewEnumResource FilesOther VB6 FeaturesIn-Process versus Out-of-ProcessIn-Process CommunicationOut-of-Process CommunicationAutomating Excel From a VB6 EXEAn Excel Automation PrimerUsing a VB6 EXE Front Loader for Your Excel ApplicationPractical ExamplesActiveX DLL—Using a Resource File to Load IconsAdding a Resource File to Your ProjectAdding Bitmaps to the Resource FileUsing Bitmaps Located in the Resource FileStandard EXE—Creating a Front Loader for Your Excel ApplicationConclusion
21. Writing Add-ins with Visual Basic 6
A Hello World Add-inThe Add-in DesignerGeneral TabAdd-in Display NameAdd-in DescriptionApplicationApplication VersionInitial Load BehaviorAdvanced TabSatellite DLL NameRegistry Key for Additional Add-in Data and Add-in-Specific DataInstallation ConsiderationsThe AddinInstance EventsInitializeOnConnectionOnStartupCompleteOnAddInsUpdateOnBeginShutdownOnDisconnectionTerminateCommand Bar HandlingUsing Command Bar Event HooksPermanent vs. Temporary Menu ItemsA Permanent-Menu ArchitectureA Temporary-Menu ArchitectureCustom Toolbar FacesThe Paste Special Bar COM Add-inWhy Use a COM Add-in?Improved Code SecurityMulti-Application Add-insExploiting Separate ThreadingAutomation Add-insCreating the IfError Automation Add-inUsing the IfError Automation Add-inAccessing the Excel Application Object from an Automation Add-inPractical ExampleConclusion
22. Using VB.NET and the Visual Studio Tools for Office
OverviewTarget AudienceWhat Is VSTO?How Does .NET Interact with Office?Is This the End for VBA?TerminologyHow to Leverage the .NET FrameworkManaged WorkbooksConceptA Hello World Managed WorkbookThe Default VSTO TemplateThe ProExcel VSTO TemplateMGlobalsVSTOHooksCExcelAppCThisWorkbookCSheet1MStandardCodeSharing Command BarsManaged Excel Add-insThe Paste Special Bar VSTO Add-inHybrid VBA/VSTO SolutionsThe VSTO Security ModelStrong NamesStrong Name RisksCreating and Using Strong NamesTrusting a Strong NameCaspolThe Big IssuesFunctional GapsApplication LinksGlobal SolutionsSecurity and Sharing Managed WorkbooksMigrating from VBAOffice VersionsFurther ReadingPractical ExamplePETRAS Timesheet Add-inPETRAS Reporting ApplicationConclusion
23. Excel, XML and Web Services
XMLAn Example XML FileAn Example XSD fileOverview of Excel 2003's XML FeaturesA Simple Financial ModelCreating an XML Schema DefinitionXML MapsExporting and Importing XML DataThe XML Object Model and EventsXML Support in Earlier VersionsUsing NamespacesWeb ServicesCreating a Web Service with VB.NETUsing a Web ServicePractical ExamplePETRAS Web ServicePETRAS TimesheetPETRAS ReportingConclusion
24. Providing Help, Securing, Packaging and Distributing
Providing HelpOverviewGetting StartedCreate a Help Project FileUpdate the Project OptionsCreate an Introductory HTML FileCreate a “No Help Available” Topic FileCreate a List of TopicsGive Each Topic a Numeric IDCompile the ProjectWriting ContentTable of ContentsIndexDisplaying Help from VBASecuringExcel SecurityChecking Network GroupsMacro Security and Digital SignaturesAlternatives to Digital SignaturesPackagingInstallation LocationInstallation RequirementsTemplatesAdd-insCOM Add-insAutomation Add-insInstallation MechanismsManualAn Installation WorkbookWindows InstallerDistributingOriginalsUpdatesPhone HomeConclusion
Where are the Companion Content Files?

Overview

Finally, there's a book that treats Excel as the powerful development platform it really is, and covers every facet of developing commercial-quality Excel applications.

This is not a book for beginners. Writing for professional developers and true Excel experts, the authors share insider's knowledge they've acquired building Excel applications for many of the world's largest companies—including Microsoft. Professional Excel Development demonstrates how to get the utmost from Excel, addressing everything from application architectures through worksheet and userform design, charting, debugging, error handling and optimizing performance. Along the way, the authors offer best practices for every type of Excel development, from building add-ins through interacting with XML Web services. Coverage includes

Building add-ins to provide new Excel functions

Designing effective worksheets, userforms and other user interface elements

Leveraging Excel's powerful data analysis features

Creating sophisticated custom charts

Handling errors, debugging applications and optimizing performance

Using class modules and interfaces to create custom objects

Understanding Windows API calls: when to use them, and how to modify them

Adding worksheet functions with

C-based XLLs

Programming with databases

Controlling external applications from Excel

Integrating with Visual Basic 6, VB.NET and Visual Studio Tools for Office

Using XML to import and export data and communicate with Web services

Providing help, securing, packaging and distributing

The accompanying CD-ROM contains the book's sample timesheet application at every stage of construction, with detailed code comments. It also includes many examples of the concepts introduced in each chapter and a collection of the authors' Excel development utilities.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft® Office Excel 2003 Programming Inside Out

Publisher Resources

ISBN: 0321262506Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills