Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is a public object module in VBA?

Tags:

oop

excel

vba

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.

like image 828
leeand00 Avatar asked Sep 24 '12 19:09

leeand00


People also ask

How do I create a public module in VBA?

Instead of the word “DIM,” we need to use the word either “PUBLIC” or “GLOBAL” to make them available to use across all modules of Macros. We have used the word “Global” to make the variable declaration public. You can also use the word “Public.”

What does public mean in VBA?

Public [insert variable name] means that the variable can be accessed or used by subroutines in outside modules. These variables must be declared outside of a subroutine (usually at the very top of your module).

What is public and private in VBA?

Private/Dim variables cannot be seen by the VBA code in different Modules, Forms or Classes in the same VBA project. You use public if you want all of your VBA code to be able to see/call it. You use private/dim if you want it to only be visible/callable from within it's own module.

What does a module do in VBA?

What is VBA Module. VBA module is a “. bcf” extension file that holds the code in the visual basic editor. Each module has its own code window where you can write.


1 Answers

The error comes from full-fledged VB, where you can create an ActiveX dll project, create a public class there and put a UDT into that class.

In VBA, you use classes instead of UDTs when you need to coerce to or from a variant.

So just declare a class with all the fields you have in your UDT, and delete the UDT.

Alternatively, create a DLL in VB6 that would only contain the declaration of the type, and reference that dll from VBA. Or, if you're comfortable with IDL, just create a TLB file directly.

like image 159
GSerg Avatar answered Oct 16 '22 10:10

GSerg