Excel VBA: How to Write Like a Professional
4.4 (198 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,191 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA: How to Write Like a Professional to your Wishlist.

Add to Wishlist

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.4 (198 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,191 students enrolled
Created by Paul Kelly
Last updated 10/2016
English
Current price: $10 Original price: $200 Discount: 95% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4.5 hours on-demand video
  • 1 Article
  • 12 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn little-known techniques used by the top professionals
  • Over 70 example Macros for you to use in your own projects
  • Discover the best method to access a worksheet and the ones you should avoid
  • Learn how to use Intellisense to help you write your code
  • Learn how to supercharge your Excel experience using Loops
  • How to resolve errors quickly and painlessly
  • Learn programming techniques you can use in any programming language
  • Learn When to use "If" and when to use "Select"
  • Learn the 4 Loops of Excel and when to use each one
  • How to test your output before writing to a worksheet
  • Discover how to protect your code against users changing the worksheet name
  • Learn how to use the Compiler to check for errors before you run
  • Learn the best way to use Range, Cells and Offset to access the cells
  • Learn how to easily format any cell or range
  • Discover how easy it is to step through the code line by line
View Curriculum
Requirements
  • Knowledge of Microsoft Excel
  • A Laptop/PC with a copy of Microsoft Excel
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!!!

Who 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
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 80 Lectures Collapse All 80 Lectures 04:54:57
+
Introduction
5 Lectures 02:23

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

  1. Assign some time each day
  2. Practice writing code
  3. Break tasks down into parts
  4. Don't let errors stop you
  5. Close all workbooks except the one your working with
  6. Enjoy it
Preview 02:23

A Welcome Note From Paul and Course Source Code
1 page

BONUS 1: FREE VBA CHEAT SHEET - The Essentials
1 page


+
GETTING STARTED
6 Lectures 17:37

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

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


Preview 01:55

Create a simple Macro using the Developer Ribbon.

Run the Macro from Excel and then Visual Basic

Your First Macro
04:47

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

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


Preview 04:36

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

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

The Immediate Window - How to easily test your output
02:40

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

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

Preview 03:39

Course Notes for the Section: Getting Started

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

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

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

Preview 01:46

How to get any Workbook
03:45

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

When to use each Workbook method
01:00

In this lesson we look at using a workbook variable.

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

Making your code easy to read - Use a Workbook variable
03:20

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

Worksheets("Sheetname")

Worksheets(Index)

ActiveSheet

How to get any Worksheet
03:32

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

Putting it all together - A sample Macro
06:16

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

It has two distinct advantages:

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

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

The Sample Macro - How the code name improves it
03:08

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

Summary of Worksheets and Workbooks
04:09

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

Workbooks and Worksheets - Downloadable Guide
10 pages

Workbooks and Worksheets Quiz
10 questions
+
Using Variables
3 Lectures 11:15

An Introduction to variables and how to use them.

An introduction to Variables
04:37

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
How to do almost anything with a string
03:30

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

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

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

Using Range to manipulate cells
03:14

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

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

For Example

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

Using Cells - How to choose cells at run time part 1
04: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.

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

A sample Macro showing the benefit of using Offset.

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

Sample Macro - Writing to months using Offset
03:17

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

Sample Macro - Writing to months using Cells
02:39

How to format cells. Included in this lesson:

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


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

The two workbooks used in this sections videos.

Mini Project Workbooks
00:02

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

Introduction to Mini Project
00:54

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

Formatting the Cells
03:42

We write the values to our report.

Write the Totals
02:16

Make the code more flexible using Offset

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

An introduction to this section on Debugging

What is Debugging
01:01

How to step through the code

Stepping through code
02:17

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

Using Breakpoints
02:29

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

The Locals Window
02:53

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

The Watch Window
03:30

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

More on the Watch Window
02:11
+
If and Select
5 Lectures 17:06

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

  • x > 1
  • x<1
  • x=5
  • x<>5
  • x>5 and x<=10
Using Conditions
04:54

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

The If Statement
02:41

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

If Statement with ElseIf
03:07

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

The Select Statement
02:35

Another example of using the Select Statement

More with the Select Statement
03:49
+
Loops
9 Lectures 27:38

An introduction to using Loops

Introduction to Loops
02:06

A guide to using the For Loop.

This loop uses the format:

For i = 1 To 10

Next i

The For Loop
04:07

More on the For Loop
04:51

The For Each Loop is used to read through collections

an example is

For Each wk in Workbooks

Next

The For Each Loop
03:42

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

The Do Loop
04:21

Using the Do Loop with Until

Using Do Loop with Until
03:12

Using the Do Loop with While

Using Do Loop with While
02:27

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

While and Wend
00:57

A Summary of all the Loops we covered in this section

Summary of Loops
01:55
+
Errors
4 Lectures 10:47

What are syntax errors and how to deal with them.

Syntax errors occur when you enter a line.

Syntax Errors
04:28

A guide to Compiler errors.

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

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

Compiler Errors
04:17

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

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

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

Runtime Errors
02:02

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

Preview 3 pages
5 More Sections
About the Instructor
Paul Kelly
4.4 Average rating
198 Reviews
2,191 Students
1 Course
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.