oovbaapi/ooo/vba/excel/XRange.idl | 2 ++
sc/source/ui/vba/vbarange.cxx | 33 ++++++++++++++++++++++++++++-----
sc/source/ui/vba/vbarange.hxx | 4 ++++
3 files changed, 34 insertions(+), 5 deletions(-)
New commits:
commit 15a97bcaf17f0f3bc64efd1239c365868480ec43
Author: Eike Rathke <er...@redhat.com>
AuthorDate: Fri Apr 9 13:19:37 2021 +0200
Commit: Eike Rathke <er...@redhat.com>
CommitDate: Fri Apr 9 16:27:09 2021 +0200
[API-CHANGE] tdf#141543 VBA Range.Formula Range.FormulaR1C1 non-localized
The VBA compatibility Range.Formula and Range.FormulaR1C1
properties wrongly used localized formula expressions as if they
were Range.FormulaLocal and Range.FormulaR1C1Local. That worked in
English UI and locales, but not in translated UI or with locale
dependent different separators, imported Excel documents using
these properties failed there.
Instead, use English formula expressions and separators, and
additionally implement Range.FormulaLocal and
Range.FormulaR1C1Local for localized formula expressions.
See
https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula
https://docs.microsoft.com/en-us/office/vba/api/excel.range.formular1c1
https://docs.microsoft.com/en-us/office/vba/api/excel.range.formulalocal
https://docs.microsoft.com/en-us/office/vba/api/excel.range.formular1c1local
Unfortunately this change means for macros created in LibreOffice
that relied on the erroneous beaviour in a localized environment
those macros will cease to work, the remedy in these cases is to
replace setting Formula and FormulaR1C1 attributes with
FormulaLocal and FormulaR1C1Local instead. Obtaining formulas
never worked reliably unless the document's native grammar was
very similar to the API grammar (English UI function names,
English locale and separators, address convention).
For this to work a prerequisite is
commit d0b4719ca3d4608bcb7431dbeb097146dd5a5127
CommitDate: Wed Apr 7 02:22:54 2021 +0200
Related: tdf#128334 Make VBA Range getFormula(R1C1) work not only
by accident
Change-Id: Ifce9ac7557b6a3703d47ee81b57dd8246f3fc3ca
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/113846
Reviewed-by: Eike Rathke <er...@redhat.com>
Tested-by: Jenkins
diff --git a/oovbaapi/ooo/vba/excel/XRange.idl
b/oovbaapi/ooo/vba/excel/XRange.idl
index 8630b87241ba..32f75a309795 100644
--- a/oovbaapi/ooo/vba/excel/XRange.idl
+++ b/oovbaapi/ooo/vba/excel/XRange.idl
@@ -59,6 +59,8 @@ interface XRange
[attribute] any Formula;
[attribute] any FormulaArray;
[attribute] any FormulaR1C1;
+ [attribute] any FormulaLocal;
+ [attribute] any FormulaR1C1Local;
[attribute, readonly] long Count;
[attribute, readonly] long Row;
[attribute, readonly] long Column;
diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx
index bb732f56feb8..4837f5525aa3 100644
--- a/sc/source/ui/vba/vbarange.cxx
+++ b/sc/source/ui/vba/vbarange.cxx
@@ -1669,31 +1669,54 @@ ScVbaRange::getFormulaValue(
formula::FormulaGrammar::Grammar eGram )
}
+uno::Any
+ScVbaRange::getFormula()
+{
+ return getFormulaValue( formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 );
+}
+
void
ScVbaRange::setFormula(const uno::Any &rFormula )
{
- // #FIXME converting "=$a$1" e.g. CONV_XL_A1 -> CONV_OOO
// results in "=$a$1:a1", temporarily disable conversion
- setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
+ setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 );
}
uno::Any
ScVbaRange::getFormulaR1C1()
{
- return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
+ return getFormulaValue( formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1 );
}
void
ScVbaRange::setFormulaR1C1(const uno::Any& rFormula )
{
- setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
+ setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1 );
}
uno::Any
-ScVbaRange::getFormula()
+ScVbaRange::getFormulaLocal()
{
return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
}
+void
+ScVbaRange::setFormulaLocal(const uno::Any &rFormula )
+{
+ setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
+}
+
+uno::Any
+ScVbaRange::getFormulaR1C1Local()
+{
+ return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
+}
+
+void
+ScVbaRange::setFormulaR1C1Local(const uno::Any& rFormula )
+{
+ setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
+}
+
sal_Int32
ScVbaRange::getCount()
{
diff --git a/sc/source/ui/vba/vbarange.hxx b/sc/source/ui/vba/vbarange.hxx
index 59404f9a730c..2e9b71746879 100644
--- a/sc/source/ui/vba/vbarange.hxx
+++ b/sc/source/ui/vba/vbarange.hxx
@@ -178,6 +178,10 @@ public:
virtual void SAL_CALL setFormulaArray(const css::uno::Any& rFormula)
override;
virtual css::uno::Any SAL_CALL getFormulaR1C1() override;
virtual void SAL_CALL setFormulaR1C1( const css::uno::Any &rFormula )
override;
+ virtual css::uno::Any SAL_CALL getFormulaLocal() override;
+ virtual void SAL_CALL setFormulaLocal( const css::uno::Any &rFormula )
override;
+ virtual css::uno::Any SAL_CALL getFormulaR1C1Local() override;
+ virtual void SAL_CALL setFormulaR1C1Local( const css::uno::Any &rFormula
) override;
virtual ::sal_Int32 SAL_CALL getCount() override;
virtual ::sal_Int32 SAL_CALL getRow() override;
virtual ::sal_Int32 SAL_CALL getColumn() override;
_______________________________________________
Libreoffice-commits mailing list
libreoffice-comm...@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
