Multi-Level Properties 1

[Elysium]

Is it possible to create your own 'multi-level' properties using VBA? For example, I want to have something like:

oClass.Title.First_Level = "My First Title Line"
oClass.Title.Second_Level = "My Second Title Line"

I am making an Excel Export class for use mainly in MS Access.

Regards,

Randy

 

[RickSpr]

You do this by defining an object model--a set of classes wherein one class is at the top of the hierarchy, and contains a property that returns an object of another class, which contains a property...etc.

For the case you cited, you would have class MyClass (which you presumably have already created) containing a property Title, which is defined one of these ways:
Public Title As SomeOtherClass
or
Public Property Get Title() As SomeOtherClass
You would also have class SomeOtherClass which has properties First_Level and Second_Level.

However, you may be able to use an alternative, simpler technique if all you want to do is obtain this syntax. It would go something like this:
(In a standard module - not allowed in a class module)
Public Type Title_Type
First_Level As String
Second_Level As String
End Type
(In class MyClass
Public Property Title As Title_Type

If you want more levels of pseudo-properties, use another User Defined Type and include members from one in the definition of the other:
Public Type Levels_Type
First As String
Second As String
End Type
Public Type Title_Type
Levels As Levels_Type
End Type

You could then use the following syntax:
oClass.Title.Levels.First

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Thank you Rick. This is just what I needed to know.

Randy

 

[RickSpr]

Are you going with the object model? If so, let me warn you: objects containing properties that refer to other objects can get you into very deep water, and cause Access to stall when shutting down. (The bug in DAO which stalls Access shutdown when you forget to free DAO objects is an example of this problem.)

You can avoid these problems by avoiding circular object references. If you want to use an object model and you don't know what that means, ask me. I won't take the time to explain it unless you ask, because it's quite complicated.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Rick,

If you have the time, I would appreciate your comments on object models. That's the method I chose from your reply.

Regards,

Randy

 

[RickSpr]

The problem happens when object A has a reference to object B and object B has a reference to object A. It can involve more objects, too:
A => B => C => A
This is called a "circular object reference".

VBA does automatic garbage collection. That means that when the last reference to an object is destroyed, VBA can tell that no more references exist and it frees the object. Automatic garbage collection saves the programmer from having to explicitly free objects, which is nice. For instance, if you were using DAO Database, TableDef, and Field objects like this:

    Set db = CurrentDb()
    Set tdf = db.TableDefs("My Table")
    Set fld = tdf.Fields("My Field")

then without automatic garbage collection you would have to loop through the Fields collection, freeing each field; then loop through the TableDefs collection, freeing each table definition, before you could Set db = Nothing.

Going back to the circular reference involving A, B, and C above, suppose that A is the top of the hierarchy of objects. You would have an object variable that refers to A, from which you can get to B and C. Lets say your object variable is named X.

What happens when you set X to Nothing? Usually, automatic garbage collection would notice that object A has no more references and would therefore free it. But when you have a circular reference, as above, there is still a reference to A (it's in object C), so VBA will NOT free A. X will have been set to Nothing, so your code no longer has any way to refer to A, or B, or C. Nothing you can do will cause VBA to free these objects, and the fact that there are still objects running will make Access stall when shutting down.

If you avoid circular object references, you won't have a problem. If you can't avoid them, you have to take extra care to break the chain of circular references somehow before setting object variables to Nothing. In this case, the easiest way would be to set A's reference to B to Nothing, before setting X to nothing. Suppose A's reference to B is in a property 'BRef'. Your code would look like this:

    Set X.BRef = Nothing
    Set X = Nothing

This isn't always enough. Consider the following more complex situation, involving two circular references:
A => B => C => A
C => B (that is, C references both A and B)
If you merely ran the code above, the circular reference between B and C would still keep them "running" so they wouldn't be freed. A would be freed, however. The upshot is that you must break EVERY circular reference in the structure of objects you want to destroy.

Ok, so how hard is it to avoid circular references? In a simple object model, usually not hard at all. But in an object model that contains Collection properties, it gets tricky. The problem is that you will often want the objects stored in the collection to add or remove themselves from the collection. To do that, they would have to have a reference to the Collection object, or to some parent object of it. And that creates a circular reference. I haven't found any good way to avoid that problem.

Access and DAO object models avoid it somehow (except for the DAO bug I mentioned earlier), but I haven't figured out how they do it, so I have to do the careful cleanup as in the final code sample above. In Access and DAO, the circular references hinge around Parent properties. I have noticed that Parent properties are always of type Object, even when the specific type of object referred to is known. I have wondered whether Parent properties are implemented as procedures that somehow find the parent object, rather than storing a reference to it. But I haven't been able to figure it out.

If you have an MSDN subscription, you'll find a more thorough discussion of this problem there. Try searching for the phrase "custom object model" or "programming object models".

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Okay, let me show you what I am doing in particular so you can tell me if I am cleaning up my references properly.

I have 5 classes and one module:

clsExcel
clsProperties
clsFirst_Level
clsSecond_Level
clsThird_Level
Module1

The code for Module1 is:

Option Compare Database
---------------------------------------------------------
Private Sub test()
Dim FL_Stats As String, SL_Stats As String, TL_Stats As String

Set oUA = New clsExcel

With oUA
    With .Title
        With .First_Level
            .Text = "First Level"
            .Bold = True
            .FontSize = 12
            .Italics = True
        End With
        With .Second_Level
            .Text = "Second Level"
            .Bold = False
            .FontSize = 10
            .Italics = False
        End With
        With .Third_Level
            .Text = "Third Level"
            .Bold = True
            .FontSize = 8
            .Italics = False
        End With
    End With
End With

FL_Stats = oUA.Title.First_Level.Text & vbLf & oUA.Title.First_Level.Bold & vbLf & oUA.Title.First_Level.FontSize
SL_Stats = oUA.Title.Second_Level.Text & vbLf & oUA.Title.Second_Level.Bold & vbLf & oUA.Title.Second_Level.FontSize
TL_Stats = oUA.Title.Third_Level.Text & vbLf & oUA.Title.Third_Level.Bold & vbLf & oUA.Title.Third_Level.FontSize

MsgBox FL_Stats & vbLf & vbLf & SL_Stats & vbLf & vbLf & TL_Stats

Set oUA = Nothing

End Sub

The code for clsExcel is:

Option Compare Database

Public Title As clsProperties

----------------------------------
Private Sub Class_Initialize()

    Set Title = New clsProperties
   
End Sub
----------------------------------
Private Sub Class_Terminate()

    Set Title = Nothing

End Sub

The code for clsProperties is:

Option Compare Database
Public First_Level As clsFirst_Level
Public Second_Level As clsSecond_Level
Public Third_Level As clsThird_Level

Private Sub Class_Initialize()

    Set First_Level = New clsFirst_Level
    Set Second_Level = New clsSecond_Level
    Set Third_Level = New clsThird_Level

End Sub

Private Sub Class_Terminate()

    Set First_Level = Nothing
    Set Second_Level = Nothing
    Set Third_Level = Nothing
    
End Sub

The code for clsFirst_Level, clsSecond_Level and clsThird_Level is:

Option Compare Database
Private m_Text As String
Private m_Bold As Boolean, m_Italics As Boolean
Private m_FontSize As Integer

Public Property Let Text(sText As String)

    m_Text = sText
    
End Property

Public Property Get Text() As String

    Text = m_Text
    
End Property

Public Property Let Bold(bBold As Boolean)

    m_Bold = bBold
    
End Property

Public Property Get Bold() As Boolean

    Bold = m_Bold
    
End Property

Public Property Let Italics(bItalics As Boolean)

    m_Italics = bItalics
    
End Property

Public Property Get Italics() As Boolean

    Italics = m_Italics
    
End Property

Public Property Let FontSize(iFS As Integer)

    m_FontSize = iFS
    
End Property

Public Property Get FontSize() As Integer

    FontSize = m_FontSize
    
End Property

Do you see a danger of circular references?

I'm going home shortly, so if I don't answer back today, I haven't wasted your effort. I will get back with you tomorrow. Thanks again for your expertise with my question.

Randy

 

[RickSpr]

There is no danger of circular references here. No "child" object has a reference to its "parent" object. Your references look like this:
clsExcel => clsProperties => clsFirst_Level
=> clsSecond_Level
=> clsThird_Level

However, let me make a comment. clsFirst_Level, clsSecond_Level, and clsThird_Level have identical behavior and properties. You don't really need 3 classes for this, you only need 3 instances of a single class. Rename clsFirst_Level to clsLevel, and get rid of the other two. Then change your clsProperties to this:

Option Compare Database
Public First_Level As [red]clsLevel[/red]
Public Second_Level As [red]clsLevel[/red]
Public Third_Level As [red]clsLevel[/red]

Private Sub Class_Initialize()

    Set First_Level = New [red]clsLevel[/red]
    Set Second_Level = New [red]clsLevel[/red]
    Set Third_Level = New [red]clsLevel[/red]

End Sub

Private Sub Class_Terminate()

    Set First_Level = Nothing
    Set Second_Level = Nothing
    Set Third_Level = Nothing
    
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Rick,

Thanks for the guidance. Is my code following good design (after I collapse the level classes into one)? Or, would you go another route?

Randy

 

[RickSpr]

Just a hint: In your Sub test(), did you realize you can code [green]With oUA.Title[/green] instead of using separate With statements?

Structurally and in terms of design, your code is just fine.

Stylistically, I would change a few minor things. I would remove the "cls" prefix on the module names, and remove the various type prefixes on property parameters; this follows the Microsoft convention and makes the class seem more familiar when writing code that uses it.

I wondered why you make the properties of clsProperties Public variables, but in clsLevels you go to the trouble of creating property procedures even though no validation or manipulation logic is necessary. My personal preference is to use variables in a specialty class (one that will be used in only one application), but I always use property procedures in a class I intend for reuse.

Since you're learning to use classes anyway, why not define a method for setting Level properties all at once? For example:

(In clsLevel:)
Public Sub Initialize(Text As String, _
                      Optional Bold As Boolean = False, _
                      Optional Italics As Boolean = False, _
                      Optional FontSize As Integer = 10)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Rick,

I'm a little confused here. My intention was to provide the ability for people to set various 'properties' if they desired. For example, a person could create a template in Excel to export to. The header on the template can be pre-determined by the user as far as font size, bold, etc. If they wanted to override the defaults defined in the Excel template, I would give them the option to do so. For that reason, I wanted to develop something pleasing and familiar to the eye such as:

Title.First_Level.FontSize

I guess I'm not seeing how to accomplish that with the code you last posted. I really want to learn how to use classes because I have been ignoring them so much. I promise that I'll see the light. It may just take a bit.

Thanks again!

Randy

 

[RickSpr]

The code I last posted may not be relevant to your intentions. Typically, when you're designing a general-purpose object class, you don't know in advance how it's going to be used, so you try to provide all the properties and methods any developer would ever need, within reason.

I didn't know how you intend to use your class, so I was seeing it that way. That's why I suggested the Initialize method. If it would be useless to you, just ignore the suggestion.

However, I was thinking that by adding the Initialize method, you could simply your test function to this:

Private Sub test()
Dim FL_Stats As String, SL_Stats As String, TL_Stats As String

Set oUA = New clsExcel

With oUA.Title
    .First_Level.Initialize("First Level", True, True, 12)
    .Second_Level.Initialize("Second Level")
    .Third_Level.Initialize("Third Level", True, FontSize:=8)

    FL_Stats = .First_Level.Text & vbLf & .First_Level.Bold & vbLf & .First_Level.FontSize
    SL_Stats = .Second_Level.Text & vbLf & .Second_Level.Bold & vbLf & .Second_Level.FontSize
    TL_Stats = .Third_Level.Text & vbLf & .Third_Level.Bold & vbLf & .Third_Level.FontSize
End With

MsgBox FL_Stats & vbLf & vbLf & SL_Stats & vbLf & vbLf & TL_Stats

Set oUA = Nothing

End Sub

Now I have to say I'm confused. I don't use Excel much, and have done very little VBA coding in it, so I don't quite get how you plan to let users set the properties. Are these users supposed to be writing VBA code? Are you planning to create an Excel worksheet template file in your Access database? We've clearly had a disconnect here.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Here's what I'm trying to accomplish:

We have a team of data miners that fulfill data requests from our various departments. My group uses Access to download results from our data warehouse and then they paste the raw data into Excel from Access. I wanted to provide them with a simple example of how to set certain parameters (i.e. properties) to automate the process of moving the data from Access to Excel without having to know the VBA to accomplish such a task. So, if I had easy to read and logically laid out properties, it would be a time saver for us. Make sense?

 

[RickSpr]

Somewhat, but not completely.

You said you want to provide the data miners with an example of how to set properties. Actually setting properties is something you do either in VBA code, or in a properties sheet. For your custom classes, there is no properties sheet--you'd have to create your own with a form--so I guess you mean for them to do it with VBA code in the Immediate Window? But that seems contradictory to your not wanting them to have to know VBA.

I'm also confused about how these objects will be consumed. After the properties have been set, all you have is a bunch of objects in Access memory. Do you/will you have code that reads these objects and uses them to build a worksheet? Perhaps by using Automation to invoke Excel and call its methods?

It's not that I really need to know the answers to these questions, though. I have enough understanding to realize (I think) that you don't want the data miners to have to type in a call to my suggested Initialize() method, so it would be useless to you.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

I don't want them to have to know how to create a connection to a datasource, open the recordset and send the data over to the excel object. I would rather them know how to set a few properties. I read somewhere that classes are good for this type of activity because it prevents the user from having to know how the 'engine works under the hood'. All they need to do is give it the information it asks for. That's what I'm trying to accomplish. And yes, I could make a form with various options to fill out which would then be passed to the class. But that's my goal.