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!
Auflage
Sprache
Verlagsort
Verlagsgruppe
Maße
Höhe: 234 mm
Breite: 188 mm
Dicke: 20 mm
Gewicht
ISBN-13
978-1-119-51817-4 (9781119518174)
Schweitzer Klassifikation
Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at www.datapigtechnologies.com.
Introduction 1
About This Book 1
Obligatory Typographical Conventions Section 2
Check Your Security Settings 3
Foolish Assumptions 4
Icons Used in This Book 5
Sample Files Online 5
Where to Go from Here 6
Part 1: Getting Started with Excel VBA Programming 7
Chapter 1: What Is VBA? 9
Okay, So What Is VBA? 9
What Can You Do with VBA? 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
Advantages and Disadvantages of VBA 12
VBA advantages 12
VBA disadvantages 13
VBA in a Nutshell 13
Excel Compatibility 16
Chapter 2: Jumping Right In 17
First Things First 17
What You'll Be Doing 18
Taking the First Steps 18
Recording the Macro 19
Testing the Macro 21
Examining the Macro 21
Modifying the Macro 23
Saving Workbooks That Contain Macros 24
Understanding Macro Security 24
Revealing More about the NameAndTime Macro 26
Part 2: How VBA Works with Excel 29
Chapter 3: Working in the Visual Basic Editor 31
What Is the Visual Basic Editor? 31
Activating the VBE 32
Understanding VBE components 32
Working with the Project Window 34
Adding a new VBA module 35
Removing a VBA module 36
Exporting and importing objects 36
Working with a Code Window 37
Minimizing and maximizing windows 37
Creating a module 38
Getting VBA code into a module 39
Entering code directly 39
Using the macro recorder 42
Copying VBA code 45
Customizing the VBA Environment 45
Using the Editor tab 45
Using the Editor Format tab 48
Using the General tab 49
Using the Docking tab 50
Chapter 4: Introducing the Excel Object Model 51
Excel Is an Object? 52
Climbing Down the Object Hierarchy 52
Wrapping Your Mind around Collections 53
Referring to Objects 54
Navigating through the hierarchy 55
Simplifying object references 56
Diving into Object Properties and Methods 56
Object properties 58
Object methods 59
Object events 61
Finding Out More 61
Using VBA's Help system 61
Using the Object Browser 62
Automatically listing properties and methods 63
Chapter 5: VBA Sub and Function Procedures 65
Understanding Subs versus Functions 65
Looking at Sub procedures 66
Looking at Function procedures 66
Naming Subs and Functions 67
Executing Sub procedures 68
Executing the Sub procedure directly 70
Executing the procedure from the Macro dialog box 70
Executing a macro by using a shortcut key 71
Executing the procedure from a button or shape 72
Executing the procedure from another procedure 74
Executing Function procedures 75
Calling the function from a Sub procedure 75
Calling a function from a worksheet formula 76
Chapter 6: Using the Excel Macro Recorder 79
Recording Basics 80
Preparing to Record 81
Relative or Absolute? 82
Recording in absolute mode 82
Recording in relative mode 83
What Gets Recorded? 84
Recording Options 86
Macro name 86
Shortcut key 87
Store Macro In option 87
Description 87
Is This Thing Efficient? 87
Part 3: Programming Concepts 91
Chapter 7: Essential VBA Language Elements 93
Using Comments in Your VBA Code 93
Using Variables, Constants, and Data Types 95
Understanding variables 95
What are VBA's data types? 97
Declaring and scoping variables 98
Working with constants 104
Premade constants 105
Working with strings 106
Working with dates 107
Using Assignment Statements 108
Assignment statement examples 108
About that equal sign 108
Smooth operators 109
Working with Arrays 111
Declaring arrays 111
Multidimensional arrays 112
Dynamic arrays 112
Using Labels 113
Chapter 8: Working with Range Objects 115
A Quick Review 115
Other Ways to Refer to a Range 117
The Cells property 117
The Offset property 118
Some Useful Range Object Properties 119
The Value property 120
The Text property 121
The Count property 121
The Column and Row properties 121
The Address property 122
The HasFormula property 122
The Font property 123
The Interior property 124
The Formula property 125
The NumberFormat property 126
Some Useful Range Object Methods 127
The Select method 127
The Copy and Paste methods 127
The Clear method 128
The Delete method 128
Chapter 9: Using VBA and Worksheet Functions 131
What Is a Function? 131
Using Built-In VBA Functions 132
VBA function examples 132
VBA functions that do more than return a value 135
Discovering VBA functions 135
Using Worksheet Functions in VBA 138
Worksheet function examples 139
Entering worksheet functions 141
More about using worksheet functions 142
Using Custom Functions 142
Chapter 10: Controlling Program Flow and Making Decisions 145
Going with the Flow, Dude 145
The GoTo Statement 146
Decisions, Decisions 148
The If-Then structure 148
The Select Case structure 152
Knocking Your Code for a Loop 155
For-Next loops 156
Do-While loop 161
Do-Until loop 161
Using For Each-Next Loops with Collections 161
Chapter 11: Automatic Procedures and Events 165
Preparing for the Big Event 165
Are events useful? 167
Programming event-handler procedures 168
Where Does the VBA Code Go? 168
Writing an Event-Handler Procedure 169
Introductory Examples 171
The Open event for a workbook 171
The BeforeClose event for a workbook 173
The BeforeSave event for a workbook 174
Examples of Activation Events 175
Activate and deactivate events in a sheet 175
Activate and deactivate events in a workbook 176
Workbook activation events 178
Other Worksheet-Related Events 178
The BeforeDoubleClick event 178
The BeforeRightClick event 179
The Change event 179
Events Not Associated with Objects 181
The OnTime event 182
Keypress events 183
Chapter 12: Error-Handling Techniques 187
Types of Errors 187
An Erroneous Example 188
The macro's not quite perfect 189
The macro is still not perfect 190
Is the macro perfect yet? 191
Giving up on perfection 192
Handling Errors Another Way 192
Revisiting the EnterSquareRoot procedure 192
About the On Error statement 194
Handling Errors: The Details 194
Resuming after an error 194
Error handling in a nutshell 196
Knowing when to ignore errors 197
Identifying specific errors 197
An Intentional Error 199
Chapter 13: Bug Extermination Techniques 201
Species of Bugs 201
Identifying Bugs 202
Debugging Techniques 203
Examining your code 204
Using the MsgBox function 204
Inserting Debug.Print statements 206
Using the VBA debugger 206
About the Debugger 207
Setting breakpoints in your code 207
Using the Watches window 210
Using the Locals window 211
Bug Reduction Tips 212
Chapter 14: VBA Programming Examples 213
Working with Ranges 214
Copying a range 214
Copying a variable-size range 215
Selecting to the end of a row or column 217
Selecting a row or column 218
Moving a range 218
Looping through a range efficiently 219
Looping through a range efficiently (Part II) 220
Prompting for a cell value 221
Determining the selection type 222
Identifying a multiple selection 223
Changing Excel Settings 223
Changing Boolean settings 224
Changing non-Boolean settings 225
Working with Charts 225
AddChart versus AddChart2 226
Modifying the chart type 228
Looping through the ChartObjects collection 228
Modifying chart properties 229
Applying chart formatting 229
VBA Speed Tips 231
Turning off screen updating 231
Turning off automatic calculation 232
Eliminating those pesky alert messages 233
Simplifying object references 233
Declaring variable types 234
Using the With-End With structure 235
Part 4: Communicating with Your Users 237
Chapter 15: Simple Dialog Boxes 239
UserForm Alternatives 239
The MsgBox Function 240
Displaying a simple message box 241
Getting a response from a message box 241
Customizing message boxes 243
The InputBox Function 246
InputBox syntax 246
An InputBox example 247
Another type of InputBox 248
The GetOpenFilename Method 249
The syntax for the GetOpenFilename method 250
A GetOpenFilename example 250
The GetSaveAsFilename Method 252
Getting a Folder Name 253
Displaying Excel's Built-in Dialog Boxes 254
Chapter 16: UserForm Basics 257
Knowing When to Use a UserForm 257
Creating UserForms: An Overview 258
Working with UserForms 259
Inserting a new UserForm 259
Adding controls to a UserForm 260
Changing properties for a UserForm control 261
Viewing the UserForm Code window 262
Displaying a UserForm 263
Using information from a UserForm 263
A UserForm Example 264
Creating the UserForm 264
Adding the CommandButtons 265
Adding the OptionButtons 266
Adding event-handler procedures 268
Creating a macro to display the dialog box 270
Making the macro available 270
Testing the macro 272
Chapter 17: Using UserForm Controls 275
Getting Started with Dialog Box Controls 275
Adding controls 276
Introducing control properties 276
Dialog Box Controls: The Details 279
CheckBox control 279
ComboBox control 280
CommandButton control 280
Frame control 281
Image control 281
Label control 282
ListBox control 283
MultiPage control 284
OptionButton control 285
RefEdit control 286
ScrollBar control 286
SpinButton control 287
TabStrip control 288
TextBox control 288
ToggleButton control 289
Working with Dialog Box Controls 289
Moving and resizing controls 290
Aligning and spacing controls 290
Accommodating keyboard users 291
Testing a UserForm 292
Dialog Box Aesthetics 293
Chapter 18: UserForm Techniques and Tricks 295
Using Dialog Boxes 296
A UserForm Example 296
Creating the dialog box 296
Writing code to display the dialog box 299
Making the macro available 299
Trying out your dialog box 300
Adding event-handler procedures 300
Validating the data 302
Now the dialog box works 303
A ListBox Example 303
Filling a ListBox 304
Determining the selected item 305
Determining multiple selections 306
Selecting a Range 308
Using Multiple Sets of OptionButtons 310
Using a SpinButton and a TextBox 311
Using a UserForm as a Progress Indicator 312
Creating the progress-indicator dialog box 313
The procedures 314
How this example works 316
Creating a Modeless Tabbed Dialog Box 316
Displaying a Chart in a UserForm 318
A Dialog Box Checklist 319
Chapter 19: Accessing Your Macros through the User Interface 321
Customizing the Ribbon 321
Customizing the Ribbon manually 322
Adding a macro to the Ribbon 324
Customizing the Ribbon with XML 325
Customizing Shortcut Menus 329
Adding a new item to the Cell shortcut menu 330
What's different since Excel 2007? 332
Part 5: Putting It All Together 333
Chapter 20: Creating Worksheet Functions - and Living to Tell about It 335
Why Create Custom Functions? 336
Understanding VBA Function Basics 337
Writing Functions 338
Working with Function Arguments 338
A function with no argument 339
A function with one argument 339
A function with two arguments 341
A function with a range argument 342
A function with an optional argument 344
Introducing Wrapper Functions 346
The NumberFormat function 346
The ExtractElement function 347
The SayIt function 348
The IsLike function 348
Working with Functions That Return an Array 348
Returning an array of month names 349
Returning a sorted list 349
Using the Insert Function Dialog Box 351
Displaying the function's description 351
Adding argument descriptions 353
Chapter 21: Creating Excel Add-Ins 355
Okay So What's an Add-In? 355
Why Create Add-Ins? 356
Working with Add-Ins 357
Understanding Add-In Basics 358
Looking at an Add-In Example 359
Setting up the workbook 359
Testing the workbook 362
Adding descriptive information 362
Protecting the VBA code 363
Creating the add-in 363
Opening the add-in 364
Distributing the add-in 364
Modifying the add-in 365
Part 6: The Part of Tens 367
Chapter 22: Ten Handy Visual Basic Editor Tips 369
Applying Block Comments 370
Copying Multiple Lines of Code at Once 371
Jumping between Modules and Procedures 371
Teleporting to Your Functions 371
Staying in the Right Procedure 372
Stepping Through Your Code 372
Stepping to a Specific Line in Your Code 373
Stopping Your Code at a Predefined Point 374
Seeing the Beginning and End of Variable Values 375
Turning Off Auto Syntax Check 375
Chapter 23: Resources for VBA Help 377
Letting Excel Write Code for You 378
Pilfering Code from the Internet 379
Leveraging User Forums 379
Visiting Expert Blogs 380
Mining YouTube for Video Training 381
Attending Live and Online Training Classes 381
Learning from the Microsoft Office Dev Center 382
Dissecting the Other Excel Files in Your Organization 382
Asking Your Local Excel Guru 382
Chapter 24: Ten VBA Do's and Don'ts 383
Do Declare All Variables 383
Don't Confuse Passwords with Security 384
Do Clean Up Your Code 384
Don't Put Everything in One Procedure 385
Do Consider Other Software 385
Don't Assume That Everyone Enables Macros 385
Do Get in the Habit of Experimenting 386
Don't Assume That Your Code Will Work with Other Excel Versions 386
Do Keep Your Users in Mind 386
Don't Forget About Backups 387
Index 389
