Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures

In this article, we look at the data type and the provided data structure among the basics of the Excel VBA language.

This is a continuation of the previous article.

Excel VBA Course (7): Excel VBA Language Basics - Syntax

3.3. data type

3.3.1. basic data type

The following table summarizes the data types supported by VB (or VBA). Among the table contents of the source below, Collection and Dictionary are explained separately in “Data Structure”.

List of basic data types

source: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary

data format	storage capacity	range
Boolean	2 bytes	True or False
Byte	1 byte	0 ~ 255
Collection	Unknown	Unknown
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 ~ 922,337,203,685,477.5807
Date	8 bytes	January 1, 100, to December 31, 9999 (100-01-01 ~ 9999-12-31)
Decimal	14 bytes	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point +/-7.9228162514264337593543950335 with 28 places to the right of the decimal Smallest non-zero number is+/-0.0000000000000000000000000001
Dictionary	Unknown	Unknown
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Integer	2 bytes	-32,768 to 32,767
Long (Long integer)	4 bytes	-2,147,483,648 to 2,147,483,647
LongLong (LongLong integer)	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Valid on 64-bit platforms only.
LongPtr (Long integer on 32-bit systems, LongLong integer on 64-bit systems)	4 bytes on 32-bit systems 8 bytes on 64-bit systems	-2,147,483,648 to 2,147,483,647 on 32-bit systems -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Object	4 bytes	Any Object reference
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E38 for positive values
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
String (fixed-length)	Length of string	1 to approximately 65,400
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length (24 bytes on 64-bit systems)	Same range as for variable-length String
User-defined (using Type)	Number required by elements	The range of each element is the same as the range of its data type.

Among them, the most frequently used data types are Int, Long, String, and Boolean.

Variant and User-defined data types are further explained below.

Variant data type

Variant data type is a special data type that can store and process multiple data types. If the data type is omitted when declaring a variable, it is specified as a Variant data type. For more information about Variant data format, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/variant-data-type

To check what type of data a Variant variable is storing during execution, use the VarType function.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function

construction

VarType(varname)

essential varname The argument is a Variant containing variables, excluding variables of user-defined types.

returnform

a constant	value	Explanation
vbEmpty	0	Empty(not initialized)
vbNull	1	Null(No data available)
vbInteger	2	essence
vbLong	3	long integer
vbSingle	4	single-precision floating-point number
vbDouble	5	double precision floating point number
vbCurrency	6	currency value
vbDate	7	date value
vbString	8	string
vbObject	9	individual
vbError	10	error value
vbBoolean	11	Boolean value
vbVariant	12	Variant(variant Arrangementused only)
vbDataObject	13	data access object
vbDecimal	14	decimal value
vbByte	17	byte value
vbArray	8192	Arrangement

Personally, I don't use the Variant data format well, and I mainly use it when reading and writing sheet data at once. For how to read and write a large amount of sheet data using variant array, refer to the article below.

VBA Coding Pattern: Range Loop - Read

VBA Coding Pattern: Range Loop - Write

This is an example code using Variant data type. It shows that it can handle integer, string, real number, and date data types.

Sub VariantTest()
    Dim a As Variant '데이터 형식을 생략하면 Variant 형식임
    a = 1: Debug.Print a
    a = "1": Debug.Print a
    a = 1.1: Debug.Print a
    a = CDate("2021-07-31"): Debug.Print a
End Sub

User-defined data type

You can declare and use user-defined data types. Declare user-defined data type with 'Type' keyword similar to 'struct' in C language. User-defined data types are not objects, so when declared, an instance is created, memory is allocated, and values can be entered.

For more information on User-defined data format, refer to the URL below.

https://docs.microsoft.com/en-us/office/vba/language/how-to/user-defined-data-type

Example codes for user-defined data types are as follows.

Type EmployeeRecord    ' 사용자 정의 형식을 만듭니다.
    ID As Integer    ' 데이터 형식의 요소를 정의합니다.
    Name As String
    Address As String
    Phone As Long
    HireDate As Date
End Type

Sub CreateRecord()
    Dim MyRecord As EmployeeRecord    ' 변수를 선언합니다.
    MyRecord.ID = 12003    ' 요소에 값을 할당합니다.
    MyRecord.Name = "Andy"
    MyRecord.HireDate = CDate("2021-07-31")
End Sub

3.3.2. data structure

Commonly used data structures include Array, Collection, and Dictionary. Let's take a look at each.

Array

It has a fixed number of Items. Since it is not an object, declaring it creates an instance. When declaring, the number of dimensions and the lower limit and upper limit of each dimension can be specified, or the dimensions and range of an array can be dynamically changed at runtime. If you specify only the number of items without specifying the lower and upper bounds of each dimension, the lower bound is 0 by default. If Option Base 1 is used, the default lower bound is 1.

declaration example code

Dim DayArray(50)
Dim Matrix(3, 4) As Integer
Dim MyMatrix(1 To 5, 4 To 9, 3 To 5) As Double

Declare lower/upper bounds for each dimension, check: use LBound, UBound

Dim A(1 To 100, 0 To 3, -3 To 4) 
LBound(A, 1) ->  1,  UBound(A, 1) -> 100 ' 1차원 하한/상한
LBound(A, 2) ->  0,  UBound(A, 2) -> 3   ' 2차원 하한/상한
LBound(A, 3) -> -3,  UBound(A, 3) -> 4   ' 3차원 하한/상한

Dimension size change: Change the dimension size of a dynamic array with the Redim statement.

Dim MyArray() As Integer ' 동적 배열을 선언합니다. 
Redim MyArray(5) ' 5개의 요소를 할당합니다. 
Redim Preserve MyArray(15) ' 15개의 요소로 크기를 변경합니다.

Item Loop (For Next)

'Split & Trim 처리
Public Function SplitTrim(aExpression As String, aDelimeter As String) As String()
    Dim saOut() As String, i As Integer
    saOut = Split(aExpression, aDelimeter)
    For i = LBound(saOut) To UBound(saOut)
        saOut(i) = Trim(saOut(i))
    Next i

    SplitTrim = saOut
End Function

Initialization: Initialize array variables with the Erase statement.

' 배열 변수를 선언합니다.
Dim NumArray(10) As Integer    ' 정수 배열입니다.
Dim StrVarArray(10) As String    ' 변수 문자열 배열입니다.
Dim StrFixArray(10) As String * 10    ' 고정 문자열 배열입니다.
Dim VarArray(10) As Variant    ' Variant 배열입니다.
Dim DynamicArray() As Integer    ' 동적 배열입니다.
ReDim DynamicArray(10)    ' 저장 공간을 할당합니다.
Erase NumArray    ' 0으로 지정된 각 요소입니다.
Erase StrVarArray    ' 길이가 0인 문자열 ("")로 지정된 각 요소입니다.    
Erase StrFixArray    ' 0으로 지정된 각 요소입니다.
Erase VarArray    ' Empty로 지정된 각 요소입니다.
Erase DynamicArray    ' 배열에 의해 사용되는 빈 메모리입니다.

Collection

The Collection class is similar to a dynamic array. A variable number of unique items can be Added and Removed. Because it is an objectSet” and “New” keywords to create an instance. In the Visual Basic language, when creating a reference to an object or assigning a new reference, you must “Set” keyword must be used, and “Set” keyword is not needed when assigning a value to a variable. You need to distinguish between handling objects and variables to avoid grammatical errors. You can make mistakes often, so be careful.

Refer to the table below for codes for each task, such as collection object declaration, instance creation, and item addition.

Task	Sample Code
Declaration	Dim o Col As Collection
Instance creation	Set oCol = New Collection
Instance creation at the same time as declaration	Dim o Col As New Collection
Add Item	oCol.Add “Apple”
Access item by index	oCol(1)
Number of Items	oCol.Count
Item Loop (For)	Dim l As Long For l = 1 to oCol.Count     Debug.Print oCol(l) Next
Item Loop (For Each)	Dim fruit As Variant For Each fruit in oCol     Debug.Print fruit Next
Remove item (designate index)	oCol. Remove(1)
Remove Instance	Set oCol = Nothing

Example code using Collection is as follows. For reference, Items in Collection start from 1.

Sub CollectionTest()
    Dim oCol As Collection
    Set oCol = New Collection
    
    oCol.Add "Apple"
    oCol.Add "Strawberry"
    oCol.Add "Lemon"
    oCol.Add "Banana"
    
    Debug.Print oCol(1)
    Debug.Print oCol.Item(1)
    Debug.Print "----------"

    Dim l As Long
    For l = 1 To oCol.Count
        Debug.Print oCol(l)
    Next

    Debug.Print "----------"

    oCol.Remove (1)

    Dim fruit As Variant
    For Each fruit In oCol
        Debug.Print fruit
    Next

    Set oCol = Nothing
End Sub

'출력
Apple
Apple
----------
Apple
Strawberry
Lemon
Banana
----------
Strawberry
Lemon
Banana

Dictionary

It is possible to manage variable unique items of Key-Value structure, and to add/remove items dynamically.

It is a data structure similar to Dictionary in Python language, HashMap in C++, and HashMap in Java. When accessing by key value, the performance is O(1).

Dictionary class is not provided by default. To use it in the Early binding method, you must first refer to “Microsoft Scripting Runtime”. To use in late binding method, create an instance through CreateObject and use it.

Refer to the table below for code for each task, such as dictionary object declaration, instance creation, and item addition.

Task	Sample Code
Declaration (early binding)	Dim oDic As Dictionary
Instance creation (early binding)	Set oDic = New Dictionary
Declaration (late binding)	Dim oDic As Object
Instance creation (late binding)	Set oDic = CreateObject(“Scripting.Dictionary”)
Add Item	oDic.Add “Apples”, 50
Change Value	oDic(“Apples”) = 60
Get Value	appleCount = oDic(“Apples”)
Check if the key exists	oDic.Exists(“Apples”)
Delete Item	oDic.Remove(“Apples”)
Delete all Items	oDic. RemoveAll
Number of Items	oDic. Count
Item Loop (For Each)	Dim key As Variant For Each key in oDic. Keys     Debug. Print key, oDic(key) Next key
Item Loop (For, Early binding only)	Dim l As Long For l = 0 To oDic.Count -1     Debug.Print oDic.Keys(l), oDic.Items(l) Next l

Example code using Dictionary is as follows. For reference, Items in the Dictionary start from 0.

Sub DictionaryTest()
    Dim oDic As Dictionary
    Set oDic = New Dictionary

    oDic.Add "A", "Apple"
    oDic.Add "S", "Strawberry"
    oDic.Add "L", "Lemon"
    oDic.Add "B", "Banana"

    Debug.Print oDic("A")
    Debug.Print oDic.Items(0)
    Debug.Print "----------"

    Dim l As Long
    For l = 0 To oDic.Count - 1
        Debug.Print oDic.Items(l)
    Next
    
    Debug.Print "----------"
    
    oDic.Remove ("A")

    Dim key As Variant, fruit As String
    For Each key In oDic.Keys
        fruit = oDic(key)
        Debug.Print fruit
    Next

    Set oDic = Nothing
End Sub

'출력
Apple
Apple
----------
Apple
Strawberry
Lemon
Banana
----------
Strawberry
Lemon
Banana

When searching a Dictionary object with the For Loop statement, the variable to contain the key value can only be of Variant or Object type. In the example above, the code on lines 23 and 24 used the Variant format.

---

In this article, we looked at VBA data types and data structures. In particular, Collection and Dictionary are very likely to be used frequently, so please familiarize yourself with them. Next, we will look at how-tos that are good to know when coding VBA.

---

<< Related article list >>

• Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
• Excel VBA Course(1): Overview of Excel VBA
• Excel VBA Course (2): Excel VBA Basics
• Excel VBA Course (3): Excel Object Model
• Excel VBA Course (4): Working with Excel Object Model
• Excel VBA Course (5): Excel File Extension, VBE, Font Settings
• Excel VBA Course (6): Excel VBA Language Basics - Variables
• Excel VBA Course (7): Excel VBA Language Basics - Syntax
• Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
• Excel VBA Courses (9): Excel VBA How-To
• Excel VBA Course (10): Tools developed and used with Excel VBA
• Full Table of Contents for Excel VBA Courses