Excel VBA Course - Automate Repetitive or Complex Tasks

Learn Excel VBA and take your spreadsheets to the next level.
4.4 (160 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
2,553 students enrolled
$19
$95
80% off
Take This Course
  • Lectures 59
  • Length 11 hours
  • Skill Level Expert Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2014 English

Course Description

- Join over 2,400 Students in Learning Excel VBA

- This Course has Received 75 Five Star Reviews

- Last Updated 10th November 2016


Hear What Some of Our Students are Saying.....

"Really, really good course on VBA! *****"

"Great content, great examples, would definitely take another course from this instructor again!"

"Excellent course for getting started with VBA. The instructor makes every concept clear, and guides you through with many examples.

"Great course for a beginner or as a refresher. I know I will be coming back to some of these lectures as needed for some of his code tidbits because as he says, "you can't remember everything", and I love knowing where I can find what I am looking for.


Want to take your Excel skills to the next level by learning Excel VBA? Well you have come to the right place.

This course covers everything you need to become proficient in Excel VBA. In just a few hours you can be writing your own macros to perform complex tasks and automate reports.

Areas covered include;

  • Learning the basics of the VBA language.
  • Using loops to perform repetitive tasks.
  • Interacting with users via userforms and message boxes.
  • Working with built-in VBA Functions.
  • Accessing directories and files.
  • Automating report generation and saving as PDF.
  • Creating your own Excel functions and Add-Ins.

You will receive;

  • Support from the Instructor.
  • Lifetime access to the lectures and content.
  • Real world Excel VBA projects to complete.
  • Fun and no-nonsense lessons.

Ok, no more time for waffle. Grab a coffee and let's get cracking.

What are the requirements?

  • Microsoft Excel
  • Intermediate/Advanced knowledge of Excel

What am I going to get from this course?

  • Write powerful Excel VBA code to automate processes
  • Impress your boss and colleagues by adding code to speed up repetitive tasks
  • Identify real world situations where you can apply your new VBA skills
  • Understand, test and debug VBA errors

What is the target audience?

  • Excel users who want to learn VBA to take their skills to another level
  • No prior programming skills are necessary. The course starts with the basics
  • This course is probably not for those with VBA experience who are looking for advanced VBA.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
Introduction
Article
Article

Download these files so that you can follow along and practice the code demonstrated in the videos of the course.

This folder includes both the incomplete and finished versions of the files used on the course.

Section 2: Recording Macros
11:33

Before we begin writing our own code, we need to look at recording macros. However good you get at writing VBA code, there will always be a place for recording. It saves you time, gives you ideas and helps you understand tasks you are not familiar with.

By recording a macro, we ask Excel to watch us as we perform actions on a spreadsheet. We stop the recording when we have finished, and from now on these actions can be performed with the click of one button.

This tutorial covers;

  • Recording a macro to protect the cells on a worksheet that contain a formula
  • What you can, and cannot, do when naming macros
  • Where you should save your macro
05:34

An Excel file that contains a macro should be saved as a macro enabled file. Macros will not be available in files saved as a workbook.

Depending on the user’s settings, a user will be prompted to enable macros on the spreadsheet when opening the file.

This tutorial covers;

  • Excel warning you about saving as a macro free workbook
  • Saving as a macro enabled workbook
  • Modifying macro security settings
  • The message bar prompting a user to enable macros on a workbook
06:34

After recording a macro you will need some way of initialising it. This will commonly come in the form of a button, and the most popular place to position that button is the Quick Access Toolbar.

This toolbar is always visible at the top of the screen making it the easiest way to trigger your macro.

This tutorial covers;

  • Assigning a macro to a toolbar button
  • Making the button available to all spreadsheets, or to only one specific file
  • Customising the image and text of the button
07:18

You can customise the Ribbon in Excel 2010 and later versions. The Ribbon provides a much larger space to position your macro buttons and also any other Excel commands you wish to add.

By taking the time to customise the Ribbon and toolbar in Excel, you can make your tasks faster and easier over the long haul.

This tutorial covers;

  • Creating a new tab on the Ribbon
  • Creating additional groups on the Ribbon tabs
  • Renaming the tabs and groups
  • Assigning a macro button to a group on the Ribbon
  • Customising the image and text of a button
06:29

Buttons can also be added to the worksheet to initiate your macros. This can be popular with macros that submit forms and dashboard reports.

This tutorial covers;

  • Showing the Developer tab on the Ribbon
  • Inserting a command button and assigning a macro to it
  • Inserting a shape and assigning a macro it
  • Formatting your command buttons and shapes
07:46

VBA or Visual Basic Applications is the programming language behind all office applications. By writing or editing this code you can take your macros to another level.

You can create some awesome macros by recording, but the real power lies in writing the VBA. Using VBA you can create complex macros that enhance the capabilities of Excel.

This tutorial covers;

  • Understanding some of the VBA code generated by our protecting formulas macro
  • Edit the code to tidy it up and correct mistakes
  • Add an IF statement to exit the macro if the sheet is already protected
  • Add the msgbox VBA function to display a message to the user upon exiting
15:09

When selecting cells whilst recording a macro, the macro records these as absolute references. You can toggle between relative and absolute references during a recording to get the correct behaviour from your macro.

This tutorial covers;

  • Recording a macro that consolidates data onto one worksheet
  • Using relative references in a macro
  • Viewing the VBA code generated by the recording and editing parts
Section 3: Getting Started with VBA
08:39

The Visual Basic Editor is the environment that you will be performing your VBA work. It can be accessed via the Developer tab or by pressing Alt + F11.

This lesson covers;

  • Opening the Visual Basic Editor
  • Exploring the VBE window
  • Inserting a module for your code
23:11

In this lesson we create a macro from scratch and begin to write our first code. Its a simple little bit of code to start with so we begin to understand the VBA object model and get familiar with VBA terminology and grammar.

This lesson covers;

  • Inserting a module and renaming it
  • Creating a new sub procedure
  • Laying out code
  • Writing comments
  • VBA syntax and terminology
  • Running a sub procedure
  • Referencing cells and changing values
11:08

You will always have a need to record a macro. It will generate code for you quicker than you can type, and help you learn the VBA for specific Excel commands you have not written before.

However recorded code is not as clean as written VBA and can often need some tidying up afterwards.

This lesson covers;

  • Two typical examples of the macro recorder generating redundant code.
  • Editing the code so that it is faster and easier to read.
08:45

When you write functions and use object methods in VBA, they may prompt you for information known as arguments. This is the same behaviour that you get when you write functions in Excel.

There are a few different ways that you can write arguments in VBA. You can write them shorthand or be more descriptive.

This lesson covers;

  • Using the arguments for the Protect method of the Activeworkbook object.
  • Writing arguments in both shorthand and the more descriptive format.
  • Understanding whether an argument is required.
29:52

Your VBA code is sure to have to refer to different cells, sheets and books. There are many different techniques for doing this. What you use depends on your location when you write the reference, and why you are doing it.

This lesson covers;

  • Techniques for referring to different workbooks.
  • Techniques for referring to different sheets.
  • Techniques for referring to cells such as the ActiveCell, Range, Cells, defined names and Selection.
  • Inputting values to cells.
  • Copying and pasting values.
  • Using the Offset object.
  • Stepping through code to understand and troubleshoot it.
08:23

There are three different types of error, logical, syntax and run-time errors. Until these errors are resolved you will not be able to run your procedures, or they will not run effectively.

This lesson covers;

  • Debugging syntax errors.
  • Debugging run-time errors.
  • Working in Break Mode.
7 questions

This quiz contains questions from the Introduction to VBA section.

Section 4: Working with Variables
17:24

Variables are used to store data during a macro. They can store data captured from a function, userform or message box.

They are faster, more meaningful and more dynamic than storing data in a worksheet They are a fundamental skill of VBA programming that will be used throughout the rest of this course.

This lesson covers;

  • Using variables to store data during a procedure.
  • The importance of declaring variables.
  • How to declare variables and understanding different variable data types.
  • Tracking variables using the Locals Window.
10:27

Declaring your variables is not required in VBA. However it is highly recommended to create faster, more readable code that is also easier to track and debug.

This lesson covers;

  • Use Option Explicit to force variable declaration.
  • Set the Editor to require variable declaration in all code modules.
  • Common VBA errors relating to using variables.
09:08

The scope of a variable is its Accessibility to other procedures in the project.There are three levels of Scope: Public Module, Private Module and Procedure.

This lesson covers;

  • Declare and use a variable accessible to all procedures within a module.
  • Create a variable accessible to procedures across all modules of a project.
09:39

Using an object variable allows you to store a reference to an Excel object. This object can be a range of cells, sheet, workbook or any other Excel object.

Using such a variable makes referencing that object during your code easier, faster and more readable.

This lesson covers;

  • Declaring object variables.
  • Using the Set keyword.
  • Using Worksheet and Range object variables.
5 questions

This quiz tests your knowledge on topics covered in the Using Variables section.

Section 5: Making Decisions
10:04

The If statement provides decision making for your code. It can be used for a single or multiple tests. The structure for an If statement is shown below.

This lesson covers;

  • The structure of the If.. Then.. Else statement
  • Writing single and multiple test If statements
  • Using AND and OR logic
06:03

The Select Case statement compares a single test expression against multiple possible values. Each case test consists of a test and an outcome to that test. The outcome statements may be multiple lines.

Select Case is considered more concise and readable than the equivalent If structure. The structure of Select Case is shown below.

This lesson covers;

  • The structure of the Select Case statement
  • Using Select Case for multiple tests on a value
Section 6: Looping
08:25

Conditional loops are used to repeat a set of instructions while a certain condition is satisfied, or until a certain condition is satisfied.

This lesson covers;

  • Writing Do While and Do Until loop structures.
  • Checking a condition before the loop, or after executing the command once.
06:36

The Cells object provides a fast and effective way to reference cells when using loops. It is a better alternative to using Activecell and its Offset property when you are looping through a very large list.

This lesson covers;

  • Understanding the benefits of the Cells object in loops.
  • Use the Cells object to reference cells in a Do Until loop.
24:53

This lesson looks at using a conditional loop to check and update an inventory list.

This lesson covers;

  • Using a Boolean variable for the loops condition.
  • The Do Until loop structure to loop through a list until an entry is found, or we hit the bottom of the list.
  • Adding new stock to the list and enter todays date for the transaction.
  • Using the Cells object to reference the cells in the list.
14:49

Counter loops iterate a loop for a specific number of repetitions. We can enter the specific number of repetitions for the loop, or use a variable or collection.

This lesson covers;

  • Writing the For Next loop structure.
  • Using a counter loop to remove the blank rows from a worksheet.
  • Using the CountA function to count non-bank cells.
05:50

Counter loops can be used to iterate through every object within a collection. For example, to loop through all the worksheets in a file, or through every open workbook.

This lesson covers;

  • Using the For Next loop structure to loop through every worksheet in a file.
  • Protect the worksheets of a workbook.
08:05

Collection loops repeat a set of instructions on every object within a collection. Object variables make it easier to reference the objects during the loop.

This lesson covers;

  • Writing the For Each Next loop structure.
  • Using a collection loop to perform an action on every cell within a range selected by the user.
  • Convert text to uppercase using the Ucase function.
16:16

Loops can be inserted into other loops creating interior and exterior loops, or nested loops. Any loop can be inserted inside another is used for many reasons. For example, to loop through all the worksheets (interior loop) for multiple books (exterior loop), or to loop through all cells in a range (interior loop) on all worksheets of a book (exterior loop).

This lesson covers;

  • Using multiple Do loops.
  • Getting our inventory list spreadsheet to update for multiple stock items.
  • Making edits to your code to work with nested loops.
09:53

A big aspect of an efficient procedure is the speed that it executes. The more calculations you ask it to perform, information it stores and loops it goes through will all contribute to the weight of your procedure.

There are a few settings that can be switched off to improve the speed that a procedure executes, and to stop interference whilst it runs.

This lesson covers;

  • Changing application settings to dramatically improve the speed a macro executes.
  • Changing settings to prevent interference from the application whilst a procedure executes.
Section 7: Error Handling and Debugging
18:51

One thing you can almost guarantee is that your code will contain bugs at some point. A bug simply means that your code is not functioning properly. They are typically generated by typing mistakes, or errors in the code’s logic.

This course has already covered some techniques for solving bugs in our code. This lesson explores a few other debugging tools that you might find useful.

This lesson covers;

  • Using Breakpoints to stop code running at specific points.
  • Using the Immediate Window to test code and check the values of variables and cells.
  • Using the Watch Window to track specific variables and get code to break dependent upon expressions.
  • Solving two minor bugs in the code of our inventory list spreadsheet.
05:39

There are many reasons why Runtime Errors occur in a procedure. The ability to handle these errors before they happen is an essential part of VBA programming.

The On Error Resume Next statement is used to shut errors down so that your code continues to run, and is not interfered with.

This lesson covers;

  • How to use the On Error Resume Next statement in a procedure.
  • An example of how this statement can be used to prevent errors occurring during a procedure.
  • An understanding of the limitations of this statement and when not to use it.
09:18

Error handlers can be created to effectively handle runtime errors in a procedure. An error handler is a section of code created to run in the case of an error occurring.

This lesson covers;

  • Using the On Error GoTo statement.
  • Creating an error handler.
  • Ensuring the error handler only executes when necessary.
  • Disabling an error handler.
15:33

You may need to set up multiple error handlers in your code. When using multiple error handlers you need to be sure that the correct handlers are triggered at the correct times.

This lesson covers;

  • Creating multiple error handlers.
  • Using the Resume statement.
07:03

There are different types of runtime error that can occur in your code. The Err object can be used to identify which error occurred, and then run the required code to handle it.

This lesson covers;

  • Using the Number property of the Err object.
  • Testing the Err object to see what error occurred and take the necessary action.
08:15

For errors that you anticipate being caused by user error, there may be better ways to handle them than creating error handlers and using the On Error statement.

Other techniques can include using some of Excel’s existing functionality, or writing different VBA that tests for these specific issues.

This lesson covers;

  • Using Excel features such as Workbook Protection and Data Validation and prevent errors occurring.
  • The IsNumeric function.
Section 8: User Interaction
11:14

Message boxes are one of the most popular ways of interacting with a user. They allow you to ask a question and provide buttons to capture the user response.

This lesson covers;

  • Wrapping message box text onto more than one line.
  • Using a message box in statement and function form.
  • Displaying different buttons on a message box.
  • Capturing a user’s response for decision making in a procedure.
15:25

Input boxes are used to capture typed entries from a user. For example, asking a user which worksheet to open, or item to search for.

Input boxes can be used to capture numeric entries, dates, Boolean values and even cell references. They are however typically used to capture strings.

This lesson covers;

  • The syntax of the input box.
  • Using an input box to capture text and numeric entries.
  • Validating data entered by the user.
  • Using an input box in its function form and as a method of the application object.

Below is a list of type index numbers when using an input box in method form.

0

Formula

1

Number

2

Text

4

True or False

8

Cell reference

16

Error value

64

An array of values

14:18

The built-in dialog boxes of Excel such as the Save As, Open File and Select Folder dialog boxes can be initiated using VBA code. These dialog boxes provide a familiar interface to a user for performing common tasks such as opening a file.

This lesson covers;

  • Initiate the Save As dialog box for a user to save a file.
  • Modifying the properties of a dialog box.
  • Validating the user’s interaction with the dialog box.
  • Capturing the information entered to save the workbook.
Section 9: Working with Files and Folders
21:04

Using VBA you can create loops to repeat a set of actions for every workbook within a directory, or folder.

This lesson covers;

  • Storing a procedure in the Personal Macro Workbook.
  • Using a built-in dialog box to make it easy for a user to select a folder.
  • Using the Dir function for retrieving file names.
  • Looping through all the files within a specified folder.
12:02

When accessing files in a directory you may want to be more specific about what files you want to use. There is also various information you can access about the files.

This lesson covers;

  • Using the Dir and Right functions to retrieve only files of a specific file type.
  • Listing all the text files from a folder on a worksheet.
  • Printing information about a file such as size and date last modified to a worksheet.
14:50

When opening and saving files you may need to test to see if a directory currently exists, and if not create it during the procedure.

Saving a spreadsheet as a PDF is useful when sending data to clients, or providing it for customers. This procedure completely automates the report creation process.

This lesson covers;

  • Testing to see if a directory already exists.
  • Creating a directory on the fly in a procedure.
  • Saving each worksheet as a separate PDF to a folder.
Section 10: Working with Arrays
17:11

An array is a variable that can store more than one element of data. You can think of an array as a table of related data. Arrays can store data of any type.

Using data stored in an array is faster and more efficient than using data stored in worksheet cells.

This lesson covers;

  • Declaring a one-dimensional fixed array.
  • Populating an array with data from the cells on a worksheet.
  • Using the data stored in the array in a procedure.
  • Erasing an array.
11:45

Arrays can have multiple dimensions. An array can have as many as 60 dimensions. This makes them able to store complex lists of related data.

This lesson covers;

  • Declaring a two-dimensional fixed array.
  • Populating and using the data from the array.
14:28

You will not always know the size of an array at the time you declare it. Fortunately you can re-dimension, or resize, an array during a procedure. You can resize an array as many times as necessary. This is known as a dynamic array.

This lessons covers;

  • Declaring a dynamic array.
  • Re-dimensioning an array.
  • Populating a dynamic array.
  • The LBound and UBound functions.
  • Using Preserve when re-dimensioning an array.
Section 11: UserForms
31:31

A userform is a very useful tool for interacting with users. It enables you to create a user friendly and customised interface for a user.

Form controls such as combo boxes, option buttons, check boxes and calendar pickers can be added to make it easy for you to capture and validate information from a user.

This lesson covers;

  • Creating a new userform.
  • Naming conventions for form controls.
  • Working with different form control properties.
  • Arranging controls on a form.
  • Specifying a tab order for the form controls.
10:14

Once you have designed a userform, you will need a method of opening and closing the form. It is possible to run code as the form opens too. This can be useful for clearing fields, setting defaults and building lists.

This lesson covers;

  • Opening a userform from a button on the spreadsheet.
  • Running code as the userform opens.
  • Closing a userform from a button on the form.
19:15

Once a form has been created it will need to be made functional. Each control on a form, and the form itself, has events that you can use as a trigger for your procedures.

This lesson covers;

  • Writing code for a check boxes change event to affect the visibility of another control.
  • Adding the data collected from a form to the bottom of a list on a worksheet.
  • Converting data types such as a string to a number.
12:30

We have spoken about validating data and error checking on this course already as it is very important. This lesson gives an insight into validating the data on a form.

This lesson covers:

  • Running validation procedures when the form is submitted, and after form controls are changed.
  • Ensuring mandatory fields are completed.
  • Checking the data type of textbox entries.
Section 12: Running Procedures from Events
06:00

This lesson looks at how to run your procedures when a workbook is opened.

This lesson covers;

  • Using the Workbook Open event.
  • Selecting sheets and cells on the opening of a workbook.
08:09

This lesson looks at how to run a procedure whenever a workbook is saved.

This lesson covers;

  • Using the Workbook Before Save event.
  • Checking that a version number has been changed before saving the file.
  • Cancelling the save if certain criteria are not met.
04:19

This lesson looks at how to run a procedure whenever a new sheet is inserted.

This lesson covers;

  • Using the New Sheet event.
  • Prompting the user to enter a sheet name using an input box.
  • Using parameters provided by an event procedure.
15:02

This lesson looks at how to run a procedure when a different cell on a worksheet is selected.

This lesson covers;

  • Using the Selection Change event.
  • Highlighting the active row to make viewing a records data easier.
  • Creating and using a public variable.
09:49

This lesson looks at how to run a procedure when a value on a worksheet is changed.

This lesson covers;

  • Using the Worksheet Change event.
  • Testing which cell was changed using the Target parameter.
  • Testing what change was made and displaying a userform to prompt a user to add comments to an entry.
Section 13: Creating Function Procedures
14:33

A function procedure returns a value as its outcome. They can be called by sub procedures. This technique breaks your procedures down to smaller manageable chunks and makes code easier to read.

Function procedures can also run from an Excel worksheet in the same way that built-in functions such as VLOOKUP and SUM are used.

This provides you with a way to create your own functions to either simplify an otherwise complex formula, or create functionality that Excel does not currently provide.

This lesson covers;

  • Creating a function procedure to simplify a complex formula that calculates a salesperson’s commission.
  • A recap on using a Select Case statement.
  • Use the function procedure from a worksheet.
  • Call the function procedure from another sub procedure.
06:18

This lesson looks at adding multiple arguments to your function procedures. An argument can be optional, just like you see in Excel’s built-in functions. If an argument is optional you will need to include the handling of this being left empty in your code.

It is a good idea to specify data types for your arguments and the function procedure itself. This is similar to how we specify the data types of variables.

This lesson covers;

  • Using multiple arguments with a function procedure.
  • Adding optional arguments to a function.
  • Specifying the data type for arguments and functions.
09:30

When users come to using your function procedures, they will need to understand what it does, and what it requires.

Using VBA you can provide a description for both the function and its arguments. This description can be seen when using Excel’s function wizard.

This lesson covers;

  • Documenting a function using the macro options method.
  • Adding a description for a function and for its arguments.
07:34

Creating an add-in is the best way of saving your procedures to distribute to others. You can send the Add-In, save it on a shared drive or make available it for download.

Once the Add-In has been saved to the correct folder, it needs to be enabled. The Add-In is initialised every time Excel is opened.

This lesson covers;

  • Creating an Excel Add-In
  • Enabling the Add-In
Section 14: Conclusion
03:35

Throughout this course we have covered the VBA skills and techniques to handle almost any Excel project. Hopefully as the course has progressed you have got involved with the projects that we have been creating and tried to set them up yourselves, or even tweak and perfect them further.

The next step is to involve yourself in some VBA projects of your own. Maybe you have a work requirement for Excel VBA. If not, just practice by creating some projects to enhance Excel’s functionality, or just for fun.

To truly understand and get familiar with VBA, you have to be using it. Make up some examples and have a go at creating them and writing your own code.

Bonus Lecture: Free Resources to Keep Learning VBA
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Alan Murray, Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.

Ready to start learning?
Take This Course