|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Language independent formulas
I don't understand.
I thought you were putting the formula in all the cells using VBA. From what I've read, this should work in all languages. range("a1").formula = "=sum(a3:a5)" Srgio Martins wrote: 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 -- Dave Peterson |
Language independent formulas
Hi.
If I enter the formula in VBA I have no problem, my problem is that I have a small application in VB.NET that creates the "Excel" file and creates the formula and in that case the cell have the error. See my first post that is the original question, then I try to solve the problem and I found that interesting thing that in VBA the same code works but in the .NET application don't. Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: I don't understand. I thought you were putting the formula in all the cells using VBA. From what I've read, this should work in all languages. range("a1").formula = "=sum(a3:a5)" Sérgio Martins wrote: 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 -- Dave Peterson |
Language independent formulas
I don't know .Net and I didn't see anything in your first post that described
that you were using that. Srgio Martins wrote: Hi. If I enter the formula in VBA I have no problem, my problem is that I have a small application in VB.NET that creates the "Excel" file and creates the formula and in that case the cell have the error. See my first post that is the original question, then I try to solve the problem and I found that interesting thing that in VBA the same code works but in the .NET application don't. Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: I don't understand. I thought you were putting the formula in all the cells using VBA. From what I've read, this should work in all languages. range("a1").formula = "=sum(a3:a5)" Sérgio Martins wrote: 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 -- Dave Peterson -- Dave Peterson |
Language independent formulas
Sorry I forget to say that my small program is in "Microsoft Visual Basic
..Net 2003". "Dave Peterson" wrote: I don't know .Net and I didn't see anything in your first post that described that you were using that. Sérgio Martins wrote: Hi. If I enter the formula in VBA I have no problem, my problem is that I have a small application in VB.NET that creates the "Excel" file and creates the formula and in that case the cell have the error. See my first post that is the original question, then I try to solve the problem and I found that interesting thing that in VBA the same code works but in the .NET application don't. Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: I don't understand. I thought you were putting the formula in all the cells using VBA. From what I've read, this should work in all languages. range("a1").formula = "=sum(a3:a5)" Sérgio Martins wrote: 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 -- Dave Peterson -- Dave Peterson |
Language independent formulas
I still don't know .Net.
Maybe someone else will jump in. Srgio Martins wrote: Sorry I forget to say that my small program is in "Microsoft Visual Basic .Net 2003". "Dave Peterson" wrote: I don't know .Net and I didn't see anything in your first post that described that you were using that. Sérgio Martins wrote: Hi. If I enter the formula in VBA I have no problem, my problem is that I have a small application in VB.NET that creates the "Excel" file and creates the formula and in that case the cell have the error. See my first post that is the original question, then I try to solve the problem and I found that interesting thing that in VBA the same code works but in the .NET application don't. Thanks. Best regards, Sérgio Martins "Dave Peterson" wrote: I don't understand. I thought you were putting the formula in all the cells using VBA. From what I've read, this should work in all languages. range("a1").formula = "=sum(a3:a5)" Sérgio Martins wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Language independent formulas
Hi Sérgio
We have the same problem (the conversion language of SUM-SOMA) in the excel italian version! Can you gently tell us how did you resolve this problem? Thanks |
Sum-Soma
Good Morning We have the same problem to convert sum-soma! Can you gently tell us how did you resolve? Thanks a lot |
| All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com