CODEWITHSUNDEEP
vbaexcel
John Bustos
John Bustos

Reputation: 19554

VBA - Class vs Type for Memory Optimization

Experts out there - quick question...

In Excel VBA, I have created a class which holds 4 string variables - No constructor and nothing else.

Now, in my code, I am looping through an ENORMOUS array (100k + iterations) Constantly creating a new instance of the class, assigning values to its variables, using it then assigning it toMyClass = nothing. Then looping again:

Dim x As New MyClass
Set x = Nothing

For i = 0 to 1000000
   x.Var1 = "XYZ"
   x.Var2 = "abc"
   x.Var3 = "123"
   x.Var4 = 456

... Use x ...

Set x = Nothing

Next i

Given this usage, would it be more memory efficient to define x as a class or as a public type (or doesn't it really make a difference)??

Thanks!!

Upvotes: 1

Views: 2131

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

They way you have it written, using classes will definitely take longer than types (5x longer on my machine). This is because you're constantly deallocating and reallocating memory when you destroy the class. You may not need to destroy the class though, just keep it in memory and change its properties. This

Sub test3()

    Dim x As CMyClass
    Dim i As Long
    Dim lStart As Long

    lStart = Timer

    Set x = New CMyClass

    For i = 0 To 1000000
       x.Var1 = "XYZ"
       x.Var2 = "abc"
       x.Var3 = "123"
       x.Var4 = 456
    Next i

    Debug.Print Timer - lStart

End Sub

Took the same amount time as a UDT because the class is only created once and only destroyed when it goes out of scope. Unless there is some need to set it to Nothing - and you probably don't if you can use a Type in its place - then you shouldn't. And in that case, it looks like you can use either a Type or a Class and get the same performance.

Upvotes: 3

Related Questions