Can't assign to array

Question

Trying to initialize an object class. In the object, I keep the ID of the timekeeper (person) and an array of RelatedTimekeepers. To do this, I need to append to an array with a list of RelatedID.

The function is question is AddRelatedTimekeeperNumber. Currently, it fails a the commented line.

These are added to a object called timekeeper.

My class looks like:

' TIMEKEEPER CLASS
Private sTimekeeperNumber As String
Private sRelatedTimekeeperNumbers() As Variant

Public Property Let TimekeeperNumber(TimekeeperNumber As String)
    sTimekeeperNumber = TimekeeperNumber

End Property

Public Property Get TimekeeperNumber() As String
    TimekeeperNumber = sTimekeeperNumber

End Property

Public Sub AddRelatedTimekeeperNumber(RelatedTimkeeperNumber As String)
    Dim tmpArr() As String
    Dim i As Integer
    Dim sRelatedTimekeeperNumbersLength As Integer
    sRelatedTimekeeperNumbersLength = ArrayCount(sRelatedTimekeeperNumbers)
    ReDim tmpArr(1 To sRelatedTimekeeperNumbersLength) As String
    For i = 1 To sRelatedTimekeeperNumbersLength

        If i = sRelatedTimekeeperNumbersLength Then
            tmpArr(i) = RelatedTimekeeperNumber
        Else
            tmpArr(i) = sRelatedTimekeeperNumbers(i)
        End If

    Next i

    ReDim Preserve sRelatedTimekeeperNumbers(1 To ArrayCount(tmpArr))

    sRelatedTimekeeperNumbers = tmpArr ' <- "Can't Reassign to array, despite ReDim'ing"

End Sub

Public Sub PrintRelatedTimekeeperNumbers()
    Dim myArray() As Variant
    Dim txt As String
    Dim i As Long

    myArray = sRelatedTimekeeperNumbers

    For i = LBound(myArray) To UBound(myArray)
      txt = txt & myArray(i) & vbCrLf
    Next i

    MsgBox txt

End Sub

Function ArrayCount(ByRef vArray As Variant) As Long
    lArrayCount = UBound(vArray) - LBound(vArray) + 1

End Function

and My procedure to build the class is:

Sub Init_Timekeepers()

    Dim oTimekeeper As New clsTimekeeper

    Dim sTkID As String
    oTimekeeper.TimekeeperNumber = "00089"
    oTimekeeper.AddRelatedTimekeeperNumber ("00089")
    sTkID = oTimekeeper.TimekeeperNumber

    oTimekeeper.AddRelatedTimekeeperNumber ("00091")
    oTimekeeper.AddRelatedTimekeeperNumber ("00092")

    oTimekeeper.PrintRelatedTimekeeperNumbers

End Sub

Before setting array, I think I am ReDim'ing, however, the compiler throws and error at the commented line.

Answer 1

edited to add some hints about the rest of the code

as to the very issue of your question, just dim

Private sRelatedTimekeeperNumbers  As Variant

BTW the shown code has two typos:

lArrayCount = UBound(vArray) - LBound(vArray) + 1

should be

ArrayCount = UBound(vArray) - LBound(vArray) + 1

and:

Public Sub AddRelatedTimekeeperNumber(RelatedTimkeeperNumber As String)

should be

Public Sub AddRelatedTimekeeperNumber(RelatedTimekeeperNumber As String)

---

From your scenario description, I'd say you need the following changes (and consequent refactoring) to your code:

1. Handle sRelatedTimekeeperNumbers being initially empty

   then you have to change ArrayCount to:

   Function ArrayCount(ByRef vArray As Variant) As Long
       If IsEmpty(vArray) Then
           ArrayCount = 0
       Else
           ArrayCount = UBound(vArray) - LBound(vArray) + 1
       End If
   End Function
   

2. add a new element to the array

   then change AddRelatedTimekeeperNumber(RelatedTimekeeperNumber As String) to:

   Public Sub AddRelatedTimekeeperNumber(RelatedTimekeeperNumber As String)
       Dim sRelatedTimekeeperNumbersLength As Long
   
       sRelatedTimekeeperNumbersLength = ArrayCount(sRelatedTimekeeperNumbers)
       If sRelatedTimekeeperNumbersLength = 0 Then
           ReDim sRelatedTimekeeperNumbers(1 To 1) As String
       Else
           ReDim Preserve sRelatedTimekeeperNumbers(1 To sRelatedTimekeeperNumbersLength + 1) As String
       End If
       sRelatedTimekeeperNumbers(sRelatedTimekeeperNumbersLength + 1) = RelatedTimekeeperNumber
   End Sub
   

   and finally, change PrintRelatedTimekeeperNumbers() to:

   Public Sub PrintRelatedTimekeeperNumbers()
       If UBound(sRelatedTimekeeperNumbers) > 0 Then
           MsgBox Join(sRelatedTimekeeperNumbers, " ")
       Else
           MsgBox "no time keepers related to " & sTimekeeperNumber
       End If
   End Sub
   

---

but you make a step further by adopting other objects instead of arrays, like a Collection or a Dictionary Object

in the former case you class code would condense to:

    Option Explicit

    Private sTimekeeperNumber As String
    Private sRelatedTimekeeperNumbers As New Collection

    Public Property Let TimekeeperNumber(TimekeeperNumber As String)
        sTimekeeperNumber = TimekeeperNumber        
    End Property

    Public Property Get TimekeeperNumber() As String
        TimekeeperNumber = sTimekeeperNumber        
    End Property

    Public Sub AddRelatedTimekeeperNumber(RelatedTimekeeperNumber As String)            
        sRelatedTimekeeperNumbers.Add RelatedTimekeeperNumber
    End Sub

    Public Sub PrintRelatedTimekeeperNumbers()
        Dim item As Variant
        Dim txt As String
        If sRelatedTimekeeperNumbers.count > 0 Then
            For Each item In sRelatedTimekeeperNumbers
                txt = txt & item & " "
            Next
            MsgBox Trim(txt)
        Else
            MsgBox "no time keepers related to " & sTimekeeperNumber
        End If
    End Sub

in the latter case it'd become:

    Option Explicit

    Private sTimekeeperNumber As String
    Private sRelatedTimekeeperNumbers As New Scripting.Dictionary

    Public Property Let TimekeeperNumber(TimekeeperNumber As String)
        sTimekeeperNumber = TimekeeperNumber

    End Property

    Public Property Get TimekeeperNumber() As String
        TimekeeperNumber = sTimekeeperNumber

    End Property

    Public Sub AddRelatedTimekeeperNumber(RelatedTimekeeperNumber As String)
        sRelatedTimekeeperNumbers.Add RelatedTimekeeperNumber, ""
    End Sub

    Public Sub PrintRelatedTimekeeperNumbers()
        Dim item As Variant
        Dim txt As String
        If sRelatedTimekeeperNumbers.count > 0 Then
            MsgBox Join(sRelatedTimekeeperNumbers.Keys, " ")
        Else
            MsgBox "no time keepers related to " & sTimekeeperNumber
        End If
    End Sub