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?

Seeker

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.

Hypophysis

One of the workarounds can be found here: excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays – Latham

Answer 2

Actually, you can write in any cell if you do this in an async job as a macro.

Simple Example:

using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;

[ExcelFunction(Category = "Foo", Description = "Sets value of cell")]
public static Foo(String idx)
{
    Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
    Excel.Range range = app.ActiveCell;

    object[2,2] dummyData = new object[2, 2] {
        { "foo", "bar" },
        { 2500, 7500 }
    };

    var reference = new ExcelReference(
        range.Row, range.Row + 2 - 1, // from-to-Row
        range.Column - 1, range.Column + 2 - 1); // from-to-Column

    // Cells are written via this async task
    ExcelAsyncUtil.QueueAsMacro(() => { reference.SetValue(dummyData); });

    // Value displayed in the current cell. 
    // It still is a UDF and can be executed multiple times via F2, Return.
    return "=Foo()";
}

Writing into a single Cell:

int row = 5;
int column = 6;
var reference = new ExcelReference(row - 1, column - 1);

ExcelAsyncUtil.QueueAsMacro(() => { reference.SetValue("Foobar"); });

// edit: just fyi, you can also use:

private void WriteArray(object[,] data)
{
    Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
    Excel.Worksheet worksheet= (Excel.Worksheet)app.ActiveWorkbook.ActiveSheet;

    Excel.Range startCell = app.ActiveCell;
    Excel.Range endCell = (Excel.Range)worksheet.Cells[startCell.Row + data.GetLength(0) - 1, startCell.Column + data.GetLength(1) - 1];

    var writeRange = worksheet.Range[startCell, endCell];
    writeRange.Value2 = data;
}

And then:

object[,] data = ...;    
ExcelAsyncUtil.QueueAsMacro(() =>
{
    WriteArray();
});

Bacardi

This works well for the first call and breaks for all subsequent calls. – Figurant

Hm? We use this approach heavily in our Excel Addin (github.com/PATRONAS/opuxl) without any problems. How does it break for you on subsequent calls? – Bacardi

Sorry for the confusion. My issue was unrelated. This ended up being very helpful. The ArrayResizer is great, but I didn't actually want an array. Just wanted to write out to multiple cells and this did the job. – Figurant

Answer 3

Here is the answer using .NET VB

 Dim row As Integer = 7
        Dim column As Integer = 7
        Dim reference = New ExcelReference(row, column)

        ExcelAsyncUtil.QueueAsMacro(Sub()
                                        reference.SetValue("Test")
                                    End Sub)

Rachellerachis