Clear variables

[tiredofit]

Is it necessary to always clear your variables in a loop?

For example, of the two methods below, which is preferable or are both the same?

Dim MyVar As Class1

For Each Cell In Rng

    Set MyVar = New Class1

    Call MyVar.SomeMethod

    Set MyVar = Nothing

Next Cell

or

Dim MyVar As Class1
Set MyVar = New Class1

For Each Cell In Rng

    Call MyVar.SomeMethod

Next Cell

Thanks

 

[RickXL]

Hi,

Yes, they are different.

But, sometimes they may well do the same thing is used as per your example.

Think of a Class as an Object. It is an object that you define using code. Usually, that object will have data (Properties) as well as functions (Methods). Now think of a Collection as an example of an object. OK, it has been coded for you but it works the same way. Your two versions might be:

Sub v1()
    Dim MyVar As Collection
    
    For Each cell In Rng
        Set MyVar = New Collection
        MyVar.Add cell.Value
        Set MyVar = Nothing
    Next cell
End Sub


Sub v2()
    Dim MyVar As Collection
    
    Set MyVar = New Collection
    For Each cell In Rng
        MyVar.Add cell.Value
    Next cell
End Sub

You can see that Sub v1 will create a new Collection, add a value then destroy the object while v2 will progressively add new cell values to the collection.

If your custom method just displays a MsgBox, say, then v1 and v2 will effectively do the same thing.

I hope this helps.


Regards,

 

[tiredofit]

Thanks for the explanation.

I was hoping they'd ALWAYS be the same, hence I can use the second method to cut down the code.

 

[RickXL]

It is a bit difficult to reply without knowing what is in your class module.

However, if I assume each class instance contains a person's details then typically you would create a new instance of the class then insert some details into it. If you wanted to change those details you would use the same instance and make the change.

But if you wanted to add a different person and keep both sets of data then you would need to create a new instance for the second person.

The point I am trying to make is that it is not just a question of making the code shorter it is a question of matching the structure of your data to the real world.

Chip Pearson has a good introduction: Classes In VBA


Another way of looking at the problem ...

Worksheets are really just special types of class. You could use just one worksheet. Excel creates an instance of a worksheet for you based on the Worksheet class and all the associated methods, properties and events that apply to a worksheet are thus available.

If you create a second worksheet then the class module is called up again, a new instance is created based on what is in that class module. Then you will have two worksheets that are independent but have the same methods, properties and events.

The class module for worksheets is internal and you can't look at it.

Regards,