Setting up Excel

You can use any version of Excel back to about 2003, on either Windows or a Mac. This course was written with Windows Excel in mind.

Note

Many people with Macs install Windows on it, either as dual boot, or in a virtual machine. You might be able to get free software for that from your university.

Show the developer tab (old Excel)

Start Excel, and you'll see a bunch of tabs at the top. You need to turn on the Developer tab. It looks like this:

Developer tab

Go to the Excel options. Here's how you do it in Excel 2007:

Excel options

Turn on Show Developer tab in the Ribbon:

Show Developer tab option

Show the developer tab (new Excel)

Here's how you do it in more recent versions of Excel.

Showing the Developer tab

Optional: Tell Excel to use column numbers, not letters

Excel normally has letters for columns:

Letters for columns

However, you'll write code like this:

  • Cells(3, 7).Interior.Color = vbRed

That's row 3, column 7. Column 7, which is that? One, two, three… Oh, it's G.

If you want, you can tell Excel to use numbers for columns instead:

Numbers for columns

How? Go to Excel options, and tell it to use the R1C1 reference style:

R1C1 option

This is optional. Use the normal style, if you want.

The Visual Basic Editor (VBE)

VBE is where you'll type in your code. Some of the default settings are annoying. Let's fix that.

In Excel, press ALT+F11 to open the VBE. You'll see something like this:

VBE

In the main menu, click Tools, and then Options. Select the Editor tab. Turn Auto syntax check off, and all the others on, including Require variable declaration:

VBE options

Require variable declaration will add the line…

  • Option Explicit

... to your code. It should always be the first line.

Option explicit

If you forget to turn on option, you can just type in Option Explicit.

Georgina
Georgina

What does Option Explicit do?

It means you have to declare variables before you use them. This helps you make fewer errors, and get frustrated less. We'll talk about it more later.