Excel VBA declaring an arraylist inside of a class

user_10130215 0
Viewed 464

I'm trying to create a Staff class module, with strings for surname etc, and an arraylist to be used for storing / calling between 0-10 strings dependant on how many are added when used.

The class module is called StaffClass and contains:

Private m_surname As String
Private m_districts as ArrayList

' Surname Prop

Property Get surname() As String
    surname = m_surname
End Property

Property Let surname(surname As String)
    m_surname = Name
End Property

' District Prop
' This is where i'm getting confused

Private Sub Class_ArrList()
    Set m_districts = New ArrayList
End Sub

Property Get districts() As ArrayList
    districts = m_districts
End Property

Property Let districts(districts as ArrayList)
    m_districts = districts    
End Property

The Main Module contains:

Dim newStaff As StaffClass

Set newStaff = New StaffClass

newStaff.surname = "Smith"

' This is where I want to add to the arraylist

newStaff.districts(0) = "50"

I'm aware I'm missing loads, but struggling to find much relating to collections inside classes for VBA.

Hoping you can help!

vba excel
1 Answers

You can put the arraylist initialization routine in a Class_Initialize, and add a methods to the class to add/insert/etc each item. (Or you could add a method to add the arraylist as a single object).

Also, since ArrayList is an object, you'll need to use the Set keyword when retrieving it.

eg:

Class module

Option Explicit

Private m_surname As String
Private m_districts As ArrayList

' Surname Prop

Property Get surname() As String
    surname = m_surname
End Property

Property Let surname(surname As String)
    m_surname = surname
End Property

Property Get districts() As ArrayList
    Set districts = m_districts
End Property

Function addDistrict(Value As String)
    m_districts.Add Value
End Function

Private Sub Class_Initialize()
    Set m_districts = New ArrayList
End Sub

Regular Module

Option Explicit
Sub par()
Dim newStaff As StaffClass
Dim V As ArrayList

Set newStaff = New StaffClass

With newStaff
    .surname = "Smith"
    .addDistrict 50
    .addDistrict "xyz"
End With

Set V = newStaff.districts

Stop
End Sub
user_28729220
Related