VBA formula pasted into cell returns err508

[Flick]

I’m using a vba macro to add a formula to a cell which appears to paste into the cell correctly, however it returns an ERR508. If I go into the cell and overtype any character and press enter, the formula works correctly. For example, this is the formula entered by the macro.

=IF(($E$5088>$D$5088),($E$5088-$D$5088),0)

If i remove the trailing “)” and press enter the formula works. Actually if I replace any character and press enter it works.

Here is the code I’m using:

	dCell = right(dSheet.getCellByposition(3,dRow).absolutename,7)

	eCell = right(dSheet.getCellByposition(4,dRow).absolutename,7)

	formulaTxt = "=if((" & eCell & ">" & dCell & "),(" & eCell & "-" & dCell & "),0)"

	dSheet.getCellByposition(5,dRow).formula = formulaTxt

Any help would be greatly appreciated.

Thanks,
Flick

[Lupp]

Assigning the formula to the .Formula property of a cell may not trigger a full recognition process. Since for the internal locale independent as well as for the persistent (file) representation the semicolon is the mandatory parameter separator, the formula will not parse correctly. Having done any editing of the cell via the UI the formula will be parsed completely and transmitted to the .FormulaLocal property using the separator of the locale, but to the .Formula property using the locale independent separator.
I didn’t test or study everything yet, but I feel sure it’s that simple.
Abandon that silly comma as parameter separator. Even MS should know that the majority of locales use it as the decimal separator.

[Flick]

Lupp, You are a genius. Solved my problem. Thanks a million!

[PYS]

Hi

Are you sure that Tools▸Options▸Calc▸Formula▸Separators▸Function is , ?

Because if it isn’t, you have to use this parameter instead of the comma in your formula, e.g. for a french UI:

formulaTxt = "=if((" & eCell & ">" & dCell & ");(" & eCell & "-" & dCell & ");0)"

Regards

[Flick]

Yes. It is set to a comma. And as I mentioned in my post, the formula works fine if i simply replace any character and press enter.

[Lupp]

I meanwhile tested my proposition including some details not yet posted, and it proved.
Therefore I post the main part of my former comment as an answer now.

Assigning the formula to the .Formula property of a cell will not trigger a full recognition process. Since for the internal locale independent as well as for the persistent (file) representation the semicolon is the mandatory parameter separator, the formula will not parse correctly. Having done any editing of the cell via the UI in Calc the formula will be parsed completely and transmitted to the .FormulaLocal property using the separator of the locale, but to the .Formula property using the locale independent separator.

(Unsolicited advice:
Abandon that silly comma as parameter separator. Even MS should know that the majority of locales use the comma as the decimal separator.
See also this thread.)