Tutorial - Excel-DNA and Dynamic Arrays

[Govert van Drimmelen]

Do Excel-DNA add-ins and functions work well with the powerful new Dynamic Arrays feature of Excel 365? Yes, they form a great team, but there are some surprises . . . 

I've posted a new video to YouTube - https://youtu.be/XUoN5NUBL2M - that discusses what an Excel-DNA add-in developer should consider, after they've learnt the basics about Dynamic Arrays.

Links to introductory material for Dynamic Arrays, as well as the sample workbook and add-in solution described in this video, can be found on the Excel-DNA Tutorial site: https://github.com/Excel-DNA/Tutorials/tree/master/SpecialTopics/DynamicArrays.  

Please have a look, and let me know if you have any questions or comments.

I also welcome suggestions for future tutorial topics you'd like to see.

-Govert

[da...@dowellvina.com.vn]

Dear Mr Govert,

Thanks for your share, It works well in Excel 365, but could I use it in previous Excel version?

Dom

Vào lúc 20:35:43 UTC+7 ngày Chủ Nhật, 15 tháng 11, 2020, gov...@icon.co.za đã viết:

[Govert van Drimmelen]

Hi Dom,

 

This tutorial was about the Dynamic Arrays feature that Microsoft added to new versions of Excel, and was not available in older versions.

 

But long ago I made some sample code that shows how you can get some of the functionality in old Excel – I called it the “ArrayResizer”.

It does not work as well as the real built-in Dynamic Arrays, but it’s the best I could do.

You can find the ArrayResizer code here: https://github.com/Excel-DNA/Samples/tree/master/ArrayResizer

 

But the new feature in Excel really is much nicer.

 

-Govert

--
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/7c8ad3d0-f173-4cbf-a922-4efd4ce675b1n%40googlegroups.com.

[da...@dowellvina.com.vn]

Dear Mr Govert,

Thanks so much!

Vào lúc 14:02:24 UTC+7 ngày Thứ Ba, 17 tháng 11, 2020, gov...@icon.co.za đã viết:

[da...@dowellvina.com.vn]

Dear Mr Govert,

Could you show me the way to input DataTable into this function (Dynamic Arrays)? 

Thanks in advanced,

Dom

Vào lúc 14:18:18 UTC+7 ngày Thứ Ba, 17 tháng 11, 2020, da...@dowellvina.com.vn đã viết:

[Govert van Drimmelen]

Hi Dom,

You need to return an objct[,] array from the function.

So if you have a DataTable, you need to convert it to an object[,] array.

You can try something like this for the conversion - I'm not sure if all the datatypes would be compatible, so that could mean a bit of extra work:

    public static object[,] Convert(DataTable dt)

    {

        var rows = dt.Rows;

        int rowCount = rows.Count;

        int colCount = dt.Columns.Count;

        var result = new object[rowCount, colCount];

        for (int i = 0; i < rowCount; i++)

        {

            var row = rows[i];

            for (int j = 0; j < colCount; j++)

            {

                result[i, j] = row[j];

            }

        }

        return result;

    }

-Govert

[da...@dowellvina.com.vn]

Dear Mr Govert,

Thanks so much for your help,

Dom

Vào lúc 22:50:23 UTC+7 ngày Thứ Tư, 18 tháng 11, 2020, gov...@icon.co.za đã viết:

[Bart Duijndam]

Hi Govert.

The video is very useful. Recently, our company upgraded to the latest version of Office enabling dynamic arrays in Excel. I had to refactor my AddIn as previously (using static arrays) the user always had to predefine the number of output columns. I could use that information to decide what information to provide in these static arrays.

For example, when doing a Fourier transform, should I just provide a single column with amplitude information, or should I also provide phase information in the second column.

This is no longer feasible with dynamic arrays. So this choice now has to be defined in a parameter that is part of the UDF.

But this small downside comes with many upsides of using dynamic arrays.

Kind regards

Bart Duijndam