4

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

Community
  • 1
  • 1
Optical Race
  • 63
  • 1
  • 7
  • Even if you find an answer consider using your "3rd subroutine" approach. It's easier for debugging, and perhaps less confusing for future maintenance. – Doug Glancy Jan 05 '14 at 16:13
  • Good point Doug, the tip below works but I will consider your suggestion too. As you say, debugging would be easier knowing who is calling what from where! – Optical Race Jan 05 '14 at 18:22

1 Answers1

4

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250