Easy way to check if a macro exists, or ignore the call if it doesn't?

[Jorgen]

Hi,
I am looking for an easy way to check if a macro exists so it doesn't create an error if it isn't there or just ignores the call. The "main" macro is in a workbook that works with a lot of different workbooks, some that contain the "MyMacro" and some don't. And if it exists it should be run...

I tried

On Error Resume Next
Call ActiveWorkbook.Sheets("StartSheet").MyMacro

And if it doesn't exist, I end up with:
"Run-time error '438':

Object doesn't support this property or method"

Any ideas that doesn't require scanning through all macros of the workbook? It needs to be run without having programmatic access to the VBA project (like in http://www.cpearson.com/excel/vbe.aspx)

Thanks!

Jörgen

 

[RoryA]

It would have been a lot easier if the macros were in normal modules where they belong and not in worksheet modules. As long as they are not declared as private, you could use:

   On Error Resume Next
   CallByName ActiveWorkbook.Sheets("StartSheet"), "MyMacro", VbMethod

 

[Jorgen]

Hi Rory,
Thank you for the suggestion, but I still get the same error 438 if the macro "MyMacro" doesn't exist in the StartSheet-VBAcode...

Thanks,
Jörgen

 

[RoryA]

Then I'd have to guess you have 'Break On All Errors' set on the General tab under Tools-Options in the VBEditor.

 

[Richard Schollar]

That CallByName function's sweet

 

[RoryA]

It's like a bus - you don't use it for ages, then suddenly all these questions come along at once!