Data Types In VBA

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


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 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 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


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


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


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


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


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


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

Related Posts


As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.

In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!