Excel VBA - Public Scope for Instance of User Defined Class

Question

I have recently started to program with Excel VBA. Coming from a Java environment, I am trying to apply OOP principles to VBA, but I'm having a hard time doing so.

I created a Config Class Module to encapsulate all the initial configuration and provide easy and controlled access to it.

From Workbook_Open() Function I initialize the Config object.

What I want to do is to keep that Instance in a Public Scope to allow access to the same instance from the complete program. (static variable or singleton in java)

Option Explicit

Dim conf As Config

Private Sub Workbook_Open()
    Set conf = New Config

End Sub

Doing this works, but the scope is not public.

When I put the Public keyword instead of Dim, I get the following error:

Compile error:

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

So my question is, isn't there any way to keep an object instance public?

And if not, what would be the best way of organizing the project in an object oriented way without keeping everything together in the Workbook module?

Thanks for your time.

You may be interested in this too stackoverflow.com/questions/28817295/… — Dick Kusleika
@DickKusleika That cleared a lot of things up, the other solution seemed like a hack. Thanks a lot! — Francisco Rubin Capalbo
Glad to help. If you want to get even closer to a singleton, google VB_PredeclaredID. It's an attribute that you can set, but attributes in VBA are a bit of a hack themselves. — Dick Kusleika

Gary McGill Gary McGill · Accepted Answer · 2015-08-26T19:46:19

Just change the instancing of the config class to public? Click on the Config class, and set the instancing via the properties window.

Excel VBA - Public Scope for Instance of User Defined Class

1 Answers