Excel VBA - Class Module Logic

Question

I'm just learning Excel VBA and I'm missing some concept here I think.

Let's say I've a table of 5 car brands. I want to read those into my own class module of car brands, then print their names.

I've two ways to do this, and only one works. I'm wondering if anyone could explain why?

Working Version:

dim colCarBrands as new collection

For i = 1 To 5

    Dim newCarBrand As clsCarBrand
    Set clsCarBrand = New clsCarBrand

    newCarBrand.BrandName chaletsSheet.Cells(i, 1)

    colCarBrands.Add newCarBrand , newChalet.nameTechnical


Next

Dim b As clsCarBrand
For Each b In colCarBrands
    ' Print items
    Debug.Print b.BrandName
Next

Not Working Version:

dim colCarBrands as new collection

For i = 1 To 5

    Dim newCarBrand As New clsCarBrand

    newCarBrand.BrandName chaletsSheet.Cells(i, 1)

    colCarBrands.Add newCarBrand , newChalet.nameTechnical


Next

Dim b As clsCarBrand
For Each b In colCarBrands
    ' Print items
    Debug.Print b.BrandName
Next

In this version, all car brands in the collection are the same one (it's the last one from the sheet)

Any ideas why?

Thanks!

Maybe have a look at: https://stackoverflow.com/questions/42656468/whats-the-difference-between-dim-as-new-vs-dim-set. There are a few other posts like this you can search for. — Robin Mackenzie, Sep 18 '17 at 11:09

John Coleman · Accepted Answer · 2017-09-18T11:19:10.257

1

In the second version, Dim newCarBrand As New clsCarBrand creates a single instance of the class. Subsequent passes through that loop simply change its BrandName property and then adds another reference to that same object to the collection. This would be clearer if you moved the Dim statement to before the loop. Dim statements are not executable statements which are repeatedly run.

In the first case, the Set statement creates new instances of the object, each of which is in turn added to the collection.

edited Sep 18 '17 at 11:19

answered Sep 18 '17 at 11:13

John Coleman

51,337
7
54
119

Thanks John. So if I'm understanding you correctly the Dim statements in both cases are actually only run once (i.e. not every loop)? – Phil Teare Sep 18 '17 at 14:09
1

@PhilTeare I never think of `Dim` as being "run" at all. Its use is at compile-time rather than run-time to allocate storage and associate the variable name with that storage. `New` creates the object on *first* use. In VBA, the convention is to put all `Dim` statements at the beginning of a procedure (at least according to https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/dim-statement ): "Note When you use the Dim statement in a procedure, you generally put the Dim statement at the beginning of the procedure." – John Coleman Sep 18 '17 at 14:44

Excel VBA - Class Module Logic

1 Answers1