Language independent formulas

[Sergio Martins]

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

 

[Stefi]

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:

 

[Sérgio Martins]

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]

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:

 

[Sérgio Martins]

Sorry I forget to say that A is a constant like:

Private Const A = 1

So that's not the problem.

Sérgio

 

[Sérgio Martins]

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

 

[Dave Peterson]

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]

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]

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]

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]

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)"

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

 

[Sérgio Martins]

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]

I don't know .Net and I didn't see anything in your first post that described
that you were using that.

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

 

[Sérgio Martins]

Sorry I forget to say that my small program is in "Microsoft Visual Basic
..Net 2003".

 

[Dave Peterson]

I still don't know .Net.

Maybe someone else will jump in.

Sorry I forget to say that my small program is in "Microsoft Visual Basic
.Net 2003".

 

[Francesco]

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

 

[Fra]

Good Morning

We have the same problem to convert sum->soma!
Can you gently tell us how did you resolve?

Thanks a lot