I am using VBA in Excel 2016. I have a macro that takes data from a table and moves it to different tables according to arbitrary criteria. I expect to be using this macro with different table schemas, so I want to decouple the data gathering step.

I have the following Sub in ThisWorkbook:

Public Sub group_data(connector As IConnector, ByVal gather_style As String, ByVal mark_style As String)

Where IConnector is a class module with just the methods I want:

Option Explicit

Public Function Collect(table As ListObject) As Collection
End Function
Public Function CollectOnly(table As ListObject, style As String) As Collection

End Function

Public Function WriteToWorksheet(totals As Collection, updates As Collection, workbook_name As String) As Boolean

End Function

Public Function MarkCollected(table As ListObject, collected_style As String, marked_style As String) As Integer

End Function

and then I expect to create new class modules that implement IConnector when calling my macro from a button in one of the worksheets:

Private Sub CommandButton1_Click()
Dim connector As MyConnector
Set connector = New MyConnector

ThisWorkbook.group_data connector, "Bad", "Good"

However, I get a compile error when I try to run the macro:

Private object modules cannot be used in public object modules as parameters or return types for public procedures, as public data members, or as fields of public user defined types

I have been able to successfully pass custom classes as function parameters before, like this:

' In IFilterPredicate.cls
Option Explicit

Function test(item as Variant) As Boolean

'In NumberFilter.cls
Implements IFilterPredicate

Public Sub Initialize()
End Sub

Private Function IFilterPredicate_Test(data As Variant) As Boolean
    IFilterPredicate_Test = CInt(data) > 0
End Function

'In MyCollection.cls
Private mCol As Collection
...

Public Function filter(filter As IFilterPredicate, Optional ByVal invert_condition As Boolean) As MyCollection
...

'In ThisWorkbook
Public Sub group_data_old(ByVal gather_style As String, ByVal mark_style As String)
    Dim data As MyCollection
...
    Dim filter as NumberFilter
    Set filter = New NumberFilter

    Dim result As MyCollection
    Set result = data.filter(filter)

Here I can create the NumberFilter within the Sub no problem, but I've been unable to use the Class module as a parameter instead.

As I understand it, the compile error is caused by passing a custom class parameter to a top level Sub, which is exactly the functionality I want to achieve. I've been looking at this question, this one and this one, but I don't see any workarounds or fixes for this specific issue.

Is there really no way to pass a Class Module to a sub as I'm trying to do? If not, is there a workaround to achieve the same functionality? If not, what other design pattern can allow me to decouple the macro so I can connect to different data schemas?

I am using VBA in Excel 2016. I have a macro that takes data from a table and moves it to different tables according to arbitrary criteria. I expect to be using this macro with different table schemas, so I want to decouple the data gathering step.

I have the following Sub in ThisWorkbook:

Public Sub group_data(connector As IConnector, ByVal gather_style As String, ByVal mark_style As String)

Where IConnector is a class module with just the methods I want:

Option Explicit

Public Function Collect(table As ListObject) As Collection
End Function
Public Function CollectOnly(table As ListObject, style As String) As Collection

End Function

Public Function WriteToWorksheet(totals As Collection, updates As Collection, workbook_name As String) As Boolean

End Function

Public Function MarkCollected(table As ListObject, collected_style As String, marked_style As String) As Integer

End Function

and then I expect to create new class modules that implement IConnector when calling my macro from a button in one of the worksheets:

Private Sub CommandButton1_Click()
Dim connector As MyConnector
Set connector = New MyConnector

ThisWorkbook.group_data connector, "Bad", "Good"

However, I get a compile error when I try to run the macro:

Private object modules cannot be used in public object modules as parameters or return types for public procedures, as public data members, or as fields of public user defined types

I have been able to successfully pass custom classes as function parameters before, like this:

' In IFilterPredicate.cls
Option Explicit

Function test(item as Variant) As Boolean

'In NumberFilter.cls
Implements IFilterPredicate

Public Sub Initialize()
End Sub

Private Function IFilterPredicate_Test(data As Variant) As Boolean
    IFilterPredicate_Test = CInt(data) > 0
End Function

'In MyCollection.cls
Private mCol As Collection
...

Public Function filter(filter As IFilterPredicate, Optional ByVal invert_condition As Boolean) As MyCollection
...

'In ThisWorkbook
Public Sub group_data_old(ByVal gather_style As String, ByVal mark_style As String)
    Dim data As MyCollection
...
    Dim filter as NumberFilter
    Set filter = New NumberFilter

    Dim result As MyCollection
    Set result = data.filter(filter)

Here I can create the NumberFilter within the Sub no problem, but I've been unable to use the Class module as a parameter instead.

As I understand it, the compile error is caused by passing a custom class parameter to a top level Sub, which is exactly the functionality I want to achieve. I've been looking at this question, this one and this one, but I don't see any workarounds or fixes for this specific issue.

Is there really no way to pass a Class Module to a sub as I'm trying to do? If not, is there a workaround to achieve the same functionality? If not, what other design pattern can allow me to decouple the macro so I can connect to different data schemas?

• excel
• vba

• 1 Try moving the group_data sub to a normal module, rather than an object module. – Rory Commented Feb 6 at 12:23
• Check access modifiers (private/public) to ensure you do not violate access rules. – rotabor Commented Feb 6 at 12:40

1 Answer 1

This works for me:

Class IConnector

Option Explicit

Public Sub SayOK()
End Sub

Class clsConnect

Option Explicit
Implements IConnector

Public Sub IConnector_SayOK()
    Debug.Print "ok"
End Sub

Regular module:

Option Explicit

Sub test()
    Dim o As New clsConnect
    callMe o
End Sub

Sub callMe(o As IConnector)
    o.SayOK '>> "ok"
End Sub

If you want to do that last part in an object module then set your class instancing to "PublicNotCreateable" (in the class module properties)