How to set cell value using Excel Dna?

Question

I have the following code in my DNA DNA plugin

In my AutoOpen method, I will put the following code:

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

I have the following function that is 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 problem is that I cannot set the cell values. When I call the method, it causes an error in the last line.

My error handler gives me the following error:

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

I also tried setting the cell value as follows:

  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 out what I can do wrong here?

Answer 1

Excel does not allow you to set other sheet cells inside a custom worksheet function. This is necessary to maintain the dependency tree that Excel uses to control the recalculation. This is true if you use VBA, C API or Excel-DNA.

It’s best to add a feed button, context menu, or key combination to make changes using a macro.

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

Answer 2

Actually, you can write in any cell if you are doing this in the async task as a macro.

A 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()"; } 

Record in one 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(); });