Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are the potential consequences of setting a class to PublicNotCreatable?

Tags:

excel

vba

Say I have a class TestClass with the following definition:

Option Explicit

Public x As Variant

I want an instance of this class to be created when the workbook opens, and usable in other workbook events. So in the ThisWorkbook module, I add:

Public tc As TestClass

Private Sub Workbook_Open()

Set tc = New TestClass
tc.x = "abc"

End Sub

When I compile this, I get an error:

Private object modules cannot be used in public object modules as 
parameters or returns types for public procedures, as public data 
members, or as fields of public user defined types

I was able to resolve this by going to the Properties Window for TestClass and setting Instancing to PublicNotCreatable. It seems that this hasn't affected the behavior of the class or my ability to create local instances of it within various functions in this VBA project. Does changing this Instancing setting have any potential problems?

like image 405
sigil Avatar asked Mar 02 '15 18:03

sigil


1 Answers

PublicNotCreatable is so you can use that class in a different VBProject. The NotCreatable part means that whatever VBProject hosts the class has to create any new instances and pass those to the other VBProject. The other VBProject cannot create them by itself.

What you want to do is Public tc As TestClass in a standard module and keep your class instancing Private. Declaring a variable public in a standard module makes that variable available everywhere in your project.

What you did is created a custom property of the ThisWorkbook instance of the Workbook object. ThisWorkbook is a class module (so are the sheet modules and userforms). Those class modules are special because they have a user interface component (workbooks, worksheets, and forms), but they're class modules all the same. You can define properties and methods in ThisWorkbook just like you do in a custom class. And that's what you did. You created a custom property called tc.

To create a property in a class, you can either use Property Get/Let or Public. The shortcut of using Public looks a lot like declaring a public variable in a standard module, but it's not quite the same.

So why the error? When your custom class instance is a property of another class, VBA won't let you create an instance of the custom class because you could end up with a child class and no parent class.

So keep your instancing private and declare your public variables in a standard module. I have a special module called MGlobals where I keep all my public variables. If I have more than a couple, I'm probably doing something wrong.

like image 172
Dick Kusleika Avatar answered Oct 15 '22 00:10

Dick Kusleika