Questions about ExcelCommands

Feb 5, 2014 at 2:27 PM
Question 1:
I noticed that the IsHidden attribute of ExcelCommand is Obsolete with a comment that only ExcelFunctions can be hidden. Any ExcelCommands show up in the function wizard in a category that is named the same as the add-in. If we have commands that should not show up there how do we accomplish that? One example would be a command that you are creating so it can be called from the cell context command bar, but you don't intend to have it callable outside that context. One workaround I could think of would be to make it a hidden function with IsMacroType = true and then use ExcelAsyncUtil.QueueAsMacro for any privileged operations that you need to do. Which brings me to my next question....

Question 2:
In an ExcelCommand I had a call to XlCall.Excel(xlfSelection) and got back XlReturnInvXlfn which I thought was weird since I thought that commands and macros were the same. However if I farm out the call to a ExcelAsyncUtil.QueueAsMacro delegate it works fine. Why was that necessary?
Coordinator
Feb 5, 2014 at 8:06 PM
If your method has return type 'void', it is considered and registered as a command (a macro). Otherwise it is registered as a function. Functions can additionally be hidden.

I suspect your method is returning some value, and Excel-DNA is registering it as a function, which is why you are seeing it in the function arguments dialog box, and when you can't call xlfSelection.

-Govert
Feb 5, 2014 at 9:04 PM
That is seems un-intuitive that if you ask for the method to be a command and because it returns a value it gets registered as a function.
I am open to being wrong about this and could be biased by former experience using other add-in APIs. As a general rule my company would return error codes from macros so that when called from VBA the caller can decide what to do with the error message (for example report it in the sheet, or pop a message box, etc).
Coordinator
Feb 5, 2014 at 9:16 PM
I agree with you - I should have made these distinctions differently ten years ago when I started. I knew a bit less about Excel then. My mental model was more like VBA, and not faithful to the C API registration.
For now I'd rather maintain backward compatibility. I might try to revisit how the registration choices work for a future version, but certainly not for the next release.

If you make a hidden ExcelFunction that is marked with IsMacroType=true, do you have the xlfSelection issue when it is called from VBA or a menu?

-Govert
Feb 11, 2014 at 6:00 PM
Edited Feb 11, 2014 at 6:01 PM
The xlfSelection issue was my fault. I was calling the function directly from my Ribbon code rather than calling it as a macro.
I used to launch the macro from the cell command bar and had moved it to be callable by the Ribbon but the Ribbon code was calling it directly rather than through a macro.
Marked as answer by harleybl on 2/11/2014 at 1:12 PM