Excel VBA Setting Item as the Default for the Class Property Get and Let

Question

I created this class to experiment with it. My goal is to see if I can read/write data in this format:

x = AppInventory(r,c): AppInventory(r,c) = x
x = AppInventory("A1"): AppInventory("A1") = x
x = AppInventory(cellRange): AppInventory(cell(cellRange) = x

This works when adding .Item: x = AppInventory.Item(r,c)

But I am hoping to eliminate the .Item(), is this possible to default to .Item?

Has anyone ever defaulted Item so you don't have to type it? Or is there another easier way? I tried using Get/Let and Property in the main code, that did not work because the r,c vs string text proves to be a problem because sometimes I am using 1 parameter and other times 2.

Any ideas is appreciated. Thanks.

Class Name: AppInventory

    Private ws As Worksheet
    Private targetCell As Range
        
    Private Sub Class_Initialize()
        Set ws = ThisWorkbook.Worksheets("AppInventory")
    End Sub
    
    Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
        Set targetCell = GetTargetCell(Index1, Index2)
        Item = targetCell.Value
    End Property
        
    Public Property Let Item(Index1 As Variant, Optional Index2 As Variant, ByVal Value As Variant)
        Set targetCell = GetTargetCell(Index1, Index2)
        targetCell.Value = Value
    End Property
        
    Private Function GetTargetCell(Index1 As Variant, Optional Index2 As Variant) As Range
        Select Case VarType(Index1)
            Case vbInteger, vbLong ' Numeric, likely row number
                Set GetTargetCell = ws.Cells(Index1, Index2)
            Case vbString ' String, like "A1"
                Set GetTargetCell = ws.Range(Index1)
            Case vbObject ' Range object
                Set GetTargetCell = ws.Range(Index1.Address)
            Case Else
                MsgBox "Invalid input"
        End Select
    End Function

So far my ideas failed:

Public Default Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
Default Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant

I even tried exporting the class and adding Attribute Item.VB UserMemID = 0 just after the Get Item() and then importing it back in to see if that would work. I think I got it working once, but when I modified the class with simple comments I think it went away and I can't get that working again.

Answer 1

The code you provide look like you are not creating an instance of your class.

x = AppInventory(r,c): AppInventory(r,c) = x
x = AppInventory("A1"): AppInventory("A1") = x
x = AppInventory(cellRng): AppInventory(cell(rnd) = x

The code above will not work unless you have AppInventory set as a PredeclaredId.

Try the following

Dim MyInv as AppInventory
Set myInv = new AppInventory
myInv.Item(r,c)=x

The .Item can be eliminated if you set Item to be the default member.

A number of class attributes are not available from within VBA meaning you ave to export the code , set the attribute and then reimport.

A much easier way to proceed is to install the free and fantastic Rubberduck addin for VBA. This addin allows you to set attibutes by the use of annotations prefixed with '@

'\@DefaultMember
Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
        Set targetCell = GetTargetCell(Index1, Index2)
        Item = targetCell.Value
    End Property
        
    Public Property Let Item(Index1 As Variant, Optional Index2 As Variant, ByVal Value As Variant)
        Set targetCell = GetTargetCell(Index1, Index2)
        targetCell.Value = Value
    End Property

You need to run the Rubberduck parse to get the attibutes updated.

Good luck