Excel VBA: How to Write Macros Like a Professional
4.4 (915 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,967 students enrolled

Excel VBA: How to Write Macros Like a Professional

Learn how to write real-world Excel VBA Macros from scratch. No experience required!
4.4 (915 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,967 students enrolled
Created by Paul Kelly
Last updated 9/2018
English [Auto]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 5 hours on-demand video
  • 1 article
  • 15 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Learn little-known techniques used by the top professionals
  • Over 70 example Macros for you to use in your own projects
  • Discover the best method to access a worksheet and the ones you should avoid
  • Learn how to use Intellisense to help you write your code
  • Learn how to supercharge your Excel experience using Loops
  • How to resolve errors quickly and painlessly
  • Learn programming techniques you can use in any programming language
  • Learn When to use "If" and when to use "Select"
  • Learn the 4 Loops of Excel and when to use each one
  • How to test your output before writing to a worksheet
  • Discover how to protect your code against users changing the worksheet name
  • Learn how to use the Compiler to check for errors before you run
  • Learn the best way to use Range, Cells and Offset to access the cells
  • Learn how to easily format any cell or range
  • Discover how easy it is to step through the code line by line
  • Knowledge of Microsoft Excel
  • A Laptop/PC with a copy of Microsoft Excel

September 2018 Added New Content!

How to Copy and Filter Data using Excel VBA

This new section comes with 13 Exercises plus full solutions to help you practice and reinforce what you have learned.


April 2018 Added New Content!

Learn how to build an entire Excel VBA application from start to finish. In this brand new section you will see techniques not taught in any other Excel VBA course. 

This new section(number 15) contains proven principles and little-known techniques that will help you build an application in a fraction of the time it would normally take you. 

You will also learn tips and tricks that will reduce your errors, make your code more organised,  and make your applications easier to maintain and update.

This is the only Excel VBA course that shows you first hand, a professional developer creating a VBA application from scratch. 


January 2018 Added New Content!

Using Arrays in Excel VBA: 6 brand new lectures.

FINALLY! A Way To Write Excel VBA Like a Professional

If you think the top Excel VBA programmers are smarter or more gifted than you, then you're mistaken. Truth is, they use dozens of proven programming principles.... little-known techniques that are completely unknown to most VBA users. What are these principles? How can you learn them? And how can you use them to create your own Macros? In this course, Paul Kelly teaches you dozens of must-have secrets that he learned during his 20+ years as a professional software developer, including 

*How To Easily Open Any Workbook - I provide the code, You can use it everywhere!

* Why using Loops can increase your productivity exponentially - Start using them immediately

* How To Create a Real-World VBA Project - A step-by-step guide through a Fruit Sales example project

* The Error Translator Table - An easy way to resolve errors that should have been written years ago

* Which worksheet method to use - Your code will still work even if the user changes the sheet name!

* How to easily test the output of your macros - It's childs play to write values to this Window

* How To Quickly Create Solid Excel Macros - Tons of examples you can "steal" for yourself.

* The In Built VBA Tools That Help You Easily Find Bugs - If you think you would make a good detective then you may really enjoy this

* How to Easily Format any Cell - Changing a cells font, color, format is so simple you could do it now.

* These "Evil" Workbook types should never be used - Do You Use them?

* And Much More. 

Remember if you are not happy with the course for any reason you can get a FULL REFUND within 30 days - no questions asked!!!

Who this course is for:
  • Accountants/Auditors/Financial planners
  • Business/Financial Analysts
  • Managers/Project Managers/Project Planners
  • Anyone who wants to learn how to write VBA in a professional environment
  • Anyone who wants to exponentially increase their productivity at work
  • Anyone who wants an introduction to programming with a straightforward langauge
  • Anyone who wants to increase their value to their current or potential employer
  • Any user of Excel who wants to dramatically increase their abiltiy
Course content
Expand all 83 lectures 05:36:17
+ Introduction
4 lectures 02:23

A list of strategies to get the most out of this course

  1. Assign some time each day
  2. Practice writing code
  3. Break tasks down into parts
  4. Don't let errors stop you
  5. Close all workbooks except the one your working with
  6. Enjoy it
Preview 02:23
A Welcome Note From Paul and Course Source Code
1 page
6 lectures 17:37

A quick check to ensure you are set up correctly to write Macros! We check the following items

  1. The Developer Tab is visible
  2. Your Security settings are set correctly
  3. You know how to save a file with Macros( as a Macro-Enabled Workbook(.xlsm))

Preview 01:55

Create a simple Macro using the Developer Ribbon.

Run the Macro from Excel and then Visual Basic

Your First Macro

This lesson shows you how to create Modules and Macros in Visual Basic.

  1. Modules are used to hold Macros.
  2. You can have as many Modules as you like in a workbook
  3. Each module can have as many macros as you like
  4. Place related Macros in the same Module

Preview 04:36

The Immediate Window is an incredibly useful tool . You can test your code by writing values here. You can also use it when you want to try out code. It is also a great way of demonstrating how code works so I use it throughout this lesson.

It is simple to use. Write Debug.Print followed by the value/variables you wish to write.

The Immediate Window - How to easily test your output

Intellisense is a predictive tool that helps you write your code.

By using "Complete Word" you can write your code much quicker and with less errors

Preview 03:39

Course Notes for the Section: Getting Started

Getting Started - Support Text
11 pages
+ Workbooks and Worksheets
10 lectures 30:59

Use the Range property to write a value to a cell.

If you use Range on it's own it automatically refers to the ActiveSheet - the sheet you last clicked on. As you want to avoid this you should always get the workbook and worksheet you are going to use. The rest of this section shows the techniques for doing this.

Preview 01:46
How to get any Workbook

In the previous lesson we looked at the different ways of accessing the workbook. In this lesson we look at when you should use them.

When to use each Workbook method

In this lesson we look at using a workbook variable.

It makes your code easier to read, easier to update and more efficient.

Making your code easy to read - Use a Workbook variable

In this lecture we look at the different ways of accessing the worksheet




How to get any Worksheet

In this lecture we create a simple Macro that uses what we have learned so far about the workbook and worksheet.

Putting it all together - A sample Macro

The code name of the worksheet is the best way of getting a worksheet from the current workbook(the one containing the code).

It has two distinct advantages:

  1. If the user changes a worksheet name the code will still work
  2. You can use it directly to access a sheet - you don't need to mention the workbook
VBA's best kept secret - the worksheet codename

We revisit the min project and update the code so it use the code name of the worksheet. By doing this we can see how the code name is so useful.

The Sample Macro - How the code name improves it

A summary of what we have learned about Workbooks and Worksheets in this section. This lecture has a downloadable excel workbook with example macros the show how to use workbooks and worksheets.

Summary of Worksheets and Workbooks

The supporting text for Workbooks and Worksheets. A pdf of what we have covered in this section.

Workbooks and Worksheets - Downloadable Guide
10 pages
Workbooks and Worksheets Quiz
10 questions
+ Using Variables
3 lectures 11:15

An Introduction to variables and how to use them.

An introduction to Variables

A guide to manipulating string variables(pieces of text).


  • Left - gets the left most characters
  • Right - gets the right most characters
  • Len - gets the length of a string
How to do almost anything with a string

This lecture shows how to write and read between variables and cells.

Using variables to write and read from Cells
+ Ranges, Cells and Offsets
6 lectures 20:19

This section is all about using cells. This first lecture looks at using the Range property to access cells.

Using Range to manipulate cells

How to choose a cell a run time using the Cells property.

Cells takes row and column as arguments and so these can be easily set when the macro is running.

For Example

Cells(3,1) gives access to the cell A3

Using Cells - How to choose cells at run time part 1

Offset is used move a certain number of cells from a range. Like the Cells property it takes row and column as arguments and can be easily changed at runtime.

Using Offset - How to choose cells at run time part 2

A sample Macro showing the benefit of using Offset.

This macro reads values from a month and places the result in the correct cell using Offset.

Sample Macro - Writing to months using Offset

Updated the Macro from the previous lesson so that it uses Cells instead of Offset

Sample Macro - Writing to months using Cells

How to format cells. Included in this lesson:

  • Formatting the font to bold
  • Formatting the font color
  • Formatting the Cell background color
  • Formatting the borders around a group of cells

Formatting Ranges - Making you work look good
+ Mini Project - Fruit Sales
5 lectures 11:11

The two workbooks used in this sections videos.

Mini Project Workbooks

In this section we create a mini project similar to a real world application. We use the techniques we have learned so far on this course.

Introduction to Mini Project

We format the area of the report where we are going to write our values.

Formatting the Cells

We write the values to our report.

Write the Totals

Make the code more flexible using Offset

Make it flexible with Offset
+ Debugging - How to see what your code is actually doing
6 lectures 14:21

An introduction to this section on Debugging

What is Debugging

How to step through the code

Stepping through code

How to use breakpoints to stop your code on a certain line.

Using Breakpoints

How to use the Locals window to view the values of variables in your current Macro.

The Locals Window

Similar to the Locals Windows, the Watch Window allows you to create your own items to watch.

The Watch Window

How to look at the properties of objects(e.g. Workbooks and Worksheets) using the Watch Window.

More on the Watch Window
+ If and Select
5 lectures 17:06

Conditions are used mostly in If Statements and Loops. The are statements that evaluate to through or false. Some examples are

  • x > 1
  • x<1
  • x=5
  • x<>5
  • x>5 and x<=10
Using Conditions

The If statement allows you to make decisions in what your code does

The If Statement

Using ElseIf allows you to add more conditions to your If Statement.

an example

If x>5

Debug.Print "x is greater than 5")

ElseIf x=10

Debug.Print "x equals 10")


If Statement with ElseIf

The Select Statement is a neater way of writing If and ElseIf.

The Select Statement

Another example of using the Select Statement

More with the Select Statement
+ Loops
9 lectures 27:38

An introduction to using Loops

Introduction to Loops

A guide to using the For Loop.

This loop uses the format:

For i = 1 To 10

Next i

The For Loop
More on the For Loop

The For Each Loop is used to read through collections

an example is

For Each wk in Workbooks


The For Each Loop

A guide to the Do Loop which is normally used when you don't know in advance how often it will run.

The Do Loop

Using the Do Loop with Until

Using Do Loop with Until

Using the Do Loop with While

Using Do Loop with While

The While Wend loop is considered obsolete. You may come across it in older code so it is important to recognise it.

While and Wend

A Summary of all the Loops we covered in this section

Summary of Loops
+ Errors
4 lectures 10:47

What are syntax errors and how to deal with them.

Syntax errors occur when you enter a line.

Syntax Errors

A guide to Compiler errors.

These are errors where the individuals lines may be okay but has a whole the code may have errors.

To check for compiler errors select Debug->Compile VBA Project from the menu.

Compiler Errors

This is an introduction to Runtime errors. The occurs when your Macro is running.

A run time error can be caused by reading data(such as text instead of number) that you Macro cannot handle.

A run time error can be something simple like reading from the wrong cell.

Runtime Errors

This is a list of common VBA errors and how to resolve them. If you have an error you cannot resolve then email me at paulkellykk@gmail.com and I will resolve it for you.

Preview 3 pages