For most of us, the path to Excel VBA programming starts with the need to perform some task that can’t be done with the standard tools in Excel. That task is different for each of us.
The beautiful thing about Excel VBA is that you don’t have to be an expert to start solving problems with it. You can learn just enough to solve a particular problem, or you can go further and discover ways to handle all kinds of automation scenarios.
Whatever your goals may be, this course will help you harness the power of the VBA language to automate tasks, work smarter, and be more productive.
To get the most out of this course, you should be a relatively experienced Excel user who knows how to do the following:
- Create workbooks, insert sheets, save files, and so on
- Navigate through a workbook
- Use the Excel Ribbon user interface
- Enter formulas
- Use Excel’s worksheet functions
- Name cells and ranges
- Use basic Windows features, such as file management techniques and the Clipboard
Part I Introduction to Excel VBA
Chapter 1 Essentials of Spreadsheet Application Development
Lessons:
- Discovering the basic steps involved in spreadsheet application development
- Determining end users’ needs
- Planning applications to meet users’ needs
- Developing and testing your applications
- Documenting your development efforts and writing user documentation
Chapter 2 Introducing Visual Basic for Applications
Lessons:
- Using Excel’s macro recorder
- Working with the Visual Basic Editor
- Understanding the Excel Object Model
- Diving into the Range object
- Knowing where to turn for help
Chapter 3 VBA Programming Fundamentals
Lessons:
- Understanding VBA language elements, including variables, data types, constants, and arrays
- Using VBA built-in functions
- Manipulating objects and collections
- Controlling the execution of your procedures
Chapter 4 Working with VBA Sub Procedures
LESSONS:
- Declaring and creating VBA Sub procedures
- Executing procedures
- Passing arguments to a procedure
- Using error-handling techniques
- An example of developing a useful procedure
Chapter 5 Creating Function Procedures
Lessons:
- Understanding the difference between Sub procedures and Function procedures
- Creating custom functions
- Looking at Function procedures and Function arguments
- Creating a function that emulates Excel’s SUM function
- Using functions that enable you to work with pre-1900 dates in your worksheets
- Debugging functions, dealing with the Insert Function dialog box, and using add-ins to store custom functions
- Calling the Windows application programming interface (API) to perform otherwise impossible feats
Chapter 6 Understanding Excel’s Events
Lessons:
- Recognizing the types of events that Excel can monitor
- Figuring out what you need to know to work with events
- Exploring examples of Workbook events and Worksheet events
- Using Application events to monitor all open workbooks
- Seeing examples of processing time-based events and keystroke events
Chapter 7 VBA Programming Examples and Techniques
Lessons:
- Using VBA to work with ranges
- Using VBA to work with workbooks and sheets
- Creating custom functions for use in your VBA procedures and in worksheet formulas
- Trying miscellaneous VBA tricks and techniques
- Using Windows application programming interface (API) functions
Part II Advanced VBA Techniques
Chapter 8 Working with Pivot Tables
Lessons:
- Creating pivot tables with VBA
- Looking at examples of VBA procedures that create pivot tables
- Using VBA to create a worksheet table from a summary table
Chapter 9 Working with Charts
Lessons:
- Discovering essential background information on Excel charts
- Knowing the difference between embedded charts and chart sheets
- Understanding the Chart object model
- Using methods other than the macro recorder to help you learn about Chart objects
- Exploring examples of common charting tasks that use VBA
- Navigating more complex charting macros
- Finding out some interesting (and useful) chart-making tricks
- Working with Sparkline charts
Chapter 10 Interacting with Other Applications
Lessons:
- Understanding Microsoft Office Automation
- Automating Access from Excel
- Automating Word from Excel
- Automating PowerPoint from Excel
- Automating Outlook from Excel
- Starting other applications from Excel
Chapter 11 Working with External Data and Files
Lessons:
- Working with external data connections
- Using ActiveX Data Objects to get external data
- Performing common file operations
- Working with text files
Part III Working with UserForms
Chapter 12 Leveraging Custom Dialog Boxes
Lessons:
- Using an input box to get user input
- Using a message box to display messages or to get a simple response
- Selecting a file from a dialog box
- Selecting a directory
- Displaying Excel’s built-in dialog boxes
Chapter 13 Introducing UserForms
Lessons:
- Creating, showing, and unloading UserForms
- Exploring the UserForm controls available to you
- Setting the properties of UserForm controls
- Controlling UserForms with VBA procedures
- Creating a UserForm
- Introducing the types of events relevant to UserForms and controls
- Customizing your control Toolbox
- Going over a handy checklist for creating UserForms
Chapter 14 Looking at UserForm Examples
Lessons:
- Using a UserForm for a simple menu
- Selecting ranges from a UserForm
- Using a UserForm as a splash screen
- Changing the size of a UserForm while it’s displayed
- Zooming and scrolling a sheet from a UserForm
- Understanding various techniques that involve a ListBox control
- Using an external control
- Using the MultiPage control
- Animating a Label control
Chapter 15 Implementing Advanced UserForm Techniques
Lessons:
- Using modeless UserForms
- Displaying a progress indicator
- Creating a wizard—an interactive series of dialog boxes
- Creating a function that emulates VBA’s MsgBox function
- Allowing users to move UserForm controls
- Displaying a UserForm with no title bar
- Simulating a toolbar with a UserForm
- Emulating a task pane with a UserForm
- Allowing users to resize a UserForm
- Handling multiple controls with a single event handler
- Using a dialog box to select a color
- Displaying a chart in a UserForm
- Creating puzzles and games with UserForms
Part IV Developing Excel Applications
Chapter 16 Creating and Using Add-Ins
Lessons:
- Understanding the concept of add-ins
- Exploring Excel’s Add-in Manager
- Creating an add-in
- Comparing XLAM add-in files to XLSM files
- Viewing VBA code that manipulates add-ins
- Detecting whether an add-in is installed properly
Chapter 17 Working with the Ribbon
Lessons:
- Looking at the Excel Ribbon UI from a user’s perspective
- Using VBA to work with the Ribbon
- Customizing the Ribbon with RibbonX code
- Looking at examples of workbooks that modify the Ribbon
- Using boilerplate code for creating an old-style toolbar
Chapter 18 Working with Shortcut Menus
Lessons:
- Identifying shortcut menus
- Customizing the shortcut menus
- Disabling shortcut menus
- Using events with shortcut menus
- Creating a new shortcut menu
Chapter 19 Providing Help for Your Applications
Lessons:
- Providing user help for your applications
- Using only the components supplied with Excel to provide help
- Displaying help files created with the HTML Help system
- Associating a help file with your application
- Displaying HTML Help in other ways
Chapter 20 Leveraging Class Modules
Lessons:
- Introducing class modules
- Exploring some typical uses for class modules
- Seeing examples that demonstrate some key concepts related to class modules
Chapter 21 Understanding Compatibility Issues
Lessons:
- Increasing the probability that your Excel 2019 applications will also work with previous versions of Excel
- Declaring API functions that work with 32-bit Excel 2019, 64-bit Excel 2019, and earlier versions of Excel
- Being aware of issues when you’re developing Excel applications for international use
