VBA data types in Excel

Last Updated on January 11, 2023 by token

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 type	Description	Memory usage in Bytes	The default value	The range of accepted values
Byte	The type used for integer numbers in the range 0-255	1B	0	Integer numbers in the range from 0 to 255
Integer	A type used for integers with low values	2B	0	Integer numbers in the range from -32768 to 32767
Long	A type used for integers with high values	4B	0	Total numbers in the range from -2,147,483,648 to 2,147,486,647
Single	The type used to store real numbers	4B	0	The numbers range from -3.402823e38 to 1.401298e45
Double	The type used to store real numbers	8B	0	Numbers from -1.79769313486232e + 308 to -4.94065645841247E-324 for negative values Numbers from 4.94065645841247E-324 to 1.79769313486232e + 308 for positive values
currency	The type used to store numbers in monetary units	8B	0	Numbers 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 data	16 B	vbEmpty	Numbers 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 type	Description	Memory usage in Bytes	The default value	The range of accepted values
boolean	The type used for logical values True or False	2B	false	True or False
Date	The type that stores the date	8B	0	Dates with ranges from 100/01/01 to 9999/12/31
String (without a defined length)	The type used to store character data	10B + number of characters	A zero-length text string	from 0 to 2 billion characters
String (fixed length)	The type used to store character data	number of characters	The number of defined characters shown as spaces	From 1 to 6,500 characters
Variant (character)	A type used both for storing numeric and character data	22B + number of characters	vbEmpty	The range corresponds to the String range without a defined length, ie. from 0 to 2 billion
Object	The type used when we are operating on objects (ie. Sheet, workbook)	4B	nothing	Any 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 .

Tasks (You can enter the solution in a comment)

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.