Cyberdude
05-06-2005, 03:29 PM
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.
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.