translating formulas

I am using the code below to translate worksheet formulas to the locale
language. For some formulas this works fine (e.g. SUM), but for others
it doesn't (e.g. MOD). Can anybody explain me why?? Using Excel XP.
Thanks for any responses.
Luc

********* start of sample code ******************

Public Sub Test_Successful()
ActiveSheet.Range("A1").Formula = TranslateFunction("=SUM(B1:B2)")
End Sub

Public Sub Test_Fails()
ActiveSheet.Range("A1").Formula = Translate("=MOD(ROW(),2)=0")
End Sub

Private Function Translate(funcUS As Variant, Optional wb As Workbook)
As Variant
Dim sheet As Worksheet
If wb Is Nothing Then
Set wb = ActiveWorkbook
End If
Set sheet = wb.Sheets.Add
sheet.Visible = False
sheet.Range("A1").Formula = funcUS
TranslateFunction = sheet.Range("A1").FormulaLocal
Application.DisplayAlerts = False
sheet.Delete
Application.DisplayAlerts = True
Set sheet = Nothing
End Function

Luc,

Don't bother. Just enter the formula in VBA in English, and Excel should do
it all for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> I am using the code below to translate worksheet formulas to the locale
> language. For some formulas this works fine (e.g. SUM), but for others
> it doesn't (e.g. MOD). Can anybody explain me why?? Using Excel XP.
> Thanks for any responses.
> Luc
>
> ********* start of sample code ******************
>
> Public Sub Test_Successful()
> ActiveSheet.Range("A1").Formula = TranslateFunction("=SUM(B1:B2)")
> End Sub
>
> Public Sub Test_Fails()
> ActiveSheet.Range("A1").Formula = Translate("=MOD(ROW(),2)=0")
> End Sub
>
> Private Function Translate(funcUS As Variant, Optional wb As Workbook)
> As Variant
> Dim sheet As Worksheet
> If wb Is Nothing Then
> Set wb = ActiveWorkbook
> End If
> Set sheet = wb.Sheets.Add
> sheet.Visible = False
> sheet.Range("A1").Formula = funcUS
> TranslateFunction = sheet.Range("A1").FormulaLocal
> Application.DisplayAlerts = False
> sheet.Delete
> Application.DisplayAlerts = True
> Set sheet = Nothing
> End Function

Also, look at this thread http://tinyurl.com/5v7mz where we discussed this
with you before.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> I am using the code below to translate worksheet formulas to the locale
> language. For some formulas this works fine (e.g. SUM), but for others
> it doesn't (e.g. MOD). Can anybody explain me why?? Using Excel XP.
> Thanks for any responses.
> Luc
>
> ********* start of sample code ******************
>
> Public Sub Test_Successful()
> ActiveSheet.Range("A1").Formula = TranslateFunction("=SUM(B1:B2)")
> End Sub
>
> Public Sub Test_Fails()
> ActiveSheet.Range("A1").Formula = Translate("=MOD(ROW(),2)=0")
> End Sub
>
> Private Function Translate(funcUS As Variant, Optional wb As Workbook)
> As Variant
> Dim sheet As Worksheet
> If wb Is Nothing Then
> Set wb = ActiveWorkbook
> End If
> Set sheet = wb.Sheets.Add
> sheet.Visible = False
> sheet.Range("A1").Formula = funcUS
> TranslateFunction = sheet.Range("A1").FormulaLocal
> Application.DisplayAlerts = False
> sheet.Delete
> Application.DisplayAlerts = True
> Set sheet = Nothing
> End Function

Hi Bob
Thanks for replying.
Think I must be a bit more specific:
I need the translate function to set the formula of a conditional
formatting. In my opinion formulas in a conditional format object must
be set in the local language. Isn't this right?
Luc

Bob Phillips wrote:
> Luc,
>
> Don't bother. Just enter the formula in VBA in English, and Excel should do
> it all for you.
>

The obvious reason from your posting is that you Use Translate with the
second formula and with the first one you use TranslateFunction.

Nonetheless, excel has the built in ability to translate the formula
direclty (which you are using in your function). It is unclear why you are
even using the translatefunction formula.

What your TranslateFormula function is doing is synonymous to saying "He
Luc, what is your name?"

But then maybe this was only a simplified example

--
Regards,
Tom Ogilvy

"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> I am using the code below to translate worksheet formulas to the locale
> language. For some formulas this works fine (e.g. SUM), but for others
> it doesn't (e.g. MOD). Can anybody explain me why?? Using Excel XP.
> Thanks for any responses.
> Luc
>
> ********* start of sample code ******************
>
> Public Sub Test_Successful()
> ActiveSheet.Range("A1").Formula = TranslateFunction("=SUM(B1:B2)")
> End Sub
>
> Public Sub Test_Fails()
> ActiveSheet.Range("A1").Formula = Translate("=MOD(ROW(),2)=0")
> End Sub
>
> Private Function Translate(funcUS As Variant, Optional wb As Workbook)
> As Variant
> Dim sheet As Worksheet
> If wb Is Nothing Then
> Set wb = ActiveWorkbook
> End If
> Set sheet = wb.Sheets.Add
> sheet.Visible = False
> sheet.Range("A1").Formula = funcUS
> TranslateFunction = sheet.Range("A1").FormulaLocal
> Application.DisplayAlerts = False
> sheet.Delete
> Application.DisplayAlerts = True
> Set sheet = Nothing
> End Function

Hi Luc,

I don't use a non-English version of Excel, so I don't 'know' about these
things, but I am assured by those in the know that FormulaLocal is a total
waste of time.

Here is a suggestion:
- create the CF formula in a cell in VB as normal
- create a CF condition and copy that formula over

Here is a sample piece of code that puts =A1>SUM($B$1:$B$10) in I1, and then
sets that as the CF for A1. Just choose an unused cell somewhere

Sub SetCF()
Range("I1").Formula = "=A1>SUM($B$1:$B$10)"
With Range("A1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Range("I1").Formula
.FormatConditions(1).Interior.ColorIndex = 38
End With
Range("I1).Value=""
End Sub

If you try it, please let me know if it works for you

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
> Thanks for replying.
> Think I must be a bit more specific:
> I need the translate function to set the formula of a conditional
> formatting. In my opinion formulas in a conditional format object must
> be set in the local language. Isn't this right?
> Luc
>
> Bob Phillips wrote:
> > Luc,
> >
> > Don't bother. Just enter the formula in VBA in English, and Excel should
do
> > it all for you.
> >

Hi Tom

Yes, what you write is right. I just tried to make a simplified example.
"TranslateFunction" is wrong, I mistyped this function name.

I have to translate the formula because I want to add a new conditional
formula object and there it did not work to use the english formula name.

I would prefer to use a translate function instead of adding a static
mapping for the formulas into all needed languages. But I was very
surprised that for some formulas the translate function doesn't work. I
can't explain, if it's no excel vba bug.

Luc


Tom Ogilvy wrote:
> The obvious reason from your posting is that you Use Translate with the
> second formula and with the first one you use TranslateFunction.
>
> Nonetheless, excel has the built in ability to translate the formula
> direclty (which you are using in your function). It is unclear why you are
> even using the translatefunction formula.
>
> What your TranslateFormula function is doing is synonymous to saying "He
> Luc, what is your name?"
>
> But then maybe this was only a simplified example
>

I wouldn't think that formulas for conditional formatting would need to be
in the language of the locale. but it should be easy for you to check it
out.

--
Regards,
Tom Ogilvy


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
> Thanks for replying.
> Think I must be a bit more specific:
> I need the translate function to set the formula of a conditional
> formatting. In my opinion formulas in a conditional format object must
> be set in the local language. Isn't this right?
> Luc
>
> Bob Phillips wrote:
> > Luc,
> >
> > Don't bother. Just enter the formula in VBA in English, and Excel should
do
> > it all for you.
> >

Hi
I did some testing again. Unfortunately without success. You code does
not work as long as I do not change SUM to SUMME (german). Conditional
formatting objects seem just to support the locale language.
Luc


Bob Phillips wrote:
> Hi Luc,
>
> I don't use a non-English version of Excel, so I don't 'know' about these
> things, but I am assured by those in the know that FormulaLocal is a total
> waste of time.
>
> Here is a suggestion:
> - create the CF formula in a cell in VB as normal
> - create a CF condition and copy that formula over
>
> Here is a sample piece of code that puts =A1>SUM($B$1:$B$10) in I1, and then
> sets that as the CF for A1. Just choose an unused cell somewhere
>
> Sub SetCF()
> Range("I1").Formula = "=A1>SUM($B$1:$B$10)"
> With Range("A1")
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:= _
> Range("I1").Formula
> .FormatConditions(1).Interior.ColorIndex = 38
> End With
> Range("I1).Value=""
> End Sub
>
> If you try it, please let me know if it works for you
>

Excel doesn't work directly with formula names. Built in functions have a
token number so to speak. So if things are as you say, it would indicate
that a table that translates a function name to a token is broken. this
seems unlikely. similarly, it seems unlikely that excel would use a
completely different approach to deal with conditional formatting or data
validation.


--
Regards,
Tom Ogilvy


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom
>
> Yes, what you write is right. I just tried to make a simplified example.
> "TranslateFunction" is wrong, I mistyped this function name.
>
> I have to translate the formula because I want to add a new conditional
> formula object and there it did not work to use the english formula name.
>
> I would prefer to use a translate function instead of adding a static
> mapping for the formulas into all needed languages. But I was very
> surprised that for some formulas the translate function doesn't work. I
> can't explain, if it's no excel vba bug.
>
> Luc
>
>
> Tom Ogilvy wrote:
> > The obvious reason from your posting is that you Use Translate with the
> > second formula and with the first one you use TranslateFunction.
> >
> > Nonetheless, excel has the built in ability to translate the formula
> > direclty (which you are using in your function). It is unclear why you
are
> > even using the translatefunction formula.
> >
> > What your TranslateFormula function is doing is synonymous to saying
"He
> > Luc, what is your name?"
> >
> > But then maybe this was only a simplified example
> >

Luc,

If you use SUM in VBA, does this not create SUMME in I1, and then pick that
up for the CF.

As Tom suggests (I think if I read him correctly), you could try putting the
English formula directly into CF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Luc Benninger" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I did some testing again. Unfortunately without success. You code does
> not work as long as I do not change SUM to SUMME (german). Conditional
> formatting objects seem just to support the locale language.
> Luc
>
>
> Bob Phillips wrote:
> > Hi Luc,
> >
> > I don't use a non-English version of Excel, so I don't 'know' about
these
> > things, but I am assured by those in the know that FormulaLocal is a
total
> > waste of time.
> >
> > Here is a suggestion:
> > - create the CF formula in a cell in VB as normal
> > - create a CF condition and copy that formula over
> >
> > Here is a sample piece of code that puts =A1>SUM($B$1:$B$10) in I1, and
then
> > sets that as the CF for A1. Just choose an unused cell somewhere
> >
> > Sub SetCF()
> > Range("I1").Formula = "=A1>SUM($B$1:$B$10)"
> > With Range("A1")
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlExpression, Formula1:= _
> > Range("I1").Formula
> > .FormatConditions(1).Interior.ColorIndex = 38
> > End With
> > Range("I1).Value=""
> > End Sub
> >
> > If you try it, please let me know if it works for you
> >

> If you use SUM in VBA, does this not create SUMME in I1, and then pick that
> up for the CF.
No, in VBA, SUM stays SUM if you get the Formula property of the cell.
But FormulaLocal would return the translated formula. And that's exactly
what I tried to do (look at the code in my first post).

> As Tom suggests (I think if I read him correctly), you could try putting the
> English formula directly into CF.
This does not work.

Anyway, I think I now solved my problem. Here is what I found out:

For the Formula Property of Conditional format object:
Using english formulas does not work, but with the Translate function
from my first post, everything seems ok! This function translates the
formulas into the locale language and replaces commas with the locale
list seperator character.

For the Formula property of a cell:
English may always be used (comma is seperator). If the formulas are
translated into the locale language, commas MUST NOT be replaced with
the local list seperator!!! Quite confusing, not?? As I first was
testing my translate function with cells formula property, I got errors
if the formula expression contained commas.


Thanks again for all input I got.
Luc