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.

On the Redim Preserve line add "As String" Why do you need preserve anyway if you are going to write over the array? And another thing, use Dictionaries it saves hassle with arrays. — S Meaden
– S Meaden, Commented Mar 21, 2018 at 16:49
Should I just make all my arrays variant (since my array count is variant)? — user1093111
– user1093111, Commented Mar 21, 2018 at 16:52
I make all my dynamic 'array-like', 'list-like' structures Dictionaries. — S Meaden
– S Meaden, Commented Mar 21, 2018 at 16:53
ah in those instances I write dict.Add dict.Count, data , so the key is the count of the dict — S Meaden
– S Meaden, Commented Mar 21, 2018 at 22:05

DisplayName · Accepted Answer · 2018-03-21 19:39:07Z

3

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:

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

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

edited Mar 21, 2018 at 19:39

answered Mar 21, 2018 at 17:00

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

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

9 Comments

user1093111 Over a year ago

Getting a Subscript out of range in ArrayCount() now.

DisplayName Over a year ago

see edit to handle some flaws in your code. if my answer solved your original question you may consider marking it as accepted. thank you

user1093111 Over a year ago

Shoud it not be Private sRelatedTimekeeperNumbers() As Variant with a ()?

DisplayName Over a year ago

as you already experienced, it should not. since that way you are declaring an array of Variants instead of a Variant variable (that will eventually hold an array)

user1093111 Over a year ago

Ok, so if I do that, I get a "type mismatch" which I'm assuming "tmpArr As String" needs to be "tmpArr As Variant" as well

|

Collectives™ on Stack Overflow

Can't assign to array

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related