Excel in Microsoft Excel 4: VBA macros for Excel - an intro

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.