A beginner’s and intermediate guide to VBA programming
4.1 (32 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.
1,672 students enrolled
Wishlisted Wishlist

Please confirm that you want to add A beginner’s and intermediate guide to VBA programming to your Wishlist.

Add to Wishlist

A beginner’s and intermediate guide to VBA programming

Learn how to unlock the power of Microsoft's VBA programming language to build effective and powerful programs.
4.1 (32 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.
1,672 students enrolled
Created by Francois Barnard
Last updated 6/2014
English
Current price: $12 Original price: $20 Discount: 40% off
3 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 9 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion

Training 5 or more people?

Get your team access to Udemy's top 2,000 courses anytime, anywhere.

Try Udemy for Business
What Will I Learn?
  • By the end of this course you will be able to design and develop your own custom programs
  • In this course you will learn how to control program flow through VBA statements
  • In this course you will learn how to develop custom Excel objects
  • In this course you will learn how to develop functional programs and sub procedures
  • In this course you will learn to how access standard Excel objects from within the VBA editor using the Object browser
  • In this course you will learn to how add error handling capabilities to your programs
View Curriculum
Requirements
  • A copy of Microsoft Excel
  • Time and patience to work through the course
  • Basic problem solving skills
Description

Have you ever felt frustrated using Microsoft Excel to perform mundane and often repetitive tasks? Have you ever felt that there has to be an easier and faster way to perform to do something? Well, using Microsoft Visual Basic for Application (VBA) programming language allows you to automate manual tasks within Excel and allows you to be more effective and efficient with your time. Not only does it provide you access to a rich library of additional tools and capabilities, over and above what a standard user may have access to, it can also be used to integrate with some of the Microsoft other Office applications such as Outlook, Word and MS Access.

  • So, who should take this course?
  • Anyone who wants to learn how to be a proficient VBA programming

Anyone who wants to learn how to effectively utilize the tool to solve and automated manual tasks

In this course you will learn some of the fundamental concepts of the VBA programming language. Not only does this course include a review of the basic concepts and key functionality available within this rich programming language such as the IF, Select Case and Loop Statements, you will also learn how to design and develop your own custom objects using the concepts of object orientated programming through the use of class modules.

This course starts off with a brief refresher on the basic concepts of the VBA programming language and a review of the VBA Editor, the development environment that we will use during this course.

This program also includes a number of practical examples, to help demonstrate how to effectively utilize the concepts that you have covered during this course. This course includes the key concepts and practical examples of the following VBA statements and functionality:

  1. IF Statements
  2. Loop Statements (For Next, Do While, Do Until)
  3. Exit Statements
  4. Goto Statements
  5. Select Case Statements
  6. Enumeration Data Types
  7. The DoEvents Statement
  8. Macros
  9. Arrays (declaring, re-initializing, auto populating and deleting arrays)
  10. Error Handling
  11. Standard Excel Objects
  12. Functions and Procedures
  13. Passing Values To Functions By Value
  14. Passing Variables to Functions By Reference
  15. Advance String Manipulation using the Trim, LTrim, RTrim, Left, Right, Instr and InstrRev Statements
  16. User forms
  17. Classes / Custom Objects
  18. Variable Declaration And Scope

We finish the training course by reviewing additional practical examples of how to apply the key concepts that we have reviewed during this course. In total, this course provides approximately 9 hours of course videos, including practical files and examples to help you become a proficient and effective programming.

Who is the target audience?
  • Students
  • Beginner to intermediate programmers
  • Users looking to automate frustrating and time consuming tasks
  • Expiring programmers
  • Someone looking to build upon previous experiences and exposure to VBA
Compare to Other Excel VBA Courses
Curriculum For This Course
77 Lectures
09:12:31
+
Introduction
2 Lectures 09:36

Before we start the training course be sure to download the specific project files that you will need. Attached to the course you will find both the Excel 2013 and Excel ’97-2003 version project files attached and these files will be crucial to follow along as we progress through this course.

These project files will be used through Section 8, after which you will find the individual program files included as supplementary material to each lesson when we delve into more practical and advance coding examples in Section 9.

Download your working files first
01:35

During this training session, we will review the general layout and structure of this course. We will also review, at a high level, the specific topics that we will be covering and discuss who the target audience for this course should be. Practice makes perfect, so the only way to actually become a proficient programmer is to start coding.

The training course is also broken up into two separate components. Sections 1-8 is designed to teach you the fundamental basics of VBA programming. We will review the basic statements and functionality that you will use to control program flow within your projects. Essentially, we will be reviewing how you actually control the flow of your program execution in order to solve the problem that you are faced with.

Once we have covered the basic concepts and functionality in enough detail, which includes the review of practical examples of how they work, we will move onto Section 9, where we use these basic concepts to develop more complex and advance programs.

Being familiar with the basic VBA functionality and objects, properties, methods and events available to you through the Excel reference library is the key to becoming an effective programmer.

Preview 08:01
+
General overview
2 Lectures 04:45

VBA or Visual Basic for Applications is Microsoft’s implementation of their event driven programming language available within Excel and some of Microsoft other office products such as Word, PowerPoint etc. The programming language also comes with an integrated development environment (IDE) that provides access to key functionality that you will use to develop your programs.

What is VBA?
01:27

During this session we will be reviewing some of the lessons learned from years of using VBA to develop custom program solutions. My first approach is always to try and accomplish a task using standard Excel functionality that are available to any regular user before I crack open the code book. Using standard functionality makes the solution easier to support by someone without any prior coding experience so that you may not have to be the one to support that program indefinitely. Also, why spend the time developing a program if Microsoft have already provided that standard capability to you for your use.

However, when you do actually have to develop a program, do not take any shortcuts. Always assume that the project will grow in scope and complexity and that you will be the one supporting the application. Use copious amounts of comments and plan you design before you even start writing a single line of code. Also, do not hesitate to leverage and retool other programs that you have written to accomplish your tasks and remember that the internet provides a wealth of examples and documentation that you can use.

Lessons learned
03:18
+
VBA editor - layout and basic functionality
11 Lectures 01:18:52

During this session we will review the general layout of the integrated development environment (IDE) that Microsoft provides within their Visual Basic for Applications (VBA) programming language.

We will review the Project Explorer, used to navigate between the various Excel objects such as workbooks and worksheets and then move onto the Properties Window. We will also look at the debug or immediate window and provide a cursory review of the menus that are available.

General overview
08:06

Overview of the File Menu, specifically focused on learning how to:

  1. Save a project file
  2. Export files from your project for future use
  3. Import files into your existing project to accelerate your development efforts
File menu
06:41

Overview of the Edit Menu, specifically focused on learning how to:

  1. Deploy bookmarks to navigate within your program code
  2. Find and replace text within a function and project
  3. Use the standard text capabilities such as copy, cut, paste, undo and redo
Edit menu
08:27

Overview of the View Menu, specifically focused on learning how to:

  1. Utilize the Object Browser to navigate code libraries
  2. Toggle between the VBA editor and the Excel application
  3. Access and configure the various editor tools
View menu - Part 1
05:10

Overview of the View Menu, specifically focused on learning how to:

  1. Utilize the Object Browser to navigate code libraries
  2. Toggle between the VBA editor and the Excel application
  3. Access and configure the various editor tools

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

View Menu - Part 2
08:40

Overview of the Insert Menu, specifically focused on learning how to:

  1. Insert Procedures, Modules, UserForms and Class Modules into your project
  2. Rename Modules using the properties window
Insert menu
05:36

Overview of the Format Menu, specifically focused on learning how to:

  1. Insert a UserForm into your project and adding a couple of controls
  2. Select and change focus on any selected controls
  3. Resize and align controls
  4. Equalize vertical and horizontal spacing
Format menu
07:52

Review the basic capabilities of the Debug, Run and Tools menus, including how to reference new code libraries from within your current VBA project.

Debug, Run, Tool & Window Menu
08:58

During this session we will be reviewing a practical example of how to debug your program using the key concepts we covered in the previous training session. We will review practical examples of how you will:

  1. Compile a program prior to code execution
  2. Step into, out of and over code execution
  3. Learn how to toggle watch expressions
  4. Learn how to employ breakpoints to pause code execution for debugging purposes
  5. Learn how to utilize the watch window to monitor the variables tagged with watch expressions
Example - Debugging code - Part 1
07:51

During this session we will be reviewing a practical example of how to debug your program using the key concepts we covered in the previous training session. We will review practical examples of how you will:

  1. Compile a program prior to code execution
  2. Step into, out of and over code execution
  3. Learn how to toggle watch expressions
  4. Learn how to employ breakpoints to pause code execution for debugging purposes
  5. Learn how to utilize the watch window to monitor the variables tagged with watch expressions

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Debugging code - Part 2
09:56

During this training session we will be reviewing our progress to date and what you can expect from the next part of this training course.

In summary, we have covered the basics of VBA programming; lessons learned and reviewed the layout and main menu options available with the VBA integrated development environment (IDE).

Going into the next section we will be focusing on the basic functions available to control program flow in you projects. We will start off by reviewing various presentations describing the key concepts, functionality and syntax to use and then go into reviewing practical examples for each of the concepts covered.

Progress check
01:35
+
Learn the basics of VBA programming
32 Lectures 03:39:26

During this session we will review the various variable data types available within VBA. Specifically we will be reviewing the key characteristic of each variable and the value ranges or limits enforced.

We close this training session by looking at the concepts associated with the constant variable data types.

Variables
07:40

During this session we will review a practical example of how to declare and initialize variables prior to using them.

Example - Declaring and initializing variables
07:43

The variable’s scope dictates its accessibility to your code statements within your project. Within this session we will review a practical example of how the concept of variable scope works, which includes the review of public, private and local variables.

Example - Variable scope - Part 1
08:22

The variable’s scope dictates its accessibility to your code statements within your project. Within this session we will review a practical example of how the concept of variable scope works, which includes the review of public, private and local variables.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Variable scope - Part 2
04:26

Constant variables are similar to variables in that they are used to store values. However constant values are defined prior to program execution and cannot be changed during code execution. Within this session we will review a practical example of how to declare and use constant variable values within your project and discuss the general rule of thumb of when to deploy them within your project.

Constant variables
06:46

Functions and sub procedures consist of a series of VBA statements enclosed ether within a function statement (Function…End Function) or a sub procedure statement (Sub…End Sub). Within this session we will review the main differences and uses of both sub procedures and functions.

Functions & Procedures
05:00

Passing and returning values to Functions is critical to developing complex, modular and self-contained programs. During this session we will review practical examples of how to pass and return values from Functions using the default ByVal method.

Example - Passing & returning values - ByVal - Part 1
08:08

Passing and returning values to Functions is critical to developing complex, modular and self-contained programs. During this session we will review practical examples of how to pass and return values from Functions using the default ByVal method.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Passing & return values - ByVal - Part 2
08:11

Building upon the previous session, we will review a practical example of how to use the ByRef method to pass variable references to functions. Passing variables by reference ensures that any changes made to those variables within the receiving function are instantly reflected within those variables.

Preview 10:18

A sub procedure consists of a series of VBA statements enclosed within a sub procedure statement (SubEnd Sub). Within this session we will review a practical example of how to declare and use a sub procedure within your project.

Example - Sub Procedures
06:43

During this session we will review the IF statement, which is used to execute a group of VBA statements based on the determination of True or False based on a defined expression.

Syntax:

  1. IF…Then…End IF
  2. IF…Then…Else…End IF
  3. IF…Then…ElseIF…End IF
IF statement
05:53

During this session we will review a practical example of how construct and use a basic IF statement.

Example - IF statement
11:56

During this session we will build on the previous IF statement example and review a practical example of using the ElseIF statement to enhance a basic IF statement and include multiple conditional statements.

Example - IF statement (Continued)
11:05

The Select Case statement is similar to the If statement in that it is used to evaluate a condition for True or False and then allows you to execute a series of VBA statements based on that determination.

Select Case Statement
01:54

The Select Case statement share the basic functionality as a basic IF statement. During this session we will review a practical example of using a Select Case statement to execute code statements based on list of defined expressions.

Example - Select Case Statement
06:33

Loop statements are used to cycle through and repeat a series of VBA statements based on a predefined number of times. During this session we will review the For...Next, Do…While and Do…Until loops.

Syntax:

  1. For…Next
  2. Do…While
  3. Do…Until
Loop Statements
02:53

During this session we will review a couple of practical examples of using the For…Next, Do…While and Do…Until loop statements.

Example - Loop Statements
11:03

An Exit statement is used to break out of the current code execution. It can be used to break out of Functions, Procedures, Loop statements, IF statements etc. During this session we will review the basic uses and capabilities of the Exit statement.

Exit statement
02:35

During this session we will review some practical examples of how to terminate code execution using the Exit statement.

Example - Exit Statement
05:02

The GoTo Statement is predominantly used to implement error handling routines to your program. During this session we will review the GoTo statement, which is used to move code execution to a defined location within your program.

Goto statement
01:31

During this session we will review a practical example of using the GoTo statement to move code execution within a program.

Example - Goto statement
05:45

Enumeration data types provide the convenience way of controlling the values assigned to your variables. During this session we will review the enumeration data type and the syntax used.

Enumeration statement
02:24

During this session we will review a practical example of defining and using an enumeration data type to control the values assigned to your variables.

Example - Enumeration
08:26

The DoEvents statement is used to control CPU processing, in order to free up computing resources to avoid the Excel application becoming unresponsive when executing resource intensive functions and procedures.

DoEvents statement
01:19

During this session we will review how to use the DoEvents statement to release computer resources to enhance the overall user experience and to avoid your Excel project file from becoming ‘Unresponsive’.

Example - DoEvents
08:11

During this session we will review a practical example of adding basic error handling routines to your program.

Error Handling
07:47

During this session we will review a practical example of using the macro recorder to create a sub procedure.

Macros
06:14

An array is a group of variables of the same data type. During this session we will review the basic functionality of an array, including a review of the main differences between an one dimensional and multi-dimensional array.

Arrays
08:57

During this session we will review a practical example of how to declare and work with an array.

Example - Arrays
10:53

During this session we will review a practical example of how to use the following key statements to process and interrogate array objects. Specifically we will review how to:

  1. Use the LBound and UBound to determine the upper and lower limits of your defined array
Example - Arrays Advance - Part 1
08:45

During this session we will review a practical example of how to use the following key statements to process and interrogate array objects. Specifically we will review how to:

  1. Use the LBound and UBound to determine the upper and lower limits of your defined arrays

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Arrays Advance - Part 2
09:27

During this session we will be reviewing a practical example of how to populate an array using the Split function including deleting the values currently stored within an array using the Erase statement.

Example - Arrays (Continued)
07:36
+
Excel Objects
2 Lectures 17:39

Mastering the Excel objects, properties, methods and events provided within the Excel reference code library is critical to becoming an effective programmer. It also allows you to identify potential solutions to code problems based on your determination on how you can use that available functionality to your benefit and use.

During this session, we will be reviewing how to navigate the Object Browser to review the basic objects, properties, methods and events available to you.

General Overview
10:06

Review a practical example of accessing general Excel objects contained within the Excel code library.

A practical example
07:33
+
Classes
4 Lectures 30:47

Classes are used to deploy custom Excel objects within your projects, which in turn allow you to leverage the basic principles of object orientated programming. Using classes allow you to define unique properties, methods and events for your objects and help simplify and your code.

Classes
03:44

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

Example - Classes - Example 1 - Part 1
10:49

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Classes - Example 1 - Part 2
06:33

During this training session we will review a practical example of how to build a custom Excel object, including properties and methods using Excel classes.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Classes - Example 1 - Part 3
09:41
+
String Manipulation
6 Lectures 41:54

Excel provides a number of statements that can be combined to develop some advance string manipulation routines. During this session we will review the use of the following statements:

  1. Trim – remove leading and trailing spaces
  2. LTrim – remove the leading spaces
  3. RTrim – remove the trailing spaces
  4. Right – used to extract a substring from a string starting from the right
  5. Left – used to extract a substring from a string start from the left
  6. Instr – used to return the position of the first character match
  7. InstrRev – used to return the last position of the character match
Overview
03:32

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

Example - String Manipulation - Example 1 - Part 1
07:27

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - String Manipulation - Example 1 - Part 2
07:56

During this training session we will review an example of performing advance string manipulation routines using a combination of the Trim, LTrim, LTrim, Right, Left, Instr and IntrRev statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - String Manipulation - Example 1 - Part 3
07:21

During this training session we will continue on our previous example of building complex and advance string manipulation statements.

Example - String Manipulation - Example 2 - Part 1
05:48

During this training session we will continue on our previous example of building complex and advance string manipulation statements.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - String Manipulation - Example 2 - Part 2
09:50
+
Userforms
4 Lectures 27:24

UserForms can be used to control and validate user input. During this training session we will review a practical example of adding a UserForm to your project.

Example 1 - UserForm - Part 1
11:05

UserForms can be used to control and validate user input. During this training session we will review a practical example of adding a UserForm to your project.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example 1 - UserForm - Part 2
08:02

During this training session we will continue to build on the previous example, review a practical example of adding input validation to your custom user form.

Example 2 - UserForm
07:14

During this training session we will be reviewing our progress to date and what you can expect from the last part of this training course. In summary, to date we have covered:

  1. The basics of VBA programming
  2. Lessons learned
  3. Reviewed the general layout of capabilities of the VBA editor
  4. Reviewed how to control VBA program flow using VBA statements

During the last part of this training course we will focus on reviewing additional examples to help drive the concepts home.

Progress check
01:03
+
More advance examples
13 Lectures 01:54:57

During this training session we will review an example of copying text within a worksheet based on pre-defined routines.

Example - Copy text down - Part 1
08:19

During this training session we will review an example of copying text within a worksheet based on pre-defined routines.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Copy text down - Part 2
07:38

During this session we will be reviewing an example of merging text using the Excel range objects.

Preview 06:13

During this session we will be reviewing an example of merging text using the Excel range objects.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Preview 09:28

During this session we will be reviewing an example of merging text using the Excel range objects.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Merge text - Part 3
07:02

During this training session we will be reviewing an example of creating a user defined function to remove text from a string based on user input.

Example - Remove values
11:26

During this training session we will be reviewing a practical example of how to access text files for input and output.

Example - Access to text files - Part 1
09:37

During this training session we will be reviewing a practical example of how to access text files for input and output.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Access to text files - Part 2
05:10

During this training session we will review a practical example of how to remove duplicate values from an array using an Excel dictionary object.

Example - Remove duplicate values from arrays - Part 1
05:48

During this training session we will review a practical example of how to remove duplicate values from an array using an Excel dictionary object.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Remove duplicate values from arrays - Part 2
12:28

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

Example - Classes - Part 1
08:46

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Class - Part 2
10:47

During this training session we will be looking at a more advance example of using the concepts of object orientated programming using custom objects. We will also review how to use collections and assign objects to each other.

Note: This training session is a continuation of the previous session, which has been broken into multiple parts to make it easier for you to follow and stop whenever you need to

Example - Class - Part 3
12:15
+
Conclusion
1 Lecture 07:11

During this training session we will be reviewing what you have covered during this training course.

Conclusion
07:11
About the Instructor
Francois Barnard
4.1 Average rating
32 Reviews
1,672 Students
1 Course
Information Technology Consultant

I have always been very passionate about computers and technology in general after receiving my first 8086 Olivetti computer in the late 80’s. I still recall the day that that Double Tools for DoubleSpace was released, which allowed you to backup 2.8 mb on a 1.44 mb disk. Back then I thought that was the ultimate technological breakthrough. However, needless to say, I think we have progressed well beyond that.

I graduated from University with a BCom and Honors in Accounting and promptly went to work for one of the big public accounting firms, where I also managed to achieve my chartered accountant designation. Even though I started in accounting I have always wanted to work in technology and was fortunate enough to be introduced to programming and relational database design early on my career, developing decision support and financial models for clients using MS Access and Microsoft Excel. My experiences with these tools and programming in general have taught me the value of good coding practices and I have come to believe that you always have to assume that no matter how small a project, it can always grow and expand well beyond your original scope. Now, I always design and build programs as if I am going to have to support them for years to come.

I also have an extensive technical background and work experience in Information Security, data analytics and data mining and currently work for a large national consulting firm, located in Northern Virginia.

Other programming languages that I have experience with in addition to VB and VBA are Objective C, C++ and C#.