book

Excel® 2010 Power Programming with VBA

Name: Excel® 2010 Power Programming with VBA
Author: John Walkenbach
ISBN: 9780470475355

by John Walkenbach

April 2010

Intermediate to advanced

1080 pages

23h 37m

English

Wiley

Read now

Unlock full access

Cover
Table of Contents
Title Page
Introduction
Part I: Some Essential Background
Chapter 1: Excel 2010: Where It Came From
A Brief History of SpreadsheetsWhy Excel Is Great for DevelopersExcel's Role in Microsoft's Strategy
Chapter 2: Excel in a Nutshell
Thinking in Terms of ObjectsWorkbooksExcel's User InterfaceCustomizing the DisplayData EntryFormulas, Functions, and NamesSelecting ObjectsFormattingProtection OptionsChartsShapes and SmartArtDatabase Access Internet FeaturesAnalysis ToolsAdd-InsMacros and ProgrammingFile FormatExcel's Help System
Chapter 3: Formula Tricks and Techniques
About FormulasCalculating FormulasCell and Range ReferencesUsing NamesFormula ErrorsArray FormulasCounting and Summing TechniquesWorking with Dates and TimesCreating Megaformulas
Chapter 4: Understanding Excel Files
Starting ExcelFile TypesWorking with Template FilesInside an Excel FileThe OfficeUI FileThe XLB FileAdd-In FilesExcel Settings in the Registry
Part II: Excel Application Development

Chapter 5: What Is a Spreadsheet Application?
Spreadsheet ApplicationsThe Developer and the End UserSolving Problems with ExcelBasic Spreadsheet Types
Chapter 6: Essentials of Spreadsheet Application Development
Steps for Application DevelopmentDetermining User NeedsPlanning an Application That Meets User NeedsDetermining the Most Appropriate User InterfaceConcerning Yourself with the End UserOther Development Issues
Part III: Understanding Visual Basic for Applications
Chapter 7: Introducing Visual Basic for Applications
Getting Some BASIC BackgroundDelving in to VBACovering the Basics of VBAIntroducing the Visual Basic EditorWorking with the Project ExplorerWorking with Code WindowsCustomizing the VBE EnvironmentThe Macro RecorderAbout Objects and CollectionsProperties and MethodsThe Comment Object: A Case StudySome Useful Application PropertiesWorking with Range ObjectsThings to Know about Objects
Chapter 8: VBA Programming Fundamentals
VBA Language Elements: An OverviewCommentsVariables, Data Types, and ConstantsAssignment StatementsArraysObject VariablesUser-Defined Data TypesBuilt-in FunctionsManipulating Objects and CollectionsControlling Code Execution
Chapter 9: Working with VBA Sub Procedures
About ProceduresExecuting Sub ProceduresPassing Arguments to ProceduresError-Handling TechniquesA Realistic Example That Uses Sub Procedures
Chapter 10: Creating Function Procedures
Sub Procedures versus Function ProceduresWhy Create Custom Functions?An Introductory Function ExampleFunction ProceduresFunction ArgumentsFunction ExamplesEmulating Excel's SUM functionExtended Date FunctionsDebugging FunctionsDealing with the Insert Function Dialog BoxUsing Add-ins to Store Custom FunctionsUsing the Windows API
Chapter 11: VBA Programming Examples and Techniques
Learning by ExampleWorking with RangesWorking with Workbooks and SheetsVBA TechniquesSome Useful Functions for Use in Your CodeSome Useful Worksheet FunctionsWindows API Calls
Part IV: Working with UserForms
Chapter 12: Custom Dialog Box Alternatives
Before You Create That UserForm . . .Using an Input BoxThe VBA MsgBox FunctionThe Excel GetOpenFilename MethodThe Excel GetSaveAsFilename MethodPrompting for a DirectoryDisplaying Excel's Built-In Dialog BoxesDisplaying a Data Form
Chapter 13: Introducing UserForms
How Excel Handles Custom Dialog BoxesInserting a New UserFormAdding Controls to a UserFormToolbox ControlsAdjusting UserForm ControlsAdjusting a Control's PropertiesDisplaying a UserFormClosing a UserFormCreating a UserForm: An ExampleUnderstanding UserForm EventsReferencing UserForm ControlsCustomizing the ToolboxCreating UserForm TemplatesA UserForm Checklist
Chapter 14: UserForm Examples
Creating a UserForm “Menu”Selecting Ranges from a UserFormCreating a Splash ScreenDisabling a UserForm's Close ButtonChanging a UserForm's SizeZooming and Scrolling a Sheet from a UserFormListBox TechniquesUsing the MultiPage Control in a UserFormUsing an External ControlAnimating a Label
Chapter 15: Advanced UserForm Techniques
A Modeless Dialog BoxDisplaying a Progress IndicatorCreating WizardsEmulating the MsgBox FunctionA UserForm with Movable ControlsA UserForm with No Title BarSimulating a Toolbar with a UserFormA Resizable UserFormHandling Multiple UserForm Controls with One Event HandlerSelecting a Color in a UserFormDisplaying a Chart in a UserFormMaking a UserForm SemitransparentAn Enhanced Data FormA Puzzle on a UserFormVideo Poker on a UserForm
Part V: Advanced Programming Techniques
Chapter 16: Developing Excel Utilities with VBA
About Excel UtilitiesUsing VBA to Develop UtilitiesWhat Makes a Good Utility?Text Tools: The Anatomy of a UtilityMore about Excel Utilities
Chapter 17: Working with Pivot Tables
An Introductory Pivot Table ExampleCreating a More Complex Pivot TableCreating Multiple Pivot TablesCreating a Reverse Pivot Table
Chapter 18: Working with Charts
Getting the Inside Scoop on ChartsCreating an Embedded ChartCreating a Chart on a Chart SheetUsing VBA to Activate a ChartMoving a ChartUsing VBA to Deactivate a ChartDetermining Whether a Chart Is ActivatedDeleting from the ChartObjects or Charts CollectionLooping through All ChartsSizing and Aligning ChartObjectsExporting a ChartChanging the Data Used in a ChartUsing VBA to Display Arbitrary Data Labels on a ChartDisplaying a Chart in a UserFormUnderstanding Chart EventsDiscovering VBA Charting TricksAnimating ChartsCreating an Interactive Chart without VBAWorking with Sparkline Charts
Chapter 19: Understanding Excel's Events
What You Should Know about EventsGetting Acquainted with Workbook-Level EventsExamining Worksheet EventsChecking Out Chart EventsMonitoring with Application EventsUsing UserForm EventsAccessing Events Not Associated with an Object
Chapter 20: Interacting with Other Applications
Starting an Application from ExcelActivating an Application with ExcelRunning Control Panel Dialog BoxesUsing Automation in ExcelSending Personalized E-Mail via OutlookSending E-Mail Attachments from ExcelUsing SendKeys
Chapter 21: Creating and Using Add-Ins
What Is an Add-In?Understanding Excel's Add-In ManagerCreating an Add-inAn Add-In ExampleComparing XLAM and XLSM FilesManipulating Add-Ins with VBAOptimizing the Performance of Add-insSpecial Problems with Add-Ins
Part VI: Developing Applications
Chapter 22: Working with the Ribbon
Ribbon BasicsUsing VBA with the RibbonCustomizing the RibbonCreating an Old-Style Toolbar
Chapter 23: Working with Shortcut Menus
CommandBar OverviewUsing VBA to Customize Shortcut MenusShortcut Menus and Events
Chapter 24: Providing Help for Your Applications
Help for Your Excel ApplicationsHelp Systems That Use Excel ComponentsDisplaying Help in a Web BrowserUsing the HTML Help SystemAssociating a Help File with Your Application
Chapter 25: Developing User-Oriented Applications
What is a User-Oriented Application?The Loan Amortization WizardApplication Development Concepts
Part VII: Other Topics
Chapter 26: Compatibility Issues
What Is Compatibility?Types of Compatibility ProblemsAvoid Using New FeaturesBut Will It Work on a Mac?Dealing with 64-bit ExcelCreating an International Application
Chapter 27: Manipulating Files with VBA
Performing Common File OperationsDisplaying Extended File InformationWorking with Text FilesText File Manipulation ExamplesZipping and Unzipping FilesWorking with ADO
Chapter 28: Manipulating Visual Basic Components
Introducing the IDEThe IDE Object ModelDisplaying All Components in a VBA ProjectListing All VBA Procedures in a WorkbookReplacing a Module with an Updated VersionUsing VBA to Write VBA CodeAdding Controls to a UserForm at Design TimeCreating UserForms Programmatically
Chapter 29: Understanding Class Modules
What is a Class Module?Example: Creating a NumLock ClassMore about Class ModulesExample: A CSV File Class
Chapter 30: Working with Colors
Specifying ColorsUnderstanding GrayscaleExperimenting with ColorsUnderstanding Document ThemesWorking with Shape ObjectsModifying Chart Colors
Chapter 31: Frequently Asked Questions about Excel Programming
Getting the Scoop on FAQsGeneral Excel QuestionsThe Visual Basic EditorProceduresFunctionsObjects, Properties, Methods, and EventsUserFormsAdd-InsUser Interface
Part VIII: Appendixes
Appendix A: Excel Resources Online
The Excel Help SystemMicrosoft Technical SupportInternet NewsgroupsInternet Web sites
Appendix B: VBA Statements and Functions Reference
Invoking Excel functions in VBA instructions
Appendix C: VBA Error Codes
Appendix D: What's on the CD-ROM
System RequirementsUsing the CDFiles and Software on the CDTroubleshooting

Content preview from Excel® 2010 Power Programming with VBA

Appendix B: VBA Statements and Functions Reference

This appendix contains a complete listing of all Visual Basic for Applications (VBA) statements and built-in functions. For details, consult Excel's online help.

There are no new VBA statements in Excel 2010.

Table B-1: Summary of VBA Statements

Statement	Action
AppActivate	Activates an application window
Beep	Sounds a tone via the computer's speaker
Call	Transfers control to another procedure
ChDir	Changes the current directory
ChDrive	Changes the current drive
Close	Closes a text file
Const	Declares a constant value
Date	Sets the current system date
Declare	Declares a reference to an external procedure in a Dynamic Link Library (DLL)
DefBool	Sets the default data type to Boolean for variables that begin with specified letters
DefByte	Sets the default data type to Byte for variables that begin with specified letters
DefCur	Sets the default data type to Currency for variables that begin with specified letters
DefDate	Sets the default data type to Date for variables that begin with specified letters
DefDec	Sets the default data type to Decimal for variables that begin with specified letters
DefDbl	Sets the default data type to Double for variables that begin with specified letters
DefInt	Sets the default data type to Integer

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: 9780470475355Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel® 2010 Power Programming with VBA

by John Walkenbach

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.