translating formulas

[Luc Benninger]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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]

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]

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.

 

[Luc Benninger]

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

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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]

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