It still amazes me how Microsoft can develop these complexe applications but seems to overlook some simple functions that they should included within them to aid developers… But then, as demonstrated with the release of Office 2007 and 2010, Microsoft is not interested in the developer, they are only interested in the end-user’s opinion. Not productivity (that went down, about 30-40% drop in efficiency, the tubes with their change of format)! So all that matters is looks, the feel – very superficial (rant over)!!!
This will be the first in a series of procedure that I will be posting in the coming months in which I hope to demonstrate how you can use the ‘Microsoft Visual Basic for Application Extensibility’ library in conjuntion with the power of VBA to learn more, control more, manipulate more the VBE.
In this first post, I simply wanted to create a simple procedure that would give me a breakdown of my Access project. I wanted to return a listing of procedure per module with a line count. As you can see, the ‘Microsoft Visual Basic for Application Extensibility’ enable us to perform this task with ease with little code. Heck, half of the code below is to write to the generated text file!
'--------------------------------------------------------------------------------------- ' Procedure : GetVBEDeatils ' Author : CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Goes throught the VBE and creates a text file which give a brief listing ' of the procedures within each module and a line count for each ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). It may not be sold/resold or reposted on other sites (links ' back to this site are allowed). ' Requirements: reference to the Microsoft Visual Basic for Application Extensibility ' library. ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 2011-June-04 Initial Release '--------------------------------------------------------------------------------------- Function GetVBEDeatils() Dim vbProj As VBProject Dim vbComp As VBComponent Dim vbMod As CodeModule Dim sProcName As String Dim pk As vbext_ProcKind Dim FileNumber As Integer Dim strFile As String Const vbNormalFocus = 1 'Where do youwant the text file created strFile = "C:\VBEDetails.txt" If Len(Dir(strFile)) > 0 Then Kill strFile FileNumber = FreeFile 'Get unused file number. Open strFile For Append As #FileNumber 'Create file name. For Each vbProj In Application.VBE.VBProjects 'Loop through each project Print #FileNumber, vbProj.Name For Each vbComp In vbProj.VBComponents 'Loop through each module Set vbMod = vbComp.CodeModule Print #FileNumber, " " & vbComp.Name & " :: " & vbMod.CountOfLines & " total lines" Print #FileNumber, " " & String(80, "*") iCounter = 1 Do While iCounter < vbMod.CountOfLines 'Loop through each procedure sProcName = vbMod.ProcOfLine(iCounter, pk) If sProcName <> "" Then Print #FileNumber, " " & sProcName & " :: " & vbMod.ProcCountLines(sProcName, pk) & " lines" iCounter = iCounter + vbMod.ProcCountLines(sProcName, pk) Else iCounter = iCounter + 1 End If Loop Print #FileNumber, "" Next vbComp Next vbProj Close #FileNumber 'Close file. Set vbMod = Nothing 'Open the generated text file Shell "cmd /c """ & strFile & """", vbNormalFocus End Function |
