Hello,
As previously stated, I must first preface this with I am not a programmer, but I try.
I have been working on this for awhile. I posted a similar question on a different site but have not received an answer.
I’m looking for an Excel function to delete a subroutine(s). I found a sub on-line that does this (I wish I could find it again and give the author credit). However, since it is written to delete only one specific procedure and I have more than one I may wish to delete, I would have to copy/edit/rename this sub numerous times. I believe a function would make this process easier. Therefore, my question – Is it possible to convert the Subroutine into a Function?
Here is the existing Subroutine I would like converted to a function:
_______________________________________________________
NOW, here is a bit more info that may help –
What I had in mind is I’d like some function to delete a subroutine based on the parameter entered after the function. Let’s say the function is called DeleteEm() and the subroutine I want to delete is called TryMe. Then the syntax would be something like: DeleteEm(“TryMe”).
I also have a function I found on-line that checks if a procedure exists:
Here is my TEST routine to do something if the procedure TryMe exists (i.e., delete TryMe if it exists):
Any suggestions would be appreciated.
Thanks for viewing,
Steve K.
As previously stated, I must first preface this with I am not a programmer, but I try.
I have been working on this for awhile. I posted a similar question on a different site but have not received an answer.
I’m looking for an Excel function to delete a subroutine(s). I found a sub on-line that does this (I wish I could find it again and give the author credit). However, since it is written to delete only one specific procedure and I have more than one I may wish to delete, I would have to copy/edit/rename this sub numerous times. I believe a function would make this process easier. Therefore, my question – Is it possible to convert the Subroutine into a Function?
Here is the existing Subroutine I would like converted to a function:
VBA Code:
Sub DeleteProcedureFromModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim NumLines As Long
Dim ProcName As String
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule
ProcName = "DeleteThisProc”
With CodeMod
StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
.DeleteLines StartLine:=StartLine, Count:=NumLines
End With
End Sub_______________________________________________________
NOW, here is a bit more info that may help –
What I had in mind is I’d like some function to delete a subroutine based on the parameter entered after the function. Let’s say the function is called DeleteEm() and the subroutine I want to delete is called TryMe. Then the syntax would be something like: DeleteEm(“TryMe”).
I also have a function I found on-line that checks if a procedure exists:
Code:
Function ProcExists(ProcName) As Boolean
Dim MyModule As Object
Dim MyModuleName As String
Dim MyLine As Long
Dim FoundIT As Boolean
On Error GoTo WTF
If LCase(Left(ProcName, 2)) = "tb" Then
'This next line is the doorstop:
For Each MMN In AddIns("JGM ToolBox.xla").VBProject.VBComponents
MyModuleName = MMN.Name
Set MyModule = AddIns("JGM ToolBox.xla").VBProject.VBComponents(MyModuleName).CodeModule
MyLine = MyModule.ProcStartLine(ProcName, vbext_pk_Proc)
If MyLine <> 0 Then FoundIT = True Else FoundIT = False
If FoundIT Then Exit For
Next
Else
For Each MMN In ActiveWorkbook.VBProject.VBComponents
MyModuleName = MMN.Name
Set MyModule = ActiveWorkbook.VBProject.VBComponents(MyModuleName).CodeModule
MyLine = MyModule.ProcStartLine(ProcName, vbext_pk_Proc)
If MyLine <> 0 Then FoundIT = True Else FoundIT = False
If FoundIT Then Exit For
Next
End If
ProcExists = FoundIT
Exit FunctionHere is my TEST routine to do something if the procedure TryMe exists (i.e., delete TryMe if it exists):
Code:
Sub Test1()
If ProcExists("TryMe") = True Then
MsgBox "Yup – it’s there"
DeleteEm(“TryMe”)
Else
MsgBox "NOPE – not there"
End If
End SubAny suggestions would be appreciated.
Thanks for viewing,
Steve K.
Last edited:
