Extending the VBA Extensibility Library

The Microsoft Visual Basic for Applications Extensibility library let's us meta-program VBA, but the way it handles (or rather, doesn't handle) getting to the actual subs and functions is clunky at best. I decided to write a few class modules to make it easier.

Considering this can be kind of dangerous to do, I want to know that it's working the way I think it does without unintended side effects. Of course, I'm also interested in other feedback. I'd like to gauge if I've learned anything over the last few days here. I feel like I have the logic and style pretty tight, so I'm particularly interested in hearing thoughts on how I handled the object model.

There are three classes:

• vbeProcedure - does most of the heavy lifting of getting us the procedures.

• vbeProcedures - Simple collection class that holds only the vbeProcedure type.

• vbeCodeModule - Ties the VBIDE.CodeModule object to a vbeProcedures collection (as well as actually creating that collection.)

The project requires references to both the Microsoft Visual Basic for Applications Extensibility 5.3 and Microsoft Access 14.0 Object libraries.

vbeCodeModule

```
Option Compare Database
Option Explicit

Private mCodeModule As CodeModule
Private mVbeProcedures As VbeProcedures

Public Property Get CodeModule() As CodeModule
Set CodeModule = mCodeModule
End Property

Public Property Let CodeModule(ByRef CodeMod As CodeModule)
Me.Initialize CodeMod
End Property

Public Property Get VbeProcedures()
Set VbeProcedures = mVbeProcedures
End Property

Public Sub Initialize(CodeMod As CodeModule)
Set mCodeModule = CodeMod
Set mVbeProcedures = getProcedures(mCodeModule)
End Sub

Private Sub Class_Terminate()
Set mVbeProcedures = Nothing
Set mCodeModule = Nothing
End Sub

Private Function getProcedures(CodeMod As CodeModule) As VbeProcedures
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns collection of all vbeProcedures in a CodeModule '
'''

The only coupling I can see with MSAccess-specific vba is in your exampleCall (why is it Private anyway?):

Dim prj As vbProject
Set prj = VBE.ActiveVBProject

Your code works perfectly fine with Excel vba if you take in a VBProject parameter:

Public Sub exampleCall(project As VBProject)

If this code lives in a class module called Ext, I can then do this from the immediate pane to run the test code with Excel VBA (requires appropriate macro security settings):

set x = new Ext
x.examplecall thisworkbook.VBProject

The With block is an abuse here:

Dim proc As vbeProcedure
For Each proc In CodeMod.vbeProcedures
    With proc
        Debug.Print "ParentModule: " & .ParentModule.Name
        Debug.Print "Name: " & .Name
        Debug.Print "StarLine: " & .startLine
        Debug.Print "EndLine: " & .EndLine
        Debug.Print "CountOfLines: " & .CountOfLines
        'uncommenting the next line will print the procedure's contents
        'Debug.Print .Lines

        ' throw an error for fun.
        ' Sidenote, how can I expose this to vbeCodeModule, but not client code?
        .Initialize "ensureSQLNet", prj.VBComponents("OraConfig").CodeModule
    End With
Next proc

I don't mean to sound rude or anything, but you're just being lazy, it should read like this:

Dim proc As vbeProcedure
For Each proc In CodeMod.vbeProcedures
    Debug.Print "ParentModule: " & proc.ParentModule.Name
    Debug.Print "Name: " & proc.Name
    Debug.Print "StarLine: " & proc.startLine
    Debug.Print "EndLine: " & proc.EndLine
    Debug.Print "CountOfLines: " & proc.CountOfLines
    'uncommenting the next line will print the procedure's contents
    'Debug.Print proc.Lines

    ' throw an error for fun.
    ' Sidenote, how can I expose this to vbeCodeModule, but not client code?
    proc.Initialize "ensureSQLNet", prj.VBComponents("OraConfig").CodeModule

Next proc

The reason I'm saying this, is because With "holds" the reference for the instance it's working with, which means if there's no other reference to that instance, the Class_Terminate procedure gets called and the object is destroyed when the End With is reached. You can see this behavior in action in this post.

Using With just to do less typing (for a 4-letter identifier?) is a misuse of the keyword, in my opinion. And it gets worse when the With blocks get nested. Think of Mr. Maintainer ;)

The class names don't follow naming conventions... but then the language itself lower-cases vb when it's used as a prefix to anything, so I'd guess VbeCodeModule would just look weird. The ideal name would be simply CodeModule, but that forces you to fully-qualify the names:

Dim CodeMod As New VBAProject.CodeModule

Otherwise CodeModule clashes with VBE.CodeModule.

The naming convention in VB6/VBA is to use PascalCase for everything, but I find it annoying and I tend to make my local variables and parameters camelCase. I see you're also doing that:

Dim proc As vbeProcedure

But inconsistently:

Dim CodeMod As New vbeCodeModule

Also you're using camelCase for Private procedures and functions, which is confusing. I wouldn't make that distinction between Private and Public, and use PascalCase for all members, regardless of their accessibility.

The vbeProcedure class desperately wants to be immutable, unfortunately unless you make the setters (letters?) Friend and compile them into their own DLL (which VBA can't do), there's no way this can work, so you're stuck with settable properties that are meant to be get-only.

You've done well extracting the RaiseObjectNotInitializedError and RaiseReadOnlyPropertyError code into their own methods, however I'd push the DRY-ing up a step further and create a Private Sub ValidateIsInitialized() procedure whose responsibility would be to call RaiseObjectNotInitializedError when ParentModule is Nothing (no need to check for an empty name then), and then this:

Public Property Get Lines() As String
    If isParentModSet And isNameSet Then
        Lines = Me.ParentModule.Lines(Me.startLine, Me.CountOfLines)
    Else
        RaiseObjectNotIntializedError
    End If
End Property

Can turn into that:

Public Property Get Lines() As String
    ValidateIsInitialized
    Lines = Me.ParentModule.Lines(Me.StartLine, Me.CountOfLines)
End Property

The Name property setter (letter?) can simply throw an error if the new value is vbNullString, as part of regular value validation.

I'm surprised this works:

Public Property Let ParentModule(ByRef vNewValue As CodeModule)

CodeModule being an object, the property should have a setter:

Public Property Set ParentModule(ByRef vNewValue As CodeModule)

I like that you're using a procedure attribute to enable For Each iteration:

```
Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Set NewEnum = mCollection.[_NewEnum]
End

StackExchange Code Review Q#52364, answer score: 13