Home and Learn - Free Excel VBA Course

2.2 Excel VBA Variable Practice

In the previous section, you learnt about variables. To get the hang of variables, create a new blank spreadsheet. Click on the Developer ribbon at the top of Excel. On the Controls panel, click on View Code. This will open up the Visual Basic Editor with the Sheet1 coding window already open for you. Set up a new Sub and call it Variable_Practice. Add the following two lines for the Sub:

Dim MyNumber As Integer

MyNumber = 10

Your coding window will then look like this:

An Integer variable set up in Excel VBA

So this code just sets up a variable called MyNumber. We then store a value of 10 into this variable. This is important in programming: whatever you put on the right hand side of an equal sign is what you are trying to store; whatever you have on the left of the equal sign is the place where you're trying to store it.

However, you'd want to actually do something with this value. After all, what's the point of storing a value if you're not going to use it? What we can do is to transfer our stored value to a cell on a spreadsheet.

You have been using Range in previous lessons. One property of Range is Value. The Value property can get or set the value of a cell, or group of cells. To set a Value, the Range goes before an equal sign:

Worksheets(1).Range("A1").Value =

Here, we're first accessing the Worksheet 1 object. We want to point to the Range A1. After a dot, we then type the Value property. This is followed by the equal sign. After the equal sign, you can type your value:

Worksheets(1).Range("A1").Value = 10

Now, the Value for Range A1 on Worksheet 1 is set to 10.

Instead of typing a number, you can type the name of a variable. We have already stored a value of 10 inside of the MyNumber variable. So we can just use this:

Worksheets(1).Range("A1").Value = MyNumber

VBA sees the variable called MyNumber and then fetches whatever value is stored inside of it. That value is then stored inside of whatever is on the left of the equal sign.

Add that line to your code, just below the other two. Your coding window will then look like this:

A worksheet range being referenced with Excel VBA code

Now try it out. Go back to your spreadsheet and add a button. When the Assign Macro dialogue box appears, select your Variable Practice Sub from the list. Change the text on the button to Variable Practice. Deselect your button by clicking away. Now click it again to test it. You should find that the number 10 appears in cell A1 on your spreadsheet:

A button on an Excel spreadsheet demonstrating variables

Now return to your code. Locate the Dim line from your Variable Practice Sub. This one:

Dim MyNumber As Integer

Comment the line out by typing a single quote mark before it. You code will then look like this:

A code comment added to a Dim line

A comment, remember, means that the line will be ignored. The code that VBA will execute is now only this:

MyNumber = 10

Worksheets(1).Range("A1").Value = MyNumber

The question is, will the code still run, or will it throw up an error?

Try it out by returning to Excel and clicking your button. You should find that it runs OK, with no problems at all. But why? We didn't set a variable name with the Dim keyword, as this is now commented out. So what's going on?

Well, you can actually set up a variable like this:

Variable_Name = Value_Here

In other words, you don't actually need to say Dim Variable_Name As Ineteger. You can miss it out entirely. However, if you do VBA sets the variable type up as something called a Variant. Variant data types can hold just about any kind of value. VBA will decide, when your programme runs, what type of data to store in your variable.

So if you don't need to set up variables with the Dim keyword, why bother using them at all? Well, it turns out that using the Variant data type will cause your code to run really slowly compared to setting up variables with the Dim keyword and using a named type like As Integer.

We'll be setting up our variables with the Dim keyword wherever possible.

 

Exercise
Uncomment your Dim line by deleting the single quote. Now change the Range from "A1" to "A1:A10". Return to Excel and run your code again. What happens?


You can set up more than one variable, of course. Amend your code to this (the new lines are in bold):

Dim MyNumber As Integer
Dim MyOtherNumber As Integer

MyNumber = 10
MyOtherNumber = 20

Worksheets(1).Range("A1:A10").Value = MyNumber
Worksheets(1).Range("B1:B10").Value = MyOtherNumber

We've set up another Integer variable (As Integer) and called it MyOtherNumber. A value of 20 is being stored in this new variable. The Value of the Range B1 to B10 is being set to whatever value is stored in MyOtherNumber.

When you click the button on your spreadsheet you should now see this:

A button on a spreadsheet demonstrating variables

 

In the next lesson, you'll see learn about something called Option Explicit.

< Variables

Next Lesson: 2.3 Option Explicit >