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.
3.4 (21 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,633 students enrolled
$20
Take This Course
  • Lectures 77
  • Contents Video: 9 hours
  • Skill Level Intermediate 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 5/2014 English

Course 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.

What are the requirements?

  • A copy of Microsoft Excel
  • Time and patience to work through the course
  • Basic problem solving skills

What am I going to get from this course?

  • 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

What 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

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
01:35

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.

08:01

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.

Section 2: General overview
01:27

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.

03:18

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.

Section 3: VBA editor - layout and basic functionality
08:06

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.

06:41

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
08:27

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
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
08:40

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

05:36

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
07:52

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
08:58

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

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
09:56

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

01:35

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.

Section 4: Learn the basics of VBA programming
07:40

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.

07:43

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

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.

04:26

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

06:46

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.

05:00

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.

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.

08:11

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

10:18

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.

06:43

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.

05:53

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
11:56

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

11:05

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.

01:54

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.

06:33

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.

02:53

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
11:03

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

02:35

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.

05:02

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

01:31

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.

05:45

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

02:24

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.

08:26

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.

01:19

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.

08:11

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’.

07:47

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

06:14

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

08:57

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.

10:53

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

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 array
09:27

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

07:36

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.

Section 5: Excel Objects
10:06

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.

07:33

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

Section 6: Classes
03:44

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.

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.

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

09:41

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

Section 7: String Manipulation
03:32

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
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.

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

07:21

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

05:48

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

09:50

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

Section 8: Userforms
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.

08:02

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

07:14

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.

01:03

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.

Section 9: More advance examples
08:19

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

07:38

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

06:13

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

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

07:02

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

11:26

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.

09:37

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

05:10

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

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.

12:28

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

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.

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

12:15

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

Section 10: Conclusion
07:11

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

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Francois Barnard, 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#.

Ready to start learning?
Take This Course