Read and Paste Values using Named Ranges

[Mark Kondrla Jr]

Hi All,

My problem is simple, yet I have not been able to figure out the solution. I am trying to create a UDF that does the following

I would like to read a named range of values in from Excel to C#. These values are doubles. (I am able to do this)

dynamic Excel = ExcelDnaUtil.Application;
dynamic test = Excel.Range["Test"].Value2;

I would then like to perform some function calls on this double array. (array*5, array + 5, etc.) (I am able to do this)

I would then like to paste resulting array values to a different named range in Excel. (I am not able to do this)

Something like the following does not work:

dynamic Excel = ExcelDnaUtil.Application;
Excel.Range["ABC"].Value = 5.0;

Or even this:

dynamic Excel = ExcelDnaUtil.Application;
ExcelReference sheet1 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet1");
ExcelReference target = new ExcelReference(0, 0, 0, 0, sheet1.SheetId);
target.SetValue(5.0);

It would be nice to do the pasting on the array basis, that is, say a 5x5 chunk of cells. Any help would be greatly appreciated. Thank you.

Best,

Mark

[Govert van Drimmelen]

Hi Mark,

Excel does not allow you to alter the sheet inside a UDF calculation.

That makes sense if you consider how Excel is trying to keep track of the dependencies of different parts of the sheet, to provide fast and correct recalculation if anything changes.

If the functions called from any cell might be writing elsewhere in the sheet, it becomes impossible to know where the values you see on the sheet come from, which ones can be changes without being overwritten be a recalculation, etc. You would basically break the whole spreadsheet model and as such, you're heading for an mess. So Excel doesn't let you do that.

It's better to find a different way, like having the 'target' range depend on a function that depends on the input names range as its argument values. Or a button that the users press to do the processing, which is thus not tied into the spreadsheet calculation.

If you still insist on going down this bad and dangerous road, Excel-DNA does provide a helper method (ExcelAsyncUtil.QueueAsMacro) to schedule some code to run in a macro context, as soon as possible (typically after the calculation completes). This method can be called from any thread and from any context, including from your UDF in a calculation context.

So your function might say:

public object DangerousFunctionThatWillSurelyMakeAMess(double valueToWriteToA1)

{

    ExcelAsyncUtil.QueueAsMacro( () =>

    {

        ExcelReference target = new ExcelReference(0, 0, 0, 0, "Sheet1");

        target.SetValue(valueToWriteToA1);

    });

    return "Bad things will happen...";

}

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Mark Kondrla Jr [mkond...@gmail.com]
Sent: 01 May 2017 09:17 PM
To: Excel-DNA
Subject: [ExcelDna] Read and Paste Values using Named Ranges

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.