Home and Learn - Free Excel VBA Course
2.6 Other Variable Types in Excel VBA
As well as declaring a variable to be of type Integer, as you have been doing so far, you can also have the following numerical variable types:
As Long
As Single
As Double
As Currency
The difference between all these numerical data types is how many digits they can hold, and whether or not you want a decimal point (there is actually an As Decimal variable type, but it's a bit fiddly to use).
The first one on the list, As Long, is a great alternative to As Integer. We've been using As Integer exclusively up until now. The problem with the Integer variable type, however, is that it can only hold numbers up to a value of 32, 767. At the lower level the Integer variable type can only hold negative numbers up to -32, 768.
If you want store bigger numbers then clearly 32, 767 may not be enough. This is where As Long comes in. The Long variable type can hold positive numbers up to a value of 2, 147, 483, 647. The lowest negative number is -2, 147, 483, 648.
But Integer and Long are both used to store whole numbers. They would be no good if you wanted divide 10 by 3, say. If you want a remainder, you'll need a different variable type.
The variable types you can use for greater precision are As Single and As Double. The difference between the two are how many digits they can hold. As Single holds 4 bytes of data while As Double can hold 8 bytes. If you want a really, really long floating point number (a number with "point something" at the end) then use As Double, otherwise just use As Single.
Practical Work
Let's test some of this theory out, though. You can create a new spreadsheet for this, if you want. Or use the one you currently have open. But create a new Sub in a coding window (you should know how to do this by now). Call it RowCount. Add the following code:
Dim NumberOfRows As Long
NumberOfRows = Worksheets(1).Rows.Count
MsgBox NumberOfRows
Your coding window will then look like this:
We've set up a variable called NumberOfRows. Instead of As Integer, we've used As Long. The second line uses Rows.Count to get the number of rows on Worksheet 1. When VBA gets this number, it stores it in our NumberOfRows variable. The third line is this:
MsgBox NumberOfRows
The MsgBox stands for Message Box. We just want to quickly test some values here, so there's no need to add a button to the spreadsheet. You'll learn more about Message Boxes a little later. But the Message box will display whatever value is in the variable NumberOfRows.
Rather than a long explanation about MsgBox, try it out to see what it does.
Make sure your cursor is flashing between the Sub and End Sub of your new code. Now locate the green arrow on the toolbars at the top of the coding window:
This is the Run icon. When you click it, VBA will try to run your Subs inside of the coding environment. You can also click Run Sub/User Form from the Run menu at the top:
A shortcut to running your Subs is to press the F5 key on your keyboard.
When you run your Sub, though, you should see a message box appear:
The number 1048576 is coming from our NumberOfRows variable. In other words, just over a million rows in this version of Excel.
Click OK on your Message Box to return to your coding window. Now change As Long to As Integer:
Dim NumberOfRows As Integer
Try running your Sub again. This time, you should see an error message:
The error is Overflow. You get this error because the value you're trying to store in your variable is too big for the variable type. The As Integer variable type can only hold numbers up to a value of 32, 767. Storing a value of over a million causes the programme to bail out with Overflow error.
To test out floating point numbers, add a new Sub and call it Floats. Add the following code:
Dim FloatingPoint As Single
FloatingPoint = 10 / 3
MsgBox FloatingPoint
Your coding windows will then look like this:
With your cursor inside of the Sub and End Sub code, Press F5 on your keyboard to run it. You should see a Message Box appear:
The value in the variable called FloatingPoint is being displayed in the Message Box. It is showing the answer to 10 divided by 3 to six decimal places.
Now change this line in your code:
Dim FloatingPoint As Single
To this:
Dim FloatingPoint As Double.
Run your code again and the Message Box will display the following:
Now the FloatingPoint variable is showing the answer to 10 divided by 3 to fourteen decimal places.
So if you want greater accuracy in your calculation, use As Double rather As Single. Here's one final example of that.
Change your code to this:
Dim FloatingPoint As Single
FloatingPoint = WorksheetFunction.Pi
MsgBox FloatingPoint
This time, we're using WorksheetFunction. After a dot, you'll see a list of Excel functions you can use. Select Pi from the list.
When you run your code, the Message Box will be this:
Now change As Single to As Double. When you run your code this time, the Message Box will be as follows:
In the As Single version, the sixth number after the floating point is a 3. In the As Double version, the sixth number is a 2. VBA has rounded the value up for As Single.
So, again, if your calculation need to precise, and you don't want Excel to automatically round things up or down, then use As Double rather than As Single.
One final variable type that can come in handy is Variant:
Dim FloatingPoint As Variant
Variant is used when you're not sure what value will be returned. It can hold numbers, text, and objects. However, using it too much can slow down your programmes, as it uses 16 bytes of data for numbers and 22 bytes for text.
Variable Types and Text
If you want your variables to hold strings of text then the variable type to use is As String:
Dim MyText As String
There are lots of inbuilt methods you can use on strings of text, and you'll learn about these later in the course. Mastering these methods will greatly improve your VBA programming skills.
In the next section, you'll learn about Conditional Logic.
Next Lesson: 3.1 Conditional Logic >