Excel VBA Programming For Dummies
35%
off

Excel VBA Programming For Dummies

By (author)  , By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 2 business days
When will my order arrive?

Description

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!
show more

Product details

  • Paperback | 416 pages
  • 188 x 234 x 21mm | 546g
  • New York, United States
  • English
  • 5th Edition
  • 1119518172
  • 9781119518174
  • 7,138

Back cover copy

Extend the power of Excel with VBA Programming Build your own applications and Excel Add-ins Work smarter with VBA Automation

Work smarter with Excel VBA programming!

Inside, you'll find everything you need to quickly get started with VBA programming. Whether you're looking to be more productive, automate those redundant tasks, or create your own killer application, Excel VBA Programming For Dummies provides the essential elements and concepts for programming with Excel. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, you'll be creating Excel applications custom fit to your unique needs!

Inside... Take the grind out of routine data crunching tasks Create macros that manipulate, scrub, and shape data Maximize your productivity Make dynamically generated PivotTables and Charts Extend Excel's power with custom worksheet functions Build your own applications
show more

Table of contents

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
show more

About Michael Alexander

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.
show more