Essential VBA Training for Excel - Automate Repetitive Tasks

Set up the developer tab, record a macro to automate find and replace across two tabs, and store it in this workbook or the personal macro workbook, with security reminders.

Explore the VBA editor setup, including the project, immediate, and locals windows, and learn how to run and debug code directly from the editor.

Edit and debug a recorded VBA macro in Excel, copying and adjusting code with breakpoints and the immediate window to fix worksheet spelling errors.

Explore VBA syntax, keywords, and comments, and learn how subroutines, end statements, and the with block use the cells object and replace method.

Explore VBA objects, their properties and methods, and use the teletext help box in the VBA editor to navigate with dot notation.

Explore how to declare and assign variables in VBA, distinguishing types and object variables. Explain when to use assignment versus comparison, and how function calls and call keyword work.

Explore defining and managing variables in VBA, covering integer, long, double, string, boolean, and variant types; declare, initialize, and use them with arrays and objects, and apply option explicit.

Explore how Intellitouch relates to variable types in VBA, define constants with dim and as, distinguish basic and object types, and use VB message box result constants with immediate window.

Understand subroutines in Excel VBA, including no-parameter macros and inputs, and learn to assign them to shapes to automate tasks; subroutines cannot return values or be used in cell formulas.

Explore boolean variables and expressions in VBA, perform assignments and comparisons, use and/or, and group with parentheses to craft true/false logic for conditional statements.

Master conditional logic in VBA with if...then...else and select case. Use boolean expressions and operators to handle cases like first name length or specific values.

Master VBA user communication by using message boxes with yes/no and OK options, and excel-specific input boxes to capture user data, then integrate responses into code calculations.

Explore for and do while loops in VBA, mastering for loop syntax from start to end with an optional step, and using LBound and UBound with arrays.

Learn to use the for each loop with collections in excel vba to iterate worksheets and cells, access items by name or index, and perform find-and-replace across workbooks.

Develop VBA functions to replace subroutines, using index match over vlookup for faster lookups without rearranging data. Build array function templates and handle lookup errors.

Define the function's return type as a variant, use a variant array for flexible results, and test by entering the function in Excel as an array-entered function.

Define input parameters for functions, distinguishing required and optional inputs, set default values, and test how input parameters affect simple and lookup functions in VBA.

Explore using the worksheet function object in VBA to access Excel functions like index and match, replace VLOOKUP, and test ideas for a user-defined lookup function.

Learn to build a VBA function that uses two match lookups with an index to find a table intersection, defining table array, row/column lookups, and their ranges.

Turn a single value lookup into an array formula by sizing the input and output with a two-dimensional array in VBA.

Explore the Excel VBA application object to control calculation, alerts, screen updating, and status bars; learn to adapt output for range versus non-range calls and handle array outputs.

Learn to use VBA error traps and On Error handling to gracefully manage worksheet function failures, using On Error Resume Next, On Error GoTo, and CVErr to return clear results.

Learn to manage charts in Excel using chart objects, move and resize them over pivot tables, and configure axes titles and data series with VBA.

Master the Page Setup object in VBA to control print ranges, headers and footers, margins, and orientation, and learn to manage print areas and titles across sheets.

Master recording macros, writing interdependent subroutines and functions, using the range object and lookups to automate Excel tasks with VBA.