If you already use Excel, you know how repetitive and time consuming some tasks can be. This course is designed to teach you how to take those repetitive and time consuming Excel tasks, and make them happen in a click of a button! If you use Excel a lot in your work, this course is essential; it will reduce your workload significantly.

During this course you will build a foundation of working with Excel Macros and VBA. This foundation will be built as you engage and participate in project based Excel Macro/VBA exercises, detailed throughout the lectures within this course. Each of the projects will build upon one another, introducing a new concept each time, starting with the basic building blocks of automating any task within Excel and ending with a fully customized Excel VBA project that will automate a series of Excel tasks.

What you’ll learn

  • How to automate daily routine tasks within Excel
  • Best practices when creating macros and streamline your VBA code
  • Determine when it’s best to use the Macro Recorder versus VBA
  • How to write custom VBA Macros to create more robust automation
  • VBA programming concepts to create dynamic, re-usable code
  • Complete real world Macro/VBA projects from beginning to end

panel_start Target Audience panel_end

  • Professionals, Students, Data Scientist, Analyst working on excel and wish to upgrade their skills.
  • Prior knowledge and hands on practice of excel is desirable
  • Course will be useful to enhance your skills significantly.

Introduction to VBA (Visual Basic for Application)

  • Introduction to Object-oriented programming
  • Examining the Excel object model
  • Working in the VB Editor
  • Setting VBA project properties
  • Code Modules – Create, export and delete
  • Creating Subroutine or Function
  • Controlling procedure scope
  • Adding comments to the code
  • Running a VBA routine

Variables, Constants and calculations

  • Excel VBA Data types
  • Declaring variables & why it is needed?
  • Managing variable scope
  • What are static variables and constants?
  • Calculations using mathematical operations
  • Defining arrays
  • Defining and using object variables

Adding logic to VBA Code

  • Using With End with statement
  • Repeating a task using a For Next loop
  • Stepping through all items of a collection using For Each loop
  • Repeating a task using a Do loop
  • Using If Then statement to perform a task
  • Using Cast statements to decide an action

Debugging VBA code

  • Managing errors with “On Error” statements
  • Stepping through a subroutine or function
  • Setting breakpoints in the code
  • Verifying the output in the immediate window
  • Watching a value in a routine

Managing workbook elements and data in VBA

  • Writing a value to a cell
  • Cutting, copying and pasting cell data
  • Finding values in cells
  • Referring to cells using the “Offset” function
  • Concatenating text strings
  • Returning part of a string
  • Managing worksheets with VBA
  • Managing workbooks with VBA

Advance VBA

  • Screen updating on/off
  • Using worksheet functions in a macro
  • Using input box or message box to receive user input values
  • Calling a subroutine from another subroutine

Excel events in VBA

  • Running a procedure when you open, save or close a workbook
  • Running a procedure when a cell range changes
  • Triggering a procedure using a specific key sequence

Recording and enhancing Macro

  • Recording a Macro to drill down data
  • Improving the macro by writing our own code
  • Enhancing the macro to work with different files

Managing files using VBA

  • Determine if a workbook exits
  • Determine if a folder (directory) exits
  • Detecting whether a file is open
  • Opening a workbook
  • Closing a workbook
  • Saving a workbook under a new name
  • Saving a workbook in different formats

Managing worksheets using VBA

  • Checking if a worksheet exists
  • Creating and renaming a worksheet
  • Copying a worksheet within the active workbook
  • Copying a worksheet to a new workbook
  • Copying a worksheet to an existing workbook
  • Moving worksheets

Using Built-in Functions in VBA

  • Using the built-in Open dialog box
  • Managing alerts (Suppress and restore)
  • Calculating data using Excel Worksheet functions
  • Using the current date and time
  • Removing spaces in a string

Working with charts via VBA

  • Creating a chart
  • Moving a chart to a chart sheet
  • Adding a new data series to a chart
  • Export a chart as an image

Creating and managing User Forms with VBA

  • Creating a UserForm
  • Adding a TextBox to a UserForm
  • Adding a ListBox to a UserForm
  • Adding a ComboBox to a UserForm
  • Adding an option button/spin button to a UserForm
  • Adding graphics to a UserForm
  • Creating a multitab UserForm
  • Writing UserForm data to a worksheet
  • Running a UserForm

Interactive Pivot table dashboard with VBA

  • Creating a dashboard with Pivot Tables
  • Using form controls and active controls in dashboards
  • Simple macro codes to create an interactive pivot dashboard
  • Using Radio Buttons in a dashboard
  • Creating a Top/Bottom data view in dashboard

User Defined functions and Add-ins

  • Creating User Defined Functions
  • Saving UDF as Excel Add-ins
  • Distributing Excel Add-in files
  • Using an existing add-in file


  • Project #1: Using Excel’s Macro Recorder Tool
  • Project #2: Using VBA to automate Excel Formulas
  • Project #3: Bringing it all togther and create a weekly report
  • Project #4: Working with VBA Forms