Using the add-in to return and create cell ranges in Excel

[gotpp]

Hi,

I was looking at the FinAnSu sample and was working on my own test as
well. Would it be possible to create a function where I type in the
function in one cell (a 1x1 range) in Excel ie. =DrawMatrix(10,20) and
then it will go and create a 10x20 cell range with some data values in
Excel with the top left corner of the matrix as the cell I put my
formula in.

Basically, we don't want an array formula where we have to select an
Excel range, enter our formula, and press Ctrl+Shift+Enter. We want it
to be simple. Type a formula in one cell and render an array or Excel
range relative to this cell.

Let me know if this makes sense.

[Govert van Drimmelen]

Hi,

My preferred approach to this is to automate the array formula pasting
and resizing.
See the post here: http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/
and the ArrayResizer.dna sample in the distribution.

The advantage of auto-expanding into an array formula is that the
Excel dependencies are properly maintained, and it's clear what data
on the sheet comes from a formula.

Give it a try and let me know whether it works for you.

-Govert

[gotpp]

I'm trying to get the sample to work.

I have a renamed ExcelDna.xll to Resizer.xll and a Resizer.dna with
the code as per the blog post.

I get the following warning and error in Excel when opening the .xll
or adding the .xll as an add-in.

There were errors when compiling project:
c:\Documents and Settings\fajardg2\Local Settings\Temp\1vdtho9d.
0.cs(137,17) : warning CS0168: The variable 'ex' is declared but never
used
c:\Documents and Settings\fajardg2\Local Settings\Temp\1vdtho9d.
0.cs(159,38) : error CS0103: The name 'xlApp' does not exist in the
current context

> > Let me know if this makes sense.- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi,

Ah, I see - thanks. I've updated the blog post.

You need something like:

static void RunMacro(string macroName)
{
object xlApp = null;
try
{
xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run",
BindingFlags.InvokeMethod, null, xlApp, new object[] {macroName});
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
if (xlApp != null) Marshal.ReleaseComObject(xlApp);
}
}

but somehow I got the declaration and the finally out of sync.

The ArrayResizer.dna sample file in the Distribution\Samples directory
of your Excel-DNA distribution should be fine, though.

-Govert