VBA - Class vs Type for Memory Optimization

Question

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!!

Dick Kusleika · Accepted Answer · 2012-10-09 20:03:08Z

3

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.

answered Oct 9, 2012 at 20:03

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Bustos Over a year ago

... That is an AWESOME bit of testing - THANKS!!! - So, if I want to ensure I don't use values from the previous loop in my next loop (since not all members are always populated), would it slow down the process considerably setting all it's values to "" at the beginning of each loop??

John Bustos Over a year ago

Also, if I'm doing that, would there be any difference between the class / UDT then??

Dick Kusleika Over a year ago

I think you can set all the value to "" or 0 at each iteration and barely notice it. If you use a UDT, you pretty much have to do it so there should be no difference.

John Bustos Over a year ago

THANKS for all your help!! Strangely enough, I tried changing the routine to clearing out the variables in the class rather than setting it = Nothing and it actually took (milliseconds) longer... Just weird.

Alex K. Over a year ago

fwiw you can "reset" a udt quite simply stackoverflow.com/questions/6229076/…

|

Collectives™ on Stack Overflow

VBA - Class vs Type for Memory Optimization

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related