Array doesn't spill

[William Rummler]

Hi,

I'm new to Excel-DNA. I'm returning an array, but it won't spill. I'm using Excel 2016 and Excel-DNA 1.2.2-preview.

This is my function:

```

[ExcelFunction]

public static object UnitVector(int length)

{

    var vector = new object[length];

    var elementValue = 1 / Math.Sqrt(length);

    for (var i = 0; i < vector.Length; ++i)

    {

        vector[i] = elementValue;

    }

    return vector;

}

```

This is what the output looks like in Excel:

Only one element value appears, only in the cell where the formula is entered.

I'm almost certain I must be missing something very obvious, and perhaps nothing to do with Excel-DNA, but the documentation I've read so far seems to only mention arrays in passing --- that they just work (with 1D spilling along the row and 2D spilling in row-major order).

What am I missing?

Thank you,

William

[Govert van Drimmelen]

Hi William,

 

Your version of Excel does not have the ‘Dynamic Arrays’ feature yet.

That means your array function will not be dynamically resized by Excel.

Under up-to-date Office 365 subscriptions, Excel does have support for Dynamic Arrays and everything work great, as you expect.

 

Like with built-in Excel functions, you can pre-select a region of the right size, then type in the formula and enter it with “Ctrl+Shift+Enter” making it an ‘Array Formula’ which will extend over the selected size.

 

There is a hacky workaround to make results in old Excel automatically resize the calling array region called the ‘ArrayResizer’ but it can be problematic so I suggest you consider it an advanced feature. It looks like your example has results that have a know result size, so the Ctrl+Shift+Enter approach should work fine.

 

-Govert

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of William Rummler
Sent: 2 March 2021 20:21
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Array doesn't spill

 

Hi,

 

I'm new to Excel-DNA. I'm returning an array, but it won't spill. I'm using Excel 2016 and Excel-DNA 1.2.2-preview.

 

This is my function:

 

```

[ExcelFunction]

public static object UnitVector(int length)

{

    var vector = new object[length];

    var elementValue = 1 / Math.Sqrt(length);

    for (var i = 0; i < vector.Length; ++i)

    {

        vector[i] = elementValue;

    }

    return vector;

}

```

 

This is what the output looks like in Excel:

 

 

Only one element value appears, only in the cell where the formula is entered.

 

I'm almost certain I must be missing something very obvious, and perhaps nothing to do with Excel-DNA, but the documentation I've read so far seems to only mention arrays in passing --- that they just work (with 1D spilling along the row and 2D spilling in row-major order).

 

What am I missing?

 

Thank you,

William

 

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/23adb114-118b-4d3b-b5e3-5f8c6494ced9n%40googlegroups.com.

[William Rummler]

Thank you Govert. I got the latest Excel 365 from my company IT, and unfortunately dynamic arrays are still missing from it. (The web version has it, but no add-ins supported there, as far as I can tell.) CSE arrays work as you mentioned but are insufficient for my actual scenario.

For anyone else reading this, if it helps, here is the version I'm using:

"Microsoft Excel For Office 365 MSO (16.0.11929.20618) 64-bit" --- "Version 1908 (Build 11929.20648 Click-to-Run)"

I'm hoping my IT can update me to a version that has dynamic arrays. If they do, I'll post the version I end up with here.

--William

You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/owBVl28W5vc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/01c901d70f93%24a42b0190%24ec8104b0%24%40icon.co.za.

[Govert van Drimmelen]

Hi William

 

As far as I know, all current Excel 365 version support Dyanmica Arrays, not just the preview channels.

 

That “Version 1908” you see in your info string is a year and month.

So you actually have quite an old version there from 2019.

Are you able to look whether updates are available (maybe under File -> Account -> Update Options).

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CANuYpJQ7rrB%3DJKMDauC5h0GhyPKXDfdt_uQKkzJr7L3QauUrEg%40mail.gmail.com.

[William Rummler]

You're right, it does seem to be an old version. It was automatically pushed to my workstation by my IT folks, and the updates are managed likewise. The whole thing's a bit strange, because I have colleagues who have (and have had for a while) newer versions with dynamic array support. Seems to be just an IT mix-up on my end. Hopefully they can work it out, and then I'll be all set.

Thanks for your help!

--William

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/02d401d7104c%246f509570%244df1c050%24%40icon.co.za.