How do you set the value of a cell using Excel Dna?

Question

I've got the following code in my Excel DNA plugin

In my AutoOpen method I put the following code:

ExcelIntegration.RegisterUnhandledExceptionHandler(ex => ex.ToString());

I've got the following function that gets called from my excel sheet.

[ExcelFunction(Category = "Foo", Description = "Sets value of cell")]
public static Foo(String idx)
{
        Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
        Excel.Workbook wb = app.Workbooks[1];
        Excel.Worksheet ws = GetSheet("Main");

        // This gives us the row
        Excel.Name idxRange = wb.Names.Item("COL_Main_Index");
        var row = (int)app.WorksheetFunction.Match(idx, idxRange.RefersToRange, 0);

        // Get the Column
        Excel.Name buyOrderRange = wb.Names.Item("COL_Main_BuyOrder");
        var col = (int)buyOrderRange.RefersToRange.Cells.Column;

        // create the range and update it
        Excel.Range r = (Excel.Range)ws.Cells[row, col];
        r.Value ="Foo"; 
}

The issue is that I can't actually set any cell values. When I call the method it causes an error on the last line.

My error handler gives me the followign error:

{System.Runtime.InteropServices.COMException (0x800A03EC)

I've also tried to set the cell value like so:

        r = (Excel.Range)ws.Cells[12, 22];
        const int nCells = 1;
        Object[] args1 = new Object[1];
        args1[0] = nCells;
        r.GetType().InvokeMember("Value2", BindingFlags.SetProperty, null, r, args1);

With the same result.

Can anyone point to what I might be doing wrong here?

Answer 1

Excel does not allow you to set other worksheet cells from within a user-defined worksheet function. This is to preserve the dependency tree Excel uses to manage the recalculation. This is true whether you are using VBA, the C API or Excel-DNA.

Best is to add a ribbon button, context menu or shortcut key to effect the changes via a macro.

There are some ugly workarounds, but I would not recommend it.