Running add-in's macros from the Macro (ALT+F8) dialog

[mark99k]

Embarrasingly many years after getting into Excel macros, I've just recently learned that you can type the name of an add-in's macro in the Macro dialog (ALT+F8) and it'll execute, even though the name doesn't appear in the list. While it's great that you can make such a macro run from the workbook window, it means (1) you have to know its exact name, and (2) if you don't, you can't browse the list to jog your memory.

Isn't that a strange hole in its functionality? In Word, where I cut my teeth when VBA first arrived, macros from any installed add-ins appear in the dialog and you can either scroll to them, or force-scroll the list by typing. And except in cases of duplicate names, you don't have to prefix the macro name with its container. This seems totally logical.

It's fine to plant macros on the ribbon or QAT, but I write *way* too many for that, and often I'll just want to have certain infrequently used macros stored so they're available when needed but don't clutter up the PMW. I even started cobbling together a userform to fill the gap, but it's comically slow & primitive. Has anyone written a deep-dish add-in that gets around this obstacle?

I can't imagine why Word and Excel evolved so differently in this area. Thanks for any clues.

[rjlabs]

I have no idea either why macros in add ins are not listed in the Macro (ALT+8) dialog.

When recently moving Excel to a new laptop I wasn't aware that customization to the the Ribbon and QAT had to be EXPORTED to a file then IMPORTED on the fresh install side. Better do that PRIOR to the license move! Call me old school but I was searching the old computer for toolbar files. Ooops! As a result lost the entire GUI to my substantial library of macros.

(Adjacent, but related to the above rant,... the limited and sophomoric set of icons available to assign to macros, and the difficulty of importing a high quality/custom designed set , and the difficulty in "registering" macros so you get a description of what it does, quick link to a CHM help file, enable tool tip support for parameters/arguments Not lovin the jumbo, clunky QAT-Ribbon icons, and the automatic some large, some small, approach.)

Wasted quite a bit of time trying to make add-in macros appear on the Macro (ALT+8) dialog over at the Trust Center, including trusting the file location for the add-ins.

All told lots of time wasted dealing with MSFT tomfoolery. Until I found the above post I was convinced the add-in macros should show up, after all the add-in workbooks are indeed "opened up" during start up. List macros in "all open workbooks" is misleading.

Let's review everything about Excel custom macros and all the HCI / GUI aspects of putting them to use. It all seems about generation 1.

Wasn't Excel supposed to be one of the great applications of Visual Computing?

[Zer0Cool]

This is by design. There are many types of routines that do not show in the Macro Run Dialog.

For example Functions are not displayed in the macro run dialog, as they generally require some sort of input that cant be given by the dialog. Private routines are not show in the dialog as they are meant to be isolated (and typically run by other routines).

An addin for example may provide custom written classes for use elsewhere, or may provide many UDF's to a user. When an add-in is meant to be interacted with/have routines run by the user it is typical to create tabs/buttons on the ribbon to use.

Not sure I see how you can have "way too many" macros for a ribbon drop down list but the same amount is reasonable to scroll through in the run dialog (which as far as I am aware you cant resize).

[Zer0Cool]

I wasn't aware that customization to the the Ribbon and QAT had to be EXPORTED to a file then IMPORTED on the fresh install side.[/I]

Only when you customize them manually. When you do an addin you generally create the ribbon entries one of 2 ways.

• Directly create them using xml injected into the file/addin
• Generate and enable/disable the required xml for the ribbon using VBA

Thats what make an add-in portable, everything is self contained in it making it easy to distribute. The same method can be used in any macro enabled file, including the personal workbook. I have workbooks with their own ribbon tabs and my personal workbook had at one point its own tabs done via xml inside the file itself. At that rate if you have the file you have the interface.