Type mismatch riddle using class collection

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
747
Hello

The code stops at the highlighted line with Type Mismatch if I use oAttendee (or oGuest in the second block), but not if I use oDelegate:

Code:
Private Sub Create_Attendees()


    Dim oDelegate As Delegate
    Dim oAttendee As Attendee
    Dim oGuest As Attendee
    
    Set oAttendees = New Attendees
    
    For Each oDelegate In oDelegates
        Set oAttendee = New Attendee
        oAttendee.ID = oDelegate.ID & "a"
        oAttendee.FirstName = oDelegate.FirstName
        oAttendee.LastName = oDelegate.LastName
        Set oAttendee.Store = oDelegate.Store
        oAttendee.Role = oDelegate.Role
        If oAttendee.Role = "host" Then
[B]            oAttendee.Store.HostsToSeat.Add oAttendee, oAttendee.ID[/B]
        Else
[B]            oAttendee.Store.NonHostsToSeat.Add oAttendee, oAttendee.ID[/B]
        End If
        
        If oDelegate.GuestYN = "y" Then
            Set oGuest = New Attendee
            oGuest.ID = oDelegate.ID & "b"
            oGuest.FirstName = ChristianName(oDelegate.GuestName)
            oGuest.LastName = SurName(oDelegate.GuestName)
            Set oGuest.Store = oDelegate.Store
            oGuest.Role = "guest"
            Set oAttendee.Guest = oGuest
            If oAttendee.Role = "host" Then
[B]                oAttendee.Store.HostsToSeat.Add oGuest, oGuest.ID[/B]
            Else
[B]                oAttendee.Store.NonHostsToSeat.Add oAttendee, oGuest.ID[/B]
            End If
        End If
    Next
End Sub


Yet my Store class defines Hosts/NonHosts to seat as Attendees...

Code:
Option Explicit


Private pName As String
Private pNo As String
Private pRegion As Region
Private pHostsToSeat As Attendees
Private pNonHostsToSeat As Attendees


Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = WorksheetFunction.Proper(Value)
End Property


Public Property Get No() As String
    No = pNo
End Property
Public Property Let No(Value As String)
    pNo = Trim(CStr(Value))
End Property


Public Property Get Region() As Region
    Set Region = pRegion
End Property
Public Property Set Region(Value As Region)
    Set pRegion = Value
End Property


Public Property Get HostsToSeat() As Attendees
    Set HostsToSeat = pHostsToSeat
End Property
Public Property Set HostsToSeat(Value As Attendees)
    Set pHostsToSeat = Value
End Property


Public Property Get NonHostsToSeat() As Attendees
    Set NonHostsToSeat = pNonHostsToSeat
End Property


Public Property Set NonHostsToSeat(Value As Attendees)
    Set pNonHostsToSeat = Value
End Property

Any sharp eye spot the problem here? 'Cos I'm stumped!

Thanks very much for reading.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sorry, typo in code:
Code:
[B] oAttendee.Store.NonHostsToSeat.Add oAttendee, oGuest.ID[/B]
should read
Code:
[B] oAttendee.Store.NonHostsToSeat.Add oGuest, oGuest.ID[/B]


The HostsToSeat collection for each store is created in a previous sub:
Code:
            If oStore Is Nothing Then
                Set oStore = New Store
                Set oStore.HostsToSeat = New Attendees
                Set oStore.NonHostsToSeat = New Attendees
                oStore.No = strStoreNo
                oStore.Name = aImportData(intRecord, intHeaderColumn(StoreName))
                Set oStore.Region = oRegion
                oRegion.Stores.Add oStore, oStore.No
            End If


In immediate window:
?typename(oAttendee.Store.NonHostsToSeat)Attendees
?typename(oattendee)
Attendee

I'm trying to add an Attendee to an Attendees collection but it's a type mismatch??
 
Last edited:
Upvote 0
Attendee and Attendees aren't collections, they are custom classes.

To sort this out, we would need the code for both your custom classes Attendee and Attendees
 
Upvote 0
Hi Mike. Thanks for dropping by.

Attendees class (a collection class)

Code:
Option Explicit

Private pAttendees As Collection


Private Sub Class_Initialize()


    Set pAttendees = New Collection
    
End Sub


Public Sub Add(Item As Delegate, Key As String)


    pAttendees.Add Item, Key
    
End Sub


Public Property Get Count() As Long
    
    Count = pAttendees.Count
    
End Property


Public Sub Remove(IndexOrName As Variant)
    
    pAttendees.Remove IndexOrName
    
End Sub


Public Property Get NewEnum() As IUnknown
    Set NewEnum = pAttendees.[_NewEnum]
End Property


Public Property Get Item(IndexOrName As Variant)
    Set Item = pAttendees.Item(IndexOrName)


End Property

Attendee class:

Code:
Option Explicit


Private pID As String
Private pIndex As Long
Private pFirstName As String
Private pLastName As String
Private pGuest As Attendee
Private pStore As Store
Private pRole As String


Public Property Get ID() As String
    ID = pID
End Property
Public Property Let ID(Value As String)
    pID = Value
End Property


Public Property Get Index() As Long
    Index = pIndex
End Property
Public Property Let Index(Value As Long)
    pIndex = Value
End Property
Public Property Get FirstName() As String
    FirstName = pFirstName
End Property
Public Property Let FirstName(Value As String)
    pFirstName = Value
End Property
Public Property Get LastName() As String
    LastName = pLastName
End Property
Public Property Let LastName(Value As String)
    pLastName = Value
End Property


Public Property Get Guest() As Attendee
    Set Guest = pGuest
End Property
Public Property Set Guest(Value As Attendee)
    Set pGuest = Value
End Property


Public Property Get Store() As Store
    Set Store = pStore
End Property
Public Property Set Store(Value As Store)
    Set pStore = Value
End Property


Public Property Get Role() As String
    Role = pRole
End Property
Public Property Let Role(Value As String)
    pRole = Value
End Property

I just don't get it!

Cheers
 
Upvote 0
Incidentally, if I replace every reference to Attendees in all the declaration statements with Collection the code runs fine. But I want the code to be strongly typed, and I shouldn't have to do that.
 
Upvote 0
FOUND IT!!!!

It was in the Attendees class module (see below). Mike, I'm so grateful you dropped by. Although I found the error myself it really encouraged me that I was not alone! :-)

Code:
Option Explicit

Private pAttendees As Collection


Private Sub Class_Initialize()


Set pAttendees = New Collection

End Sub


Public Sub Add(Item As [COLOR=#b22222][B]Delegate[/B][/COLOR], Key As String)


pAttendees.Add Item, Key

End Sub


Public Property Get Count() As Long

Count = pAttendees.Count

End Property


Public Sub Remove(IndexOrName As Variant)

pAttendees.Remove IndexOrName

End Sub


Public Property Get NewEnum() As IUnknown
Set NewEnum = pAttendees.[_NewEnum]
End Property


Public Property Get Item(IndexOrName As Variant)
Set Item = pAttendees.Item(IndexOrName)


End Property
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top