Collections

[tiredofit]

In this code:

Dim Rng As Range
Set Rng = Sheet1.Range("A1:D4")

Dim RngElement As Range

For Each RngElement In Rng

    RngElement.Value = 100

Next RngElement

it populates every cell in the range A1 to D4 with the value of 100.

I was of the understanding that the variable RngElement MUST be declared as the same type as Rng (in this case as a range), or as a Variant.

Looking at code in this article:

https://excelmacromastery.com/vba-dim/#Using_Dim_with_Class_Module_Objects

in particular this part:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]' Class Module - clsStudent

Public Name As String
Public Subject As String

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]' Standard Module

Sub ReadMarks()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    ' Create a collection to store the objects
    Dim coll As New Collection
    
    ' Current Region gets the adjacent data
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long, oStudent As clsStudent
    For i = 2 To rg.Rows.Count
        
        ' Check value
        If rg.Cells(i, 1).Value > 50 Then
            ' Create the new object
            Set oStudent = New clsStudent
            
            ' Read data to the student object
            oStudent.Name = rg.Cells(i, 2).Value
            oStudent.Subject = rg.Cells(i, 3).Value
            
            ' add the object to the collection
            coll.Add oStudent
            
        End If
        
    Next i
    
    ' Print the data to the Immediate Window to test it
    Dim oData As clsStudent
    For Each oData In coll
        Debug.Print oData.Name & " studies " & oData.Subject
    Next oData[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub

oData is declared as clsStudent.

I thought oData has to be declared as a collection because in this next line:

For Each oData In coll

​

it is looping in a collection.

What am I misunderstanding?

Thanks

​

_{^{<strike>
</strike>}}​

<strike></strike>
[/FONT]
<strike></strike>

 

[Norie]

As far as I can see the collection coll is a collection of clsStudent objects.

So if you were looping through that collection it would make sense that the loop variable, e.g. oData, was the same data type as the objects in the collection, i.e. clsStudent.

 

[pgc01]

I thought oData has to be declared as a collection because in this next line:

For Each oData In coll

​

it is looping in a collection.

What am I misunderstanding?​

​

​

​

Hi

I guess you did not read the help carefully.

Syntax
...
For Each element In group
...
element ... For collections, element can only be a Variant variable, a generic object variable, or any specific object variable.

Check here:

https://docs.microsoft.com/en-us/of...ce/user-interface-help/for-eachnext-statement

 

[tiredofit]

As far as I can see the collection coll is a collection of clsStudent objects.

You mean this line:

coll.Add oStudent

​

​

 

[tiredofit]

[/LEFT]
[/FONT][/COLOR]
[/LEFT]
[/FONT] Hi

I guess you did not read the help carefully.



Check here:

https://docs.microsoft.com/en-us/of...ce/user-interface-help/for-eachnext-statement

Thanks, I must learn to read more carefully!

 

[pgc01]

Thanks, I must learn to read more carefully!

We all do.