Solved

VBA Custom Classes - Heirarchy

Posted on 2011-03-15
8
743 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
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:ErezMor
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<This gives me "nested" classes without any Collections.   >>

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

 JimD.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:cfetter2
Comment Utility
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
Comment Utility
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
Comment Utility
Great info.   Thanks so much.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Great info.   Thanks so much>>

 No problem.

JimD.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now