Option Explicit and declaring variables

Option Explicit is da boss, for realsies

Always make sure that Option Explicit is at the top of your code, like this:

The code editor will do this for you (most of the time) if you tell it to. In the main menu, click Tools, and then Options. Select the Editor tab. Turn on Require variable declaration:

With Option Explicit, VBA makes you declare every variable you use, with a Dim statement. This is a Good Thing. It will stop you wasting hours looking for tiny mistakes.

An example, with Option Explicit left out. Here's a tiny program; it couldn't get much simpler. It works out how many socks you need for your animals, depending on whether the animals are dogs or not.

animal = Cells(1, 2)

count = Cells(2, 2)

If anima1 = "dog" Then

socks = count * 4

Else

socks = count * 2

End If

Cells(3, 2) = "You need " & socks & " socks."

The program will tell you that you need count * 2 socks, no matter what you put into A2.

Figured out why? Click below to check your answer.

Click to see the answer

Line 3 says anima1, with a 1 (one), rather than animal, with an l (el).

In a larger program, mistakes like this will drive you crazy.

Now, suppose you had Option Explicit, and the code was:

Dim animal as String

Dim count as Integer

Dim socks as Integer

animal = Cells(1, 2)

count = Cells(2, 2)

If anima1 = "dog" Then

socks = count * 4

Else

socks = count * 2

End If

Cells(3, 2) = "You need " & socks & " socks."

When you run it, you'll see:

Always use Option Explicit.

Declare variables on separate lines

You declare a variable like this:

Dim count as Integer

Here's two:

Dim count as Integer, rejects as Integer

This doesn't do what you think:

Dim count, rejects as Integer

rejects will be an integer, but count won't be. Best to use separate lines to declare variables:

Dim count as Integer
Dim rejects as Integer

Use camel case

When a variable name has two words stuck together, use camel case. The first letter of the name is lowercase. All other letters are lowercase, except for the first letter in each new word.

Say you have a variable for interest rate:

interestRate

Right

interestrate

Wrong

InterestRate

Wrong

interest_rate

Wrong

h32

Very wrong

Some more examples, some a little strange:

winnieThePooh

Right

sumSquared

Right

snape

Right

returnOnInvestment

Right

roi

Right

highestRoi

Right

Check out the last three. ROI is an acronym for return on investment. When you use an acronym in a variable name, treat it like any other word. All lowercase if it's the first part of a variable name, uppercase first letter if it's not the first word.

Camel case is just one way to write variable names. There are other standards. As long as the variable names are easy to understand, each standard is as good as any other. Pick one standard and go with it. We'll go with camel case.