Excel VBA (Visual Basic for Applications) - An Introduction
4.8 (16 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.
97 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA (Visual Basic for Applications) - An Introduction to your Wishlist.

Add to Wishlist

Excel VBA (Visual Basic for Applications) - An Introduction

Go from Beginner Level of Excel VBA to Intermediate. Create your own Excel Macros in Visual Basic for Applications.
4.8 (16 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.
97 students enrolled
Created by Phillip Burton
Last updated 5/2017
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Add the Developer tab and record marcros.
  • Adapt macros using the Excel Object Model, so that you can use them in your spreadsheets.
  • Know about variables, and how to use loops and If statements.
  • Interact with Excel - find out how to access the Selection object, and output results using a MessageBox (MsgBox).
View Curriculum
Requirements
  • You need to have Microsoft Excel installed.
  • You also need to have a little knowledge of Excel itself - how to enter values, do basic formulas and color cells.
Description

Reviews:

"This is a very good starting point in VBA. But doing this course one realize how extensive and huge VBA is. As always the teacher is great and one feels his in depth knowledge" -- Daniel Sandberg

Welcome to Excel VBA - An Introduction. This course delves into the Visual Basic which allows you to automate Microsoft Excel (and other MS Office products).

Microsoft Excel is an extremely powerful tool – and yet most people only scratch the surface of what it can do. In addition to PivotTables, VLookup formulas and the like, you can also automate it. You can add buttons and forms to add interactive features – yet most people don’t even know it’s there. Maybe you have someone in your organization who is an absolute wizard, getting the computer to do things and walking away while it does it. Do you want to be able to do that? Welcome to wizard school – or in other words, Visual Basic for Applications (VBA).

In this course, I’ll assume that you have some fair knowledge of Excel – able to do basic formulas and colour cells and the like – but you don’t need any prior knowledge of VBA or any programming language for that matter. Of course, the more you already have, the better. I’ll take things nice and slowly. Each video will center around one new aspect, or we’ll consolidate what you already know with coding exercises. 

Once you have finished the course, then you will be well on your way to developing your own macros to solve your own problems. And you’ll be able to take your new-found knowledge for use in Word and PowerPoint as well.

Who is the target audience?
  • This Excel VBA course is for you if you want to learn how to automate Excel and provide a better user interaction.
  • No prior programming language is required - although, it would always help!
  • This course is not for you if you are confident using VBA, and are looking for more advanced techniques.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
34 Lectures
03:32:07
+
Introduction
2 Lectures 03:47

Hello, and welcome to the course. I'll go through what we will be learning, and what you can achieve from this course.

Preview 01:34

Before we start, let's have a quick look around Udemy, and find out how you can get in touch with me.

Introduction to Udemy
02:13
+
Recording your first macro
8 Lectures 51:02

Did you know that you may be missing the most important tab in Excel regarding VBA? Let's add it into your version of Excel .

Preview 02:30

Now that we've got the Developer Tab, let's record our first macro. It's going to be a simple one, highlighting a cell, but let's record it, then play it back.

My First Macro
06:03

When creating our second macro, we can see that there is a problem with it. Let's turn on "Use Relative References", and see if that can solve the problem.

Use Relative References
03:38

Let's look at where we will be spending most of our time - editing code.

The editing environment
08:12

Let's add a button to our spreadsheet, and get Excel to say "Hello World" when we click on it.

Preview 09:26

Saving our spreadsheet should be easy, right? Well, there is a couple of pitfalls you need to beware of. One prevents your code from being saved at all. Let's find out what file type you need to save your spreadsheet as.

Saving and opening our spreadsheet
06:45

Saving it is one thing, but how do you open your spreadsheet. Let's learn about the various security levels, and why they exist.

Security
04:42

Now that we know we may need certificates, what is the least expensive way of creating certificates? Let's look at the free way of doing so, together with the disadvantage (anyone can pretend to be you).

Self certification
09:46
+
Variables
6 Lectures 41:06

We want to add a button that counts increasing high numbers - but we want Excel to remember the number. For that we need a variable. Let's find out how to use them.

Introducing variables
08:16

How long does Excel remember that your variables exist? As long as it is scoped. Let's see what procedure scope and module and global scope are, and why you need them.

Scope
05:30

Variables are fine, but they are hidden away. Let's add Breakpoints to pause the code, and see how we can find out the value of variables using the Immediate Window and Watches.

Breakpoints, the Immediate Window and Watches
07:25

What's the difference between using a + and a &? Let's find out what the & string operator is.

String operators
04:13

What types of strings and integers are there. Let's find out!

Declaring types of variables
06:34

What is the difference between NumberVariable and NamberVariable? Only one letter, but it can make your code harder to debug. Let's find out how to avoid these typos.

Avoiding typos
09:08
+
The Excel Object Model
4 Lectures 24:55

 We've used a variable up to now. What if we use a spreadsheet cell instead? Let's do so, and we look at the advantages and disadvantages.

Interacting with the Excel spreadsheet
03:38

Let's take a trip to London's Royal Albert Hall, and see how we can describe its location. Then we'll have a look at Excel objects, starting at the top - the Application - and working downwards.

Introducing Objects
06:39

Now that we know what objects are, how do they connect together? Let's have a look at the Object Model, and see if we can make our own.

Introducing the Object Model
06:07

When writing a letter, it is not always necessary to include all of the address details, such as country. Let's see what you can exclude, and what defaults the computer uses.

Default Objects, and Default Properties
08:31
+
Loops and If statements
13 Lectures 01:30:42

Let's start looping through rows, and creating a calculator to add them up.

For...Next Loops
09:31

Now we'll add a second For...Next loop into the mix, and go through rows and columns. Then we'll find that there's an error in the code - see if you can spot it - and we'll go through some ways to debug the code.

For...Next Loops part 2 - and debugging
07:15

Instead of hard-coding how many rows and columns we need to add, why not let the end user decide? Let's use an InputBox and get some feedback from the user.

InputBox
05:53

What happens if someone inserts a string, and you try to add it to a number? An error, that's what. Let's find that before it causes an error, using IsNumeric, and using an If...Then statement.

IsNumeric and If...Then
07:54

We'll continue to play with If...End If statements, and introduce a third component - What happens if something is not true - what we want to do with it? Enter the "Else".

If...Else...End If
05:24

Let's have a look at another way. Let's convert a string into a number using the Val() function.

Val()
04:09

For...Next Loops are not the only loop. Let's reconstruct our loop as a Do...Loop While instead.

Do While...Loop
07:07

So what do Do While...Loops allow? They allow a dynamic condition. Let's change our loop to look for a contiguous area, and then stop.

Using our Do While...Loop
04:54

What's the difference between a Do While...Loop and a Do...Loop While? Let's find out, and look at some other variants, such as While...Wend.

Other types of loops
09:22

Another way of getting user input is through using the power of Excel itself. Let's use the selection range as the source of the area. But first of all, let's investigate the Selection, and see what properties it has.

Selection
06:05

Now I want you to write to some. Let's combine the For...Next Loop with the Selection to create a routine that adds all o of the numbers within the selection. And I want you to do it first!

Coding Exercise - Using For...Next Loop with Selection
09:58

It's a three letter word, but the "Set" statement and its implied variants are very powerful. Let's see what "Set" can do.

Set
09:00

Now we can combine the Set statement (implied) with a loop. We'll create a loop that goes through every cell in the Selection.

For Each...Next Loop
04:10
+
Well done
1 Lecture 00:35

Let's review what we have learned, and say goodbye - for now.

Congratulations
00:35
About the Instructor
Phillip Burton
4.4 Average rating
3,235 Reviews
45,611 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.