Translate the .Formula1 in Conditional Formats to english fo

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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 ?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Thanks Dennis, but, are you translating it without using a worksheet? that's the "thing" :biggrin: 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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
ABCD
1Data
2234
3Table
41=sum(a2:c2)*2
52=sum(a2:c2)*3
63=sum(a2:c2)*4
7
8Formula27
9
Sheet1


Paddy
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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 :biggrin:. It's .ALS).
 

Forum statistics

Threads
1,144,278
Messages
5,723,463
Members
422,498
Latest member
KAT112014

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top