Home > Mobile >  VBA: Using Let / Get Default Properties with Arrays
VBA: Using Let / Get Default Properties with Arrays

Time:11-15

This is my first class with a default member and I'm still getting a feel for it. I have a let / get property data:

class ArrayClass
private D as variant
Property Get data() As Variant
    data = D
End Property

Property Let data(arg1 As Variant)
D = arg1
End Property

I have added the following in my .cls file (using notepad) to make data the default parameter which looks like this:

Property Get data() As Variant
    Attribute data.VB_UserMemId = 0
    data = D
End Property

I'm just testing this to see how well this works using an array for "D":

Dim testArray As ArrayClass
    Dim passArray(5, 1) As Variant
    passArray(0, 0) = 1
    passArray(1, 0) = 2
    passArray(2, 0) = 3
    passArray(3, 0) = 4
    passArray(4, 0) = 5
    passArray(5, 0) = 6
    passArray(0, 1) = 7
    passArray(1, 1) = 8
    passArray(2, 1) = 9
    passArray(3, 1) = 10
    passArray(4, 1) = 11
    passArray(5, 1) = 12
    Set testArray = new ArrayClass
    testArray = passArray
    testArray(1, 1) = 5
    Debug.Print testArray(2, 1)

It "mostly" works. The "testArray = passArray" calls the "Let Data" property, and assigns the passArray to parameter "D" inside the object.

Also, "Debug.Print testArray(2,1)" also works. That calls the "Get Data" property, and it returns the index values of 2,1 of the "D" parameter in the object.

My problem is the "testArray(1,1) = 5" instruction. The intent was to assign the 1,1 index to parameter D to the number 5. But what happens is it calls the "Get Data" property, instead of the "Let Data" property.

To be clear, I wasn't really expecting it to work because I'm not yet sure how to do it. But I'm at a loss on why its calling the "get property" instead of the "let property" being that the instruction is on the left side of the equal sign.

Anyone have any ideas on how to make it work? Thanks.

CodePudding user response:

What you're experiencing is standard VBA behaviour.

The line testArray(1,1) = 5 first makes a copy of the of the D array (indeed calling Get) and then value 5 is assigned to the 1,1 index of the new/copy array.

You can only call Let to pass a single value as that's what you definition expects:

Property Let data(arg1 As Variant)
    D = arg1
End Property

You can't call it with testArray(1,1) because that passes 2 values. Obviously, you made it clear that you intend to update just one member of the internal D array but that's simply not possible via that Let property.

What you could do is to define a new property that expects 3 parameters:

Property Let item(ByVal index1 As Long, ByVal index2 As Long, ByVal newValue As Variant)
    D(index1, index2) = newValue
End Property

and call it with testArray.item(1, 1) = 5 or maybe define this new property as the default.

Consider declaring D as an array - as it currently stands, you can pass anything e.g. testArray = "test" which I don't think is what you want. So, maybe declare it as Private D() As Variant and then update the data properties to receive and return an array of Variant type:

Property Get data() As Variant()
    data = D
End Property

Property Let data(arg1() As Variant)
    D = arg1
End Property
  • Related