Translate the .Formula1 in Conditional Formats to english fo

[Juan Pablo González]

In VBA, one good thing is that you can use the local language formulas or, you can code the english formulas, with the same results.

However, this does not apply to all objects, very unfortunately. Two that come to mind right now are Conditional formatting and Data Validation.

They both return the formula in the local language. This could be "easily" translated using a Sub, assigning the FormulaLocal to this cell, then, using the Formula, getting it in english, right ? well, I need to do this in a UDF... and, since a UDF can't make changes to the cells, I'm stuck.

I have a big table, downloaded from http://www.bmsltd.co.uk that has the "formula translations", that is good, I can do a massive substitute, but I feel I'm complicating things.

Any ideas on how to do this ?

 

[XL-Dennis]

Juan,

For some time age I made an Add-in, which I use to translate from
- English to Swedish and
- Swedish to English.

The steps are (in the userform):
- Paste in the formula to be translated in a textfield.
- Push the button for translation, the result is shown in a label-caption
- Push another button and the formula is copied to the active cell with an apostrophe in front.

However, functions or formulas based on Analysis Toolpak (since it´s an Add-in") cannot be translated in a easy way.

Another important point is that it can´t be used in an english version of XL only non-english but for all version from 97 to XP

I´ve been thinking of developing this Add-in a little bit further by using a setting which control which language all text and helpfile will be showed in. But for this step I need help with translation.

Back to the mind!

Kind regards,
Dennis

 

[Juan Pablo González]

Thanks Dennis, but, are you translating it without using a worksheet? that's the "thing" The thing that sucks in this, is that the FormatConditions(i).Formula1 (And .Formula2 obviously) return the FormulaLocal version of the formula, and there's no other method of getting that !.

After I get the formula, I try to use the Evaluate method in order to see if that condition is met or not, but that creates an error because Excel doesn't recognize those formulas (!), hence, my small issue...

If you need help with the translation let me know !

 

[XL-Dennis]

Juan,

The thing that sucks in this, is that the FormatConditions(i).Formula1 (And .Formula2 obviously) return the FormulaLocal version of the formula, and there's no other method of getting that !.

I did´nt wrote it explicit but no, as far as I know, it´s not possible...I´ve been also testing with names but without any success

If you need help with the translation let me know !

For spanish? Great and thanks

All the best from Östersund,
Dennis

 

[Juan Pablo González]

Ok, so the brute force method would have to work... how's that ? the list I was talking about. I have 6 languages there, I would have to use the Language settings, somehow, to find out what Excel version (And if I have it included !) is running in there... but, now, the other thing that's making me think

With a formula like

=COUNTIF(A:A,A1)

it's "easy" to spot the function name, right ? after the "=" and before the "(". Good. Now, more complex formulas:

=IF(SUM(A1:A2)=1,VLOOKUP(A1,{1,2;3,4},2,0),"My date is"&TEXT(TODAY(),"mmm"))

Here we have this formulas: IF, SUM, VLOOKUP, TEXT and TODAY. Known "begginers" of formulas:

"=", "(", ",", "&"

the "ender" is always a "("

Can I assume this ? ahh... I need to get some sleep...

 

[PaddyD]

Juan,

Re the assumptions about distinguishing between formula names etc, how would you go about dealing with situations where the formula was itself the result of a formula?

e.g.

Book7
	A	B	C	D
1	Data
2	2	3	4
3	Table
4	1	=sum(a2:c2)*2
5	2	=sum(a2:c2)*3
6	3	=sum(a2:c2)*4
7
8	Formula	27
9
Sheet1

Paddy

 

[Juan Pablo González]

Well, those kind of formulas are not very common AND, conditional formatting formulas result in either a TRUE or FALSE decition. So, if the translation goes right, in your example I should see on the sheet this:

=EVAL(BUSCARV(A2,A4:B6,2,0))

translated this:

=EVAL(VLOOKUP(A2,A4:B6,2,0))

wich would be evaluated correctly by Excel. This, would then, return 27 (In the example too), wich would be assumed as TRUE, kicking the format that has that formula... so, I guess this shouldn't be a problem (Hopefulyl)

 

[Aladin Akyurek]

One thing that you should track is numbers between double quotes:

Ex.

=COUNTIF(A1:A10,"2.24")

will not work if translated say to Dutch using simple function name substitution:

=AANTAL.ARG(A1:A10,"2.24")



EDIT. It's AANTAL.ALS, not AANTAL.ARG (which is COUNTA).
This message was edited by Aladin Akyurek on 2002-09-11 18:15

 

[Juan Pablo González]

On 2002-09-11 17:43, Aladin Akyurek wrote:

One thing that you should track is numbers between double quotes:

Ex.

=COUNTIF(A1:A10,"2.24")

will not work if translated say to Dutch using simple function name substitution:

=AANTAL.ARG(A1:A10,"2.24")

Really ? wow... what does the formula look like ?

I am also worried about different international separator... for example, in "some" spanish versions (Yes, some !), the IF I put before would look like this:

=SI(SUMA(A1:A2)=1;BUSCARV(A1;{1;23;4};2;0);"My date is"&TEXTO(HOY();"mmm"))

 

[Aladin Akyurek]

On 2002-09-11 18:02, Juan Pablo G. wrote:

On 2002-09-11 17:43, Aladin Akyurek wrote:

One thing that you should track is numbers between double quotes:

Ex.

=COUNTIF(A1:A10,"2.24")

will not work if translated say to Dutch using simple function name substitution:

=AANTAL.ARG(A1:A10,"2.24")

Really ? wow... what does the formula look like ?

I am also worried about different international separator... for example, in "some" spanish versions (Yes, some !), the IF I put before would look like this:

=SI(SUMA(A1:A2)=1;BUSCARV(A1;{1;23;4};2;0);"My date is"&TEXTO(HOY();"mmm"))

=COUNTIF(A1:A10,">=3.6")

should be translated as

=AANTAL.ALS(A1:A10,">=3,6")

The issue is that the decimal separator of a number in double quotes are not translated by Excel from dot to comma. (.ARG was my mistake . It's .ALS).