CODEWITHSUNDEEP
excelvbaoopproperties
Campbell Reid
Campbell Reid

Reputation: 139

List the properties of a class in VBA 2003

I've searched all over to see if there is an easy answer to this question, but there doesn't seem to be...

I'm using Excel VBA 2003 (yes, I know it's out-of date, but I can't change this), and all I want to do is list the names and values of all the readable properties in a given custom class.

I'd like to do something like this:

Class definition (for class entitled cFooBar)

Option Explicit

Private pFoo As String
Private pBar As String

Public Property Get Foo() As String
Foo=pFoo
End Property

Public Property Get Bar() As String
Bar=pBar
End Property

Calling code

Dim myFooBar as cFooBar, P as Property
myFooBar=new cFooBar
For Each P in myFooBar.Properties
Debug.Print P.Name, P.Value
Next

Of course, this doesn't work because there doesn't seem to be a "Properties" collection member for custom classes (or at least not one that you can get at), and there isn't a "Property" type either.

Does anybody know a way around this?

TIA,

Campbell

Upvotes: 2

Views: 4069

Answers (1)

David Zemens
David Zemens

Reputation: 53623

As John mentions above, reflection is not supported in VBA. Here is a hack that I have used before. Basically you can create a Collection or Dictionary object to store your "properties" by name.

Option Explicit

Private pProperties As Object

Public Property Get Properties() As Object
    Set Properties=pProperties
End Property

Public Property Let Properties(p as Object) 
    Set pProperties = p
End Property


Sub Class_Initialize()
    Set pProperties = CreateObject("Scripting.Dictionary")

    'Add/instantiate your properties here
    pProperties("foo") = "this is foo"
    pProperties("bar") = "this is bar"


End Sub

Calling code

Dim myFooBar As New cFooBar, P As Variant

For Each P In myFooBar.Properties.Keys()
    Debug.Print P, myFooBar.Properties(P)
Next

Upvotes: 3

Related Questions