Excel VBA: How to Write Like a Professional

See how with zero experience you can write professional quality VBA. A step-by-step guide with each step clearly shown.
4.3 (102 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,543 students enrolled
$200
Take This Course
  • Lectures 80
  • Contents Video: 4.5 hours
    Other: 28 mins
  • Skill Level All Levels
  • 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/2015 English

Course Description

14th Aug 2016 Added New Content!

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

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

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

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

-----------------------------------------------------------------------------------------------------------------

5th July 2015 Added New Content!

Using Arrays in VBA: 6 brand new lectures.

-----------------------------------------------------------------------------------------------------------------

13th April 2015 Added New Content!

  1. Using Functions and Subs(6 Lectures)
  2. How to Run Your Macros(3 Lectures)

-----------------------------------------------------------------------------------------------------------------

FINALLY! A Way To Write VBA Like a Professional

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

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

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

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

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

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

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

* The In Built VBA Tools That Help You Easily Find Bugs - If you think you would make a good detective then you may really enjoy this* How to Easily Format any Cell - Changing a cells font, color, format is so simple you could do it now.* These "Evil" Workbook types should never be used - Do You Use them?

* And Much More. 

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

What are the requirements?

  • Knowledge of Microsoft Excel
  • A Laptop/PC with a copy of Microsoft Excel

What am I going to get from this course?

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

What is the target audience?

  • Accountants/Auditors/Financial planners
  • Business/Financial Analysts
  • Managers/Project Managers/Project Planners
  • Anyone who wants to learn how to write VBA in a professoinal environment
  • Anyone who wants to exponentially increase their productivity at work
  • Anyone who wants an introduction to programming with a straightforward langauge
  • Anyone who wants to increase their value to their current or potential employer
  • Any user of Excel who wants to dramatically increase their abiltiy

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
02:23

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

  1. Assign some time each day
  2. Practice writing code
  3. Break tasks down into parts
  4. Don't let errors stop you
  5. Close all workbooks except the one your working with
  6. Enjoy it
A Welcome Note From Paul and Course Source Code
1 page
BONUS 1: FREE VBA CHEAT SHEET - The Essentials
1 page
BONUS 2: FREE VBA CHEAT SHEET - Workbooks, Worksheets and Ranges
Preview
1 page
BONUS 3: Excel VBA Blog Resource
Preview
1 page
Section 2: GETTING STARTED
01:55

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

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


04:47

Create a simple Macro using the Developer Ribbon.

Run the Macro from Excel and then Visual Basic

04:36

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

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


02:40

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

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

03:39

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

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

11 pages

Course Notes for the Section: Getting Started

Section 3: Workbooks and Worksheets
01:46

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

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

How to get any Workbook
03:45
01:00

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

03:20

In this lesson we look at using a workbook variable.

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

03:32

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

Worksheets("Sheetname")

Worksheets(Index)

ActiveSheet

06:16

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

04:03

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

It has two distinct advantages:

  1. If the user changes a worksheet name the code will still work
  2. You can use it directly to access a sheet - you don't need to mention the workbook
03:08

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

04:09

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

10 pages

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

Workbooks and Worksheets Quiz
10 questions
Section 4: Using Variables
04:37

An Introduction to variables and how to use them.

03:30

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

Includes

  • Left - gets the left most characters
  • Right - gets the right most characters
  • Len - gets the length of a string
03:08

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

Section 5: Ranges, Cells and Offsets
03:14

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

04:38

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

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

For Example

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

02:38

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

03:17

A sample Macro showing the benefit of using Offset.

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

02:39

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

03:53

How to format cells. Included in this lesson:

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


Section 6: Mini Project - Fruit Sales
Article

The two workbooks used in this sections videos.

00:54

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

03:42

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

02:16

We write the values to our report.

04:17

Make the code more flexible using Offset

Section 7: Debugging - How to see what your code is actually doing
01:01

An introduction to this section on Debugging

02:17

How to step through the code

02:29

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

02:53

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

03:30

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

02:11

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

Section 8: If and Select
04:54

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

  • x > 1
  • x<1
  • x=5
  • x<>5
  • x>5 and x<=10
02:41

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

03:07

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

an example

If x>5

Debug.Print "x is greater than 5")

ElseIf x=10

Debug.Print "x equals 10")

Endif

02:35

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

03:49

Another example of using the Select Statement

Section 9: Loops
02:06

An introduction to using Loops

04:07

A guide to using the For Loop.

This loop uses the format:

For i = 1 To 10

Next i

More on the For Loop
04:51
03:42

The For Each Loop is used to read through collections

an example is

For Each wk in Workbooks

Next

04:21

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

03:12

Using the Do Loop with Until

02:27

Using the Do Loop with While

00:57

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

01:55

A Summary of all the Loops we covered in this section

Section 10: Errors
04:28

What are syntax errors and how to deal with them.

Syntax errors occur when you enter a line.

04:17

A guide to Compiler errors.

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

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

02:02

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

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

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

3 pages

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

Section 11: How to open workbooks and use them
04:18

A lot of the time in VBA you will deal with workbooks that are closed. The code to open workbooks can be tricky. The good news it that I have already written in and you can use it. This section of the course is a guide to using this code.

02:42

Sometimes you may want to allow the user to select the workbook when your Macro is running. I provide the code to do this. This lecture shows you how to easily use it.

02:44

How to easily Import and Export modules using VBA.

Includes the downloadable code for Opening workbooks.

Section 12: Using Functions and Subs
Calling Subs
03:20
Arguments and Parameters
04:38
Functions
03:38
Subs v Functions
02:51
Passing Argument Types
01:35
Optional Arguments
04:03
Section 13: How to Run Your Macros
Running - Using a Shortcut Key
01:53
Running - Quick Toolbar
02:19
Running - Using Buttons
02:53
Section 14: Arrays
Introduction to Arrays
03:24
Declaring Arrays
04:21
Assigninng Arrays
02:46
Loop through arrays
04:36
Pass Arguments
04:07
Return from Function
04:07
Section 15: How to Build a VBA Application from Scratch
Building a VBA Application Part 1
13:07
Building a VBA Application Part 2
14:06
Building a VBA Application Part 3
15:52

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Paul Kelly, Udemy Instructor, Professional Software Developer

Paul Kelly has written over a million lines of code in his 20 year software development career. He has worked for the largest insurance company in the UK, was a senior software consultant for one of the biggest Car Rental Companies in the world and has built card processing systems for a number of leading financial institutions.

In recent years a great deal of his work has been building advanced Excel applications and training Excel users to get the most from Excel Macros in very competitive environments.
Paul has built a number of VBA tools including an automated code generator, a formula version tracker, broken link identifier, module version control and many more.

When not writing code he likes to travel around South America, try his luck at Poker and follow he favourite team in the ancient Gaelic sport of hurling.

Ready to start learning?
Take This Course