By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

Global class instantiation in VBA?

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2. Option Explicit 
  3.  
  4. Implements IFormOps
  5.  
  6. Private Sub IFormOps_ClearForm(frm As Form)
  7.  
  8. Dim ctl As Control
  9. For Each ctl In frm.Controls
  10.     If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
  11.         ctl.SetFocus
  12.         ctl.Text = ""
  13.     End If
  14. Next
  15.  
  16. End Sub
  17.  
And an implementation for it, IFormOps:

Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2. Option Explicit 
  3.  
  4. Public Sub ClearForm(frm As Form): End Sub
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdClear_Click() 
  2.  
  3. Dim cMyFormOps As cFormOps
  4. Dim iMyFormOps As IFormOps
  5.  
  6. Set cMyFormOps = New cFormOps
  7. Set iMyFormOps = cMyFormOps
  8.  
  9. iMyFormOps.ClearForm Me
  10.  
  11. End Sub
  12.  
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
May 16 '08 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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
May 16 '08 #2

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public cMyFormOps As cFormOps
  3. Public iMyFormOps As IFormOps
  4.  
  5.  
And then this in the module that sits behind my main form, which is always open:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_Open(Cancel As Integer) 
  2.  
  3. Set cMyFormOps = New cFormOps
  4. Set iMyFormOps = cMyFormOps
  5.  
  6. End Sub
  7.  
Thanks for your input...

Pat
May 16 '08 #3

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish.
May 16 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
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 :)
May 16 '08 #5

patjones
Expert 100+
P: 931
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! :-)
May 16 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
Yes there is. It's about when they stop digging the 6-foot hole :D
May 16 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.