Global class instantiation in VBA?

[patjones]

Hi:

I'm fairly new to object-oriented programming, and am trying to implement a simple class in my VBA code in an attempt to move away from procedural based coding. Here's the set-up. I defined a class called cFormOps as follows:

 Option Compare Database
Option Explicit 
 
Implements IFormOps
 
Private Sub IFormOps_ClearForm(frm As Form)
 
Dim ctl As Control
For Each ctl In frm.Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        ctl.SetFocus
        ctl.Text = ""
    End If
Next
 
End Sub
 

And an implementation for it, IFormOps:

 Option Compare Database
Option Explicit 
 
Public Sub ClearForm(frm As Form): End Sub
 

I want to build on this class to perform some formatting tasks on various forms in my front end. So, in the form module I write out something like:

 Private Sub cmdClear_Click() 
 
Dim cMyFormOps As cFormOps
Dim iMyFormOps As IFormOps
 
Set cMyFormOps = New cFormOps
Set iMyFormOps = cMyFormOps
 
iMyFormOps.ClearForm Me
 
End Sub
 

My question is this. Is there a way for me to instantiate the class on a global level so that I don't have to do it in each form module where I want to use it?

In VB .NET, I know there's even a way to use class functionality without having to instantiate the class. Is that possible in VBA? Thanks.

Pat

[FishVal]

Hello, Pat.

I think you have at least 3 options:

• global variable in code module
• local variable in a code module accessible via the module function
• variable in MainForm module (if you have in your db everrunning form), accessible via MainForm property

But the way you are using class seems somewhat bizarre (for me at least). I don't see any advantage over regular public function. Really it has so generic functionality. ;)

Regards,
Fish

[patjones]

Hello, Pat.

I think you have at least 3 options:

• global variable in code module
• local variable in a code module accessible via the module function
• variable in MainForm module (if you have in your db everrunning form), accessible via MainForm property

But the way you are using class seems somewhat bizarre (for me at least). I don't see any advantage over regular public function. Really it has so generic functionality. ;)

Regards,
Fish

I know, this class doesn't even have any properties. It essentially behaves like a global function. But it's really more academic for me than anything else at this point. Later on, I will be adding more substantial classes to this db to represent employees and leave of absence instances.

So, I chose to do this in my global code module:

 
Public cMyFormOps As cFormOps
Public iMyFormOps As IFormOps
 
 

And then this in the module that sits behind my main form, which is always open:

 Private Sub Form_Open(Cancel As Integer) 
 
Set cMyFormOps = New cFormOps
Set iMyFormOps = cMyFormOps
 
End Sub
 

Thanks for your input...

Pat

[FishVal]

You are welcome.

Best regards,
Fish.

[NeoPa]

It's a bit like a "Hello World" for OOP in VBA right Pat?

Just playing with it to get some hands-on experience and to get the concepts into your head :)

[patjones]

It's a bit like a "Hello World" for OOP in VBA right Pat?

Just playing with it to get some hands-on experience and to get the concepts into your head :)

Pretty much. I've found that writing out some common, simple tasks in OOP helps. The syntactical differences between VBA and .NET are a little annoying, but the concepts seem to be the same.

I guess there never comes a point where you stop learning! :-)

[NeoPa]

Yes there is. It's about when they stop digging the 6-foot hole :D