VBA - Class Module - access to property by index

Question

is there way how to make class working similar to Arrays? Let's say, I have Class (e.g. Workers) where main property is array of the Workers, nothing else. Then I'm filling the class as follows

Dim wks as new Workers
wks.add("Worker1")
wks.add("Worker2")
wks.add("Worker3")

Then in Workers Class module:

Private Workers as Variant

Public Function add(ByVal val As Variant) As Long
  ReDim Preserve Workers(LBound(Workers) To UBound(Workers) + 1)
  Workers(UBound(Workers)) = val
  add = UBound(Workers) - LBound(Workers) +1
End Function

Workers representation -> {"Worker1", "Worker2", "Worker3"}

Then I want to access Worker by its index. I know, how to access it by e.g wks.getWorker(1) but what I want to do, is to access it directly by wks(1) which should return "Worker 1". Example above looks, that usual Array or Collection can be used, but I have many internal methods done, only what I'm missing is to access Workers property to read/write directly by its index number.

Is it possible?

Edit After transfer to Collections, Class looks like:

Option Explicit

Private Workers As Collection

Private Sub Class_Initialize()
  Set Workers = New Collection
End Sub

Public Function add(ByVal val As Variant) As Long
  Workers.add val
End Function

Public Property Get Item(Index As Integer) As Variant
    Item = Workers(Index)
End Property

Public Property Set Item(Index As Integer, Value As Variant)
  Workers.Remove Index
  Workers.add Value, Before:=Index
End Property

with hidden attributes Attribute Item.VB_UserMemId = 0 at Getter and Setter.

Getting works fine:

Dim wks As New Workers
wks.add "Worker1"
wks.add "Worker2"
wks.add "Worker3"
Debug.Print wks(2) ' <-- OK here
'wks(2) = "Second Worker" ' <-- By debugging this go to Getter not Setter and after Getter is done, it allerts with Runtime error '424': Object required
Set wks(2) = "Second Worker" ' <-- This alert immediately Compile error: Object required on "Second Worker" string
Debug.Print wks(2)

Prints "Worker2" into console, thanks for this, but still I'm not able to set a new value to the required Index of the Workers Collection.

user_4216021 · Answer

You could use a default member in VBA. Though you can't make the default memeber directly through VBA editor, but you can use any text editor.

Export your class from VBA editor, i.e. File->Export File
Open your exported class in Notepad (or any text editor)
Add this attribute line on your method or property you want to make it default. Attribute Item.VB_UserMemId = 0

You can for example make getWorker default member as.

Public Function GetWorker(Index As Integer) As Worker
Attribute Item.VB_UserMemId = 0
    GetWorker = Workers(Index)
End Function

you can then use it like.

Set wk = wks(1)

Here is some detail about default members http://www.cpearson.com/excel/DefaultMember.aspx

Edits

An example to make Getter/Setter as default member

Public Property Get Item(Index as Integer) as Worker
Attribute Item.VB_UserMemId = 0
    Set Item = Workers(Index)
End Property

Public Property Set Item(Index as Integer, Value as Worker)
Attribute Item.VB_UserMemId = 0
    Set Workers(Index) = Value
End Property

VBA - Class Module - access to property by index

1 Answers