Solved

VBA Custom Classes - Heirarchy

Posted on 2011-03-15
8
798 Views
Last Modified: 2013-11-27
I'm trying to implement custom Class Modules in Microsoft Access 2003 (VBA).

I would like to build my classes such that they have what I would call "heirarchy" e.g.:
   Staff.RegionalManager.Name

I have built the "Staff" level (clsStaff) and the "Regional Manager" level (clsRM) with a Property Get for ".Name".  
I'm having unbelievable difficulty in making them line up behind the periods embedding one
inside the next.   More than that, I can't seem to find example code online.  
I don't know what that method of class definition is called to drive Intellisense to prompt
 for the next level.

I've tried searching on:

Implements / Inherits
Base Class
Parent
Subclassing
Heirarchy
Class levels

I think if I knew what to call it, I could follow online examples posted by others.
How do you describe what I see in VBA all the time with Automation?   Multiple levels
of classes period after period after period:

Excel.Application.xxx.yyyy.value

I am not trying to create these levels with Custom Collections.  They are single pieces of info
with increasing specificity at each level.

Sorry.  I know my question is inelegant, but I'm pretty good with VBA and this one has me stumped because I can't even look up an example.
0
Comment
Question by:cfetter2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:ErezMor
ID: 35141226
there is no mechanism to make this "effect". an object will ONLY show it's public properties behind a dot/period.
and therefore, one way to go is create the "child" object as a public variable inside the parent object.
another, simpler way, since you said it's all "flat" with no collections involved, you can create user defined types instead of classes, they look the way you're after and they are easier to create/manage
0
 
LVL 57
ID: 35141342
<<I think if I knew what to call it, I could follow online examples posted by others.
How do you describe what I see in VBA all the time with Automation?>>

  Can't do it.    VBA is an object based language, not object orientated one.

  What your seeing it not a class heirarchy at work, but simply objects.

JimD.

0
 

Author Comment

by:cfetter2
ID: 35141530
For anyone who stumbles across this in the future...

Found it.   I guess asking the question prompted me to think of the term "nesting"...  Not sure why I couldn't think of that over that past week and a half.

This gives me "nested" classes without any Collections.   I can display the .Name property (hard coded for this example) two levels deep.

(I removed extraneous code for clarity)

Fired by a button click on my Form:

 
Private Sub cmdTestRM_Click()
    Dim Staff As clsStaff
    
    Set Staff = New clsStaff
    
    MsgBox Staff.TerritoryRM.Name
End Sub

Open in new window


In clsRM:

 
Option Compare Database
Option Explicit

'**********
' Public Properties
'**********

'**********
' Private Variables
'**********
Private pName As String

'**********
' Initialize / Terminate
'**********

Private Sub Class_Initialize()
    pName = "Dan Molloy"
End Sub

'---------------------
' PROPERTIES

'**********
' Name Property
'**********
Property Get Name() As String
    Name = pName
End Property
Property Let Name(pstrNewValue As String)
    pName = pstrNewValue
End Property

'----------------------
' METHODS

'----------------------
' PRIVATE PROC / FUNCTIONS

Open in new window


In clsStaff:

 
Option Compare Database
Option Explicit

'**********
' Public Properties
'**********

'**********
' Private Variables
'**********
Private pobjRM As New clsRM

'**********
' Initialize / Terminate
'**********

'---------------------
' PROPERTIES
'**********
' TerritoryRM Property
'**********
Public Property Get TerritoryRM() As clsRM
    Set TerritoryRM = pobjRM
End Property
Public Property Let TerritoryRM(ByVal objNewValue As clsRM)
    Set pobjRM = objNewValue
End Property

'----------------------
' METHODS

'----------------------
' PRIVATE PROC / FUNCTIONS

Open in new window

0
Independent Software Vendors: 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!

 
LVL 57
ID: 35141664
<<This gives me "nested" classes without any Collections.   >>

  You nesting objects, not classes.  What you have is an object pointer.

 JimD.
0
 

Author Comment

by:cfetter2
ID: 35142295
Jim,

I'm putting these in "Class Modules", not "Modules" or "Forms" of MSAccess.   In these Class Modules, I'm defining Let and Get Properties as well as Methods (Subs and Functions).  I redacted a bunch of the code for simplicity in the example I posted.  

To my understanding, Let and Get cannot be replicated anywhere outside of Class Modules in VBA.

Intellisense doesn't just allow me the Parameters of the Subs and Functions, but exposes all the Properties and Methods.  I'm not being belligerent in any way...    What am I missing?   How are these not Classes?

Thanks for the insight,

Chris
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 35142506
Chris,

<<What am I missing?   How are these not Classes>>

  Yes they are classes, but they don't inherit from one another.  When you speak of a class heirarchy, base classes, sub-classing, your talking about one class that is built on top of another.  That cannot be done in VBA.  That's why your searches turned up nothing.

  What your doing here is creating objects with these classes, then associating the objects with each other through a property of the class.  This:

Public Property Let TerritoryRM(ByVal objNewValue As clsRM)
    Set pobjRM = objNewValue
End Property

   is accepting a pointer to an object (an instance of the clsRM) and storing it as a property.  This would be a "parent" property.

   What your doing is creating an object heirarchy, not a class one.

JimD.


0
 

Author Comment

by:cfetter2
ID: 35142600
Great info.   Thanks so much.
0
 
LVL 57
ID: 35142619
<<Great info.   Thanks so much>>

 No problem.

JimD.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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