VBA Framework II

See VBA Framework for the first revision.

For this revision, you can select which VBProject in which to add the code and you can identify a parent class.

I refactored the code to use a class module to make the code more readable. Concatenating strings is always messy, so I hope this helps a little. I made a property for every variation of the class name I’d need. Here are some examples using the Paycheck class:

CSingular CPaycheck
clsSingular clsPaycheck
mcolPlural mcolPaychecks
ParentName Employee
CParentName CEmployee
tblPlural tblPaychecks

I couldn’t identify the name of an unsaved workbook given the VBProject, so I used this code to fill the project combobox:

For Each vbProj In Application.VBE.VBProjects
    sWbName = “”
    lProjCnt = lProjCnt + 1
    If vbProj.Protection = vbext_pp_none Then
        Me.cbxProject.AddItem vbProj.Name
        On Error Resume Next
            sWbName = Dir(vbProj.Filename)
        On Error GoTo 0
       
        If Len(sWbName) = 0 Then
            Me.cbxProject.List(Me.cbxProject.ListCount – 1, 1) = “(Unsaved Workbook)”
        Else
            Me.cbxProject.List(Me.cbxProject.ListCount – 1, 1) = sWbName
        End If
       
        Me.cbxProject.List(Me.cbxProject.ListCount – 1, 2) = lProjCnt
    End If
   
Next vbProj

I just identify “Unsaved Workbook” if there is not a Filename property. I only list projects that aren’t protected to avoid errors writing to protected projects. If there are existing classes in the project, I fill the Parent combobox thusly:

Private Sub cbxProject_AfterUpdate()
   
    Dim vbc As VBComponent
   
    Me.cbxParent.Clear
    Me.cbxParent.AddItem gsNOPARENT
    Me.cbxParent.ListIndex = 0
   
    If Me.cbxProject.ListIndex > -1 Then
        If mclsClass Is Nothing Then Set mclsClass = New CClass
       
        Set mclsClass.Project = Application.VBE.VBProjects(Val(Me.cbxProject.List(Me.cbxProject.ListIndex, 2)))
       
        For Each vbc In mclsClass.Project.VBComponents
            If vbc.Type = vbext_ct_ClassModule Then
                Me.cbxParent.AddItem vbc.Name
            End If
        Next vbc
    End If
   
End Sub

I’d like to only include the singular versions of the classes, but I haven’t gotten there yet. I imagine there are a lot of ways to relate classes, but I only code for one. Using the example mdb, there are a number of Employees stored in a global Collection variable. Each Employee instance has a Paychecks property which retrieves a CPaychecks collecton class. This holds CPaycheck objects that only relate to the Employee.

Download VBAFramework2.zip

Still on the to do list:

  • Browse to mdb instead of hardcoded
  • Create a userform to add/edit/delete records
  • Automatically set an ADO reference
  • Option to make functions instead of subs (for error handling)
  • Allow recreation of the classes and fill code
  • Option to make properties read-only
  • Your suggestions
Posted in Uncategorized

9 thoughts on “VBA Framework II

  1. I’m trying to put this on sourceforge. I’ve applied for a project and it’s pending approval. I’m not sure how a VBA project will fare on sourceforge, but I thought it was worth a try.

  2. When it comes to setting references to ADO, I’d set it for the lowest reference you can without interferring with the ADO functions. I’ve run into situations where I’ll set a ADO 2.7 reference for something and some of the end users didn’t have it, so I had to go and manually set the reference to 2.5.

    Maybe part of the whole package could be some type of Admin form, where you can choose the path of the DB, choose the reference you wnat (i.e. have access to), etc.

  3. Scott: Yeah, that’s why that one is still on my to-do list. I guess I could late-bind it, but I almost never use late-binding with ADO, so it would lose some utility for me.

    I have never been able to iterate through the list of available references. I would think that would be in the registry somewhere, but I’ve never been able to find it.

  4. Maybe it’s a simple as looping through the reference folder? Problem is (and this may be nothing, but you never know), I don’t know if the reference folder would ever be anywhere other than Common FilesSystemADO.

    Sub IterateADOReferences()

        Const sDIR As String = “C:Program FilesCommon FilesSystemADO”
        Const sADO_REF As String = “MSADO”
        Const sTLB As String = “TLB”
        Dim sFile As String
       
        sFile = Dir(sDIR)
       
        Do While Len(sFile)  0
            If (UCase(Left(sFile, 5)) = sADO_REF) And _
                (UCase(Right(sFile, 3)) = sTLB) Then
                Debug.Print sFile
            End If
            sFile = Dir
        Loop
       
    End Sub

  5. Regarding the ADO references I would suggest to attempt to add the reference from a list of known GUIDs, using the VBIDE.References.AddFromGuid method found in “Microsoft Visual Basic for Applications Extensibility” library..

    Using this approach you can attempt to add a reference and trap the error that occurs if the library isn’t available on the target computer. You could also warn the user if none of the references could be set, this way the user would be aware that the generated code wouldn’t work “out of the box”.

    Here’s info on the ADO libraries I found on my computer:
    Filename: msado20.tlb
    Name: ADODB
    GUID: {00000200-0000-0010-8000-00AA006D2EA4}
    Desc.: Microsoft ActiveX Data Objects 2.0 Library

    Filename: msado21.tlb
    Name: ADODB
    GUID: {00000201-0000-0010-8000-00AA006D2EA4}
    Desc.: Microsoft ActiveX Data Objects 2.1 Library

    Filename: msado25.tlb
    Name: ADODB
    GUID: {00000205-0000-0010-8000-00AA006D2EA4}
    Desc.: Microsoft ActiveX Data Objects 2.5 Library

    Filename: msado26.tlb
    Name: ADODB
    GUID: {00000206-0000-0010-8000-00AA006D2EA4}
    Desc.: Microsoft ActiveX Data Objects 2.6 Library

    Filename: msado27.tlb
    Name: ADODB
    GUID: {EF53050B-882E-4776-B643-EDA472E8E3F2}
    Desc.: Microsoft ActiveX Data Objects 2.7 Library

    Filename: msado15.dll
    Name: ADODB
    GUID: {2A75196C-D9EB-4129-B803-931327F72D5C}
    Desc.: Microsoft ActiveX Data Objects 2.8 Library

  6. Alternative for filling te combobox

      Dim sq()
      ReDim sq(Workbooks.Count – 1, 2)
       
      For j = 1 To Workbooks.Count
        sq(j – 1, 0) = “VBAProject”
        sq(j – 1, 1) = Workbooks(j).Name
        sq(j – 1, 2) = j
      Next
      Me.cbxProject.List = sq
  7. hello,
    A question: personally, I prefer using a Dictionary over a Collection (because of Exists method, Key property, removeall method).
    Is there a reason why you go for a Collection?

    regards,

  8. Jurgen: Habit. Collections are native and dictionaries make me set a reference. I really need to force myself to use a dictionary object, but I haven’t yet.

    Thanks for the list Peder, that seems like a good approach.

    Hans: I considered looping through the workbooks, but didn’t use it for some reason – can’t remember. Maybe it had something to do with add-ins.

Leave a Reply

Your email address will not be published. Required fields are marked *