Configuring Excel for VBA

Showing the developer tab

Excel has tabs at the top. You need to turn on the developer tab:

Developer tab

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


Turn on Show Developer tab in the Ribbon:

Show developer tab

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


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.

That’s a pain, and easy to mess up. You can tell Excel to show this instead:

Numbers for columns

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


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

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.

Press ALT+F11 to open the VBE. You’ll see something like this:


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:


Require variable declaration will add the line…

Option Explicit

… to your code. That should be the first line in your code:

Option Explicit

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