1. Course content
  2. Tips and tricks
  3. Option Explicit and declaring variables

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:

Option Explicit

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:

Options

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.

  1. animal = Cells(1, 2)
  2. count = Cells(2, 2)
  3. If anima1 = "dog" Then
  4.     socks = count * 4
  5. Else
  6.     socks = count * 2
  7. End If
  8. 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:

  1. Dim animal as String
  2. Dim count as Integer
  3. Dim socks as Integer
  4. animal = Cells(1, 2)
  5. count = Cells(2, 2)
  6. If anima1 = "dog" Then
  7.     socks = count * 4
  8. Else
  9.     socks = count * 2
  10. End If
  11. Cells(3, 2) = "You need " & socks & " socks."
When you run it, you'll see:

Error

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.