Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Local formula evaluate
Hello.
I will try to explain why I need to do this strange thing and what's my problem. For validation purposes, I need to add a second formula to some cells, with this condition: the second formula needs to track the position changes of the cells she belongs to (and of the cells she points to) The only way I have -in my humble knowledge- is using the property formula1 from the validation object. I use a custom validation engine and this let me choose validation errors in a dedicated toolbar. Unfortunately, this property (validation.formula1) needs to be set in english, and returns a value in local language. (*) For example: With r.MergeArea.Validation .Delete .Add Type:=xlValidateCustom, Formula1:="=not(isblank(A1))" End With returns in the inmediate console: ?activesheet.cells(1,1).validation.formula1 =NO(ESBLANCO(A1)) When the validation engine needs to know if a cell is validated, he needs to evaluate a local formula which is not possible ?application.Evaluate("=NO(ESBLANCO(A1))") Error 2029 ?application.Evaluate("=NOT(ISBLANK(A1))") Falso Currently, I have to translate the local formula to english with a method which is not as clean as it should be. (**) This as been a shoddy work during some months but I'm not very happy whit this portion of code. So I would like to know if anyone can help me please: 1) I'm looking for a simple way to evaluate a local formula ¿any ideas? 2) If the first question is not so simple, I would like to read the "validation.formula1" property not as a local formula, but as an english one. 3) I would like also to set this "formula1" in local language. ¿Is it possible? Thanks in advance. Best regards, Luis. (*) This is true, as far as I know, in Excel 2000, 2002, and 2003 (**) __________________________________________________ ___ Function Translate(str As String, Rng As Range) As String 'function to translate from local language to English 'Evaluate does not work with local formula 'where Rng is previously searched as the first empty cell 'who can be used as an aux cell Rng.FormulaLocal = str Translate = Rng.Formula Rng.Formula = vbNullString End Function __________________________________________________ __ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for local time and GMT? | Excel Worksheet Functions | |||
Formula will not evaluate in VBA | Excel Programming | |||
Might be a bug in Evaluate Formula? | Excel Discussion (Misc queries) | |||
Formula Will Not Evaluate | Excel Worksheet Functions | |||
Reference styles and local/non-local formulae - international problems. | Excel Programming |