Universal change of all existing elements in a VBA nested array

Question

I have a composite data structure which I have defined using two nested classes. I am presenting a simplified version of the structure here:-

    Routine  Variable  Type
    -------  --------  ----
    Sub_1    l         Long
             y         Class_A
             z         Class_B     

    Class_A  a()       Class_B  
             i         Integer

    Class_B  s         String

So my my first question: Is the above allowable?

Here is the code:

    '1. sub
    Sub NestedArray()

      Const ciNumberToString As Integer = 64

      Dim l As Long
      Dim y As Class_A
      Dim z As Class_B

      Set y = New Class_A
      Set z = New Class_B
  
      For l = 0 To 10
        y.pI = l
        z.pS = Chr(l + ciNumberToString)
        y.pA = z
      Next l

    End Sub

    '2. Class_A
    Option Explicit

      Private a() As Class_B
      Private i As Integer

      Public Property Get pI() As Integer
        pI = i
      End Property

      Public Property Let pI(oI As Integer)
        If Not oI < i Then
          ReDim Preserve a(oI)
        End If
        i = oI
      End Property

      Public Property Get pA() As Class_B
        Set pA = a(i)
      End Property

      Public Property Let pA(oA As Class_B)
        Set a(i) = oA
      End Property


    '3 Class_B
    Option Explicit

      Private i As Integer

      Public Property Get pI() As Integer
        pI = i
      End Property

      Public Property Let pI(oI As Integer)
        i = oI
      End Property`

I put a stop on the 'End Sub' line. This is the expected result (as it appears in the Watches window):-

    Expression    Value    Type
    ----------    -----    ----
    y                      Class_A
      a                    Class_B(0 to 10)
        a(0)               Class_B
          s       "@"      String
          pS      "@"      String
        a(1)               Class_B
          s       "A"      String
          pS      "A"      String
        a(2)               Class_B
          s       "B"      String
          pS      "B"      String
                  
                  .
                  .
                  .

        a(10)              Class_B
          s       "J"      String
          pS      "J"      String
      i           10       Integer
        pA                 Class_B
          s       "J"      String
          pS      "J"      String
        pI        10       Integer

Here is my actual result:-

    Expression    Value    Type
    ----------    -----    ----
    y                      Class_A
      a                    Class_B(0 to 10)
      a(0)                 Class_B
        s         "J"      String
        pS        "J"      String
      a(1)                 Class_B
        s         "J"      String
        pS        "J"      String
      a(2)                 Class_B
        s         "J"      String
        pS        "J"      String

                  .
                  .
                  .

      a(10)                 Class_B
        s         "J"       String
        pS        "J"       String
      i           10        Integer
    pA                      Class_B
      s           "J"       String
      pS          "J"       String
    pI            10        Integer

y.a(0) is set correctly initially, i.e. it has a value of 0. However on the next iteration it is reset to 1. this is done before y.a(1) is set by the code here:-

    For l = 0 To 10
      y.pI = l
      z.pS = Chr(l + ciNumberToString)
      y.pA = z
    Next l

This is the Class_B code which is executed

    Public Property Let pS(oS As Integer)
      s = oS
    End Property

Because z.pS has changed, y.a is altered from:-

    Expression     Value    Type
    ----------     -----    ----
    y                       Class_A
    a                       Class_B(0 to 10)
      a(0)                  Class_B
        pS         "@"      String
        s          "@"      String

to:-

    y                       Class_A
      a                     Class_B(0 to 10)
        a(0)                Class_B
          pS       "A"      String
          s        "A"      String

... and every iteration of an existing element in the array is set to z.pS

rotabor · Accepted Answer · 2025-02-09 11:29:15Z

1

With small modifications, it works:

Sub NestedArray()
  Const ciNumberToString As Integer = 64
  Dim l As Long
  Dim y As Class_A
  Dim z As Class_B
  Set y = New Class_A
  Set z = New Class_B
  For l = 0 To 10
    Debug.Print "l", l
    y.pI = l
    z.pS = Chr(l + ciNumberToString)
    y.pA = z
    Debug.Print "------------- -------------"
  Next l
End Sub

Class_A

Private a() As Class_B
Private i As Integer

Public Property Get pI() As Integer
  pI = i
End Property

Public Property Let pI(oI As Integer)
  If i <= oI Then
    ReDim Preserve a(oI)
    i = oI
  End If
End Property

Public Property Get pA() As Class_B
  Set pA = a(i)
End Property

Public Property Let pA(oA As Class_B)
  Set a(i) = oA
End Property

Class_B

Private i As Integer

Public Property Get pS() As String
End Property

Public Property Let pS(oA As String)
  i = Asc(oA)
  Debug.Print "pS", oA
End Property

-->

l              0 
pS            @
------------- -------------
l              1 
pS            A
------------- -------------
l              2 
pS            B
------------- -------------
l              3 
pS            C
------------- -------------
l              4 
pS            D
------------- -------------
l              5 
pS            E
------------- -------------
l              6 
pS            F
------------- -------------
l              7 
pS            G
------------- -------------
l              8 
pS            H
------------- -------------
l              9 
pS            I
------------- -------------
l              10 
pS            J
------------- -------------

Surely, 'y.a' contains 11 references to a single Class_B object. And, at the end, any 'a(i).i' is the same - 74 (J). To recover it, modify:

Sub NestedArray()
  Const ciNumberToString As Integer = 64
  Dim l As Long
  Dim y As Class_A
  Dim z As Class_B
  Set y = New Class_A
  For l = 0 To 10
    Debug.Print "l", l
    y.pI = l
    Set z = New Class_B
    z.pS = Chr(l + ciNumberToString)
    y.pA = z
    Debug.Print "------------- -------------"
  Next l
End Sub

edited Feb 9 at 11:29

answered Feb 9 at 11:21

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Roberts Feb 12 at 15:12

Problem resolved. Many thanks for the response.

Steve Roberts Feb 12 at 15:24

Problem resolved. Many thanks for the response. Stupid me !! I forgot that an Object is not a Class but an instantiation of a Class. So moving the code "Set z = New Class_B" into the For loop is the only thing I needed to do. I would like to clarify one thing. I provided an outdated example of Class_B in my submission. "Private i As Integer" should have read as "Private s As String" followed by the necessary changes within the Get & Let Property code to ensure a clean compilation.

Collectives™ on Stack Overflow

Universal change of all existing elements in a VBA nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related