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 ?

 

[Juan Pablo González]

On 2002-09-11 18:14, Aladin Akyurek wrote:
=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 /board/images/smiles/icon_biggrin.gif. It's .ALS).

Hey, don't confuse me more ! I forgot about that, decimal separator as well... I'm now curious about something, can you try this please ? in the VB Editor, press Control G (Make sure you have an (empty) worksheet selected), and put this in there and press Enter:

ActiveCell.Formula = "=COUNTIF(A1:A10,"">=3.6"")"

Does the formula appear correctly in Excel ? I mean, does it work ? I would think that it should ! or MS is really missing the point here !

 

[Aladin Akyurek]

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

On 2002-09-11 18:14, Aladin Akyurek wrote:
=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).

Hey, don't confuse me more ! I forgot about that, decimal separator as well... I'm now curious about something, can you try this please ? in the VB Editor, press Control G (Make sure you have an (empty) worksheet selected), and put this in there and press Enter:

ActiveCell.Formula = "=COUNTIF(A1:A10,"">=3.6"")"

Does the formula appear correctly in Excel ? I mean, does it work ? I would think that it should ! or MS is really missing the point here !

Juan,

I don't have a Dutch version at home. Believe me Excel's parser does not look at what is between double quotes...presumably because such things are simply text. Programmers have apparently forgatten all about the use of double quotes as arg specifiers in COUNTIF and SUMIF.

Aladin

 

[Juan Pablo González]

Yes, just tried that on my English version of Excel. Changed my regional settings to work with , as decimal separator, and the COUNTIF took it as a string... man, I think I'm going to give up on this...

This is for the GETCOLOR() formula, have you had the chance to look at it ? I wanted to include the current conditional formatting color in there, not just the cell's color, but the cell that the user sees. Everything works fine except for this little detail... I think this should work ok in an english version tough.

 

[Aladin Akyurek]

On 2002-09-11 18:46, Juan Pablo G. wrote:
Yes, just tried that on my English version of Excel. Changed my regional settings to work with , as decimal separator, and the COUNTIF took it as a string... man, I think I'm going to give up on this...

You might need to write a preprocessor that uses a large lookup table of function names and does some syntactic parsing...

This is for the GETCOLOR() formula, have you had the chance to look at it ? I wanted to include the current conditional formatting color in there, not just the cell's color, but the cell that the user sees. Everything works fine except for this little detail... I think this should work ok in an english version tough.

You mean the GETCOLOR function of your add-in?

OK. A1 has Yellow as Fill Color. What formulas with GETCOLOR are possible regarding A1?
This message was edited by Aladin Akyurek on 2002-09-11 19:11

 

[Juan Pablo González]

On 2002-09-11 19:10, Aladin Akyurek wrote:
You mean the GETCOLOR function of your add-in?
OK. A1 has Yellow as Fill Color. What formulas with GETCOLOR are possible regarding A1?

Ok, in its simplest form
=GETCOLOR(A1)
would return 6, wich is the ColorIndex of yellow. Well, I think it's easier to show with some examples:

Book4
	A	B	C	D	E
1		BackgroundColorIndex	BackgroundRGBColor	FontColorIndex	FontRGBColor
2	ThistextisinAutomatic	6	65535	-4105	0
3	ThistextisinBlack	38	13408767	1	0
4	ThistextisinRed	37	16764057	3	255
Sheet1

and working with more cells...

Book4
	A	B	C	D	E
6		ArrayofAllColors	ArrayofUniqueColors	CountofAllColors	CountofUniqueColors
7		6	6	8	5
8		-4142	-4142
9		38	38	8	5
10		6	37
11		37	36
12		38
13		36
14		38
Sheet1

you can combine this, with SUMPRODUCT for example to get a COLORCOUNTIF or COLORSUMIF (Altough I have both in the new version, much faster than using SUMPRODUCT), or enable you to sort by color, etc.

 

[Aladin Akyurek]

Remember I have still that problem that the functions are "unregistered". That is, the evaluator of the calculation engine doesn't execute them.