What is a public object module in VBA?

Question posted 2012 · +6 upvotes

I’m trying to get as close to function pointers / abstract classes as I can in VBA.

I have a class called VerificationManager and verifies a bunch of cells in a couple of spreadsheets match up. This will be done in different ways depending on the information and spreadsheets being used with it.

I’d like to be able to make the code reusable by specifying a method to be called in a string using the Application.Run function. So I can rewrite the function that changes.

Now if I was using Java or C# I would be able to extend an abstract class and rewrite the internals to the function. If I was using JavaScript I could store a function in a variable and pass the variable to the class and call it from there.

Inside my class I have a public property called “verificationModule” which I set to the name of the function I want it to call.

Sub VerifyWorkLocations(empLoc As EmployerLocation)
...
    For i = 0 To empLoc.numOfEmp
        Application.Run verificationModule, empLoc.taxdescmatch, empLoc.employees(i)
    Next i
...
End Sub

However, when I try to call Application.Run I receive the following error:

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”

I already tried placing my User Defined Types in a Class Module but it basically said that a class module was the wrong place for a type.