2

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.

3
  • You may be interested in this too stackoverflow.com/questions/28817295/… Commented Aug 26, 2015 at 20:21
  • @DickKusleika That cleared a lot of things up, the other solution seemed like a hack. Thanks a lot! Commented Aug 26, 2015 at 20:38
  • 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. Commented Aug 26, 2015 at 20:50

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.