What do you think of...

  • ...my signature?

    Seems like this is the standard syntax people should be using for all macros now.

    Optional - allows ribbon callbacks or direct referencing
    Variant - allows 2003 backward compat

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: What do you think of...

    That's what makes it a good signature...

    It sorta reads like a joke, but actually, I'm serious. That is (literally) the format that I'm going to use for all macros.

    It does actually make sense.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: What do you think of...

    Interesting, would it read better as:

    1. Sub All_Macros(Optional variable As Variant)

    Check out our new reputation system. Click on the Like button under the post!

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend[/SIZE]

  • Re: What do you think of...

    Quote from Dave Hawley


    Alright, a little background...

    If you want to call a macro from the ribbon, the syntax of the macro must be:

    1. Sub MyMacro(control As IRibbonControl)

    Above is the typical code example provided online, in books and so forth...

    I was converting a bunch of my old macros to use with the ribbon and I was just adding the above control syntax. I ran into a problem when some of my macros were making calls to their subroutine buddies that now had this control variable requirement.

    There are 3 ways you can solve that problem:
    1) make the IRibbonControl optional, or...
    2) dimension a DummyRibbon as an IRibbonControl and pass it
    3) create a seperate sub with the IRibbonControl variable that makes the call to MyMacro without the control requirement

    The first option above obviously makes more sense because it requires no additional coding in subs that call MyMacro and it also works fine if you happen to assign macros to shape click events (as I often do).

    So, if I want to allow my macros to be called directly or from the ribbon it seems to make sense to always use the following "Optional" syntax.

    1. Sub MyMacro(Optional control As IRibbonControl)

    Now, if I want to write my macros so I can run the code in Excel 2003 or 2007 I have a new problem; the IRibbonControl did not exist in Excel 2003. As it turns out, the Ribbon doesn't necessarily care if it's allowed to pass along itself to a routine that is setup to receive a Variant as opposed to an IRibbonControl, and we know that Excel 2003 allows Variants. So now I have this final syntax that allows for direct and ribbon calling in Excel 2007, and will also work if someone tries to run the code in Excel 2003.

    1. Sub MyMacro(Optional control As Variant)

    I saw Ger Plante's suggestion to change the name of the variable from "control" to "variable". Obviously, it can be named anything you want. I'll probably leave mine as "control" just because it reminds me of the origin of why I included it. I'm also hopeful that when people see it in my signature, the "control" reference might help them to make the lateral connection.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]