Add-Express UDFs and Windows Region Settings

Add-in Express™ Support Service
That's what is more important than anything else

Add-Express UDFs and Windows Region Settings
 
William Hughes




Posts: 20
Joined: 2014-03-12
We are using an Add-In Express automation add-in to develop custom worksheet formulas (UDFs) for Microsoft Excel. We are having a problem when changing to Windows region settings that cause a change in the delimiters used in Excel formulas. As an example, changing from US settings to French Canadian.

The issue comes about when a semicolon is used as a delimiter in formulas instead of the standard comma. We can pass in absolute values such as strings into the formula but the UDF completely fails when specifying a cell address which contains a value.

In other words =MyUDF(1000; 1) will work fine but
=MyUDF(A2;1) will fail with a #VALUE returned to the cell. The A2 cell contains the value 1000

Our code in VB.NET for the MYUDF function is as follows

Public Function MyUDF(ByVal Input1 As String, ByVal Option1 As String) As Object

Everything works normally with US settings, but after changing to a region with a semicolon delimiter debugging will fail (returning #VALUE) without even stepping into the public function.

Please advise if you have any experience with this.
Posted 23 Aug, 2016 16:07:06 Top
Andrei Smolin


Add-in Express team


Posts: 16061
Joined: 2006-05-11
Hello William,

Does the issue occur if you restart Excel after changing the region settings?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 24 Aug, 2016 02:18:09 Top
William Hughes




Posts: 20
Joined: 2014-03-12
Andre,

Thanks for your reply.

The problem still happens after restarting Excel or rebooting. We've also tried this on both 32 and 64 bit versions of Excel.

Best Regards,
William Hughes
Posted 24 Aug, 2016 09:26:54 Top
Andrei Smolin


Add-in Express team


Posts: 16061
Joined: 2006-05-11
Hello William,

I can't reproduce the issue if I change the List Separator setting in Control Panel | Region | Formats | Additional settings... | Numbers. After changing this settings I see that the formulas have changed to reflect the new list separator.

Note that I pass "1000" to the UDF, not 1000. Similarly, the cell contains "1000", not 1000.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 24 Aug, 2016 09:59:13 Top
William Hughes




Posts: 20
Joined: 2014-03-12
Andrei,

I can cause the problem by changing the region to "French Canadian" and restarting Excel. This apparently changes additional things in addition to the separator.

I don't have the problem by just changing the separator to semi-colon in the US Version.

Thanks,
William Hughes
Posted 24 Aug, 2016 10:36:04 Top
Andrei Smolin


Add-in Express team


Posts: 16061
Joined: 2006-05-11
William,

I've reproduced the issue. To solve it, declare the first parameter of the UDF as Object.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 24 Aug, 2016 10:46:41 Top
William Hughes




Posts: 20
Joined: 2014-03-12
Andrei,

I see that setting the type to object will get us into the function.

There will be additional changes required to sense that the object is an Excel Range and then extract the first value in that range for the rest of the function code to work.

It's odd that this works differently for certain region settings vs US. I think a lot of users will not be aware of this.

Thanks,
William Hughes
Posted 24 Aug, 2016 11:59:52 Top
Andrei Smolin


Add-in Express team


Posts: 16061
Joined: 2006-05-11
Hello William,

I agree. This isn't something that you would expect.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 25 Aug, 2016 05:14:55 Top