| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Language independent formulas
Hi.
I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
If you run the code in a Portuguese Excel then you should use
objWs.Cells(6, A).FormulaLocal = "=SOMA(A3:A5)" If you use in an Excel of any language objWs.Cells(6, A).Formula = "=SUM(A3:A5)" then it'll translate it to its national language equivalent, e.g. =SOMA(A3:A5) in case of a Portuguese version, or =SZUM(A3:A5) in my Hungarian version, so this is the way you are looking for. You cannot use objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" in an English version. Regards, Stefi Sergio Martins ezt *rta: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Hi.
Thanks for your help, but I try to use objWs.Cells(6, A).Formula = "=SUM(A3:A5)" in a Portuguese "Excel" but when I open the "Excel" file the cell have the error. I must now the language of the "Excel" in the machine and then use "Soma" or "SUM" if the "Excel" is Portuguese or "English"? Thanks. Best regards, Sérgio Martins "Stefi" wrote: If you run the code in a Portuguese Excel then you should use objWs.Cells(6, A).FormulaLocal = "=SOMA(A3:A5)" If you use in an Excel of any language objWs.Cells(6, A).Formula = "=SUM(A3:A5)" then it'll translate it to its national language equivalent, e.g. =SOMA(A3:A5) in case of a Portuguese version, or =SZUM(A3:A5) in my Hungarian version, so this is the way you are looking for. You cannot use objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" in an English version. Regards, Stefi Sergio Martins ezt *rta: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Now I see that your problem doesn't come from language versions but wrong
syntax. The right syntax is objWs.Cells(6, "A").Formula = "=SUM(A3:A5)" Stefi Sérgio Martins ezt *rta: Hi. Thanks for your help, but I try to use objWs.Cells(6, A).Formula = "=SUM(A3:A5)" in a Portuguese "Excel" but when I open the "Excel" file the cell have the error. I must now the language of the "Excel" in the machine and then use "Soma" or "SUM" if the "Excel" is Portuguese or "English"? Thanks. Best regards, Sérgio Martins "Stefi" wrote: If you run the code in a Portuguese Excel then you should use objWs.Cells(6, A).FormulaLocal = "=SOMA(A3:A5)" If you use in an Excel of any language objWs.Cells(6, A).Formula = "=SUM(A3:A5)" then it'll translate it to its national language equivalent, e.g. =SOMA(A3:A5) in case of a Portuguese version, or =SZUM(A3:A5) in my Hungarian version, so this is the way you are looking for. You cannot use objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" in an English version. Regards, Stefi Sergio Martins ezt *rta: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Sorry I forget to say that A is a constant like:
Private Const A = 1 So that's not the problem. Sérgio "Stefi" wrote: Now I see that your problem doesn't come from language versions but wrong syntax. The right syntax is objWs.Cells(6, "A").Formula = "=SUM(A3:A5)" Stefi Sérgio Martins ezt *rta: Hi. Thanks for your help, but I try to use objWs.Cells(6, A).Formula = "=SUM(A3:A5)" in a Portuguese "Excel" but when I open the "Excel" file the cell have the error. I must now the language of the "Excel" in the machine and then use "Soma" or "SUM" if the "Excel" is Portuguese or "English"? Thanks. Best regards, Sérgio Martins "Stefi" wrote: If you run the code in a Portuguese Excel then you should use objWs.Cells(6, A).FormulaLocal = "=SOMA(A3:A5)" If you use in an Excel of any language objWs.Cells(6, A).Formula = "=SUM(A3:A5)" then it'll translate it to its national language equivalent, e.g. =SOMA(A3:A5) in case of a Portuguese version, or =SZUM(A3:A5) in my Hungarian version, so this is the way you are looking for. You cannot use objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" in an English version. Regards, Stefi Sergio Martins ezt *rta: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Hi.
I found another interesting thing. If I create the "Excel" file in a Portuguese version with the cell filled with: objWs.Cells(6, 1).Formula = "=SUM(A3:A5)" When I open the file the cell have an error. If then I go to VBA and fill another like that: objWs.Cells(7, 1).Formula = "=SUM(A3:A5)" And then go back to my worksheet, the new cell is correct. So I have two cells one with error and one correct. Then I go back to VBA and try to see the Formula of both cells and they are the same. Can anyone explain that? My problem should be same property that I forget to activate when I create the "Excel" file? Thanks in advance. Best regards, Sérgio Martins "Sergio Martins" wrote: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
What error do you see?
Maybe it's not the formula causing the error--maybe it's one (or more) of the values in A3:A5. What are in those cells? Srgio Martins wrote: Hi. I found another interesting thing. If I create the "Excel" file in a Portuguese version with the cell filled with: objWs.Cells(6, 1).Formula = "=SUM(A3:A5)" When I open the file the cell have an error. If then I go to VBA and fill another like that: objWs.Cells(7, 1).Formula = "=SUM(A3:A5)" And then go back to my worksheet, the new cell is correct. So I have two cells one with error and one correct. Then I go back to VBA and try to see the Formula of both cells and they are the same. Can anyone explain that? My problem should be same property that I forget to activate when I create the "Excel" file? Thanks in advance. Best regards, Sérgio Martins "Sergio Martins" wrote: Hi. I have a small program that creates by code a Excel file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have Excel in Portuguese or Excel in English, if the code fill the cell with Soma() when the Excel file is created in Portuguese Excel the file is valid in any Excel, however if the file is created in English Excel the cell have an error. I also try to set the formula like SUM() but in this case I got the error if the file is created in Portuguese Excel. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a Excel file with formulas that are independent from the language of the Excel in the machine that create the file? Thanks in advance. Best regards Sérgio Martins -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Hi.
Those cells are not the problem tey only have the values 1, 2 and 3. The content of the cell layout is like #Name? and the tooltip is something like The formula have not recognized text My error is in Portuguese that's why I say "something like". Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: What error do you see? Maybe it's not the formula causing the error--maybe it's one (or more) of the values in A3:A5. What are in those cells? Sérgio Martins wrote: Hi. I found another interesting thing. If I create the "Excel" file in a Portuguese version with the cell filled with: objWs.Cells(6, 1).Formula = "=SUM(A3:A5)" When I open the file the cell have an error. If then I go to VBA and fill another like that: objWs.Cells(7, 1).Formula = "=SUM(A3:A5)" And then go back to my worksheet, the new cell is correct. So I have two cells one with error and one correct. Then I go back to VBA and try to see the Formula of both cells and they are the same. Can anyone explain that? My problem should be same property that I forget to activate when I create the "Excel" file? Thanks in advance. Best regards, Sérgio Martins "Sergio Martins" wrote: Hi. I have a small program that creates by code a âœExcelâ file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have âœExcelâ in Portuguese or âœExcelâ in English, if the code fill the cell with âœSoma()â when the âœExcelâ file is created in Portuguese âœExcelâ the file is valid in any âœExcelâ, however if the file is created in English âœExcelâ the cell have an error. I also try to set the formula like âœSUM()â but in this case I got the error if the file is created in Portuguese âœExcelâ. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a âœExcelâ file with formulas that are independent from the language of the âœExcelâ in the machine that create the file? Thanks in advance. Best regards Sérgio Martins -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
And what do you see in the formulabar when you select that range?
Does the formula look correct in your language? If you select the cell, hit F2, then enter (and nothing more), what happens? If you force a recalculation (F9 or one of its variants), does the formula get evaluated correctly? ======== If I recall correctly, there have been other posts that describe this kind of problem -- and not all are related to language differences! I'm not sure that I've ever seen a reason why it happens, but one suggestion I've seen is to add a little bit to your code. After you populate the worksheet with all your formulas: objWs.cells.replace what:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False This changes all the = signs to = signs and give excel another chance to reevaluated all the formulas. Srgio Martins wrote: Hi. Those cells are not the problem tey only have the values 1, 2 and 3. The content of the cell layout is like #Name? and the tooltip is something like The formula have not recognized text My error is in Portuguese that's why I say "something like". Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: What error do you see? Maybe it's not the formula causing the error--maybe it's one (or more) of the values in A3:A5. What are in those cells? Sérgio Martins wrote: Hi. I found another interesting thing. If I create the "Excel" file in a Portuguese version with the cell filled with: objWs.Cells(6, 1).Formula = "=SUM(A3:A5)" When I open the file the cell have an error. If then I go to VBA and fill another like that: objWs.Cells(7, 1).Formula = "=SUM(A3:A5)" And then go back to my worksheet, the new cell is correct. So I have two cells one with error and one correct. Then I go back to VBA and try to see the Formula of both cells and they are the same. Can anyone explain that? My problem should be same property that I forget to activate when I create the "Excel" file? Thanks in advance. Best regards, Sérgio Martins "Sergio Martins" wrote: Hi. I have a small program that creates by code a âœExcelâ file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have âœExcelâ in Portuguese or âœExcelâ in English, if the code fill the cell with âœSoma()â when the âœExcelâ file is created in Portuguese âœExcelâ the file is valid in any âœExcelâ, however if the file is created in English âœExcelâ the cell have an error. I also try to set the formula like âœSUM()â but in this case I got the error if the file is created in Portuguese âœExcelâ. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a âœExcelâ file with formulas that are independent from the language of the âœExcelâ in the machine that create the file? Thanks in advance. Best regards Sérgio Martins -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Language independent formulas
Hi.
In the formulabar when I select that range I see the formula like "=SUM(A3:A5)", what is not correct in my language. But the when selecting the cell that I create using VBA in the formulabar I see "=SOMA(A3:A5)", what is correct in my language. If I hit "F2" and "Enter" everything looks the same, the error stills in the cell. If I force a recalculation everything looks the same, the error stills in the cell. I also try implement the code that changes all the = signs to = signs and give excel another chance to reevaluated all the formulas and everything looks the same, the error stills in the cell. Any other options? Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: And what do you see in the formulabar when you select that range? Does the formula look correct in your language? If you select the cell, hit F2, then enter (and nothing more), what happens? If you force a recalculation (F9 or one of its variants), does the formula get evaluated correctly? ======== If I recall correctly, there have been other posts that describe this kind of problem -- and not all are related to language differences! I'm not sure that I've ever seen a reason why it happens, but one suggestion I've seen is to add a little bit to your code. After you populate the worksheet with all your formulas: objWs.cells.replace what:="=", Replacement:="=", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False This changes all the = signs to = signs and give excel another chance to reevaluated all the formulas. Sérgio Martins wrote: Hi. Those cells are not the problem tey only have the values 1, 2 and 3. The content of the cell layout is like #Name? and the tooltip is something like The formula have not recognized text My error is in Portuguese that's why I say "something like". Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: What error do you see? Maybe it's not the formula causing the error--maybe it's one (or more) of the values in A3:A5. What are in those cells? Sérgio Martins wrote: Hi. I found another interesting thing. If I create the "Excel" file in a Portuguese version with the cell filled with: objWs.Cells(6, 1).Formula = "=SUM(A3:A5)" When I open the file the cell have an error. If then I go to VBA and fill another like that: objWs.Cells(7, 1).Formula = "=SUM(A3:A5)" And then go back to my worksheet, the new cell is correct. So I have two cells one with error and one correct. Then I go back to VBA and try to see the Formula of both cells and they are the same. Can anyone explain that? My problem should be same property that I forget to activate when I create the "Excel" file? Thanks in advance. Best regards, Sérgio Martins "Sergio Martins" wrote: Hi. I have a small program that creates by code a ââ¬ÅExcelââ¬Â file. One of the cells is fill with a sum formula. My problem is: The program can be executed in machines that have ââ¬ÅExcelââ¬Â in Portuguese or ââ¬ÅExcelââ¬Â in English, if the code fill the cell with ââ¬ÅSoma()ââ¬Â when the ââ¬ÅExcelââ¬Â file is created in Portuguese ââ¬ÅExcelââ¬Â the file is valid in any ââ¬ÅExcelââ¬Â, however if the file is created in English ââ¬ÅExcelââ¬Â the cell have an error. I also try to set the formula like ââ¬ÅSUM()ââ¬Â but in this case I got the error if the file is created in Portuguese ââ¬ÅExcelââ¬Â. To create de formula in the cell I use the following code: objWs.Cells(6, A).Formula = "=SOMA(A3:A5)" Can anyone explain, how can I create a ââ¬ÅExcelââ¬Â file with formulas that are independent from the language of the ââ¬ÅExcelââ¬Â in the machine that create the file? Thanks in advance. Best regards Sérgio Martins -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Language independent Pivot settings | Excel Programming | |||
| Insertion of formulas in foreign language in english version of ex | Excel Discussion (Misc queries) | |||
| language support in excel sheet using a third party language tool | Excel Worksheet Functions | |||
| How to write a language independent formula | Excel Programming | |||
| Excel Language bug formulas | Excel Programming | |||
Hybrid Mode
