Macros & VBA - Level 1
VBA1 - Workbook Table of Contents
VBA1 - Videos List; Suggested Schedule VBA1 - Course Overview, Goal & Outcomes
These online workshops are ideally suited to Excel users who do not want to become a VBA Programmer, but want to automate their repetitive Excel projects, by simply adding some additional non-programmer methods to their Excel skillset.
This workshop lays foundational skills and concepts for use of Excel's Macro Recorder. You'll learn the Best Practices that I've learned, to generate VBA code that automates your work, that needs very little if any code modification.
To achieve this goal you'll Learn to:
- Create Data Validation lists that automatically detect new data validation values added to their list.
- Understand Absolute and Relative Cell Addressing, and how to best use which method when generating VBA code.
- Generate "unbreakable" VBA code that continues to execute, without error, should an end user insert rows or columns into its target worksheet.
- Identify when generated code is "breakable" and needs fixing, and learn to make the needed modifications.
- Create a series of macros with Buttons and Message Boxes, that enable input to and update of an Excel database.
This workshop builds upon the Best Practice Recording skills learned in Level 1. And you'll also learn to use some VBA code snippets and tools, for the execution of VBA processing that is otherwise not possible via Macro Recorder generated code, including:
- Loops that can run appropriately modified generated code as many times as needed, for example, based on how many files need to be processed.
- Detection of VBA run-time errors to display user-friendly messages that keep your macro running smoothly.
- How best to organize placement of files to be opened during a macro, so that your macros continue to run if there is a change in the path to the files.
Through all of this you will automate repetitive tasks used to do Data Analysis and Reporting, including: Consolidations, Filters, PivotTables, Sorts and Subtotals, as well as Charts.
If you have worksheets that currently use very complex formulas that are next to impossible to understand and are even more difficult to maintain, then this Level 3 Workshop is for you.
This training transitions from the use of Excel's Macro Recorder, to 100% VBA coding. But, with the familiarity gained during Levels 1 and 2 code review and modification, students are now typically ready to handle this beginner level of VBA coding and programming.
You'll learn to create Custom Worksheet Functions, also called User Defined Functions (UDFs), to not only replace your very complex formulas, but you'll also learn how to simplify use of complex Built-in Functions. In this process you will see that UDF coding is much easier to understand, and to maintain than what you're currently needing to do, with your use of very complex worksheet formulas.
