VBA - classes and structures videos | Excel VBA Part 34 - Type Declarations

Hi Dave,

You might it helpful to read this post first https://stackoverflow.com/questions/975982/user-defined-type-udt-as-parameter-in-public-sub-in-class-module-vb6

The TL;DR version is that you can't do what you're attempting.  You could accomplish this using two different class modules.  The first class module will simulate what your type does, call it SupplierType for example:

'SUPPLIER TYPE CLASS MODULE
Option Explicit

Public SupplierASearchString As String
Public SupplierAWorksheet As String
Public SupplierBSearchString As String
Public SupplierBWorksheet As String

You can then use another class module which has a single property whose type is the SupplierType class.  Call this class module SupplierClass for example:

'SUPPLIER CLASS MODULE
Option Explicit

Private pSupplier As SupplierType

Private Sub Class_Initialize()
    Set pSupplier = New SupplierType
End Sub

Public Property Get Supplier() As SupplierType
    Set Supplier = pSupplier
End Property

You can then create a regular module which has code similar to this:

Option Explicit

Sub TestSupplier()

    Dim s As SupplierClass
    
    Set s = New SupplierClass
    
    s.Supplier.SupplierASearchString = "supplier A"
    
End Sub

I hope that helps!

You're welcome Alexander!

Hi Alexander, I'm not quite sure what you mean, is this something I mentioned in the video?  If so please can you let me know where and I'll see if I can remember what I was talking about (this video was posted in 2014 so it's been a while!)