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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
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
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.
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.
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.
Great work with this, Dick!
Hope it gets approved on Sourceforge.
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.
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
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
Alternative for filling te combobox
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
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,
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.