How to determine which version of excel?

[zhenyu Chen]

Is there any ways to determine excel version when user open excel?

[Govert van Drimmelen]

You can check ExcelDnaUtil.ExcelVersion.

 

-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 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.

[zhenyu Chen]

It works! Thank you !

[Alberto Cherubini]

Hi Govert,
I am currently running Excel365 locally on my machine (which updated recently, on the monthly channel).
If in excel I look at what version it is (i.e. in file/account) it tells me it is 1902,
excelDnaUtil.ExcelVersion returns 16.

Do you know what is the relationship between those two numbers?

The aim here is to find out whether an excel users (running our dll with UDF written using excelDna) has been upgraded to "dynamic array Excel" or not.

(this is because despite Microsoft attempts at making that upgrade backward compatible, they failed when UDF are involved, e.g. we had written our own auto-resizing functionality, which is now broken - so ideally we'd like to leave it working for users that have not been upgraded, and disable it automatically if they have).

On Tuesday, 14 February 2017 15:35:00 UTC, zhenyu Chen wrote:

It works! Thank you !

[Alberto Cherubini]

looking into microsoft docs/support pages, it seems the 4-char "version" e.g. "1912" is not v useful, as the same 4-char string can refer to different builds with different features.

Below I typoed, my version is not "1902" but "1912", in fact that is not unique...  the latest seems to be 16.0.12325.20298 

Also note that the excel/info/ reports inconsistent builds when you press "about"... I see 12324.20298 in the front, and .20280 in the about pop-up

[Alberto Cherubini]

I have asked on microsoft community, as of now no useful replies
https://answers.microsoft.com/en-us/msoffice/forum/all/what-exact-version-is-my-excel/f98f34f2-bcbd-4a39-a334-1334bace7e2b

[Govert van Drimmelen]

Hi Alberto,

 

As far as I know, the dynamic arrays feature was turned gradually across a range of versions.

So even if you know the exact version details of Excel, you would not know whether dynamic arrays are enabled.

 

The right way to detect dynamic arrays is probably to check for one of the built-in functions that accompany the feature, like SORT or FILTER.

I think the C API code for the FILTER function is 614.

 

Maybe you can help to test something like this:

 

        static bool? _supportsDynamicArrays; 

        [ExcelFunction(IsHidden=true)]

        public static bool SupportsDynamicArrays()

        {

            if (!_supportsDynamicArrays.HasValue)

            {

                try

                {

                    var result = XlCall.Excel(614, new object[] { 1 }, new object[] { true });

                    _supportsDynamicArrays = true;

                }

                catch

                {

                    _supportsDynamicArrays = false;

                }

            }

            return _supportsDynamicArrays.Value;

        }

 

I have not tried in a version that does not support dynamic arrays, but AFAIK it should throw an exception (as opposed to return #VALUE from the XlCall.Excel).

 

Please let me know if you have a chance to try it.

 

-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/f9f88b72-450b-4e5d-a1d8-581958ad1503%40googlegroups.com.

[Patrick O'Beirne]

If the user can type =SORT( then they have the dynamic arrays.
So in VBA you could test typename(evaluate("SORT(A1:A4)")) - variant()
if supported, error if not.

Charles Williams offers a CheckDA addin:
https://fastexcel.wordpress.com/2019/05/20/compatibility-of-office-365-dynamic-array-formulas/

[Alberto Cherubini]

Thanks Govert -  we will test that in both versions of excel.

(in case it doesn't throw, there is a similar one which does throw, using reflection, I'll post it here if needed)

To unsubscribe from this group and stop receiving emails from it, send an email to exce...@googlegroups.com.

[Alberto Cherubini]

Hi Govert, 

tested, and that code is behaving as desired with both version of excel.

Do you think it might be worthwhile to make it a bool property available in ExcelDnaUtil?

[Govert van Drimmelen]

OK great!

Yes that’s a good idea – I’ll add it there for the next version.

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/448fc50b-f5ef-46ce-9725-1d60bf153155%40googlegroups.com.

[Alberto Cherubini]

Hi Govert,

one more thought. 
Using a throw is not ideal especially when running the addin under debug, as it will stop there every time (if you have an old excel).
I have tried to use reflection on the excel application object to find out there the new function e.g. FILTER is there or not, but standard reflection on the object does not show anything, I guess because it's COM.
Any thoughts on how to do it without the throw + try-catch?

tx

A

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/448fc50b-f5ef-46ce-9725-1d60bf153155%40googlegroups.com.

[Govert van Drimmelen]

Hi Alberto,

 

You can avoid the exception with something like this:

var returnValue = XlCall.TryExcel(614, out object result, new object[] { 1 }, new object[] { true });

// Now examine returnValue, which should be of type XlReturn – it will presumably be XlReturn.XlReturnSuccess for Dynamic Array Excel, otherwise XlReturn.XlReturnFailed or similar for non-DA Excel.

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/9e98c97c-c54d-4a5b-acef-45e31da1e8df%40googlegroups.com.

[Alberto Cherubini]

thanks Govert I'll try that and let you know

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/9e98c97c-c54d-4a5b-acef-45e31da1e8df%40googlegroups.com.

[Alberto Cherubini]

Checked both cases, it works thanks

A.