"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.
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 .
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.
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.
Let's look at where we will be spending most of our time - editing code.
Let's add a button to our spreadsheet, and get Excel to say "Hello World" when we click on it.
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 it is one thing, but how do you open your spreadsheet. Let's learn about the various security levels, and why they exist.
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).
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.
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.
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.
What's the difference between using a + and a &? Let's find out what the & string operator is.
What types of strings and integers are there. Let's find out!
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.
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.
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.
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.
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.
Let's start looping through rows, and creating a calculator to add them up.
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.
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.
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.
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".
Let's have a look at another way. Let's convert a string into a number using the Val() function.
For...Next Loops are not the only loop. Let's reconstruct our loop as a Do...Loop While instead.
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.
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.
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.
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!
It's a three letter word, but the "Set" statement and its implied variants are very powerful. Let's see what "Set" can do.
Now we can combine the Set statement (implied) with a loop. We'll create a loop that goes through every cell in the Selection.
Let's review what we have learned, and say goodbye - for now.
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.