UDF Registration and Excel Interop

May 30, 2012 at 7:03 PM

Hello,

I have a 2-part question regarding UDF registration ala Excel DNA and their capability to use the COM Interop wrappers ala ExcelDnaUtil.Application.

  • First, I am able to register functions in both DNA script and in external C# libraries, but one of my functions below (SetValue) is not registered.  I have built debug versions of Excel DNA to see the log output, but do not see obvious errors in the trace. Can someone see why in the following example?
using System;
using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;

    public class MyFunctions
    {
        [ExcelFunction(Description = "Get Value", Category = MdeModule.CATEGORY)]
        public static string GetValue(string val)
        {
            return val;
        }

        [ExcelFunction(Description="Get Excel Version", Category=MdeModule.CATEGORY)]
        public static string GetExcelVersion()
        {
	   // registered
            dynamic xlApp = ExcelDnaUtil.Application;
            return xlApp.Version;
        }

        [ExcelFunction(Description = "Set Value", Category = MdeModule.CATEGORY)]
        public static void SetValue(string rangeRef, string val)
        {
	   // not registered
            dynamic xlApp = ExcelDnaUtil.Application;
            if (xlApp != null)
            {
                //dynamic xlSheet = xlApp.ActiveSheet;
                //if (xlSheet != null)
                //{
                //    dynamic xlRange = xlSheet.Range[rangeRef];
                //    if (xlRange != null)
                //    {
                //        xlRange.Value = val;
                //    }
                //}
            }
        }
    }
  • I am also getting a COM Interop error inside another UDF when I try to set the Range.Value and Range.Value2 property in the thread context of the UDF.  I am able to walk the Excel object tree alright, but am encountering the following error upon manipulating the Range values.  My search online suggests there is either Range reference issue or a localization/culture formatting issue; I feel like Approach 1 below rules out the bad ref, because the Range is well-known and well-referenced.  but I wanted to know if others had experienced the same and if so, how they corrected the code.
    Approach 1: currrentRange.Value = "Test";
    
    Approach 2: currentRange.Value2 = context.Data.Cells; // 2d array
    
    Approach 3: 
                for (int i = 0; i < source.Height; i++)
                    for (int j = 0; j < source.Width; j++)
                    {
                        target[i + 1, j + 1].Value = 0; // source.Cells[i, j];
                    }
    
    ExcelDna.Error: Exception from HRESULT: 0x800A03EC
    System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object value)
Cheers, bt
 
P.S. Here are the links related to part 2.
Coordinator
May 30, 2012 at 9:01 PM

Hi,

1. Your method SetValue is not registered as a worksheet function since it does not return anything. To be registered as a function you need to change the return type to something like 'string'. Anyway, create a general Range 'Setter' function like you seem to have there is a really bad idea - see the discussion for point 2. below.

2.  In general Excel does not allow you to change other parts of the sheet from within a worksheet calculation. This is true whether you are using VBA, the C API or Excel-DNA. There are some workarounds for this, but I'd strongly recommend you not do this.

Why would Excel prevent this? Excel builds a dependency tree for the sheet to figure out which cells depend on which functions. By setting an arbitrary cell's values from within you function, you effectively break Excel's ability to keep track of which cells to recalculate when specific cells changes.

What can you do? I suggest you set those cells with a function that depends on whatever the inputs are. Alternatively you can create a macro that is attached to a menu item or keyboard shortcut, for setting the required cells.

-Govert