Excel 2010 and MacroOptions - does this actually work?

[pbastian]

I am a very experienced VBA and Excel programmer. I have been using Excel 2003 and have an extensive add-in library, that I use Lorent Longre's free UDFCustomize.dll to automatically load a custom function category and provide the links to Help. My add-in functions look like and behave like any other Excel function.

Now, here comes XL 2010 (32-bit). Using it on W7 64-biit system. Trying to move away from the Lorent Longre solution because it does not work completely with XLSX files.

OK - no problem. MS has provided the Application.MacroOptions object to handle things the way I need them to. BUT, no matter what I try, I cannot get this thing to work. I even just wrote a little stub routine to plant a function into a custom category, just to understand how it works. No dice! It's so frustrating, because there is info all over the net on how this works, but I cant get it to work.

Here is what I have:
1) I have a small VBA code in the Workbook_Open event of the ADD-IN (an XLAM file called PetroUtil.xlam)

Option Explicit
Private Sub Workbook_Open()
Excel.Application.OnTime VBA.Now() + VBA.TimeValue("00:00:00"), "AddFunc"
End Sub
​

2) The AddFunc subroutine is in a standard code module in the ADD-IN file PetroUtil.xlam, and it looks like this:

Public Sub AddFunc()
Excel.Application.MacroOptions Macro:="PetroUtil.xlam!Months", Category:="PetroUtil", Description:="This is a test"
End Sub
​

Note that I have fully qualified the Macro option, and I am using the Workbook_Open event to run the MacroOptions. I am putting the MacroOptions call in its own routine based on an error I get about modifying a hidden workbook, which internet posts pointed to the OnTime solution shown here.

If I open Excel and a new workbook, I have confirmed that the ADD-IN loads and runs, and executes the AddFunc function.

But, looking at the function library, there is no PetroUtil category, and there is no function Months.

What am I doing wrong?

 

[Arcticsponge]

Hi pbastian,

This one had me stumpped for a few mins too until it hit me - its a sub therefore it needs to be ran [F5] Doh!

In order to get it to run on startup, place a call to the sub in the Workbook open decs in the ThisWorkbook object.

Trouble is, that as an add-in, it shall be auto-loaded each time which then means that each time you open a workbook, Excel shall try to run the UDFReg (or whatever you have called it) sub which shall then cause an error as Excel shall not be able to implement the changes caused by re-running the sub (this is only after the add-in has been added).

To negate this add the following to the top of the sub:

On Error Resume Next
'Checks for the presence of the add-in, if exists, exits this sub
If AddIns("<Your Add-in Name here>").Installed = True Then Exit Sub

An important point to keep in mind: remember, the add-in name is that of the name in the document properties > summary > title field

 

[Arcticsponge]

Sorry, reposting the code as it looks like the forum doesn't like angled brackets:

On Error Resume Next
'Checks for the presence of the add-in, if exists, exits this sub
If AddIns("your add-in name here").Installed = True Then Exit Sub

That shall learn me for being so arrogant as to post without first previewing!