Excel VBA Programming For Dummies
, by Kusleika, Dick- ISBN: 9781119843078 | 1119843073
- Cover: Paperback
- Copyright: 2/2/2022
Short of changing the tires on your car, Microsoft Excel can do pretty much anything. And the possibilities are even more endless when you learn to program with Excel Visual Basic for Applications (VBA). Regardless of your familiarity with Excel VBA, Excel VBA Programming For Dummies can enhance your experience with the popular spreadsheet software.
Pretty soon, you'll be doing things you didn't think were possible in Excel, from automating processes to writing your own worksheet functions. You'll learn how to:
- Understand the basic tools and operations of Visual Basic for Applications
- Create custom spreadsheet functions that make life easier for you and the people maintaining your spreadsheets
- Deal with errors and exceptions and eliminate the bugs in your code
Perfect for anyone who's never even heard of Excel VBA, Excel VBA Programming For Dummies is also a fantastic resource for intermediate and advanced Excel users looking for a heads-up on the latest features and newest functionality of this simple yet powerful scripting language.
Dick Kusleika has over 25 years’ experience helping Office users get the most out of Microsoft’s bestselling software. From online forums to blogs, books, and conferences, he delivers sound and straightforward advice to readers of all skill levels.
Introduction 1
About This Book 1
Typographical conventions 2
Macro security 3
Foolish Assumptions 5
Icons Used in This Book 5
Beyond the Book 6
Where to Go from Here 6
Part 1: Starting Excel VBA Programming 7
Chapter 1: Getting to Know VBA 9
Understanding VBA Basics 9
Knowing What VBA Can Do 10
Inserting a bunch of text 11
Automating a task you perform frequently 11
Automating repetitive operations 11
Creating a custom command 11
Creating a custom button 12
Developing new worksheet functions 12
Creating custom add-ins for Excel 12
Getting the Most from VBA 12
Knowing what VBA does best 12
Recognizing the disadvantages of using VBA 13
Understanding VBA Concepts 14
Ensuring Excel Compatibility 16
Chapter 2: Building Simple Macros 17
Displaying the Developer Tab 17
Creating a Macro 18
Preparing the Environment 18
Recording a Macro 19
Running the Macro 21
Viewing a Macro in the Visual Basic Editor 22
Modifying the Macro 24
Saving Workbooks That Contain Macros 25
Understanding Macro Security 25
Part 2: Employing VBA with Excel 29
Chapter 3: Working in the Visual Basic Editor 31
Getting to Know the Visual Basic Editor 31
Activating the VBE 32
Exploring VBE components 32
Working with the Project Explorer 34
Adding a new VBA module 35
Working with a Code Pane 36
Minimizing and maximizing windows 36
Looking at the parts of a module 37
Getting VBA code into a module 38
Entering code directly 38
Using the macro recorder 41
Copying VBA code 44
Customizing the VBE 44
Using the Editor tab 44
Using the Editor Format tab 47
Using the General tab 48
Using the Docking tab 48
Chapter 4: Introducing the Excel Object Model 51
Working with the Excel Object Model 51
Understanding the object hierarchy 52
Referring to objects 53
Diving into Object Properties and Methods 57
Setting object properties 58
Taking action with object methods 60
Triggering actions with object events 61
Finding Out More from VBA Resources 62
Using VBA’s Help system 62
Using the Object Browser 63
Automatically listing properties and methods 64
Chapter 5: VBA Sub and Function Procedures 67
Understanding Subs versus Functions 67
Looking at Sub procedures 68
Looking at Function procedures 68
Naming Subs and Functions 69
Executing Sub procedures 70
Executing the Sub procedure directly 72
Executing the procedure from the Macro dialog box 73
Executing a macro by using a shortcut key 74
Executing the procedure from a button or shape 75
Executing the procedure from another procedure 77
Executing the procedure from the Immediate window 77
Executing Function Procedures 78
Calling the function from a Sub procedure 78
Calling a function from the Immediate window 79
Calling a function from a worksheet formula 80
Chapter 6: Using the Excel Macro Recorder 83
Recording Basics 84
Preparing to Record 85
Choosing Between Relative and Absolute Mode 86
Recording in absolute mode 86
Recording in relative mode 87
Watching the Macro Recorder in Action 89
Specifying Recording Options for Your Macro 91
Streamlining Code Generated by the Macro Recorder 92
Part 3: Programming Concepts 97
Chapter 7: Essential VBA Language Elements 99
Using Comments in Your VBA Code 99
Using Variables, Constants, and Data Types 101
Understanding variables 101
What are VBA’s data types? 103
Declaring and scoping variables 103
Working with constants 110
Using premade constants 110
Working with strings 111
Working with dates 112
Using Assignment Statements 113
Assignment statement examples 113
About that equal sign 114
Smooth operators 114
Working with Arrays 116
Declaring arrays 116
Multidimensional arrays 117
Dynamic arrays 117
Using Labels 118
Chapter 8: Working with Range Objects 119
Referring to Range Objects 119
Referring to a Range Using Properties 121
The Cells property 122
The Offset property 122
The Resize property 123
Working with Range Object Properties 124
The Value property 124
The Text property 125
The Count property 126
The Column and Row properties 126
The Address property 126
The HasFormula property 127
The Font property 128
The Interior property 128
The Formula property 130
The NumberFormat property 131
Taking Action with Range Object Methods 131
The Select method 132
The Copy and Paste methods 132
The Clear method 133
The Delete method 133
Chapter 9: Using VBA and Worksheet Functions 135
Understanding Functions 135
Using Built-In VBA Functions 136
Working with dates and times 136
Manipulating strings 138
Identifying objects and data 139
VBA functions that do more than return a value 140
Discovering VBA functions 141
Using Worksheet Functions in VBA 144
Worksheet function examples 144
Entering worksheet functions 147
More about using worksheet functions 148
Using Custom Functions 148
Chapter 10: Controlling Program Flow and Making Decisions 151
Going with the Flow, Dude 151
The GoTo Statement 152
Decisions, Decisions 154
The If-Then structure 154
The Select Case structure 158
Knocking Your Code for a Loop 162
For-Next loops 162
Do-While loop 167
Do-Until loop 168
Using For Each-Next Loops with Collections 168
Chapter 11: Automatic Procedures and Events 171
Preparing for the Big Event 171
Learning when to use event procedures 173
Programming event-handler procedures 174
Knowing Where to Put the Event Code 174
Writing an Event-Handler Procedure 175
Triggering Workbook Events 177
The Open event for a workbook 177
The BeforeClose event for a workbook 179
The BeforeSave event for a workbook 180
Using Activation Events 181
Activate and deactivate events in a sheet 181
Activate and deactivate events in a workbook 182
Workbook activation events 184
Programming Worksheet-Related Events 185
The BeforeDoubleClick event 185
The BeforeRightClick event 185
The Change event 186
Understanding Events Not Associated with Objects 188
The OnTime event 188
Keypress events 190
Chapter 12: Error-Handling Techniques 193
Types of Errors 193
An Erroneous Macro Example 194
Not-quite-perfect macros 195
Perfecting the macro 196
Giving up on perfection 198
Alternate Ways of Handling Errors 198
Revisiting the EnterSquareRoot procedure 198
Trapping errors with the On Error statement 200
Handling Errors: The Details 200
Resuming after an error 200
Error handling in a nutshell 202
An Intentional Error 203
Chapter 13: Bug Extermination Techniques 205
Species of Bugs 205
Identifying Bugs 207
Debugging Techniques 208
Examining your code 208
Using the MsgBox function 208
Inserting Debug.Print statements 210
Using the VBA debugger 210
Using the Debugger’s Tools 211
Setting breakpoints in your code 211
Using the Watches window 214
Using the Locals window 216
Bug Reduction Tips 217
Chapter 14: VBA Programming Examples 219
Working with Ranges 220
Copying a range 220
Copying a variable-size range 221
Selecting to the end of a row or column 223
Selecting a row or column 224
Moving a range 224
Looping through a range efficiently 225
Looping through a range efficiently (Part II) 227
Prompting for a cell value 227
Determining the selection type 228
Identifying a multiple selection 229
Changing Excel Settings 230
Changing Boolean settings 230
Changing non-Boolean settings 231
Working with Charts 231
AddChart versus AddChart2 232
Modifying the chart type 234
Looping through the ChartObjects collection 234
Modifying chart properties 235
Applying chart formatting 235
VBA Speed Tips 237
Turning off screen updating 237
Turning off automatic calculation 238
Eliminating those pesky alert messages 239
Simplifying object references 240
Declaring variable types 241
Using the With-End With structure 242
Part 4: Communicating with Your Users 243
Chapter 15: Simple Dialog Boxes 245
Interacting with the User in VBA 246
Displaying Messages with the MsgBox Function 247
Displaying a simple message box 247
Getting a response from a message box 248
Customizing message boxes 250
Getting Data with an Input Box 252
Understanding the InputBox syntax 253
Using the InputBox function 253
Using the InputBox method 255
Allowing the User to Select a File or Folder 256
Constructing a GetOpenFilename statement 256
Selecting a file with GetOpenFilename 256
Picking a file with GetSaveAsFilename 259
Getting a folder name 259
Displaying Excel’s Built-In Dialog Boxes 260
Chapter 16: UserForm Basics 263
Knowing When to Use a UserForm 263
Creating UserForms: An Overview 265
Working with UserForms 266
Inserting a new UserForm 266
Adding controls to a UserForm 267
Changing properties for a UserForm control 268
Viewing the UserForm Code pane 269
Displaying a UserForm 269
Using information from a UserForm 270
A UserForm Example 271
Creating the UserForm 271
Adding the CommandButtons 272
Adding the OptionButtons 272
Adding event-handler procedures 275
Creating a macro to display the dialog box 277
Making the macro available 277
Testing the macro 278
Chapter 17: Using UserForm Controls 281
Getting Started with Dialog Box Controls 281
Adding controls 282
Introducing control properties 283
Learning Dialog Box Controls Details 285
CheckBox control 285
ComboBox control 286
CommandButton control 287
Frame control 288
Image control 288
Label control 289
ListBox control 289
MultiPage control 291
OptionButton control 292
RefEdit control 292
ScrollBar control 293
SpinButton control 294
TabStrip control 295
TextBox control 295
ToggleButton control 296
Working with Dialog Box Controls 296
Moving and resizing controls 297
Aligning and spacing controls 297
Accommodating keyboard users 297
Testing a UserForm 300
Dialog Box Aesthetics 300
Chapter 18: UserForm Techniques and Tricks 301
Using Dialog Boxes 302
A UserForm Example 302
Creating the dialog box 302
Writing code to display the dialog box 305
Making the macro available 305
Trying out your dialog box 306
Adding event-handler procedures 307
Validating the data 309
Now the dialog box works 309
A ListBox Control Example 309
Filling a ListBox Control 310
Determining the selected item 312
Determining multiple selections 313
Selecting a Range 314
Using Multiple Sets of Option Buttons 316
Using a Spin Button and a Text Box 317
Using a UserForm as a Progress Indicator 319
Creating the progress-indicator dialog box 320
The procedures 321
How this example works 322
Creating a Modeless Tabbed Dialog Box 323
Displaying a Chart in a UserForm 325
A Dialog Box Checklist 326
Chapter 19: Accessing Your Macros through the User Interface 329
Customizing the Ribbon 329
Customizing the Ribbon manually 330
Adding a macro to the Ribbon 332
Customizing the Ribbon with XML 333
Customizing the Excel UI with VBA 337
Adding commands to the Add-ins Ribbon tab 338
Adding a new item to the Cell shortcut menu 339
Adding customizations automatically 340
Understanding shortcut menus and the single document interface 341
Part 5: Putting It All Together 343
Chapter 20: Creating Worksheet Functions 345
Create Custom Functions to Simplify Your Work 345
Understanding VBA Function Basics 347
Writing Functions 347
Working with Function Arguments 348
A function with no argument 349
A function with one argument 349
A function with two arguments 351
A function with a range argument 352
A function with an optional argument 354
Introducing Wrapper Functions 356
The NumberFormat function 356
The ExtractElement function 357
The SayIt function 358
The IsLike function 358
Working with Functions That Return an Array 359
Returning an array of month names 359
Returning a sorted list 360
Using the Insert Function Dialog Box 362
Displaying the function’s description 362
Adding argument descriptions 364
Chapter 21: Creating Excel Add-Ins 365
Add-Ins Defined 365
Reasons to Create Add-Ins 366
Working with Add-Ins 367
Understanding Add-In Basics 368
Looking at an Add-In Example 369
Setting up the workbook 369
Testing the workbook 372
Adding descriptive information 372
Protecting the VBA code 373
Creating the add-in 374
Opening the add-in 374
Distributing the add-in 375
Modifying the add-in 375
Part 6: The Part of Tens 377
Chapter 22: Ten Handy Visual Basic Editor Tips 379
Applying Block Comments 380
Copying Multiple Lines of Code at Once 381
Jumping between Modules and Procedures 382
Teleporting to Your Functions 382
Staying in the Right Procedure 383
Stepping through Your Code 383
Stepping to a Specific Line in Your Code 384
Stopping Your Code at a Predefined Point 385
Seeing the Beginning and End of Variable Values 386
Turning Off Auto Syntax Check 387
Chapter 23: Resources for VBA Help 389
Letting Excel Write Code for You 390
Referencing the Help System 390
Pilfering Code from the Internet 390
Leveraging User Forums 391
Visiting Expert Blogs 392
Mining YouTube for Video Training 393
Attending Live and Online Training Classes 393
Learning from the Microsoft Office Dev Center 393
Dissecting the Other Excel Files in Your Organization 394
Asking Your Local Excel Guru 394
Chapter 24: Ten VBA Do’s and Don’ts 395
Do Declare All Variables 395
Don’t Confuse Passwords with Security 396
Do Clean Up Your Code 396
Don’t Put Everything in One Procedure 397
Do Consider Other Software 397
Don’t Assume That Everyone Enables Macros 397
Do Get in the Habit of Experimenting 398
Don’t Assume That Your Code Will Work with Other Excel Versions 398
Do Keep Your Users in Mind 399
Don’t Forget about Backups 399
Index 401
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.