Excel.Application.Run (macroName) calls macros named in sheet. How to check automatically that macroName does actually exist as a sub?

I have a list of macros in a hidden Excel sheet, which supplies the macro to be called when a specified customised ribbon BUTTON is clicked
Using Excel.Application.Run (macroName) , how do I deal with the event that macroName is listed in my lookup sheet, but does not exist as a sub?

The call for a macro includes this:
If macroName <> "" Then
            Application.ScreenUpdating = False
                rib_context.Visible = xlSheetVisible
                    With rib_context
                        Excel.Application.Run (macroName)
                    End With
                rib_context.Visible = xlSheetHidden
            Application.ScreenUpdating = True
End If

BUT if I call a macroName for a non-existent Macro, no error is reported by vba. Is there a vba check that I can apply to guard against calling a non-existent macro?

This is the first time I've used for my Customised Ribbon and I depend on it working.
If you have criticisms of my approach - I'd surely welcome them now!

Thanks
Kelvin4
Kelvin4Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You could check for the existence of the name in the sheet, but it seems an odd way of doing this. Why would you need a list of the macro names in a sheet? Something like this

If Application.WorksheetFunction.CountIf("range to check", macroname) > 0 Then
''continue with code

Else: MsgBox macroname & " is not there"


End If

Open in new window


A sight of the code in full would help

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne Taylor (webtubbs)Commented:
Do you already have error handling in the rest of the code? There will definitely be an error reported for a non existent macro. It will be error number 1004 and the description will be "Cannot run the macro 'unknown_macro_name'. The macro may not be available in this workbook or all macros may be disabled."
Kelvin4Author Commented:
Thanks, both.
The core issue is that most of my ribbon buttons call the same macro, so I can tackle the fiddly issue of setting the MyTag values that contexturally control the enablement of the ribbon buttons. I find this much easier to do centrally, once, in an excel sheet, rather than in 'MyTag =  ' statements in macros distributed about the piece. I admit I could do all this via  the Select Case method using Control.ID as Case. Then I would certainly know if I called an non-existent macro (my original question). But the excel Sheet approach is visually very helpful.

This is a problem I have solved for myself , but your participation was appreciated - it helped me think about my options and solution.
I describe my thinking here. Because I owe you it, if you want to read it, and because someone else might read it sometime.
Kelvin4
If you wish to make further comments/warnings - I will be attending!

Explanation:
First, I can confirm that Excel.Application.Run (macroName) does not return an error when no sub with name = macroName exits.
 - this is different from (say) row = Application.WorksheetFunction.Match(str, MyRange, 0), where a match failure yields the error Wayne describes. Perhaps this feature of Excel.Application.Run is understandable, since Excel.Application.Run can call procedures in other WBs?

Since THIS QUESTION, i've created public variable 'strMacroCalled' which appears in the first line of sub macroCalled:
strMacroCalled = Name of called macro.
The following snippet shows strMacroCalled being used in a sub is named:  "MyMacroName":

Here is the code that runs MyMacroName via Excel.Application.Run (macroName)

macroName = "MyMacroName"
If macroName <> "" Then
            Application.ScreenUpdating = False
                rib_context.Visible = xlSheetVisible
                    With rib_context
                        Excel.Application.Run (macroName)
                    End With
                rib_context.Visible = xlSheetHidden
            Application.ScreenUpdating = True
End If  
'check strMacroCalled <> macroNam

if strMacroCalled <> macroName then
      MsgBox "Excel.Application.Run  failed to find the expected macro: " & macroName
      'no need for On error statement, as no error is detected.. so....
      Exit sub
end if

'Public strMacroCalled as string 'declared outside subs

'specimen sub to be called by Application.Run and which sets the value of  strMacroCalled:
sub MyMacroName()    
    strMacroCalled      = "MyMacroName"
    do stuff..
end sub
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kelvin4Author Commented:
As I say in my comment:
Thanks!
Kelvin4
Kelvin4Author Commented:
Thanks!
Roy CoxGroup Finance ManagerCommented:
Pleased to help
Wayne Taylor (webtubbs)Commented:
First, I can confirm that Excel.Application.Run (macroName) does not return an error when no sub with name = macroName exits.
  - this is different from (say) row = Application.WorksheetFunction.Match(str, MyRange, 0), where a match failure yields the error Wayne describes.

Glad you got it working, but the above statement is not correct. You will definitely get an error when the macro is not found, and it is definitely different to the error returned by the worksheet function.
Kelvin4Author Commented:
Wayne - thanks for taking the time to write.
Its late here.
I'll get my evidence together tomorrow am, and let you see it.
Thanks
Kelvin
Wayne Taylor (webtubbs)Commented:
No worries, but the evidence is pretty clear. Run the below macro and you'll get the error...

Sub Test()
Excel.Application.Run "dodgymacroname"
End Sub

Open in new window

Kelvin4Author Commented:
Wayne, I found the error was on my part
At the top of the procedure, I Matched a value to a range:

    On Error Resume Next
        row = Application.WorksheetFunction.Match(str, rg_rib_Context_colA, 0)

I provided for Error reporting, but did not cancel that On Error statement.
Consequently, when later the Application.run statement occurred, the report of a failure to find a macro was suppressed.

The addition of:
      Err.clear
     On Error Goto 0
led my Application.Run to perform as you wrote to me from the start.

Thanks for the follow-up
Kelvin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.