Problem with ; and , in formulas modified with an add-in

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

Problem with ; and , in formulas modified with an add-in
 
gui




Posts: 13
Joined: 2016-05-11
Hi,

I have an add-in written with Add-in Express for VCL.

It set ups a command in Excel that modifies formulas in ranges.

I have the following problem with use of comma or semi-colon in formulas.
If I use:

IRange.Cells.Formula := '=SUM(A1,A7)'


it works.

If I use:

IRange.Cells.Formula := '=SUM(A1;A7)'


I get an OLE error "0x800A03EC".

What is strange is that with the "=SUM(A1,A7)" Excel then shows "=SUM(A1;A7)" in the cell!

I thought it would be a locale problem, so I used:
ExcelAPP.International[xlListSeparator]

IRange.Cells.Formula := '=SUM(A1' + ExcelAPP.International[xlListSeparator] +  'A7)';


But same OLE error "0x800A03EC" because ExcelAPP.International[xlListSeparator] returns ; on my system.

I'm afraid that this OLE error "0x800A03EC" will appear for my international customers.

Is it safe in VBA through Delphi to always use the comma in formulas modified by code?

If no, do you have some suggestion?

Thanks!
Posted 25 Mar, 2018 05:58:57 Top
Andrei Smolin


Add-in Express team


Posts: 15738
Joined: 2006-05-11
Hello Gui,

When constructing the value of a Formula property, you work in terms of the English locale. Whence, you use ",", not ";". In the Excel UI you see the value of the FormulaLocal property.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Mar, 2018 09:21:09 Top
gui




Posts: 13
Joined: 2016-05-11
This makes sense! Thank you for the info!
Posted 26 Mar, 2018 19:16:02 Top
Andrei Smolin


Add-in Express team


Posts: 15738
Joined: 2006-05-11
You are welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 Mar, 2018 04:09:04 Top