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 .

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: