Is there a way to determine if a sub exists? I have code in an add-in that
calls a sub in the active workbook per the example below. How do I determine
if that sub exists in the activeworkbook or not, to avoid an error?

WBName = ActiveWorkbook.Name
Application.Run "'" & WBName & "'!RemoveSN"

Just add an error handler:

Sub test()

On Error GoTo ERROROUT

Application.Run "xxx"

Exit Sub

ERROROUT:
MsgBox Err.Description 'or do whatever is suitable

End Sub


RBS


"Paige" <(E-Mail Removed)> wrote in message
news:794F27DB-1223-4A0B-8491-(E-Mail Removed)...
> Is there a way to determine if a sub exists? I have code in an add-in
> that
> calls a sub in the active workbook per the example below. How do I
> determine
> if that sub exists in the activeworkbook or not, to avoid an error?
>
> WBName = ActiveWorkbook.Name
> Application.Run "'" & WBName & "'!RemoveSN"

Hi,
Try the following:

If ProcExists("RemoveSN") Then Application.Run "'" & WBName & "'!RemoveSN"

Function ProcExists(ProcName As String) As Boolean
Dim i
On Error Resume Next
MsgBox
ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule.ProcBodyLine(ProcName,
vbext_pk_Proc)

If Err.Number <> 0 Then
ProcExists = False
Else
ProcExists = True
End If
End Function
--
A. Ch. Eirinberg


"Paige" wrote:

> Is there a way to determine if a sub exists? I have code in an add-in that
> calls a sub in the active workbook per the example below. How do I determine
> if that sub exists in the activeworkbook or not, to avoid an error?
>
> WBName = ActiveWorkbook.Name
> Application.Run "'" & WBName & "'!RemoveSN"

Actually there is no use for a MsgBox instead use the i variable as follows:

If ProcExists("RemoveSN") Then Application.Run "'" & WBName & "'!RemoveSN"

Function ProcExists(ProcName As String) As Boolean
Dim i
On Error Resume Next
i =
ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule.ProcBodyLine(ProcName,
vbext_pk_Proc)

If Err.Number <> 0 Then
ProcExists = False
Else
ProcExists = True
End If
End Function

--
A. Ch. Eirinberg


"Paige" wrote:

> Is there a way to determine if a sub exists? I have code in an add-in that
> calls a sub in the active workbook per the example below. How do I determine
> if that sub exists in the activeworkbook or not, to avoid an error?
>
> WBName = ActiveWorkbook.Name
> Application.Run "'" & WBName & "'!RemoveSN"

Thanks, RB and Howard, for both great ideas!

"Howard31" wrote:

> Actually there is no use for a MsgBox instead use the i variable as follows:
>
> If ProcExists("RemoveSN") Then Application.Run "'" & WBName & "'!RemoveSN"
>
> Function ProcExists(ProcName As String) As Boolean
> Dim i
> On Error Resume Next
> i =
> ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule.ProcBodyLine(ProcName,
> vbext_pk_Proc)
>
> If Err.Number <> 0 Then
> ProcExists = False
> Else
> ProcExists = True
> End If
> End Function
>
> --
> A. Ch. Eirinberg
>
>
> "Paige" wrote:
>
> > Is there a way to determine if a sub exists? I have code in an add-in that
> > calls a sub in the active workbook per the example below. How do I determine
> > if that sub exists in the activeworkbook or not, to avoid an error?
> >
> > WBName = ActiveWorkbook.Name
> > Application.Run "'" & WBName & "'!RemoveSN"