Go Premium for a chance to win a PS4. Enter to Win


what's the use of PublicNotCreatable?

Posted on 2013-06-28
Medium Priority
Last Modified: 2013-08-08
hey guys, i wanted to create a resource library / resource repository for my code in Access and i was thinking of using PublicNotCreatable. however after playing around with it for awhile and reading some articles on it i realised that it's meant for a different reason - not to emulate a static class or abstract class.

from my understanding it's used by a function in its containing project to be created and passed to its caller. completely doesn't make sense to me at all. below is the reference article i read.


question time!

1) how come the PublicNotCreatable class can be created by a function in its containing project? shouldn't it then be ProtectedCreatable?
2) what's the use of this enforced additional function layer to create this object?
3) how do i create a static class in vba?
4) and can PublicNotCreatable behave as a static class or is the only way to create a static class to use a singleton pattern to implement this? ok i know it's still an instance but at least it's a single instance right? = )
Question by:developingprogrammer
LVL 31

Expert Comment

by:Helen Feddema
ID: 39285050
A number of versions ago, there was a Code Library add-in for Access that could be used for this purpose, but it was dropped the next version.  I keep code segments in plain text files in a folder.  Crude, but it works, and it won't go obsolete.

Author Comment

ID: 39286520
i see - so how do you refer to the code in your text file then? thanks Helen!
LVL 24

Accepted Solution

Bitsqueezer earned 1400 total points
ID: 39304662

I cannot really explain why an external class module cannot be instantiated directly (even your article says "Might seem strange, but that's the way it is." as explanation...), maybe because a separate VBA file will be executed in an own context.

VBA is not a real object oriented language, it's only an "object based language" which uses some methods of a real OOP language so don't expect too much of it. I would say, in general it's OK what VBA offers, more would be too much for the most VBA programmers (the target customer of Access in Microsoft's eyes is not the professional C++ or Java programmer, but an Office user, so we can be glad that there IS a kind of object orientation in VBA).

Public means here: You can use such class in any project, by simply adding the file to the VBA references. The class methods and properties are offered externally so you can use this like a DLL. But as they restricted it to be instantiated in the running VBA context you are forced to use the "function bridge" which instantiate the object "in the other VBA" and returns the completed object to the using VBA.

A static class in VBA is simply a standard module. That's a class module which is automatically instantiated at the time you leave a row after writing it. If you define a Public variable in a standard module you can immediately use it. You do not have all the same possibilities of a real static class in OOP languages but always remember: It's not OOP here.

A kind of an abstract class is the concept of the Interface class in VBA. You can use the keyword "Implements" in a class module to insert an interface class into a class module. The interface class only defines empty subs, properties and so on and contains no code (but it CAN also contain code if you want).

You can handle any class (including external) as a static class by using a simple object variable/function in a standard module (which is a static class):

Private objMyClass As clsMyClass

Public Function MyClass() As clsMyClass
    If objMyClass Is Nothing Then Set objMyClass = New clsMyClass
    Set MyClass = objMyClass
End Function

Open in new window

With this you can always directly instantiate a single object of a class and you always get the same object back. Moreover, you can go on instantiating independent object variables additionally.

The possibility of using external code libraries in all Office applications is a great thing I think because you can use anything which is a class module in other applications. That includes also Access Forms or Reports which are also nothing else than class modules with a graphical user interface. Means: You can reuse standard forms (like your own message box created as a form in a library file) in other applications on the same way.


Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 39305444
whao Christian, this is definitely a lot to digest cause we're talking more about a meta level now which i am not too familiar with, but i get your drift and just need to understand the implementation of it. but what you're saying is extremely powerful and reuseable. let me spend a bit more time on this and reply you with intelligent pointed answers = ) thanks Christian and you're amazing!! = ))
LVL 46

Assisted Solution

aikimark earned 600 total points
ID: 39308844
If you share many modules/classes across your Access projects, you will want to create some code that will import the latest copies of the (text) files into your VBProject object.

If you can package your code into an ActiveX object, then you only need to instantiate that object to get at its methods and properties.

Author Comment

ID: 39343726
guys i need a bit more time to digest this. i'll get back to yall! = )

Author Comment

ID: 39388084
guy need more time as still not at the finishing stages of the project yet sorry!

Author Comment

ID: 39394086
hey Christian and aikimark,

thanks so much for your help and once again i'm so sorry for the delayed response.

Christian i think you've very clearly answered my question here in terms of why it is PublicNotCreatable - probably cause it runs in its own context = ) so i'm closing this question as per what the admins have advised me to do.

however i've got more questions which i will ask at a later date - saving this in my personal knowledgebase.

basically i think it's a bit fuzzy in my head now as to what constitutes the domain of creating reuseable components which we can pass around.

the things in my head now are as per below - we've got a few terms, how do they relate to each other and how do they form the realm and choices of how we implement reuseable components.

will ask at a later date but here they are below. thanks Christian and aikimark once again for being so patient with me!! = )

activex object
import the latest copies of the (text) files into your VBProject object.
add ins
external code libraries

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question