VBA data types in Excel

Introduction to data types in Excel VBA

Why is it so important to assign a variable to the correct data type? Your program and its variables use the resources of your computer. If you want to ensure that your program works quickly, choose the types of data that you really want to use. For example, you don’t write dates and numbers in text form, although this is. You do not use, for example, the type Double to write numbers, if you want to assign numbers from scale from 1 to 10. For this purpose, the Byte type will works perfectly.

The simples way to group data types in VBA is grouping it to numeric and non-numeric groups.

Numeric VBA data types

List of numerical data types in the Excel VBA. Their detailed description is presented in the table:

Data typeDescriptionMemory usage in BytesThe default valueThe range of accepted values
ByteThe type used for integer numbers in the range 0-2551B0Integer numbers in the range from 0 to 255
IntegerA type used for integers with low values2B0 Integer numbers in the range from -32768 to 32767
LongA type used for integers with high values4B0Total numbers in the range from -2,147,483,648 to 2,147,486,647
SingleThe type used to store real numbers4B0The numbers range from -3.402823e38 to 1.401298e45
DoubleThe type used to store real numbers8B0Numbers from -1.79769313486232e + 308 to -4.94065645841247E-324 for negative values
Numbers from 4.94065645841247E-324 to 1.79769313486232e + 308 for positive values
currencyThe type used to store numbers in monetary units8B0Numbers in the range from -922,337,203,477.5808 to 922,337,203,685,477,5807
Variant (numeric)A type used both for storing numeric and character data16 BvbEmptyNumbers from ranges corresponding to the Long and Double types

We can assign variable to a specific type of data. Examples are described in the table listed below. After defining the variable, you can also directly assign a value to it. You can do this as shown in the example below.

'OfficeInside.Org
Sub numDataTypesAssign() 
'numeric vars declaration
Dim ByteExample As Byte 
Dim IntExample As Integer 
Dim LngExample As Long 
Dim SglExample As Single 
Dim DblExample As Double 
Dim CurrExample As Currency 
Dim VarExample As Variant '(numeric) 

'Setting values to variables
ByteExample = 255 
IntExample = 123 
LngExample = 1000000 
SglExample = 123.456 
DblExample = -4.94065645841247E-324 
CurrExample = 123.456 
VarExample = -4.94065645841247E-324 
End Sub

Non-numeric VBA data types

Data typeDescriptionMemory usage in BytesThe default valueThe range of accepted values
booleanThe type used for logical values ​​True or False2BfalseTrue or False
DateThe type that stores the date8B0Dates with ranges from 100/01/01 to 9999/12/31
String (without a defined length)The type used to store character data10B + number of charactersA zero-length text stringfrom 0 to 2 billion characters
String (fixed length)The type used to store character datanumber of charactersThe number of defined characters shown as spacesFrom 1 to 6,500 characters
Variant (character)A type used both for storing numeric and character data22B + number of charactersvbEmptyThe range corresponds to the String range without a defined length, ie. from 0 to 2 billion
ObjectThe type used when we are operating on objects (ie. Sheet, workbook)4BnothingAny embedded object
'OfficeInside.Org
Sub NonNumDataTypesAssign() 
'non-numeric
Dim BlExample As Boolean
Dim DtExample As Date
Dim StrExample As String '(without length def)
Dim StrExample2 As String * 10 '(with length def)
Dim VarExample2 As Variant '(string)
Dim ObjExample As Object 'object def

'Setting values to variables 
BlExample = True
DtExample = Date 'data bieżąca
StrExample = "Dowolny tekst"
StrExample2 = "10 znaków"
VarExample2 = "Dowolny tekst"
End Sub

As you see, the Variant type can be used to save numbers and strings. If you know in advance which target value your variables should take, then using this type of data is not recommended. You can use it in cases of short, fast-written programs. Types of VBA data in the Office package are also written on the Microsoft website  on this link .

Introduction to data types in Excel VBA
Introduction to data types in Excel VBA

Tasks (You can enter the solution in a comment)

  1. Create a simple example. Define 4 numeric variables with different data types. Check if you can perform math operations on them. e.g. a * b + c ^ d. Use the Byte, Single, Integer, Double types.

More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and  Examples in VBA  section. If you have any questions about this chapter, you can post them in the comments.

Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “VBA data types in Excel”

%d bloggers like this: