Call an Item from a Collection of Items

user_7675437 0
Viewed 61

I have a class "Currency" and a class "Currencies" which is a collection of instances of "Currency" class objects. I think this resembles Excel's Worksheet class which is a member of the Sheets collection. I can address any member by index or "Key", like Sheets(1) or `Sheets("Sheet1").

Here is code from my "Currencies" class module. It's abbreviated for use here and may not run, which is not the issue.

Private Sub Class_Initialize()
    ' Class "Currencies"
    
    Dim R       As Long
    
    Set AllCcys = New Collection
    Arr = .Range("Currencies").Value
    For R = 1 To UBound(Arr)
        Set Ccy = Me.Add(Arr(R, 1))
    Next R
End Sub

Public Function Add(ByVal Key As String) As cCcy

    Dim Fun     As cCcy

    Set Fun = New cCcy
    AllCcys.Add Fun, Key
    Fun.Key = Key
    Set Add = Fun
End Function

Public Property Get Item(Key As Variant) As cCcy
    Set Item = AllCcys(Key)
End Property

With my setup I can access any of the "Currency" objects with syntax like Currencies.Item("USD").Rate or Currencies.Item(1).Rate which I consider convoluted. I would like to use Currencies("USD").Rate analog to what I do when accessing Excel's Sheets collection.

How can I achieve that?

vba excel collections class
1 Answers

According to http://www.cpearson.com/excel/DefaultMember.aspx, you can specify any procedure in a custom class to be the default member by the steps below:

  1. Export Currencies class module
  2. Open the exported module in Notepad
  3. Add Attribute Value.VB_UserMemId = 0 in your Item property like this:
Public Property Get Item(Key As Variant) As cCcy
    Attribute Value.VB_UserMemId = 0    
    Set Item = AllCcys(Key)
End Property
  1. Save and Import back into your file.

Note: You can only have 1 procedure be the default member and you will not see the line Attribute Value.VB_UserMemId = 0 in the VBE.

user_155979360
Related