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.
This course will help you harness the power of the VBA language to automate tasks, work smarter, and be more productive.
Chapter 1: 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 2: 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
Chapter 3: 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 4: 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 5: 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 6: 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
Chapter 7: 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 8: 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 9: 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 10: 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 11: 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 12: 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
