Iterating through the Object Browser in VBA

I would like to iter开发者_Python百科ate through members of any class in a referenced library much like is done using the Object Browser. How can this be done using VBA?

Actually, how to do this is undocumented, but is possible. If your looking to implement a for..Each syntax for a collection, then you can do the following:

Option Compare Database
Option Explicit

Public colT       As New Collection

Public Function NewEnum() As IUnknown

   Set NewEnum = colT.[_NewEnum]

End Function

Public Property Get NextItem() As IUnknown
Attribute NextItem.VB_UserMemId = -4
Attribute NextItem.VB_MemberFlags = "40"

   Set NextItem = colT.[_NewEnum]

End Property

Note the Attribute settings in the above. You have to use the SaveAsText and edit code as above in notepad. You then re-import the code using loadfromText in the debug command line. Once you do the above, then you can go:

Dim n       As clstest1
Dim v       As Variant

Set n = New clstest1

[ code here that adds to collection]

For Each v In n
   Debug.Print v
Next

And, if you not looking to use for...each for a collection, you could/can also setup a default property of the class by going:

Public Property Get Item(Optional ndx As Integer = 1) As Variant
Attribute Item.VB_UserMemId = 0
   Select Case ndx
      Case 1: Item = Me.s1
      Case 2: Item = Me.s2
      Case 3: Item = Me.s3
   End Select

End Property

Public Property Get count() As Integer

   count = 3

End Property

Then, you can go:

Dim n       As clstest1
Dim i       As Integer

Set n = New clstest1

For i = 1 To n.count
   Debug.Print n(i)
Next

However, I not aware how you can automatic add each method/member of the class to a built-in object collection automatic (there is no way to serialize this with compiler options, but I have seen code with each procedure having Attribute Item.VB_UserMemId = 1, then 2 then 3). Perhaps someone with more knowledge can jump in).

However, as the above shows, you can implement a for..each for collections. And you can implement an index for each of the properties/methods if you create a custom item property. And, as the above shows, you can even set that item property you create as the default. I put in the "optional", and thus even:

debug.print n

Will work, or

debug.print n.Item(1)

I found a KB from Microsoft which allowed me to do just that. It also covers iteration over Member details as well.

Private Sub ListClassesInAccess()        
    Dim TypeLibrary As TypeLibInfo
    Dim ClassList As CoClasses
    Dim i As Integer    
    Dim Path As String
    Path = "C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB"

    Set TypeLibrary = TypeLibInfoFromFile(Path)
    Set ClassList = TypeLibrary.CoClasses

    For i = 1 To ClassList.Count
        MsgBox ClassList.Item(i).Name     
    Next

    Set TypeLibrary = Nothing
    Set ClassList = Nothing
End Sub

Unfortunately, Access VBA doesn't support reflection. You could try creating your own abstraction of the object hierarchy that will inspect property values, etc. for you. You might start with something like this:

http://msdn.microsoft.com/en-us/library/aa663065%28office.11%29.aspx

If you have VB6 installed - then you can try tlbinf32.dll. AFAIR - if has number of classes to get info of any typelibrary See http://support.microsoft.com/kb/224331

This may be helpful http://msdn.microsoft.com/en-us/magazine/bb985086.aspx