CODEWITHSUNDEEP
vbaclassexceloop
Francisco Rubin Capalbo
Francisco Rubin Capalbo

Reputation: 165

Excel VBA - Public Scope for Instance of User Defined Class

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.

Upvotes: 2

Views: 1092

Answers (1)

Gary McGill
Gary McGill

Reputation: 27546

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

Upvotes: 2

Related Questions