Excel VBA - call same macro from both Ribbon and AutoOpen

excel, excel-2013, ribbon, vba

Solution

A simple code as this will help?

Option Explicit

Sub AutoOpen()
    Dim ctl As IRibbonControl

    myMacro ctl
End Sub

Sub myMacro(control As IRibbonControl)
    MsgBox "Hello World"
End Sub

Problem

Having just upgraded to Excel 2013, I've moved my macros from a legacy custom toolbar to a custom ribbon menu. All works well, except for one thing. I used to have a macro that ran on AutoOpen, but could also be called manually via a button on the toolbar. I call my macro from the ribbon using `Sub myMacro(control As IRibbonControl)` which works. But if I `Call myMacro(control As IRibbonControl)` in AutoOpen I get an "expected list separator" error. Conversely if I just `Call myMacro()` in AutoOpen I obviously get a "argument not optional" error. Bit of a Catch 22! I know that I could just move my code to a third sub-routine, called by two separate macros in the ribbon and in AutoOpen, but before I admit defeat and do this I wonder if there is a way around this. I have searched the web for a solution to this, but couldn't find anything that answered my particular query. Thanks Rob

Original source