book

Excel VBA Programming For Dummies, 6th Edition

by Dick Kusleika

February 2022

Beginner to intermediate

432 pages

9h 21m

English

For Dummies

Read now

Unlock full access

Copyright
About This BookFoolish AssumptionsIcons Used in This BookBeyond the BookWhere to Go from Here
Understanding VBA BasicsKnowing What VBA Can DoGetting the Most from VBAUnderstanding VBA ConceptsEnsuring Excel Compatibility
Displaying the Developer TabCreating a MacroPreparing the EnvironmentRecording a MacroRunning the MacroViewing a Macro in the Visual Basic EditorModifying the MacroSaving Workbooks That Contain MacrosUnderstanding Macro Security
Getting to Know the Visual Basic EditorWorking with the Project ExplorerWorking with a Code PaneCustomizing the VBE
Working with the Excel Object ModelDiving into Object Properties and MethodsFinding Out More from VBA Resources
Understanding Subs versus FunctionsNaming Subs and FunctionsExecuting Sub proceduresExecuting Function Procedures

Recording BasicsPreparing to RecordChoosing Between Relative and Absolute ModeWatching the Macro Recorder in ActionSpecifying Recording Options for Your MacroStreamlining Code Generated by the Macro Recorder
Using Comments in Your VBA CodeUsing Variables, Constants, and Data TypesUsing Assignment StatementsWorking with ArraysUsing Labels
Referring to Range ObjectsReferring to a Range Using PropertiesWorking with Range Object PropertiesTaking Action with Range Object Methods
Understanding FunctionsUsing 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 EventKnowing Where to Put the Event CodeWriting an Event-Handler ProcedureTriggering Workbook EventsUsing Activation EventsProgramming Worksheet-Related EventsUnderstanding Events Not Associated with Objects
Types of ErrorsAn Erroneous Macro ExampleAlternate Ways of Handling ErrorsHandling Errors: The DetailsAn Intentional Error
Species of BugsIdentifying BugsDebugging TechniquesUsing the Debugger’s ToolsBug Reduction Tips
Working with RangesChanging Excel SettingsWorking with ChartsVBA Speed Tips
Interacting with the User in VBADisplaying Messages with the MsgBox FunctionGetting Data with an Input BoxAllowing the User to Select a File or FolderDisplaying Excel’s Built-In Dialog Boxes
Knowing When to Use a UserFormCreating UserForms: An OverviewWorking with UserFormsA UserForm Example
Getting Started with Dialog Box ControlsLearning Dialog Box Controls DetailsWorking with Dialog Box ControlsDialog Box Aesthetics
Using Dialog BoxesA UserForm ExampleA ListBox Control ExampleSelecting a RangeUsing Multiple Sets of Option ButtonsUsing a Spin Button and a Text BoxUsing a UserForm as a Progress IndicatorCreating a Modeless Tabbed Dialog BoxDisplaying a Chart in a UserFormA Dialog Box Checklist
Customizing the RibbonCustomizing the Excel UI with VBA
Create Custom Functions to Simplify Your WorkUnderstanding VBA Function BasicsWriting FunctionsWorking with Function ArgumentsIntroducing Wrapper FunctionsWorking with Functions That Return an ArrayUsing the Insert Function Dialog Box
Add-Ins DefinedReasons to Create Add-InsWorking 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 YouReferencing the Help SystemPilfering 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

Content preview from Excel VBA Programming For Dummies, 6th Edition

Index

A

Abs function, 142
absolute mode, macro recorder, 86–87
Accelerator property, UserForm controls, 284
Activate event, 173
activation events, 181–184
active objects, 15
Add Watch dialog box, 215
add-ins
- adding description, 372–373
- creating, 374
- customizing with macros, 12
- defined, 365–366
- distributing, 375
- modifying, 375–376
- opening, 374–375
- overview, 368–369
- protecting VBA code, 373
- reasons to create, 366–367
- setting up workbook, 369–372
- testing workbook, 372
- working with, 367–368
Add-ins Ribbon tab, adding commands to, 338–339
AddItem method, ListBox control, 310
Addition operator, 114
Address property, Range objects, 126–127
And operator, 115
Application object, 53–56
arguments
- defined, 60
- GetOpenFilename statement, 257
- GetSaveAsFilename method, 259
- worksheet functions
  - no-argument functions, 349
  - optional arguments, 354–356
  - overview, 348
  - range argument, 352–354
  - single-argument function, 349–351
  - two-argument function, 351–352
Array function, 142
arrays
- declaring, 116–117
- defined, 116
- dynamic, 117–118
- MonthNames function, 359–360
- multidimensional, 117
- SORT function, 360–361
assignment statements
- equal sign operator, 114
- logical operators, 115
- operator precedence, 114–115
- overview, 113–114
- smooth operators, 114–116
Auto Data Tips option, VBE Editor tab, 46
Auto Indent setting, VBE Editor tab, 46
Auto List Members feature, VBE, 45, 64–65
Auto Quick Info option, VBE, 46
Auto Syntax Check, VBE, 44, 387–388
automatic event procedures. See events
AutoSize property, ...