Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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 =A1SUM($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 = "=A1SUM($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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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 =A1SUM($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 = "=A1SUM($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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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" wrote in message ... 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 =A1SUM($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 = "=A1SUM($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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
translating formulas
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Translating | Excel Discussion (Misc queries) | |||
translating lotus 123 formulas into excel | Excel Worksheet Functions | |||
Translating formulas | Excel Discussion (Misc queries) | |||
Translating spreadsheet formula to VBA | Excel Programming | |||
Overflow when translating from C++ HELP!! | Excel Programming |