Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
1 About CC Interface V1.0
1.1 Features of CCInterface
2 General information
2.1 What is an "Interface"?
2.2 What does that mean for programmers?
2.2.1 Example Interface
2.2.2 Example Module Implementing the Interface
2.2.3 Example Module using the Interface
2.2.4 Advantages of Interfaces
2.3 Why an interface can be useless in VBA
2.3.1 Issues with "Implements"
2.3.2 Possible Methods to Workaround
2.4 Enough of Workarounds
3 How to use
3.1 How it works
3.1.1 The Idea
3.1.2 How to Use
1. Adjusting VBA References
2. Using in your own Access file
3. First create the interface wrapper class module
4. Second rewrite the code in the class module using the interface
5. Third rewrite the code using the interface anywhere
6. Final work
The purpose of CCInterface is to completely replace the standard interface technology built in into VBA. Not that the interfaces which you can insert into your code using the "Implements" statement are not a really useful technology. It would be a very great feature if it would work without problems. Unfortunately at least with Access 2010 (and maybe later versions) interfaces in VBA are full of bugs and can destroy your project.
This is where the CCInterface module comes in whose purpose is to allow using interfaces without the "Implements" statement so that your code is stable without loosing the possibility of using interfaces.
An interface object is an object that should be used by different other objects for the same purpose. It gives these different objects the opportunity to be used in a specific standard way.
To give an example from the real world: If you want to listen to your favorite music you need a device which outputs sound. This can be, for example, a pair of loudspeakers or a headphone. These devices cannot produce music, they only offer the ability to output sound.
So you now also need a device that can play music. This could be a CD player or an MP3 player. Both have no device to output the music as sound so you need your headphones or loudspeakers. To get them connected, you need an object between them, a phone jack plug and a socket. (So these are in fact two interfaces, the plug on the speaker side and the socket on the player side. To not make it too complicated we handle it as one interface here.)
The interface has itself no functionality. It cannot produce sound and it cannot play. But it offers both objects (two players and two speaker systems here) the same standard possibilities; to be connected with each other and to forward the sound signal to the output.
So the interface object offers to the player a way to be connected to speakers or headphones, and in the same way it offers to the headphones a way to use an MP3 or CD player (and a lot of others). All players and all output devices will use the same interface so the manufacturers of an output device don't need to think about what player it will be interfaced with nor do the manufacturers of the players need to think about which output device will be connected.
An interface in a programming language like VBA is a collection of procedures (subs, functions or properties). In opposite to normal class modules it usually doesn't contain its own code, it only contains the empty procedures with all parameters, as needed.
The sense of an interface is to provide what the name says: an interface, not code. It should only describe the interface generally without being itself an active object.
That means: If you create an MP3 player class then your class module provides the functionality to output audio from a given file. Your interface class module (an interface is always a class module) is a 3.5mm phone jack which should be used from an output class. The interface module itself is a "dead" module which itself does simply nothing. It is also named a "contract" between the one and the other class module which wants to use it - therefore it doesn't contain any code.
Option Compare Database Option Explicit ' This should be used to inform the objects that the ' program should be closed now. Public Sub BeforeQuitProgram(ByRef Cancel As Boolean) End Sub
Create a new class module in the VBA editor and insert the code above. We name it "IStdFunctions" here (usually interface class modules are named with a big "I" at the beginning of the name).
This should be used to tell the using object module that we want to quit the application before it really does. Moreover it contains a "Cancel" parameter which should give the using object the opportunity to avoid that the program will be closed (maybe because the user needs to decide what to do with unsaved data).
You have now created your interface. Really. That's it, nothing else. That was easy.
Now how you can insert the new "phone jack" into your module?
At first, using an interface is only possible to class modules. Fortunately these are not only pure class modules but also forms and reports which have a code module - these are also class modules (that's the reason why you can open more than one equal form at the same time).
So we create a blank form now, name it "frmTest" and set the "Has Module" property of the form to "Yes" which immediately creates the class module which you can see in the list in the VBA editor (Form_frmTest).
The code for the form is also not complicate for now:
Option Compare Database Option Explicit Implements IStdFunctions
The code contains only the "Implements" statement which tells VBA that the interface class "IStdFunctions" should be used in this class module.
Try to compile your VBA code (using the "Debug" menu) now and you'll get an error message which says that you need to insert the "BeforeQuitProgram" sub in your form's class module (it will only tell you about the first missing procedure, so if your interface module will have more procedures it reports about the next missing code in the next try to compile if you only inserted this one).
So what "Implements" also does is that it tells the compiler which procedures needs to be implemented. You can find a list if you open the left drop-down list in the VBA editor where you'll find "IStdFunction" (because of the "Implements" statement) which you need to select. Then you'll find all procedures of the interface in the right drop-down list:
Selecting the procedure will insert it into your form code now, but because we only have one it was already inserted by selecting the interface name. If you would have more you would need to select each entry one by one until all are inserted (inserted are displayed in bold font in the drop-down list).
This is the code which was inserted:
Private Sub IStdFunctions_BeforeQuitProgram(Cancel As Boolean) End Sub
You can now compile your project because now the contract is fulfilled, all procedures of your interface module are implemented into the form module now.
But if you read the code more thoroughly you'll see that there are differences between the form's code and the code in the interface module:
This has the following reasons:
So now you have an interface class module and a form which implements your interface. We now need a third module that wants to use the interface. This can also be a standard module so please create a standard module named "modTest" now.
This is the code for the new standard module:
Option Compare Database Option Explicit Public Sub TestIStdFunctions() Dim objIStdFunctions As IStdFunctions Dim frm As Form_frmTest DoCmd.OpenForm "frmTest" Set frm = Forms("frmTest") Set objIStdFunctions = frm Stop End Sub
Run the code now, it will break at the "Stop" command highlighting it.
Now you can go to the immediate window and if you enter "objIStdFunctions." you will see the following:
This is the only sub which is in the interface object so no surprise.
You can call that here now if you enter "objIStdFunctions.BeforeQuitProgram False". Nothing happens, because there's no code implementation anywhere. But what did we do in the test sub above?
First we declared two object variables, one as the interface class and one as the specific form class.
Then the form is opened and the "frm" variable is assigned to the opened form. You can now look into the IntelliSense list if you type "frm." into the immediate window, you'll not find the "IStdFunctions" as described above as it is declared "Private".
Now the speciality of the interface classes is used: We do not write "Set objIStdFunctions As New IStdFunctions" as we would expect for any normal class. Instead we assign the form reference of the loaded form which is in the "frm" variable to the object variable of the interface class. This couples all procedures which are in the form module belonging to the interface class to the object variable. So using "BeforeQuitProgram" doesn't start anything in the interface class "IStdFunction", instead it calls the (privately declared!) sub "IStdFunctions_BeforeQuitProgram" sub in the form module!
That's cool, isn't it? You now have an alternative way of calling a list of procedures in a form. But that you could also have done by "normal" methods like declaring these procedures as "Public". So what are the advantages of using an interface now?
First, the interface is not dependent on your form. You can implement it into any other kind of class modules, too, so you could also insert that on the same way to a report or to a general class module.
In the same way as the MP3 player uses the phone jack, the CD player or mobile phone or amplifier could insert the phone jack. But internally they may need to do different things to fulfil the "contract" with the interface; maybe the CD player must initiate the CD load, or the MP3 player needs to load a file and decode it, or the amplifier needs to look for the activated sound source and route that to the phone jack - but in the end the interface does the same for all devices: It outputs the sound exactly the same for each device.
That's the same here; you want to tell different objects to be informed about quitting the program. So you could add the form reference "frm" and also a report reference "rpt" (created with OpenReport etc. on the same way) into a collection and then use this:
Dim bolCancel As Boolean For Each objIStdFunctions In MyCollection objIStdFunctions.BeforeQuitProgram bolCancel If bolCancel then Exit Sub Next
All completely different objects in the collection use the same functionality, they all can be used as interface objects and they all can decide how the functionality is implemented in their specific instances. The form may look for unsaved data and set the "Cancel" variable to "True" if the user must do anything here before stopping the program, the report may stop printing the report and clear the printer queue for example.
The module using the interface doesn't need to know anything about the specific objects, it only uses the interface as this would be the only list of existing procedures. The specific object (like the form implementing the interface) doesn't need to know who has called the interface code and doesn't need to access it directly in any way (it can't as it has no reference to it). You could also provide a reference to the calling module if you want to access anything from there - without knowing what it is if this also has an interface to communicate with.
In this way you could, for example, exchange information between modules, e.g. a form and a popup form where the popup form should be given an ID to display and the popup form should return a chosen ID to the calling form without knowing which form it was. So the popup form could be used for two different main forms which should select a supplier for example and return the chosen supplier without directly accessing the specific main form - because you forward a reference to the main form's interface object only using an interface of the popup form and the popup form accesses this interface object reference only and can be sure that it will be sent to the right main form.
This decouples the code of both forms completely so it is never a problem to rename a control on the main or the popup form as both never accesses a control name of the other form directly.
Really great what you can do with an interface and how it can make your life easier and the code more stable.
"Stable" is unfortunately the big problem using interfaces. As it is a not often used feature of VBA it seems that it is not really tested by the Microsoft developers thoroughly. At least with Access 2010 (never had this issue with A2007) and maybe later versions using the "Implements" statement creates some really bad issues which are nearly impossible to solve.
After using all these workarounds for month I now decided to create my own one, the CCInterface modules.
I wanted to be sure that I can provide the functionality of a normal interface so that I don't need to reprogram my whole project which is a lot code. I also don't wanted to loose the interface technology in general as I'm a fan of the idea. So this is why I programmed this little tool to help me doing that.
As I cannot implement my own code into the VBA compiler I need to use what I have and that is that I need to use a class module.
So instead of rewriting the code I created a wrapper class which should be the replacement of the real interface class. So in our example above with "IStdFunctions" I add a class module "IStdFunctions_Wrapper" which also contains all procedures of the normal interface class but additionally it also contains a reference to the object using it (e.g. a form reference) and code to call the objects in the form (or whatever class module using the interface).
Unfortunately that would need to make all the interface procedures originally declared as "Private" to "Public" which is of course against the rules of a real interface as they now could be called from everywhere. But this is something I can live with as long as the rest is the same as before.
The code of the wrapper interface class ("IStdFunctions_Wrapper") would look like this:
Option Compare Database Option Explicit ' --------- Interface Wrapper for interface IStdFunctions------------ Private prv_objWithInterface As Object Public Property Get ObjWithInterface() As Object Set ObjWithInterface = prv_objWithInterface End Property Public Property Set ObjWithInterface(obj As Object) Set prv_objWithInterface = obj End Property ' This should be used to inform the objects that the ' program should be closed now. Public Sub BeforeQuitProgram(ByRef Cancel As Boolean) If Not prv_objWithInterface Is Nothing Then prv_objWithInterface.IStdFunctions_BeforeQuitProgram Cancel End If End Sub
So we have a private variable to save the object's reference (a form in this example) and public properties to handle it (using properties would also allow to add more code to handle the reference, otherwise a public variable would also be enough).
Next, we need the form implementing the interface. It already exists, we have created that above. It has the "Implements" statement and the procedure from the old interface. The wrapper should now handle this so we need to change the code a little bit:
Option Compare Database Option Explicit 'Implements IStdFunctions Private prv_objIStdFunctions_Wrapper As IStdFunctions_Wrapper Public Property Get ObjIStdFunctions() As IStdFunctions_Wrapper If prv_objIStdFunctions_Wrapper Is Nothing Then Set prv_objIStdFunctions_Wrapper = New IStdFunctions_Wrapper Set prv_objIStdFunctions_Wrapper.ObjWithInterface = Me End If Set ObjITest1 = prv_objIStdFunctions_Wrapper End Property Public Sub IStdFunctions_BeforeQuitProgram(Cancel As Boolean) End Sub
The difference is that the interface sub is now declared as "Public" and a new object variable for the wrapper class module is created and a property which automatically initialises it if it was not created before or lost due an unhandled error (of course unhandled errors only exist during design time...). Moreover the "Implements" statement is deactivated with a comment character.
Next the module using the interface need a little change:
Option Compare Database Option Explicit Public Sub TestIStdFunctions() Dim ObjIStdFunctions As IStdFunctions_Wrapper Dim frm As Form_frmTest DoCmd.OpenForm "frmTest" Set frm = Forms("frmTest") Set ObjIStdFunctions = frm.ObjIStdFunctions Stop End Sub
The difference here is that the interface object variable is now changed to "IStdFunctions_Wrapper" and the assignment is extended by the new property "ObjIStdFunctions" above.
If you run the "TestIStdFunctions" sub now and reach the "Stop" you can now access the new interface like before. IntelliSense also displays only the interface procedures and you could also use "For Each" like in 2.2.4, only you would need to not add the form/report reference directly to the collection, instead you would add "frm.ObjIStdFunctions" or "rpt.ObjIStdFunctions". If you want to use more than one interface in the For loop you could add the form and report references to the collection and use "For Each obj in MyCollection" where "obj" is declared as "Object" and then use "obj.ObjIStdFunctions" or "obj.ObjIOtherInterface".
As there is a lot to do to rewrite all the procedures in the project that already heavily use the interface I wrote this class to automate a lot of those issues.
To use it you must check the references in VBA ("Tools" - "References") and add the "Microsoft Visual Basic for Applications Extensibility 5.3" reference (or whatever version you have, 5.3 here).
This is only needed at design time, you can remove the reference and the two "CCInterface" modules for the final release of your project.
If you've adjusted the references, start "Debug" - "Compile" to make sure the code is compiled and all references are correct.
Before using it please make sure that you have created a backup of your project in a safe place, I'm not responsible if your project gets broken.
As this tool doesn't use specific features of newer versions of Access it should work in all Access versions.
For now you need to insert the code modules "modCCInterface" and "clsCCInterface" into your project. It is also recommended to insert the "modRecreate" module which I wrote to create a new ADP from an existing one by exporting and importing all objects and settings. It has the sub "procRecreateSaveAllAsText" procedure which only does the export. Calling that before using CCInterface makes sure you also have a textual backup of all your objects. As it was written for ADPs it cannot export tables or queries, but at least all forms, reports, macros and modules.
Go to the immediate window and use this command to create the interface wrapper class from your interface module. In this example I use the "IStdFunctions" interface of the previous examples:
That's all, the new interface class module "IStdFunctions_Wrapper" should now exist in the module list in the VBA editor.
Compile and save the code now.
That's as easy as above:
CCIF.RewriteModuleWithInterface "IStdFunctions", "Form_frmTest"
In this example the form "frmTest" was rewritten to use the wrapper instead. That means, the deactivated "Implements", the new property and variable "ObjIStdFunctions" and all interface procedures are now Public.
You now need to do that for any object using the interface, don't forget to compile and save before you go on to the next object.
That was not as easy as the above steps. The module can find the object that declares " As IStdFunctions" and rewrite that. But as there are many possibilities to initialize and use that, it only rewrites the declaration and adds a "TODO:" comment to it so you can easily find that and add the adjustment (adding ".ObjIStdFunctions" when assigning the reference).
To rewrite the declarations you can use this one:
In future, if you want to insert a new module with the interface and you already have the wrapper module you can use "Implements" to insert all the procedures, before compiling that to check if everything is OK.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.