The Quest For How to Insert a Line in a Macro

[Cyberdude]

I always put the name of a macro in the ?End Sub? line of the macro. If the macro name is ?Cookieputz?, then I write the last line as End Sub ?Cookieputz?.

I?ve been trying to figure out how to write a macro that will modify the end line of each new macro I write. The basic problem I keep running into is that, in order to insert the new line (then delete the old one), I must know the line number where I want to insert the new line. The following macro will tell me the start and end line numbers of a macro.


Sub GetSubLines()
Dim StartLine As Long, StartCol As Long, EndLine As Long, EndCol As Long, Msg$
With Application.VBE.ActiveCodePane.CodeModule
.CodePane.GetSelection StartLine, StartCol, EndLine, EndCol
Msg = "In '" & .Name & "'" & vbCr & vbCr & _
"Start Line: " & StartLine & vbCr & _
"Start Col: " & StartCol & vbCr & _
"End Line: " & EndLine & vbCr & _
"End Col: " & EndCol
MsgBox Msg
End With
End Sub

The problem is that I must select the macro that I want to get the information on. I do that by running my cursor over all the lines in the macro, then press PF5. This macro will then tell me the start and end line numbers for the subject macro. This is NOT a handy way to get the line number. So my question is, how can I select the macro that I want the info on? I gave up on using the preceding macro and decided to use the guts of the following two macros to create the macro I wanted. To begin, the following macro will tell me what the start line of a macro is, and it allows me to specify the macro name, ?Cookieputz?:


Sub GetSubStartLine()
Dim StartLine&
StartLine = Application.VBE.ActiveCodePane.CodeModule.ProcStartLine("Cookieputz", vbext_pk_Proc)
MsgBox StartLine
End Sub


But it doesn?t tell me the line number of the last line. The next macro will tell me how many lines are in the macro ?Cookieputz?:


Sub GetNumberOfLines()
Dim NoOfLines&
NoOfLines = Application.VBE.ActiveCodePane.CodeModule.ProcCountLines("Cookieputz", vbext_pk_Proc)
MsgBox NoOfLines
End Sub

Well, it adds 1 to the count for some reason, maybe to account for the blank line between macros. OK, so I can take the starting line number and add count-of-lines minus 1, and I should have the line number of the ?End Sub? line. I put it all together and the result is:


Sub ReplaceEndSubLine()
Dim StartLine&, StartCol&, EndLine&,EndCol&,NoOfLines&
StartLine = Application.VBE.ActiveCodePane.CodeModule.ProcStartLine("Cookieputz", vbext_pk_Proc)
NoOfLines = Application.VBE.ActiveCodePane.CodeModule.ProcCountLines("Cookieputz", vbext_pk_Proc)
?For some reason I didn?t have to add 1 to the line count
EndLine = StartLine + NoOfLines
Application.VBE.ActiveCodePane.CodeModule.InsertLines EndLine, "End Sub 'Cookieputz'"
?The inserted line follows the specified line number, so now
?I must remove the original line.
Application.VBE.ActiveCodePane.CodeModule.DeleteLines EndLine - 1
End Sub

So there you have it ? a macro that modifies another macro. It works. It seems like a VERY clutzy way to write it. If anyone has some suggestions for a better way to get there, I?ll listen.

[Richie(UK)]

Hi Dude,

Two suggestions spring to mind.

1. Add some arguments to the Sub so that you can re-use it without having to select the relevant code.

2. Use .ReplaceLine rather than inserting then deleting.

Only briefly tested, but something like this perhaps:


Sub Test()
Dim wb As Workbook
Set wb = ThisWorkbook
ReplaceEndSub wb, "Module1", "SwapMe"
End Sub

Sub ReplaceEndSub(wbk As Workbook, strModName As String, strSubName As String)
Dim VBCodeMod As CodeModule
' Need to set a reference to the Extensibility library
Dim x As Long, y As Long
Const strOld As String = "End Sub"
Set VBCodeMod = wbk.VBProject.VBComponents(strModName).CodeModule
With VBCodeMod
For x = 1 To .CountOfLines
If InStr(1, .Lines(x, 1), "Sub " & strSubName, vbBinaryCompare) > 0 Then
Do
y = y + 1
If y = 10000 Then
MsgBox "OK, can't find 'End Sub' so I'm giving up!"
Exit Sub
End If
Loop Until .Lines(x + y, 1) = strOld
On Error Resume Next
.ReplaceLine x + y, strOld & " '" & strSubName & "'"
On Error GoTo 0
End If
Next x
End With
End Sub

Sub SwapMe()
MsgBox "Just a test sub"
End Sub

HTH

Edit : PS You have to be careful with .ProcCountLines as it may include lines before the procedure itself and, if it is the last procedure, lines after the procedure. Help says :
The ProcCountLines property returns the count of all blank or comment lines preceding the procedure declaration and, if the procedure is the last procedure in a code module, any blank lines following the procedure.

[Cyberdude]

Thanx for the reply, Richie. While I'm finally starting to succeed to doing something I've wanted to do for years, I can see how not too many others share my enthusiasm.
Regarding the line numbers, what a chaos that is! I've actually created a situation in which I have two "Sub" lines that have the same line number! That's hard to work with.
My current effort is to replace the "Sub" line of a procedure to that I can add a comment (date, etc.) on that line. It's very confusing.
For example, the first Sub following the Option Explicit seems to always have the line number 2, no matter how far down it is. Adding blank lines above any of the other "Sub" lines does NOT change the Sub line's line number! However, adding blank lines above any Sub line preceding it, WILL change the Sub line's line number.
I think I could get there from here if I could just understand the GetSelection Method. The Help says something like "Returns the selection in a code pane." My problem is how to select a specific procedure without giving its line number or its name. The GetSelection method doesn't provide a way to specify either. So it returns values for the procedure that's executing. I want to fetch the contents of the "Sub" line in their entirety, so I used the following sequence:


With Application.VBE.ActiveCodePane.CodeModule
.CodePane.GetSelection StartLine, StartCol, EndLine, EndCol
SubLine = .Lines(StartLine, 1) 'Extract the "Sub" line
End With

but that returns the Sub line of the procedure that is executing the GetSelection method. I can't figure out what constitutes "selection of a macro". What must I do?? :banghead:

[Bob Phillips]

Here is my attempt.

I cannot say it is extensivley tested, I only knocked it up tonight, but it does process all procedures within a module, and caters for Properties, Subs and Functions.

Call it with the module name as the argument.

Let me know how you get on with it.


Sub ProcedureComments(module As String)
Dim VBCodeMod As Object
Dim StartLine As Long
Dim cLines As Long
Dim ProcType As Long '0 Property, 1 Sub, 2 Function
Dim ProcName As String
Dim ProcEnd As String
Dim ProcKind As Long
Dim aryTypes
aryTypes = Array("Property", "Sub", "Function")
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(module).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
ReDim aryProcs(0)
Do Until StartLine >= .CountOfLines
'get the procedure name and count of line
'.ProcOfLine modifies ProcKind to type
ProcKind = 0
ProcName = .ProcOfLine(StartLine, ProcKind)
cLines = .ProcCountLines(ProcName, ProcKind)
'loop back until we hit last 'real'line, ignore blanks
Do Until Left(.Lines(StartLine + cLines, 1), 3) = "End"
cLines = cLines - 1
Loop
'determine procedure type
If InStr(.Lines(StartLine + cLines, 1), "Property") > 0 Then
ProcType = 0
ElseIf InStr(.Lines(StartLine + cLines, 1), "Sub") > 0 Then
ProcType = 1
Else
ProcType = 2
End If
'now delete original and insert new line
.DeleteLines StartLine + cLines
.InsertLines StartLine + cLines, "End " & aryTypes(ProcType) & " '" & ProcName
'onto the next procedure
StartLine = StartLine + _
.ProcCountLines(ProcName, ProcKind)
Loop
End With
End Sub

[Cyberdude]

Thanx, XLD. Gotta take a shower (a week goes by so fast), then I'll give it a run. I appreciate your taking the time to look at this. I am just learning the language of manipulating macros in the VBE, but I'll get there with help from guys like you and Richie. :beerchug: