book

Microsoft® Office Excel 2003 Programming Inside Out

by Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

March 2004

Intermediate to advanced

624 pages

17h 59m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Office Excel 2003 Programming Inside Out
A Note Regarding Supplemental Files
Acknowledgments
We’d Like to Hear from You
About the CD
What’s on the CDUsing the CDSystem RequirementsSupport Information
Conventions and Features Used in this Book
Text ConventionsDesign Conventions
1. Preliminaries
1. What’s New in Excel 2003
Using Improved Statistical FunctionsCreating Smart DocumentsCreating Document WorkspacesImplementing Information Rights ManagementComparing Workbooks Side By SideGetting Information with the Research Task PaneUsing Extended XML CapabilitiesCreating Excel ListsMapping Excel Data to XML SchemasPublishing Spreadsheets with Web Components
2. Exploring Excel
WorkbooksWorksheetsCells and RangesThe Excel InterfaceFormulas
2. Visual Basic for Applications

3. Exploring Visual Basic for Applications
An Overview of Object-Oriented ProgrammingPropertiesMethodsEventsCollectionsWorking with MacrosRecording and Viewing MacrosRunning a MacroAssigning a Macro to a Key SequenceAssigning a Macro to a Toolbar ButtonAssigning a Macro to a Menu ItemDebugging a MacroImplementing Macro Security and Digital SignaturesIntroducing Digital SignaturesDigitally Signing Workbooks and MacrosUsing Digital Signatures with the High Macro Security Setting
4. VBA Programming Starter Kit
Introducing the Visual Basic EditorOpening the Visual Basic EditorRecognizing Parts of the Visual Basic EditorThe View MenuThe Insert MenuThe Format MenuThe Debug MenuThe Run MenuThe Tools MenuCustomizing the Visual Basic EditorManaging Code WindowsHandling WindowsAdding CodeNotes on Programming Style and Code ReadabilityDeveloping Projects with the Project ExplorerCreating VBA ModulesDeleting VBA ModulesCopying Modules Between ProjectsDeclaring Variables, Constants, and Data TypesDeclaring VariablesDefining Data TypesDefining ConstantsHandling StringsHandling Dates and TimesHandling Variants and Data Type ConversionsUnderstanding Variable Scope and LifetimesAssigning Values to VariablesWorking with ArraysCreating an ArrayCreating Multidimensional ArraysCreating Dynamic ArraysWriting to the Screen and Accepting InputCreating a Message BoxCreating an Input BoxDefining Object VariablesCreating Custom Data TypesWith...End With CommandControlling Program FlowSelection StatementsIf...Then...Else StatementSelect Case StatementsLoopsIteration LoopsLogical LoopsGoTo StatementError Handling
5. Creating Sub and Function Procedures
Defining Sub ProceduresDefining the Scope of a Sub ProcedureRunning a Sub Procedure from Within Another ProcedureRetaining Values Between Procedure CallsDefining Function ProceduresCreating a Function ProcedureRunning Function ProceduresPassing Arguments to ProceduresPassing Named ArgumentsOrganizing for Success
3. The Excel Object Model
6. The Application Object
Introducing the Application ObjectPropertiesActiveCell PropertyActiveChart PropertyActiveSheet PropertyActiveWindow PropertyActiveWorkbook PropertyDisplayAlerts PropertyRangeSelection PropertySelection PropertyStatusBar PropertyScreenUpdating PropertyThisWorkbook PropertyMethodsCalculate MethodCalculateFull MethodFindFile MethodInputBox MethodIntersect MethodOnKey MethodSendKeys Method
7. Workbooks and Worksheets
The Workbooks CollectionCreating New WorkbooksOpening WorkbooksSaving WorkbooksActivating WorkbooksClosing WorkbooksWorkbook PropertiesUsing the ActiveChart PropertyDisplaying Drawing ObjectsManaging File SettingsRequiring a Password to Open a WorkbookProtecting Workbooks from ChangesWorkbook MethodsActivate MethodAddToFavorites MethodFollowHyperlink MethodPrinting and Previewing WorkbooksThe Sheets and Worksheets CollectionsPropertiesCount PropertyName PropertyVisible PropertyMethodsAdd MethodDelete MethodMove and Copy MethodsPageSetup ObjectWorksheet MethodsCalculate MethodProtect MethodSaveAs MethodSelect Method
8. Ranges and Cells
Basic Range ManipulationsFinding the Active RangeSelecting a RangeReferring to RangesReferencing Ranges on the Active WorksheetReferencing Ranges on an Inactive WorksheetReferencing Cells in a RangeReferencing Cells Using the Offset PropertyDefining a Range Using the Cells PropertyReferencing Columns and RowsReferencing Non-Contiguous Groups of CellsManipulating Groups of CellsResizing Cells ProgrammaticallyJoining Two Ranges TogetherDetecting Empty CellsUsing Named RangesDefining a Named RangeChanging Notation StylesReserved Range NamesCopying Data Between Ranges and ArraysGetting Data Entry Right the First Time
4. Advanced VBA
9. Manipulating Data with VBA
Manipulating TextDetermining if the Value in a Cell Is TextPreparing String Data for ProcessingDetermining the Number of Characters in a StringConcatenating Text from Two or More Cells or VariablesReturning the First or Last Several Characters from a StringReturning Characters from Arbitrary Positions in a StringFinding a String Within Another StringManipulating NumbersPerforming Summary CalculationsPerforming Financial CalculationsDetermining the Present Value of an InvestmentDetermining the Net Present Value of an InvestmentManipulating Dates and TimesTime and Date Serial NumbersDate and Time Functions
10. Formatting Excel Objects
Using Excel Color Constants and the Color PaletteManipulating the Current Excel Color PaletteUsing the Excel Color Palette on the WebFormatting Worksheet ElementsFormatting FontsFormatting CellsFormatting Borders
11. Creating Add-Ins and COM Add-Ins
Introducing Add-InsUsing the Add-Ins dialog boxInstalling an Add-InUnloading an Add-InCreating Excel Add-InsCreating an Add-InSaving the Add-InInstalling the Add-InUsing the AddIns CollectionAddIns CollectionAddIn ObjectCreating Automation and COM Add-InsUsing the IDTExtensibility2 InterfaceRegistry KeysBuilding an Automation Add-In with Visual Basic 6Designing the Add-InRegistry EntriesBuilding a COM Add-In with Visual Basic .NETRunning the Shared Add-In WizardModifying the TemplateInstalling the Add-In
12. Understanding and Using Events
Enabling and Disabling EventsWorkbook EventsOpen EventActivate EventSheetActivate EventNewSheet EventBeforeSave EventDeactivate EventBeforePrint EventBeforeClose EventWorksheet EventsChange EventSelectionChange EventBeforeRightClick EventApplication EventsTurning on Application Event MonitoringDetecting When a Workbook Is Opened
13. Manipulating Files
Locating External FilesReturning All FilesLimiting the File SearchFinding Files with the FileDialog Dialog BoxWriting to an External FileReading from an External FileSearching a File for a Value
14. Developing Class Modules
What Is an Object?What Is a Class?What Are Properties?What Are Methods?What Are Events?Introducing Class ModulesAccessing ObjectsDeclaring ObjectsObjects and NothingObjects with Multiple Object VariablesProperties, Methods, and EventsPublic vs. Private Properties, Methods, and EventsPropertiesMethodsEventsBuilding a ClassCreating a Class ModuleDefining Simple PropertiesDefining Property RoutinesDefining MethodsDefining EventsDefining Private Variables, Subroutines, and FunctionsSpecial Events for ClassesResolving ReferencesPractical Class DesignA Simple ClassExtending a Simple ClassA Collection ClassA Class with Business Rules
5. Manipulating Excel Objects
15. Charts
Introducing ChartsCreating Embedded Charts or Chart SheetsDefining the Chart Object ModelManipulating ChartsActivating a ChartDeactivating a ChartModifying a Chart’s Data SeriesModifying a Chart to Use Data from ArraysDefining a Chart’s LabelsFormatting a ChartModifying All Charts in a WorkbookPrinting ChartsFinal Thoughts on Programming Charts
16. PivotTables and PivotCharts
PivotTables and PivotChartsIntroducing PivotTablesCreating a PivotTable with the PivotTable WizardIntroducing PivotChartsCreating a PivotChart with the PivotTable WizardOnline Analytical Processing (OLAP) IssuesPivotTable ObjectsPivotTables CollectionPivotTable ObjectPivotCaches CollectionPivotCache ObjectPivotField ObjectsPivotItem ObjectProgramming PivotTablesCreating a PivotTableCreating a PivotChartManipulating PivotTables ProgrammaticallyPivoting a PivotTable ProgrammaticallyResetting a PivotTable to Its Original PositionRecording and Restoring Arbitrary PivotTable Positions
17. Command Bars
Identifying Parts of the Menu SystemCommandBars CollectionCommandBar ObjectsListing CommandBar ObjectsAdding a Floating Command BarDeleting a Command BarCommand Bar ControlsCommandBarControls CollectionCommandBarControl ObjectButton ControlsCreating ToolbarsCombo Box ControlsUsing a Combo BoxPop-Up ControlsDisplaying a Pop-UpAdding Items to an Existing Menu
18. Customizing Dialog Boxes
Displaying Existing Dialog BoxesModifying Existing Dialog BoxesExploring the Dialogs CollectionPassing Arguments to Existing Dialog BoxesPlanning with Dialog Boxes
19. Creating User Forms
Creating a UserFormAdding a UserFormDesigning a UserFormModifying a UserFormProperties of a UserFormDisplaying a UserFormUserForm ControlsProgramming ControlsCommon Properties, Methods, and EventsThe Label ControlThe CommandButton ControlThe TextBox ControlThe CheckBox ControlThe ToggleButton ControlThe SpinButton ControlThe Frame ControlThe OptionButton ControlThe Image ControlThe ScrollBar ControlThe ListBox ControlThe ComboBox ControlThe RefEdit ControlThe TabStrip ControlThe MultiPage Control
20. Creating Advanced User Forms
Capturing InformationForm Application OverviewDesigning a FormDisplaying DataNavigating The WorksheetEditing DataAdding DataValidating DataDisplaying the User FormBuilding a Multi-Step WizardWizard Application OverviewHandling MenusBuilding the UserFormNavigating the PagesCollecting Options for the WizardSummarizing the OptionsRunning the Wizard
6. Excel and the Outside World: Collaborating Made Easy
21. Excel and Other Office Applications
Starting Another ApplicationActivating Another ApplicationBindingLate BindingEarly BindingInteracting with Other Office ApplicationsOpening a Document in WordAccessing an Active Word DocumentCreating a New Word DocumentControlling Excel from Other Office ApplicationsWorking with Multiple Applications to Get the Job Done
22. Excel and the Structured Query Language
Comparing Spreadsheets and DatabasesFundamental Database ConceptsDatabase KeysAccessing Databases from ExcelManipulating Databases with SQLThe Select StatementSimple Select StatementsRetrieving EverythingRetrieving a List of ColumnsRetrieving RowsUsing Simple Search ExpressionsUsing Complex Search ExpressionsUsing NullUsing the Like OperatorSorting RowsUsing Multiple TablesThe Wrong Way to Join Two TablesThe Right Way to Join Two TablesResolving Column NamesUsing AliasesUsing FunctionsThe Insert StatementUsing the Insert StatementThe Update StatementThe Delete Statement
23. Introducing ADO
The ADO Object ModelUsing the ADO Object ModelThe Connection ObjectKey Properties and Methods of the Connection ObjectConnecting to Different Database Management SystemsConnecting to Access DatabasesConnecting to SQL Server DatabasesConnecting to Other DatabasesUsing the Errors CollectionUsing the Error ObjectThe Command ObjectKey Properties and Methods of the Command ObjectUsing the Parameters CollectionUsing the Parameter ObjectThe Recordset ObjectKey Properties and Methods of the Recordset ObjectUsing the Fields CollectionUsing the Field Object
24. Excel Query Program
Excel Query Program OverviewSetting Up the ProjectInitializing the ProgramEnding the ProgramConnecting to a DatabaseInitializing the DBInfo UserFormChanging Database ProvidersSelecting Windows AuthenticationSaving the Database InfoEditing a QueryExecuting a Database QueryGetting the Information to Run the QueryBuilding a Connection StringGetting the QueryRunning a QueryCopying RowsUsing the Excel Query ProgramConfigure the Connection InformationEnter a QueryRun the Query
25. Excel and the Web
HTMLSaving a Worksheet as a Web PagePublishing a Worksheet to the WebMaking Web-Based Worksheets InteractiveUsing the Internet as a Data SourceOpening Web Pages as WorkbooksUsing Web QueriesParsing Web Pages for Specific InformationUsing the Internet to Publish ResultsSetting Up a Web ServerSaving Worksheets as Web PagesAdding Interactivity with the Web ComponentsCommunicating with a Web ServerUsing Internet Solutions with Excel
26. Excel and the Extensible Markup Language (XML)
Introducing Data ListsCreating Data Lists ProgrammaticallyCreating XML SchemasCreating XML Data FilesAdding XML to a Workbook ManuallyImporting XML Data ManuallyAdding XML to a Worksheet ProgrammaticallyMapping a Schema to a Worksheet ProgrammaticallyMapping Schema Elements to Cells Using XPath
About the Authors
Index
About the Authors
Copyright

Content preview from Microsoft® Office Excel 2003 Programming Inside Out

Chapter 5. Creating Sub and Function Procedures

Defining Sub Procedures

Defining Function Procedures

Organizing for Success

The first several chapters of this book, but particularly Chapter 4, used sample procedures to illustrate how you can use Visual Basic for Applications (VBA) to make Microsoft Excel do your bidding. The goal in Chapter 4 was to introduce the programming constructs you can use to create and control how your VBA code operates, whether that means repeating a bit of code a set number of times using a For...Next loop, operating on every member of a collection using a For Each...Next loop, or repeating the code until a condition is met. There was a lot of hand waving at that stage when it came to putting your code into a usable package, ...

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

Publisher Resources

ISBN: 0735619859Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Office Excel 2003 Programming Inside Out

by Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Chapter 5. Creating Sub and Function Procedures

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.