book

Excel VBA Programming For Dummies, 5th Edition

by Michael Alexander, John Walkenbach

November 2018

Intermediate to advanced

416 pages

8h 52m

English

For Dummies

Read now

Unlock full access

About This BookObligatory Typographical Conventions SectionCheck Your Security SettingsFoolish AssumptionsIcons Used in This BookSample Files OnlineWhere to Go from Here
Okay, So What Is VBA?What Can You Do with VBA?Advantages and Disadvantages of VBAVBA in a NutshellExcel Compatibility
First Things FirstWhat You’ll Be DoingTaking the First StepsRecording the MacroTesting the MacroExamining the MacroModifying the MacroSaving Workbooks That Contain MacrosUnderstanding Macro SecurityRevealing More about the NameAndTime Macro
What Is the Visual Basic Editor?Working with the Project WindowWorking with a Code WindowCustomizing the VBA Environment
Excel Is an Object?Climbing Down the Object HierarchyWrapping Your Mind around CollectionsReferring to ObjectsDiving into Object Properties and MethodsFinding Out More
Understanding Subs versus FunctionsNaming Subs and Functions
Recording BasicsPreparing to RecordRelative or Absolute?What Gets Recorded?Recording OptionsIs This Thing Efficient?

Using Comments in Your VBA CodeUsing Variables, Constants, and Data TypesUsing Assignment StatementsWorking with ArraysUsing Labels
A Quick ReviewOther Ways to Refer to a RangeSome Useful Range Object PropertiesSome Useful Range Object Methods
What Is a Function?Using Built-In VBA FunctionsUsing Worksheet Functions in VBAUsing Custom Functions
Going with the Flow, DudeThe GoTo StatementDecisions, DecisionsKnocking Your Code for a LoopUsing For Each-Next Loops with Collections
Preparing for the Big EventWhere Does the VBA Code Go?Writing an Event-Handler ProcedureIntroductory ExamplesExamples of Activation EventsOther Worksheet-Related EventsEvents Not Associated with Objects
Types of ErrorsAn Erroneous ExampleHandling Errors Another WayHandling Errors: The DetailsAn Intentional Error
Species of BugsIdentifying BugsDebugging TechniquesAbout the DebuggerBug Reduction Tips
Working with RangesChanging Excel SettingsWorking with ChartsVBA Speed Tips
UserForm AlternativesThe MsgBox FunctionThe InputBox FunctionThe GetOpenFilename MethodThe GetSaveAsFilename MethodGetting a Folder NameDisplaying Excel’s Built-in Dialog Boxes
Knowing When to Use a UserFormCreating UserForms: An OverviewWorking with UserFormsA UserForm Example
Getting Started with Dialog Box ControlsDialog Box Controls: The DetailsWorking with Dialog Box ControlsDialog Box Aesthetics
Using Dialog BoxesA UserForm ExampleA ListBox ExampleSelecting a RangeUsing Multiple Sets of OptionButtonsUsing a SpinButton and a TextBoxUsing a UserForm as a Progress IndicatorCreating a Modeless Tabbed Dialog BoxDisplaying a Chart in a UserFormA Dialog Box Checklist
Customizing the RibbonCustomizing Shortcut Menus
Why Create Custom Functions?Understanding VBA Function BasicsWriting FunctionsWorking with Function ArgumentsIntroducing Wrapper FunctionsWorking with Functions That Return an ArrayUsing the Insert Function Dialog Box
Okay … So What’s an Add-In?Why Create Add-Ins?Working with Add-InsUnderstanding Add-In BasicsLooking at an Add-In Example
Applying Block CommentsCopying Multiple Lines of Code at OnceJumping between Modules and ProceduresTeleporting to Your FunctionsStaying in the Right ProcedureStepping Through Your CodeStepping to a Specific Line in Your CodeStopping Your Code at a Predefined PointSeeing the Beginning and End of Variable ValuesTurning Off Auto Syntax Check
Letting Excel Write Code for YouPilfering Code from the InternetLeveraging User ForumsVisiting Expert BlogsMining YouTube for Video TrainingAttending Live and Online Training ClassesLearning from the Microsoft Office Dev CenterDissecting the Other Excel Files in Your OrganizationAsking Your Local Excel Guru
Do Declare All VariablesDon’t Confuse Passwords with SecurityDo Clean Up Your CodeDon’t Put Everything in One ProcedureDo Consider Other SoftwareDon’t Assume That Everyone Enables MacrosDo Get in the Habit of ExperimentingDon’t Assume That Your Code Will Work with Other Excel VersionsDo Keep Your Users in MindDon’t Forget About Backups

Overview

Take your Excel programming skills to the next level

To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools and operations for the Visual Basic Editor.

Inside, you’ll find an overview of the essential elements and concepts for programming with Excel. In no time, you’ll discover techniques for handling errors and exterminating bugs, working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, readers will be creating Excel applications custom fit to their unique needs!

Fully updated for the new Excel 2019
Step-by-step instructions for creating VBA macros to maximize productivity
Guidance on customizing your applications so they work the way you want
All sample programs, VBA code, and worksheets are available at dummies.com

Beginning VBA programmers rejoice! This easy-to-follow book makes it easier than ever to excel at Excel VBA!

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.

Start your free trial

Excel VBA Programming For Dummies, 3rd Edition

Publisher Resources

ISBN: 9781119518174Purchase book

Excel VBA Programming For Dummies, 5th Edition

by Michael Alexander, John Walkenbach

Overview

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.

You might also like

Excel VBA Programming For Dummies, 3rd Edition