VBA: assign dynamic array in user object definition

Question

I have this definitions:

Type Linea
    name As String
    color As Long
End Type

Type lineas
    from As Integer
    to As Integer
    lineas() As Linea
End Type

How could I instantiate a "lineas" variable assigning several lines on the fly like this pseudocode in proper VBA code?

Sub colours()
    Dim lineas_CMTS As lineas

    lineas_CMTS.from = 200
    lineas_CMTS.to = 219
    lineas_CMTS.lineas() = New Linea {name="Cisco", color=RGB(0,50,100)}
    lineas_CMTS.lineas() = New Linea {name="Huawei", color=RGB(50,0,100)}

End Sub

There is no syntactic shortcut. Using a class with a backing array you could LineA.AddLine(a, b, c) otherwise the best you can do is helper functions. — Alex K.
– Alex K., Commented Feb 5, 2018 at 11:21

DisplayName · Accepted Answer · 2018-02-05 11:34:03Z

As @Alex K said, there's no way with those two structures as you wrote them

but you could consider a workaround, substituting the Linea Type with an Array

Type lineas
    from As Integer
    to As Integer
    lineas() As Variant ' use a variant type instead of a "Linea" one
End Type

Sub colours()
    Dim lineas_CMTS As lineas

    With lineas_CMTS
        .from = 200
        .to = 219
        ReDim .lineas(0 To 1) 
        .lineas(0) = Array("Cisco", RGB(0, 50, 100))
        .lineas(1) = Array("Huawei", RGB(50, 0, 100))
    End With
End Sub

you could avoid ReDim .lineas(0 To 1) if you already know lineas() array dimension in advance

Type lineas
    from As Integer
    to As Integer
    lineas(0 To 1) As Variant
End Type


Sub colours()
    Dim lineas_CMTS As lineas

    With lineas_CMTS
        .from = 200
        .to = 219
        .lineas(0) = Array("Cisco", RGB(0, 50, 100))
        .lineas(1) = Array("Huawei", RGB(50, 0, 100))
    End With
End Sub

Vityata · Accepted Answer · 2018-02-05 14:36:06Z

A possible solution is to define two classes and to refer to them. Class LineaCollection holding a collection of the class Linea. Thus, you may refer to them in a module like this:

Public Sub TestMe()

    Dim lineasCMTS As New LineasCollection

    lineasCMTS.From = 200
    lineasCMTS.ToV = 219

    lineasCMTS.AddValue RGB(0, 50, 100), "Cisco"
    lineasCMTS.AddValue RGB(0, 50, 101), "Huawei"

    Dim cnt As Long
    For cnt = 1 To lineasCMTS.InfoCollection.Count
        Debug.Print lineasCMTS.InfoCollection(cnt).Name
        Debug.Print lineasCMTS.InfoCollection(cnt).Color
    Next cnt

End Sub

The idea of these classes, is that Linea has the Name and the Color properties, including a factory design patter for creating itself:

Option Explicit

Private m_sName As String
Private m_lColor As Long

Public Property Get Name() As String    
    Name = m_sName    
End Property

Public Property Get Color() As Long    
    Color = m_lColor    
End Property

Public Property Let Color(ByVal lNewValue As Long)    
    m_lColor = lNewValue    
End Property

Public Property Let Name(ByVal sNewValue As String)    
    m_sName = sNewValue    
End Property

Public Sub CreateLinea(newObj As Linea, newColor As Long, newName As String)        
    newObj.Name = newName
    newObj.Color = newColor        
End Sub

The LineasCollection has the AddValue, AddToCollection methods, which communicate with the Linea class or with the module outside. From and To are properties and InfoCollection is the possibility to access the whole collection:

Option Explicit

Private m_lFrom             As Long
Private m_lTov              As Long
Private m_cLineasCollection As New Collection

Public Property Get InfoCollection() As Collection
    Set InfoCollection = m_cLineasCollection
End Property

Public Property Get From() As Long
    From = m_lFrom
End Property

Public Property Get ToV() As Long
    ToV = m_lTov
End Property

Public Property Let ToV(ByVal lNewValue As Long)
    m_lTov = lNewValue
End Property

Public Property Let From(ByVal lNewValue As Long)
    m_lFrom = lNewValue
End Property

Public Sub AddToCollection(newObj As Linea)
    m_cLineasCollection.Add newObj
End Sub

Public Sub AddValue(colorNew As Long, nameNew As String)
    Dim newObj  As New Linea
    newObj.CreateLinea newObj, colorNew, nameNew
    AddToCollection newObj
End Sub

Collectives™ on Stack Overflow

VBA: assign dynamic array in user object definition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related