UDF is called by Excel Insert Function button

Sep 4, 2013 at 3:17 PM
Edited Sep 4, 2013 at 3:26 PM
Hi,

I have noticed that my UDF function is being executed when the user clicks the Insert function button in Excel.

Is there any way for the function to determine if the Insert function button was hit so that it can prevent code from being executed until the user clicks the OK button for the function?

Also, I noticed that there is a category that appears once the Insert function button is hit with the name of my class library. Is there any way to hide this? Making every static property/method be ComVisible(false) did not hide them.

Thanks,

Lee
Coordinator
Sep 5, 2013 at 7:15 PM
Hi Lee,

Your function can detect whether the Function Arguments dialog is being displayed by checking ExcelDnaUtil.IsInFunctionWizard(), and you can return some other value in this case.

Your functions can be hidden from the Insert Function list by marking them as IsHidden=true:
[ExcelFunction(IsHidden=true)]
public static string SecretFunc()
{
   return Found me!";
}
-Govert
Sep 6, 2013 at 3:10 PM
Thanks for the reply Govert,

The IsInFunctionWizard worked perfectly and the ExcelFunction(IsHidden=true) handles almost everything.

I have a class that inherits from IExcelAddIn. Within this class, there are 3 static properties. I cannot use the ExcelFunction on a property and these properties are showing as categories in the Insert function dialog. The category name is the name of my class library. How would I hide these static properties?

Thanks,

Lee
Sep 26, 2013 at 5:32 PM
Hi Govert,

Is there any way to prevent static properties from appearing in the Insert function dialog? I cannot find any ways to prevent these properties from showing.

Thanks,

Lee
Coordinator
Sep 26, 2013 at 8:03 PM
Hi Lee,

Yes there are a few ways to prevent a function from being registered, and that also applies to the get_MyProperty function from a static property.
  1. Mark the function or property as internal or private. Only public methods are registered with Excel.
  2. Mark the property get with [ExcelFunction(IsHidden=true)]. The method will still be registered with Excel, but will be hidden from the IntelliSense and insert function dialog.
  3. Make sure that all the methods (functions and macros) that you want to register are marked with ExcelFunction and ExcelCommand attributes, and then mark you ExternalLibrary as ExplicitExports="true" in the .dna file:
<DnaLibrary RuntimeVersion="v4.0" >
<ExternalLibrary Path="MyFunctions.dll" ExplicitExports="true" />
</DnaLibrary>
With this option only methods that are explicit marked with the attributes will be registered.


-Govert