Excel Automation: Learn VBA & Macros From Scratch!

Explore what VBA and macros are, learn the difference between them, and write your first macro in the VBA window while mastering Excel and workbook objects, functions, properties, and methods.

Discover how Excel's front end interfaces with a VBA back end, and how macros trigger button clicks to execute code in Excel.

Discover how object oriented programming language uses objects and their properties to communicate with the system in VBA, enabling outsourcing of work beyond plain language.

Learn object referencing in VBA for Excel by using names such as workbooks, sheets, and range, or by index positions, including referencing cells like B5 by row and column.

Learn how to reference Excel functions in VBA by navigating the function library and using worksheet function with dot notation to access sum, lookup, and other Excel specific functions.

Explore VBA's pillars, focusing on properties as object attributes (such as name, row, column, color) and metrics—actions like selecting, deleting, and inserting cells—for practical implementation.

Explore the VBA window and developer tab; learn to create and run macros with sub and end sub, including running via play or F5.

Save workbooks with macros as an Excel macro-enabled workbook to preserve all code, and confirm macros by checking the Visual Basic section.

Explore how objects, functions, properties, and methods form the four pillars that define every action in Excel, with functions on cells, properties on objects, and methods on sheet objects.

Learn how objects connect to properties in Excel with dot notation, and practice getting or changing properties via vb macros on the active sheet.

Learn how objects and methods connect in VBA, using dot notation to call a sheet delete method. Distinguish methods from properties with a hands-on Excel macro.

Explore ways to execute Excel macros, including the play button and F5, plus line-by-line execution with F8 and break on a line using F9.

Learn to write macros by applying variables, loops, and conditional execution with assignments, practicing multiple tasks in this section of Excel automation.

Use variables to make VBA macros dynamic and differentiate them from constants that stay fixed. Define variables before use and store numbers or text in quotes to ensure reusable code.

Explore how to use a for loop to display numbers 1 to 10 in message boxes, defining a range and utilizing next for controlled repetition.

Learn how to use conditional loops in VBA with do until and do while, start from 1, define the ending condition at 10, and manually increment n to repeat actions.

Master conditional statements in VBA to automate Excel tasks, using if, and, or with a logical test to drive a message box, including else and end if.

Discover how select case statements replace multiple if statements in Excel VBA by testing a variable against cases, using else and end select.

Handle Excel VBA macro errors using on error resume next to ignore them or on error goto to take action. Guard against runtime errors like subscript out of range.

Master the core macro vocabulary for Excel as you learn how keywords guide writing macros, enabling tasks like coloring a cell, sorting or filtering data, and adjusting sheet settings.

Learn how the record macro feature in VBA watches your Excel actions and outputs VBA code, helping you extract keywords for basic automation.

Combine writing and recording to create advanced macros, explain when to write versus record, and cover conditional statements and sheet or cell references for driving actions.

Learn to design VBA macros from an end-user perspective, focusing on event-driven execution—time, button, keystroke, or situation—and prepare for subroutines, private subroutines, and the functions routine.

Reveal how excel macros use public subroutines to give end users control, by binding macros to events, buttons, or keyboard shortcuts and clarifying who defines what the macro does.

Learn to create a custom keystroke in Excel by mapping the home key to a macro with VBA, demonstrating the setup and its workbook-only persistence.

Explore private subroutines and system-created macros in Excel automation, using predefined events to trigger macros. Learn where to find macros and how to assign tasks, choosing appropriate end user controls.

Learn how to auto-run a macro on workbook open using VBA, display a message box, and navigate workbook events and private subs within the VBA editor.

Learn how to implement a workbook close event in VBA to timestamp the time of last edits, with manual or automatic recording via prebuilt macros.

Learn to use a worksheet change event to trigger a message box when expenses in the first column exceed 20000, avoiding a button.

Explore how private sub routines govern macro execution in Excel VBA, explain why users cannot directly trigger them, and relate execution conditions to an airbag analogy.

Explore how Excel functions work by examining the sum function, and learn to create a basic VBA function in a module that returns a static value.

Learn to build a dynamic VBA function in Excel that uses cell references as input. Multiply values by 100 and understand how Excel and VBA interact for reusable functions.

Learn application level execution to build macros that work across workbooks, using generic keywords like active sheet, workbook, and active cell selection.

Learn to create an application-specific macro in Excel to count the number of sheets in the active workbook, accessible from any workbook via the quick access toolbar.

Create global functions in Excel using VBA that are accessible from any workbook by defining them in the VB window and saving your macros.

Show how private subs in an add-in file relate to the current workbook and why they are not global across workbooks, with open and close events.

Identify pain points in Excel workflows, automate manual and repetitive tasks, and build customized VBA solutions using recorded code and helpful references.