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.
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