ExcelDNA Dynamically Registering UDF at Runtime

Question

My aim is to dynamically build and register an Excel user defined function at runtime in an ExcelDNA AddIn.

Here is an example provided by the ExcelDNA author which highlights how you can compile a UDF from a simple C# code string.

As you can see, this code is executed by calling RegisterMyClassfrom within the AddIn's AutoOpen method; and everything works perfectly.

However, if you move the RegisterMyClass method into (for example) a Ribbon button's action method, the registration of the dynamic UDF does not work and results in the following error:

Registration [Error] xlfRegister call failed for function or command: 'MyDynamicAdd'

In fact, it seems like any calls to ExcelIntegration.RegisterMethods fail in the above error message - unless they are called from within the AutoOpen method.

My question is:

How can I dynamically register a new UDF at runtime and triggered by a click on a Ribbon button?

Referenced Gist code for completeness' sake:

<DnaLibrary Name="ExcelDna Test Dynamic Method" Language="C#">
<Reference Name="System.Windows.Forms" />
    <![CDATA[
    using System;
    using System.CodeDom.Compiler;
    using System.Collections.Generic;
    using System.IO;
    using System.Reflection;
    using System.Windows.Forms;
    using Microsoft.CSharp;
    using ExcelDna.Integration;

    public class Test : IExcelAddIn
    {
        // Just to test that we are loaded.
        public static double MyAdd(double d1, double d2)
        {
            return d1 + d2;
        }

        public void AutoOpen()
        {
            RegisterMyClass();
        }

        public void AutoClose()
        {
        }

        private void RegisterMyClass()
        {
            string code = 
                @"
                public class Script 
                { 
                    public static double MyDynamicAdd(double d1, double d2)
                    {
                        return d1 + d2;
                    }
                }";

        CompilerParameters cp = new CompilerParameters();
        cp.GenerateExecutable = false;
        cp.GenerateInMemory = true;
        cp.TreatWarningsAsErrors = false;
        cp.ReferencedAssemblies.Add("System.dll"); //, "System.Windows.Forms.dll", "ExcelDna.Integration.dll" );
        CSharpCodeProvider provider = new CSharpCodeProvider();
        CompilerResults cr = provider.CompileAssemblyFromSource(cp, new string[] { code });
        if (!cr.Errors.HasErrors)
        {
            Assembly asm = cr.CompiledAssembly;
            Type[] types = asm.GetTypes();
            List<MethodInfo> methods = new List<MethodInfo>();

            // Get list of MethodInfo's from assembly for each method with ExcelFunction attribute
            foreach (Type type in types)
            {
                foreach (MethodInfo info in type.GetMethods(BindingFlags.Public | BindingFlags.Static))
                {
                    methods.Add(info);
                }
            }
            Integration.RegisterMethods(methods);
        }
        else
        {
            MessageBox.Show("Errors during compile!");
        }
    }
    }
]]>
</DnaLibrary>

score 1 · Accepted Answer · answered Aug 25 '18 at 16:12

Your code to register the function needs to be in a context where the C API is available. It won't work in a ribbon callback or any other COM event handler.

One option to switch into a macro context where the C API is available is to call the ExcelAsyncUtil.QueueAsMacro helper, and run the registration code in the delegate you pass in.

ExcelDNA Dynamically Registering UDF at Runtime

My question is:

Referenced Gist code for completeness' sake:

1 Answers1

Linked