Use attributes for XLL function descriptions

Yuri Astrakhan

Posts: 53
Joined: 2006-05-19

In order to describe functions in an XLL project, we have to use "Excel Function Category" object. This is very inconvenient because two matching lists must be maintained - in code and in the UI configuration. Any functions/parameters renaming or changes to parameter order must be manually reflected in the UI configuration.

It would be significantly more convenient if we could attach various specialized attributes to functions and arguments. For example:


[UdfFunction("Some description", "Category", HelpID="helpTopicID", Hidden=False, Volatile=True]
object MyUdfFunction(

   [UdfParameter(...)]
   object param1

)

Some things, like description and category, should probably be mandatory, whereas other attribute parameters can be made optional.

Thanks for the great product!

Yuri Astrakhan

Posts: 53
Joined: 2006-05-19

Here's the code that dynamically does what I described in the previous post. It includes the definition of two attributes (one for function and one for parameter), and the code to iterate over a given Type and create all description objects. To use this code, call the method after InitializeComponent() call in the constructor.


    [AttributeUsage(AttributeTargets.Method, Inherited = false, AllowMultiple = false)]
    public class UdfFunctionAttribute : Attribute
    {
        private readonly string category;
        private readonly string description;
        private string helpTopicID;
        private bool hidden;
        private bool @volatile;

        public UdfFunctionAttribute(string description)
        {
            this.description = description;
        }

        public UdfFunctionAttribute(string description, string category)
        {
            this.description = description;
            this.category = category;
        }

        public string Description
        {
            get { return description; }
        }

        public string Category
        {
            get { return category; }
        }

        public bool Hidden
        {
            get { return hidden; }
            set { hidden = value; }
        }

        public bool Volatile
        {
            get { return @volatile; }
            set { @volatile = value; }
        }

        public string HelpTopicID
        {
            get { return helpTopicID; }
            set { helpTopicID = value; }
        }
    }

    [AttributeUsage(AttributeTargets.Parameter, Inherited = false, AllowMultiple = false)]
    public class UdfParameterAttribute : Attribute
    {
        private readonly string description;
        private bool acceptAsArray;

        public UdfParameterAttribute(string description)
        {
            this.description = description;
        }

        public string Description
        {
            get { return description; }
        }

        public bool AcceptAsArray
        {
            get { return acceptAsArray; }
            set { acceptAsArray = value; }
        }
    }

        private void AddFunctionDescriptions(Type udfClassType)
        {
            Dictionary<string, ADXExcelFunctionCategory> categories = new Dictionary<string, ADXExcelFunctionCategory>();

            foreach (MethodInfo method in udfClassType.GetMethods(BindingFlags.Public | BindingFlags.Static))
            {
                object[] funcAttrs = method.GetCustomAttributes(typeof (UdfFunctionAttribute), true);
                if (funcAttrs != null && funcAttrs.Length > 0)
                {
                    UdfFunctionAttribute funcAttr = (UdfFunctionAttribute) funcAttrs[0]; // use first only?
                    string categoryName = funcAttr.Category ?? AddinName;

                    ADXExcelFunctionCategory categoryDesc;
                    if (!categories.TryGetValue(categoryName, out categoryDesc))
                    {
                        categoryDesc = new ADXExcelFunctionCategory(GetContainer());
                        categoryDesc.CategoryName = categoryName;
                        categories.Add(categoryName, categoryDesc);
                    }

                    ADXExcelFunctionDescriptor funcDesc = new ADXExcelFunctionDescriptor(GetContainer());
                    categoryDesc.FunctionDescriptors.Add(funcDesc);

                    funcDesc.FunctionName = method.Name;
                    funcDesc.Description = funcAttr.Description;
                    funcDesc.IsHidden = funcAttr.Hidden;
                    funcDesc.IsVolatile = funcAttr.Volatile;
                    funcDesc.HelpTopicId = funcAttr.HelpTopicID;

                    foreach (ParameterInfo parameter in method.GetParameters())
                    {
                        object[] paramAttrs = parameter.GetCustomAttributes(typeof (UdfParameterAttribute), true);
                        if (paramAttrs != null && paramAttrs.Length > 0)
                        {
                            UdfParameterAttribute paramAttr = (UdfParameterAttribute) paramAttrs[0]; // should not have > 1
                            ADXExcelParameterDescriptor paramDesc = new ADXExcelParameterDescriptor(GetContainer());
                            funcDesc.ParameterDescriptors.Add(paramDesc);

                            paramDesc.ParameterName = parameter.Name;
                            paramDesc.Description = paramAttr.Description;
                            paramDesc.AcceptAsArray = paramAttr.AcceptAsArray;
                        }
                    }
                }
            }
        }

Sergey Grischenko

Add-in Express team

Posts: 7233
Joined: 2004-07-05

Hi, Yuri.

Thank you for the suggestion. But we have already released the XLL module and I think we will not change anything in the current UI.
I would advise you to add the "Excel Function Category" object when the add-in code has already been tested and you are going to release your product.

Yuri Astrakhan

Posts: 53
Joined: 2006-05-19

Sergey, there is no need to change the current UI - those users who wish to use UI to define categories and description, may continue doing so. The attribute infrastructure may work seamlessly alongside it. For example, when I define a UDF, I would add an attribute as I described above, and the startup function would simply enumerate them and create exactly the same objects as the UI.

My thought was to eliminate duplicate error-prone efforts of coders. With the UI, developer first defines the function, then goes into UI, creates a category object, goes into properties, types in/selects newly created function, defines all the arguments, etc. This process is very tedious and most importantly - error prone. If the function is renamed or deleted, the function/parameters might not be updated.

With the attributes, all a developer has to do is add attributes next to the function and parameters. Any function deletion, renaming, parameter changes, etc, are always in sync because they are right next to each other, and function or parameter names do not have to be kept the same. This greatly reduces the number of potential errors, and removes the necessity to switch to UI after every new declaration change.

I am already using this in my code, but I think everyone writing UDFs would greatly benefit from the simple attributes.

Thanks!

Sergey Grischenko

Add-in Express team

Posts: 7233
Joined: 2004-07-05

Yuri, if I add the attributes support, I will have to remove ADXExcelFunctionCategory component because it will no longer needed.
And it is not possible. I can only improve the design-time support only.
Anyway, thank you for the investigation. I will think it over.