Wrote some code that will add line numbers to procedures that contain
certain strings.
This works with the free MZ-Tools add-in and all working fine and saving a
lot of time.
There is only one small problem and that is I haven't managed to always keep
the IDE window
invisible. This is better as if this window becomes visible then the code
will run a lot slower
as it has to update all the screens.
This is the full code:
Sub AddLineNumbersToErlProcs()
Dim i As Long
Dim n As Long
Dim c As Long
Dim x As Long
Dim WB As Workbook
Dim VBProj As VBProject
Dim VBC As VBComponent
Dim VBProjectForLineNumbers As VBProject
Dim strFile As String
Dim strFileToNumber As String
Dim msgResult As VbMsgBoxResult
Dim cmb As CommandBarControl
Dim cmbLineNumbers As CommandBarControl
Dim strPreviousProc As String
Dim strCurrentProc As String
Dim bHasLineNumber As Boolean
Dim strPreviousParentName As String
For Each VBProj In Application.VBE.VBProjects
On Error Resume Next
Select Case MsgBox("Add line numbers (if Procedure has Erl) to this
project?", _
vbYesNoCancel + vbDefaultButton2, _
VBProj.Filename)
Case vbYes
Set VBProjectForLineNumbers = VBProj
strFileToNumber = VBProj.Filename
Exit For
Case vbNo
Case vbCancel
Exit Sub
End Select
Next
If VBProjectForLineNumbers Is Nothing Then
Exit Sub
End If
Application.VBE.MainWindow.Visible = True
'find the MZ-Tools add line numbers button
'-----------------------------------------
For Each cmb In Application.VBE.CommandBars("MZ-Tools 3.0").Controls
If cmb.Caption = "Add Line Numbers" Then
Set cmbLineNumbers = cmb
Exit For
End If
Next
If cmbLineNumbers Is Nothing Then
MsgBox "Could not find the MZ-Tools Add line numbers button!", , _
"adding line numbers"
Exit Sub
End If
Application.VBE.MainWindow.Visible = False
Application.Cursor = xlWait
For Each VBC In VBProjectForLineNumbers.VBComponents
With VBC.CodeModule
For i = .CountOfDeclarationLines + 1 To .CountOfLines
strCurrentProc = .ProcOfLine(i, vbext_pk_Proc)
If strCurrentProc <> "AddLineNumbersToErlProcs" Then
If InStr(1, .Lines(i, 1), " Erl ", vbBinaryCompare) > 0 Or _
InStr(1, .Lines(i, 1), "Erl, ", vbBinaryCompare) > 0 Or _
InStr(1, .Lines(i, 1), "CStr(Erl)", vbBinaryCompare) > 0 And _
(strCurrentProc <> strPreviousProc Or Len(strPreviousProc) = 0)
Then
bHasLineNumber = False
If Asc(Left$(.Lines(i, 1), 1)) > 48 And _
Asc(Left$(.Lines(i, 1), 1)) < 58 Then
bHasLineNumber = True
End If
x = 1
If bHasLineNumber = False Then
Do Until Right$(.Lines(i - x, 1), 2) <> " _"
If Asc(Left$(.Lines(i - x, 1), 1)) > 48 And _
Asc(Left$(.Lines(i - x, 1), 1)) < 58 Then
bHasLineNumber = True
Exit Do
End If
x = x + 1
Loop
End If
If bHasLineNumber = False Then
'this avoids making the IDE window visible, which otherwise
'can happen occasionally, slowing the procedure down a lot
'it still can become visible hence the
'Application.VBE.MainWindow.Visible = False
'----------------------------------------------------------
If .Parent.Name <> strPreviousParentName Then
.Parent.Activate
If Application.VBE.MainWindow.Visible = True Then
Application.VBE.MainWindow.Visible = False
End If
End If
.CodePane.SetSelection i, 1, i, 1
cmbLineNumbers.Execute
c = c + 1
Application.StatusBar = " " & c & " procedures done. " &
_
"Last done: " & strCurrentProc
strPreviousParentName = .Parent.Name
End If
strPreviousProc = strCurrentProc
End If
End If
Next i
End With
Next VBC
MsgBox "Added line numbers to " & c & " procedures", , _
"adding line numbers"
With Application
.Cursor = xlDefault
.StatusBar = False
End With
End Sub
Any suggestions how to do handle this better?
RBS
|
Similar Threads
