ingnore nonexisting subrouitine

[Jack Sons]

Hi all,

In the code below ABCD can be executed if it is a subroutine - sub ABCD() -
in a module elsewhere in the same project. If ABCD is a not an existing sub
the execution will halt, an error message wil appear on screen and a sound
wil be produced. Fore some reason I want to hear that sound but I don't want
the the program to halt. If possible it would be nice if also the message
box does not appear, but I need to hear the error sound. To my regret I did
not succeed with the code below.

Anybody who knows what code is needed to let the program continue when
execution comes to the line with the undefined ABCD but do let the error
message sound hear?

Jack Sons
The Netherlands


Sub ingnoreABCD()
............ ' here the program already did some things
Application.DisplayAlerts = False
ABCD
Application.DisplayAlerts = True
............... ' here I want to do what the program should do
End Sub

 

[Ron de Bruin]

Hi Jack

See this code on Chip's site

You can use the VBA Extensibility tools to determine whether a module exists, or a procedure exists in a module.

Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next
ModuleExists = Len( _
ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
End Function

Function ProcedureExists(ProcedureName As String, _
ModuleName As String) As Boolean
On Error Resume Next
If ModuleExists(ModuleName) = True Then
ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
.CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) <> 0
End If
End Function

http://www.cpearson.com/excel/vbe.htm

 

[Ron Rosenfeld]

Hi all,

In the code below ABCD can be executed if it is a subroutine - sub ABCD() -
in a module elsewhere in the same project. If ABCD is a not an existing sub
the execution will halt, an error message wil appear on screen and a sound
wil be produced. Fore some reason I want to hear that sound but I don't want
the the program to halt. If possible it would be nice if also the message
box does not appear, but I need to hear the error sound. To my regret I did
not succeed with the code below.

Anybody who knows what code is needed to let the program continue when
execution comes to the line with the undefined ABCD but do let the error
message sound hear?

Jack Sons
The Netherlands


Sub ingnoreABCD()
........... ' here the program already did some things
Application.DisplayAlerts = False
ABCD
Application.DisplayAlerts = True
.............. ' here I want to do what the program should do
End Sub

Take a look at the On Error statement and also the Beep statement.


--ron

 

[Norman Jones]

Hi Ron.

Take a look at the On Error statement and also the Beep statement.

I do not think that a call to a non-existant procedure produces a trappable
error.

I think that the approach similar to that advocated by Ron de Bruin would be
necessary.

 

[Jack Sons]

Ron,

I already tried On Error resume next like low, but it didn't help. Other
ideas?

Jack.


Sub ingnoreABCD()
............ ' here the program already did some things
Application.DisplayAlerts = False
On Error Resume Next
ABCD
Application.DisplayAlerts = True
............... ' here I want to do what the program should do
End Sub

 

[Ron Rosenfeld]

Hi Ron.


I do not think that a call to a non-existant procedure produces a trappable
error.

I think that the approach similar to that advocated by Ron de Bruin would be
necessary.

I didn't realize that. Thank you for pointing it out.
--ron

 

[Ron Rosenfeld]

Ron,

I already tried On Error resume next like low, but it didn't help. Other
ideas?

Jack.

See Ron de Bruin's suggestion
--ron

 

[Dave Peterson]

I think Ron forgot to add to change the Call to Application.Run <bg>.

This "worked" for me if I spelled "doesitexist" as "NoItDoesNot".

Option Explicit
Sub aaaa()
On Error Resume Next
Application.Run ThisWorkbook.Name & "!doesitexist"
If Err.Number <> 0 Then
Beep
Err.Clear
End If
On Error GoTo 0
End Sub
Sub doesitexist()
MsgBox "yes"
End Sub

 

[Norman Jones]

Hi Dave,

A good idea!

I had not thought of using the procedure's name string (as required by the
Run method) and thus avoiding the compile error.

Thank you!

 

[Jack Sons]

Dave,

Thanks for the code.

Norman,

Thanks for the explanation.

Jack.

Hi Dave,

A good idea!

I had not thought of using the procedure's name string (as required by the
Run method) and thus avoiding the compile error.

Thank you!