Structured data and Collections - Do I have to use a class?

[DanEsch]

Gah!

Okay, I have a structured set of data and I need to create collections or dictionaries of instances of these sets and I can do this with classes. In fact I have done this. It works.

But I'm looking at that list of modules and realizing how far I have to go in this project and asking why in hell can't I just pass a UDT as the item to a collection?

That's all I want - I don't need the framework of an entire class, and don't want the overhead here - because I don't need anything but the data structure. When I try to use a UDT I get a compile-time error message about assigning UDT's to late-bound objects and public object modules - enough to make me think there's a way around this here - an attribute hack or something - that I don't know about.

I could use arrays, but then I lose any meaning except the array index, and I've have to use an ENUM to create meaning for the array indices and make damn sure nothing every corrupted the array structures and... ugh!...

Given that I'm stuck in VBA and Excel for the time being (so no C or Python --don't I wish!-- smart-ass comments thankyouverymuch) am I missing something? Is there a way to get a collection or dictionary object to accept a variable of user-defined type as an item? Enquiring minds want to know!

Dan Esch

 

[PHV]

Could you please post some code with a Scripting.Dictionary object raising the compile error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DanEsch]

To be specific, I'm talking about collections, not dictionaries.

Here's an example of what I'm talking about:

I also tried wrapping the collection in a class and setting then setting a reference to a new instance of that, but that didn't do any good either...

This is the snippet I tried when I went to explicitly play around with this. The error it threw is below.

Option Explicit

Type TestUDT
    Label1 As String
    Label2 As String
    Value1 As Double
    Value2 As Double
    Indicator1 As Integer
End Type

Dim mcollTest As Collection


Private Sub testColls()
    Dim temp1 As TestUDT
    Dim temp2 As TestUDT
    
    Set mcollTest = New Collection
    
    With temp1
        .Label1 = "This"
        .Label1 = "That"
        .Value1 = 25.65
        .Value2 = 78.5
        .Indicator1 = 8
    End With
    
    With temp2
        .Indicator1 = 2
        .Label1 = "The Other"
        .Label2 = "And then"
        .Value1 = 5.4
        .Value2 = 0.6
    End With
    
    mcollTest.Add temp1, temp1.Label1
    
    mcollTest.Add temp2, temp2.Label1


End Sub

Compile error:

Only user-defined types defined in public object modules can be coerced to or from a variant or
passed to late-bound functions ​

Any advice or insight will be appreciated.

 

[Zathras]

Not sure what you mean about overhead, but this seems to be able to do what you want:

Ordinary module:

Option Explicit

Dim mcollTest As Collection

Public Sub testColls()
    Dim temp1 As New TestUDT
    Dim temp2 As New TestUDT
    Set mcollTest = New Collection
    
    temp1.Populate 8, "This", "That", 25.65, 78.5
    temp2.Populate 2, "The Other", "And then", 5.4, 0.6
    mcollTest.Add temp1, temp1.Label1
    mcollTest.Add temp2, temp2.Label1
End Sub

Sub readOut()
  Dim tempx As TestUDT
    
  For Each tempx In mcollTest
    With tempx
      MsgBox .Indicator1 & ": " & .Label1 & " = " & .Value1 _
                          & " " & .Label2 & " = " & .Value2
    End With
  Next
End Sub

Class module:

   Private pLabel1 As String
   Private pLabel2 As String
   Private pValue1 As Double
   Private pValue2 As Double
   Private pIndicator1 As Integer

Property Let Label1(s As String)
  pLabel1 = s
End Property
Property Let Label2(s As String)
  pLabel2 = s
End Property
Property Let Value1(v As Double)
  pValue1 = v
End Property
Property Let Value2(v As Double)
  pValue2 = v
End Property
Property Let Indicator1(i As Integer)
  pIndicator1 = i
End Property

Property Get Label1() As String
  Label1 = pLabel1
End Property
Property Get Label2() As String
  Label2 = pLabel2
End Property
Property Get Value1() As Double
  Value1 = pValue1
End Property
Property Get Value2() As Double
  Value2 = pValue2
End Property
Property Get Indicator1() As Integer
  Indicator1 = pIndicator1
End Property

Public Sub Populate(Indicator As Integer, Label1 As String, _
        Label2 As String, Value1 As Double, Value2 As Double)
  pLabel1 = Label1
  pLabel2 = Label2
  pValue1 = Value1
  pValue2 = Value2
  pIndicator1 = Indicator
End Sub

There may be a more elegant way to change the name of a class, but I had to save it and modify the line where it says Attribute VB_Name = "Class1" and change it to Attribute VB_Name = "TestUDT" then save it as TestUDI.cls

Put the cursor over the testColls() routine and press F5, then hover over the readOut() routine and press F5 again.

 

[DanEsch]

Yep, that was what I was afraid of.



I was hoping I wouldn't have to code classes for each of the (multiple) UDT's I need to handle. I have that in hand and it works, kinda. I was just looking for a more elegant solution if one was to be had.

Doesn't look like there's a way around it though. Just frustrating when VBA documentation says "any" type can be added to a collection, they don't include the fine print.

Or better... ...this wrong tool. Never use this.


Thanks for the time and effort, though, I appreciate it.

Best,

Dan