Performance & Memory – Large Collection vs. UDT Array in VBA

This article is focused on the use of “UDT” user defined types in VBA. I came across this while looking upon performance and memory consumption issues in VBA while dealing with large datasets.

As classes in object oriented concept provide a wrapper and functionality for one or more related elements. So UDT in VBA is also a wrapper mechanism where it can encapsulate the properties of an object, subtracting the custom functionality as provided by the classes.

The primary utility of an UDT is that, it’s a very light weight composition of object (similar in lines to struct in C#). Its more performance oriented and can be used for representing data series for stocks (encapsulating ISIN, Price, volume and more).

This UDT mechanism is commonly used in counter part of collection object because of the following 2 reasons:

1. VBA Collection contains nothing but variant data types, which, as you know, have one of the highest costs with regard to system resources.

2. Collection object uses a linked list storage structure that also adds to its overhead.

As illustrated in the code below a similar structure of data when implemented using user defined type (UDT) against classes, the performance was considerably higher with the UDT mechanism.

Main Module (Module.bas) :

Option Explicit
'--Declaration for getting millisecond count since system startup --
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

'--Define the Max Loop count for the interation to measure performance --
Private Const MAXCOUNT As Integer = 30000

'--User Type Defination--
Public Type StockPrice
    Price As Integer
    volume As Long
End Type

'--Routine creating and adding elements to array/collections
Sub compare_UDT()

    Dim stock_array(MAXCOUNT) As StockPrice
    Dim iCount As Integer
    Dim stock_collection As Collection
    Dim stock As clsStock
    Dim stTime As Long

    Set stock_collection = New Collection

    'User Defined Type
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        With stock_array(iCount)
            .Price = Rnd
            .volume = Rnd
        End With
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for UDT"

    'Class Objects
    stTime = GetTickCount
    For iCount = 0 To MAXCOUNT
        Set stock = New clsStock
        With stock
            .Price = Rnd
            .volume = Rnd
        End With
        stock_collection.Add stock
    Next iCount
    Debug.Print GetTickCount - stTime & " Milliseconds for Collection"

End Sub
'--Results --
'31 Milliseconds for UDT
'171 Milliseconds for Collection

Class Object (clsStock.bas):

Option Explicit

Private m_iPrice As Integer
Private m_lvolume As Long

Public Property Get volume() As Long

    volume = m_lvolume

End Property

Public Property Let volume(ByVal lvolume As Long)

    m_lvolume = lvolume

End Property

Public Property Get Price() As Integer

    Price = m_iPrice

End Property

Public Property Let Price(ByVal iPrice As Integer)

    m_iPrice = iPrice

End Property 

Refrences: 
Link1: http://msdn.microsoft.com/en-us/library/aa260421%28v=vs.60%29.aspx
Link2: http://msdn.microsoft.com/en-us/library/030kb3e9%28v=vs.90%29.aspx