In the previous section, you learnt about variables. To get the hang of variables, create a new blank spreadsheet. Click on theDeveloper ribbon at the top of Excel. On the Controls panel,
click onView 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:
So this code just sets up a variable calledMyNumber. 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 theWorksheet 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 to10.
Instead of typing a number, you can type the name of a variable. We have already stored a value of 10 inside of theMyNumber variable. So we can just use this:
Worksheets(1).Range("A1").Value = MyNumber
VBA sees the variable calledMyNumber 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:
Now try it out. Go back to your spreadsheet and add a button. When the Assign Macro
dialogue box appears, select yourVariable 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
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 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 sayDim 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 theDim 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 theDim keyword and using a named type likeAs Integer.
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 itMyOtherNumber. 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 inMyOtherNumber.
When you click the button on your spreadsheet you should now see this:
In the next lesson, you'll see learn about something called Option Explicit.