Array of Class Module type not updating

问题:

I've struck my foot against some weird arrays, to which i can assign value to the whole array but not individual array members.

This is a data class module, named Class_E

    Public Name As Variant
    Public Age As Variant

and this is the body code, which just assigns and re-assigns values to 2 arrays

Sub main()
    Dim r As Class_E
    Set r = Band()
End Sub

Function Band() As Class_E
    Set Band = New Class_E

    'Part 1: Initialize the variables
    Debug.Print "Part 1"
    Band.Name = Array("Edison", "Tesla", "Faraday", "Turing")
    Debug.Print "Before: Name={" & Band.Name(0) & ", " & Band.Name(1) & ", " & Band.Name(2) & ", " & Band.Name(3) & "}"
    Band.Age = Array(10, 10, 10, 10)
    Debug.Print "Before: Age={" & Band.Age(0) & ", " & Band.Age(1) & ", " & Band.Age(2) & ", " & Band.Age(3) & "}"

    'Part 2: Change values of arrays, by item
    Debug.Print "Part 2"
    For k = LBound(Band.Name) To UBound(Band.Name)
        Debug.Print "k=" & k
        Band.Name(k) = "Foo"
        Band.Age(k) = 999
    Next k
    Debug.Print "After: Name={" & Band.Name(0) & ", " & Band.Name(1) & ", " & Band.Name(2) & ", " & Band.Name(3) & "}"
    Debug.Print "After: Age={" & Band.Age(0) & " " & Band.Age(1) & " " & Band.Age(2) & " " & Band.Age(3) & "}"

    'Part 3: Change values of array, entirely
    Debug.Print "Part 3"
    Band.Name = Array("Spring", "Summer", "Autumn", "Winter")
    Debug.Print "Again: Name={" & Band.Name(0) & ", " & Band.Name(1) & ", " & Band.Name(2) & ", " & Band.Name(3) & "}"
    Band.Age = Array(11, 11, 11, 11)
    Debug.Print "Again: Age={" & Band.Age(0) & ", " & Band.Age(1) & ", " & Band.Age(2) & ", " & Band.Age(3) & "}"

    'Part 4: Use another temp array variable
    Debug.Print "Part 4"
    Dim Temp_Name As Variant, Temp_Age As Variant
    Temp_Name = Band.Name
    Temp_Age = Band.Age
    For k = LBound(Band.Name) To UBound(Band.Name)
        Debug.Print "k=" & k
        Temp_Name(k) = "Foo"
        Temp_Age(k) = 999
    Next k
    Debug.Print "Temp: Name={" & Temp_Name(0) & ", " & Temp_Name(1) & ", " & Temp_Name(2) & ", " & Temp_Name(3) & "}"
    Debug.Print "Temp: Age={" & Temp_Age(0) & " " & Temp_Age(1) & " " & Temp_Age(2) & " " & Temp_Age(3) & "}"

    'Part 5: Original arrays again
    Debug.Print "Part 5"
    Debug.Print "Again: Name={" & Band.Name(0) & ", " & Band.Name(1) & ", " & Band.Name(2) & ", " & Band.Name(3) & "}"
    Debug.Print "Again: Age={" & Band.Age(0) & ", " & Band.Age(1) & ", " & Band.Age(2) & ", " & Band.Age(3) & "}"

End Function

This is the output screen I've got

Part 1
Before: Name={Edison, Tesla, Faraday, Turing}
Before: Age={10, 10, 10, 10}
Part 2
k=0
k=1
k=2
k=3
After: Name={Edison, Tesla, Faraday, Turing}
After: Age={10 10 10 10}
Part 3
Again: Name={Spring, Summer, Autumn, Winter}
Again: Age={11, 11, 11, 11}
Part 4
k=0
k=1
k=2
k=3
Temp: Name={Foo, Foo, Foo, Foo}
Temp: Age={999 999 999 999}
Part 5
Again: Name={Spring, Summer, Autumn, Winter}
Again: Age={11, 11, 11, 11}

Questions:

1. Why are the simple assignments in Part 2 not executed ? and there is no error message. we have no problem re-assigning the arrays as shown in Part 3.

2. Why "Temp_Name = Band.Name" in Part 4 a copy of array values instead of the array pointer ? There is no instruction for memory allocation to the temporary arrays.

Your inputs are highly appreciated.

回答1:

Using arrays as public class members is problematic. There's a reason why VBA does not allow it. You tried to overcome this restriction by declaring the members as Variant, but this does not solve the problem.

You can possibly use Collections or Dictionarys, because these are Objects so you can Set references on them. But VBA Arrays are Value-types. Hence when you get an array from a function, you will get a copy of the array.

1. Why are the simple assignments in Part 2 not executed ? and there is no error message. we have no problem re-assigning the arrays as shown in Part 3.

Because you actually worked on a copy of the array. In the statement

Band.Name(k) = "Foo"

Although Band.Name seems to access directly a field of the class, it is actually accessing it through a property get "wrapper"; this is always the case with public class members, the compiler automatically adds get/let wrappers needed for COM's IDispatch interface and eventual late-binding (accessing the class in an object reference). The wrapper returns a copy of the array, not a reference, because as stated earlier, Arrays in VBA (and Strings for that matter) are value-types, not objects. So yoiu worked on the copy of the array.

You can overcome this problem by adding Property Let methods that assign entries in the member array. Consider this code for your class:

Public Names As Variant '<-- Change to plural to add property get/let
Public Ages As Variant  ' <-- Same

Public Property Let Name(i As Long, s As String)
  Names(i) = s
End Property

Public Property Let Age(i As Long, val As Long)
  Ages(i) = val
End Property

You can use these to assign entries in the member arrays. Your Part2 will then work as expected.

2. Why "Temp_Name = Band.Name" in Part 4 a copy of array values instead of the array pointer? There is no instruction for memory allocation to the temporary arrays.

As stated earlier, arrays are value-types, not objects. Assigning an array to a variable always creates a copy, never a duplicate reference like the object case. (add to that the wrapper property get and you find yourself two miles away from the initial class's array).

回答2:

In accordance with my recommendation to flip how you are storing the arrays from Band.Name(0 to 3) to Band(0 to 3).Name, here is some working code.

Class_E (Code) Class Module code sheet

Option Explicit

Private pName As String
Private pAge As Long

Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

Public Property Get Age() As Long
    Age = pAge
End Property
Public Property Let Age(Value As Long)
    pAge = Value
End Property

Standard Module1 (Code) code sheet

Option Explicit

Sub main()
    Dim i As Long, r() As New Class_E
    r = fillBand()
    For i = LBound(r) To UBound(r)
        Debug.Print r(i).Name & " - " & r(i).Age
    Next i
End Sub

Function fillBand() As Class_E()
    Dim tmpBands() As New Class_E, tmp1 As Variant, tmp2 As Variant, i As Long

    'Part 1: Initialize the variables
    Debug.Print "Part 1"
    tmp1 = Array("Edison", "Tesla", "Faraday", "Turing")
    tmp2 = Array(5, 10, 15, 20)
    ReDim tmpBands(LBound(tmp1) To UBound(tmp1))

    For i = LBound(tmp1) To UBound(tmp1)
        With tmpBands(i)
            .Name = tmp1(i)
            .Age = tmp2(i)
        End With
    Next i

    Debug.Print "Before: Name={" & tmpBands(0).Name & ", " & tmpBands(1).Name & ", " & tmpBands(2).Name & ", " & tmpBands(3).Name & "}"
    Debug.Print "Before: Age={" & tmpBands(0).Age & ", " & tmpBands(1).Age & ", " & tmpBands(2).Age & ", " & tmpBands(3).Age & "}"

    'Part 2: Initialize the variables
    Debug.Print "Part 2"
    tmp1 = Array("foo", "bar", "foobar", "else")
    tmp2 = Array(9, 99, 999, 9999)
    ReDim tmpBands(LBound(tmp1) To UBound(tmp1))

    For i = LBound(tmp1) To UBound(tmp1)
        tmpBands(i).Name = tmp1(i)
        tmpBands(i).Age = tmp2(i)
    Next i

    Debug.Print "Before: Name={" & tmpBands(0).Name & ", " & tmpBands(1).Name & ", " & tmpBands(2).Name & ", " & tmpBands(3).Name & "}"
    Debug.Print "Before: Age={" & tmpBands(0).Age & ", " & tmpBands(1).Age & ", " & tmpBands(2).Age & ", " & tmpBands(3).Age & "}"

    'Part 3: Initialize the variables
    Debug.Print "Part 3"
    tmp1 = Array("spring", "summer", "fall", "winter")
    tmp2 = Array(1, 11, 111, 1111)
    ReDim tmpBands(LBound(tmp1) To UBound(tmp1))

    For i = LBound(tmp1) To UBound(tmp1)
        tmpBands(i).Name = tmp1(i)
        tmpBands(i).Age = tmp2(i)
    Next i

    Debug.Print "Before: Name={" & tmpBands(0).Name & ", " & tmpBands(1).Name & ", " & tmpBands(2).Name & ", " & tmpBands(3).Name & "}"
    Debug.Print "Before: Age={" & tmpBands(0).Age & ", " & tmpBands(1).Age & ", " & tmpBands(2).Age & ", " & tmpBands(3).Age & "}"

    fillBand = tmpBands
End Function

Results from Immediate Window

Part 1
Before: Name={Edison, Tesla, Faraday, Turing}
Before: Age={5, 10, 15, 20}
Part 2
Before: Name={foo, bar, foobar, else}
Before: Age={9, 99, 999, 9999}
Part 3
Before: Name={spring, summer, fall, winter}
Before: Age={1, 11, 111, 1111}
Returned Values:
spring - 1
summer - 11
fall - 111
winter - 1111