Excel VBA Programming For Dummies, 6th Edition

Excel VBA Programming For Dummies, 6th Edition

by Dick Kusleika
Released February 2022
Publisher(s): For Dummies
ISBN: 9781119843078

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Find out what Excel is capable of with this step-by-step guide to VBA

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.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Introduction
    1. About This Book
    2. Foolish Assumptions
    3. Icons Used in This Book
    4. Beyond the Book
    5. Where to Go from Here
  5. Part 1: Starting Excel VBA Programming
    1. Chapter 1: Getting to Know VBA
      1. Understanding VBA Basics
      2. Knowing What VBA Can Do
      3. Getting the Most from VBA
      4. Understanding VBA Concepts
      5. Ensuring Excel Compatibility
    2. Chapter 2: Building Simple Macros
      1. Displaying the Developer Tab
      2. Creating a Macro
      3. Preparing the Environment
      4. Recording a Macro
      5. Running the Macro
      6. Viewing a Macro in the Visual Basic Editor
      7. Modifying the Macro
      8. Saving Workbooks That Contain Macros
      9. Understanding Macro Security
  6. Part 2: Employing VBA with Excel
    1. Chapter 3: Working in the Visual Basic Editor
      1. Getting to Know the Visual Basic Editor
      2. Working with the Project Explorer
      3. Working with a Code Pane
      4. Customizing the VBE
    2. Chapter 4: Introducing the Excel Object Model
      1. Working with the Excel Object Model
      2. Diving into Object Properties and Methods
      3. Finding Out More from VBA Resources
    3. Chapter 5: VBA Sub and Function Procedures
      1. Understanding Subs versus Functions
      2. Naming Subs and Functions
      3. Executing Sub procedures
      4. Executing Function Procedures
    4. Chapter 6: Using the Excel Macro Recorder
      1. Recording Basics
      2. Preparing to Record
      3. Choosing Between Relative and Absolute Mode
      4. Watching the Macro Recorder in Action
      5. Specifying Recording Options for Your Macro
      6. Streamlining Code Generated by the Macro Recorder
  7. Part 3: Programming Concepts
    1. Chapter 7: Essential VBA Language Elements
      1. Using Comments in Your VBA Code
      2. Using Variables, Constants, and Data Types
      3. Using Assignment Statements
      4. Working with Arrays
      5. Using Labels
    2. Chapter 8: Working with Range Objects
      1. Referring to Range Objects
      2. Referring to a Range Using Properties
      3. Working with Range Object Properties
      4. Taking Action with Range Object Methods
    3. Chapter 9: Using VBA and Worksheet Functions
      1. Understanding Functions
      2. Using Built-In VBA Functions
      3. Using Worksheet Functions in VBA
      4. Using Custom Functions
    4. Chapter 10: Controlling Program Flow and Making Decisions
      1. Going with the Flow, Dude
      2. The GoTo Statement
      3. Decisions, Decisions
      4. Knocking Your Code for a Loop
      5. Using For Each-Next Loops with Collections
    5. Chapter 11: Automatic Procedures and Events
      1. Preparing for the Big Event
      2. Knowing Where to Put the Event Code
      3. Writing an Event-Handler Procedure
      4. Triggering Workbook Events
      5. Using Activation Events
      6. Programming Worksheet-Related Events
      7. Understanding Events Not Associated with Objects
    6. Chapter 12: Error-Handling Techniques
      1. Types of Errors
      2. An Erroneous Macro Example
      3. Alternate Ways of Handling Errors
      4. Handling Errors: The Details
      5. An Intentional Error
    7. Chapter 13: Bug Extermination Techniques
      1. Species of Bugs
      2. Identifying Bugs
      3. Debugging Techniques
      4. Using the Debugger’s Tools
      5. Bug Reduction Tips
    8. Chapter 14: VBA Programming Examples
      1. Working with Ranges
      2. Changing Excel Settings
      3. Working with Charts
      4. VBA Speed Tips
  8. Part 4: Communicating with Your Users
    1. Chapter 15: Simple Dialog Boxes
      1. Interacting with the User in VBA
      2. Displaying Messages with the MsgBox Function
      3. Getting Data with an Input Box
      4. Allowing the User to Select a File or Folder
      5. Displaying Excel’s Built-In Dialog Boxes
    2. Chapter 16: UserForm Basics
      1. Knowing When to Use a UserForm
      2. Creating UserForms: An Overview
      3. Working with UserForms
      4. A UserForm Example
    3. Chapter 17: Using UserForm Controls
      1. Getting Started with Dialog Box Controls
      2. Learning Dialog Box Controls Details
      3. Working with Dialog Box Controls
      4. Dialog Box Aesthetics
    4. Chapter 18: UserForm Techniques and Tricks
      1. Using Dialog Boxes
      2. A UserForm Example
      3. A ListBox Control Example
      4. Selecting a Range
      5. Using Multiple Sets of Option Buttons
      6. Using a Spin Button and a Text Box
      7. Using a UserForm as a Progress Indicator
      8. Creating a Modeless Tabbed Dialog Box
      9. Displaying a Chart in a UserForm
      10. A Dialog Box Checklist
    5. Chapter 19: Accessing Your Macros through the User Interface
      1. Customizing the Ribbon
      2. Customizing the Excel UI with VBA
  9. Part 5: Putting It All Together
    1. Chapter 20: Creating Worksheet Functions
      1. Create Custom Functions to Simplify Your Work
      2. Understanding VBA Function Basics
      3. Writing Functions
      4. Working with Function Arguments
      5. Introducing Wrapper Functions
      6. Working with Functions That Return an Array
      7. Using the Insert Function Dialog Box
    2. Chapter 21: Creating Excel Add-Ins
      1. Add-Ins Defined
      2. Reasons to Create Add-Ins
      3. Working with Add-Ins
      4. Understanding Add-In Basics
      5. Looking at an Add-In Example
  10. Part 6: The Part of Tens
    1. Chapter 22: Ten Handy Visual Basic Editor Tips
      1. Applying Block Comments
      2. Copying Multiple Lines of Code at Once
      3. Jumping between Modules and Procedures
      4. Teleporting to Your Functions
      5. Staying in the Right Procedure
      6. Stepping through Your Code
      7. Stepping to a Specific Line in Your Code
      8. Stopping Your Code at a Predefined Point
      9. Seeing the Beginning and End of Variable Values
      10. Turning Off Auto Syntax Check
    2. Chapter 23: Resources for VBA Help
      1. Letting Excel Write Code for You
      2. Referencing the Help System
      3. Pilfering Code from the Internet
      4. Leveraging User Forums
      5. Visiting Expert Blogs
      6. Mining YouTube for Video Training
      7. Attending Live and Online Training Classes
      8. Learning from the Microsoft Office Dev Center
      9. Dissecting the Other Excel Files in Your Organization
      10. Asking Your Local Excel Guru
    3. Chapter 24: Ten VBA Do’s and Don’ts
      1. Do Declare All Variables
      2. Don’t Confuse Passwords with Security
      3. Do Clean Up Your Code
      4. Don’t Put Everything in One Procedure
      5. Do Consider Other Software
      6. Don’t Assume That Everyone Enables Macros
      7. Do Get in the Habit of Experimenting
      8. Don’t Assume That Your Code Will Work with Other Excel Versions
      9. Do Keep Your Users in Mind
      10. Don’t Forget about Backups
  11. Index
  12. About the Author
  13. Connect with Dummies
  14. End User License Agreement

Product information

  • Title: Excel VBA Programming For Dummies, 6th Edition
  • Author(s): Dick Kusleika
  • Release date: February 2022
  • Publisher(s): For Dummies
  • ISBN: 9781119843078