Outcome of this Lesson

How do you make a new variable?

Excel gives you two ways.

Learn the pros and cons of each way.

And why to use Option Explicit…

---

To Declare or Not to Declare

When you want to use a new variable…

How do you tell Excel VBA?

---

Method 1 = Declare the variable

Example: Dim MyVariable as String

You use the word “Dim” to name a variable.

“Dim” is short for Dimension.

You declare a variable using “Dim” before you use the variable.

After you declare the variable, you can use it like this:

MyVariable = “This is a declared variable”

---

Method 2 = Don’t declare the variable

Example: MyVariable = “This is some text”

By default Excel VBA allows you to just type out a variable name.

This may seem easier than Method 1...

Because you don’t need that extra line “Dim MyVariable as String”

However there is a trade-off…

---

Why Declare your Variables?

When you follow method 1 and declare every variable you use…

You might think it’s a pain in the neck.

Why write that extra “Dim” line when Excel doesn’t need you to?

Because it’s easy to mis-spell a variable name like this:

CorrectVariableName (Correct)

CorretVariableName (Wrong)

Did you see the typo?

It’s very hard to spot this kind of typo when you are writing code.

---

The problem with not declaring your variables

You might be using one variable name, happily running code.

Then you mis-spell the variable and create a totally new one.

And your code runs with both variables.

As it doesn’t know you mis-spelled the original name.

You think there’s only one variable.

Your code thinks there’s two variables.

And it treats the two differently.

That’s bad!

And it gets worse as you write longer programs.

So I recommend you start declaring your variables right away.

It’s a great habit to pick up.

---

Option Explicit = Force yourself to declare variables

To solve this problem, you just need two words.

Option Explicit

You type in “Option Explicit” at the top of every VBA Project Module.

And Excel will force you to declare every variable.

If you have “Option Explicit” and don’t declare a variable…

Excel will let you know with an error message!

Compile error: “Variable not defined”

This will help prevent mis-spelled variable names.

When you see this error, just check your variable names.

---

How to Make Option Explicit the Default

You can have Excel set Option Explicit as the default.

Inside the VBA Editor, go to the menu.

Tools > Options > Code Settings

Check the option “Require Variable Declaration” is ON (ticked)

Excel will help you add “Option Explicit” to the top of every new module you make.

Note: it will not add “Option Explicit” to existing modules, only new ones.

---

Another benefit of Declaring Variables

When you declare all your variables you get another benefit.

Your programs will run faster and take less space in memory.

This is because declaring variables makes it easier for Excel.

If you don’t declare variables…

Excel needs to do more work to figure out what data type you need for every variable.

And it slows your program down.

---

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]