In this blog post we explain data types in VBA and provide detailed definitions for when they should be used.
Firstly a word on VBA variable names; a variable may be named anything you wish as long as it conforms to VBA’s naming rules. Variable names must start with a letter, may contain number characters or underscores ( _ ) but that’s it! Punctuation marks are not allowed. Also unlike other languages VBA is case-insensitive! This is important to understand and is demonstrated below.
Finally, there are some keywords that cannot be used as variable names.
Sub Declarations() Dim a as String ‘ a valid variable name Dim b_ as String ‘ a valid variable name Dim _b as String ‘ variable names must start with a letter Dim 2b as String ‘ variable names must start with a letter Dim c1 as String ‘ a valid variable name Dim d12 as String ‘ a valid variable name Dim e_e1 as String ‘ a valid variable name Dim f! as String ‘ punctuation not allowed in variable names Dim g as String ‘ a valid variable name Dim G as String ‘ an invalid variable name. VBA is case- ‘ insensitive, variables also cannot be ‘ declared more than once in a code block Dim aVariableName as String ‘ a valid variable name Dim a_Variable_Name as String ‘ a valid variable name Dim HELLOWORLD as String ‘ a valid variable name Dim dim as String ‘ variable name is invalid as Dim is a keyword End Sub
Boolean – (Yes/No)
A variable of type Boolean is the simplest possible data type available in VBA. It can only be set to 0 or -1. These are often thought of as states and correspond to Access’s Yes/No fields. In VBA you can assign a Boolean variable to True (-1) or False (0) or the numbers indicated in the brackets.
Notice we used capitalised words for True and False, which is because they are VBA keywords and you cannot name a variable a Keyword.
Sub trueOrFalse() Dim foo As Boolean Dim bar As Boolean foo = True ' foo holds the value True bar = False ' bar holds the value False End Sub
Integer
At the beginning of the post we said that we have to tell the computer what type of data to expect before we can work on it. An Integer is another number data type, but its value must be between -32,768 and 32,767, and it must be a whole number, that is to say, it mustn’t contain decimal places. If you or your users try to save a decimal value (eg 2.5) to an integer variable, VBA will round the decimal value up or down to fit into an Integer data-type.
Sub IntegerDataType() Dim foo As Integer Dim bar As Integer Dim oof As Integer foo = 12345 ' foo is assigned the value 12,345 bar = 2.5 ' bar is assigned the value 3 as VBA rounds it up bar = 2.4 ' bar is assigned the value 3 as VBA rounds it down foo = 32768 ' causes an overflow error as 32,768 is too big End Sub
Long
Long is another number type and works just like Integer except it can hold a much larger range; Any number between -2,147,483,648 and +2,147,483,647.
Sub LongDataType() Dim foo As Long foo = 74345 ' foo is a variable assigned the value 74,345 End Sub
Single
Single is the smaller of the two “floating point” data types. Singles can represent any decimal number between -3.4028235E+38 through 1.401298E-45 for negative numbers and 1.401298E-45 through 3.4028235E+38 for positive numbers. Put more simply, the single data type has a decimal point in it.
Sub DoubleDataType() Dim foo As Single Dim bar As Single foo = 1.1 ' foo keeps the .1 decimal part bar = -20.2 ' bar also keep the decimal part foo = foo * bar ' foo equals -22.2200008392334 End Sub
Double
This is a “floating point” number as well and range in value from -1.79769313486231570E+308 through -4.94065645841246544E-324 for negative values and from 4.94065645841246544E-324 through 1.79769313486231570E+308 for positive values.
Sub DoubleDataType() Dim foo As Double Dim bar As Double foo = 1.1 ' foo keeps the .1 decimal part bar = -20.2 ' bar also keep the decimal part foo = foo * bar ' foo equals -22.2200008392334 End Sub
Currency
This data-type is a third “floating-point data” type in disguise. It’s a Single which has been engineered to represent behaviours typical of currencies. In particular it rounds off numbers to four decimal places. See the Figure below:
Sub CurrencyDataType()
Dim bar As Single
Dim foo As Currency
bar = 1.1234567 ' this is the Single
foo = bar ' add the Single to the Currency
MsgBox bar ' bar contains 1.1234567
MsgBox foo ' foo contains 1.1235. Notice that the 4th digit
' has been rounded up to 5
End Sub
Date
The Date data type is used to perform operations that involve dates AND times. In VBA there are several functions that operate on date variables which perform date and time calculations. It is important to know that date and time operations can be quite complicated and to help ease your burden you can use VBA’s DateTime object which encapsulates a lot of the difficulty of working with dates and time and can make them a little less of a headache to deal with. Date data types are the most complicated of all the data types to work with.
Here are a few operations we can do with date data types.
Sub DateDataTypes() Dim bar As Date Dim foo As Date bar = #11/15/1978# ' bar set to this date but has no time foo = #12/10/2012 11:37:00 PM# ' foo is set to this date and time bar = #1:00:09 AM# ' bar is 1 hour and 9 seconds foo = #9:00:00 PM# ' foo is 9PM foo = foo + bar ' foo is now 22:00:09 MsgBox foo foo = foo - bar ' foo is back to 9PM MsgBox foo End Sub
String
A String is any set of characters that are surrounded by double-quotation marks. For example “dog” is a String that contains three characters. Strings are very important to us as they can contain human language, and in fact contain almost any data we want, even numbers and punctuation marks. Strings are very versatile and you will use them extensively in your code. Often when you ask your users for information you will first store their input in a String before actually using the data provided; in this way Strings are often thought of as a safe data type.
Below are some Figures of Strings in action.
Sub StringDataTypes() Dim bar As String Dim foo As String Dim foobar As String bar = "Hello" ' bar now contains "Hello" foo = "world!" ' foo contains "world!" foobar = bar & " " & foo ' foobar now contains "Hello world!" ' notice that foobar has a +" "+ this means a SPACE character has been ' inserted into the String, without it foobar would contain "Helloworld!" foobar = bar + " " + foo ' This Figure also shows that you can add ' Strings together (but you cannot subtract!) foo = "H" & "E" & "L" & "P" ' foo now contains "HELP" bar = foo & foo ' bar now contains "HELPHELP" End Sub
Confusion
As stated above, when you collect input from a user you will usually collect it into a String. But be careful not to confuse String with Number data types. For example:
Sub Confusion()
Dim bar, foo As String
Dim foobar As String
foo = "12.5" ' user inputs "12.5"
bar = "6.3" ' user inputs "6.3"
foobar = foo * bar ' we multiple 12.5 and 6.3
Debug.Print foobar ' print the result - 0
' It's ZERO!
' Remember foo and bar are STRING data types,
'so multiplying foo and bar as above is like
'saying "aaa" * "bbb" = 11 ? It doesn't make sense.
'But we collect data in a String because a String
'can accept all user input, even if they 12 put a
'punctuation mark in there.
foo = "12.5.2" ' user made a mistake
bar = "ifvgj212m" ' cat walks across the keyboard
' When collecting user input the data held in a String
'can be tested for accuracy and correctness before we
'load it into an Integer. If the user has not entered
'data correctly we ignore or display a useful message
'like "Error"...
End Sub
Variant
A variant is a special type which can contain any of the data types mentioned above (along with some others).
When a value is assigned to the variant data type the variable mutates into the type of the data assigned to it, and in some cases VBA can “detect” the type of data being passed and automatically assign a “correct” data type. This is useful for collecting data from users and also for writing procedures and functions for which you want to be able to call with a variable of any type.
Sub VariantDataType() Dim bar As Variant Dim foo As Variant Dim foobar As Variant bar = 1 ' bar is now an Integer foo = "oi!" ' foo is now a String foobar = bar + 1.1 ' foobar is now a Double with the value of 2.1 MsgBox TypeName(bar) ' Integer MsgBox TypeName(foo) ' String MsgBox TypeName(foobar) ' Double End Sub
