Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate Equation in VBA
I am trying to build a routine to build a set of formulas to use in VBA to
loop through data and apply formatting Basically I created an array 1st col = equation to evaluate and 2nd col = formatting to be applied Then I loop through the records trying to test the equation against that row's value (in a specific col). I thought I had found the perfect function for this using the Evaluate(), but it doesn't seem to be able to handle slightly complicate equations and onlyreturns the proper value on the most simple equations. Mine is set up as follows Evaluate(Replace(aColor(0, m), "x", iLL)) = True In which my equations can be of 2 types x120 OR 80<x=119 for the first EValuate works just fine, but for the latter it does work??? What function would permit me to properly evaluate if my variable iLL meets the second type of equation or not? Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate Equation in VBA
Evaluate evaluates strings as though they were Excel formulae.
You can test your formula strings by putting them in an Excel cell There are some limitations on what Evalutae can handle but mostly they are relatively easy to bypass. See http://www.decisionmodels.com/calcsecretsh.htm Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Daniel" wrote in message ... I am trying to build a routine to build a set of formulas to use in VBA to loop through data and apply formatting Basically I created an array 1st col = equation to evaluate and 2nd col = formatting to be applied Then I loop through the records trying to test the equation against that row's value (in a specific col). I thought I had found the perfect function for this using the Evaluate(), but it doesn't seem to be able to handle slightly complicate equations and onlyreturns the proper value on the most simple equations. Mine is set up as follows Evaluate(Replace(aColor(0, m), "x", iLL)) = True In which my equations can be of 2 types x120 OR 80<x=119 for the first EValuate works just fine, but for the latter it does work??? What function would permit me to properly evaluate if my variable iLL meets the second type of equation or not? Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate Equation in VBA
Afraid I don't follow what you are trying to do.
How the expression Replace(aColor(0, m), "x", iLL)) evaluate to a boolean (except perhaps 0 or not 0 assuming the result of Replace is a number) 80<x=119 What is that, an equation? Regards, Peter T "Daniel" wrote in message ... I am trying to build a routine to build a set of formulas to use in VBA to loop through data and apply formatting Basically I created an array 1st col = equation to evaluate and 2nd col = formatting to be applied Then I loop through the records trying to test the equation against that row's value (in a specific col). I thought I had found the perfect function for this using the Evaluate(), but it doesn't seem to be able to handle slightly complicate equations and onlyreturns the proper value on the most simple equations. Mine is set up as follows Evaluate(Replace(aColor(0, m), "x", iLL)) = True In which my equations can be of 2 types x120 OR 80<x=119 for the first EValuate works just fine, but for the latter it does work??? What function would permit me to properly evaluate if my variable iLL meets the second type of equation or not? Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate Equation in VBA
I did this in a project by letting the end-user enter a piece of text for
the format like this: red 60 blue "everything below 60 is red, 60 and above is blue" 60 green 80 "format green any value 60 and above and below 80" yellow 50 "everthing below 50 is yellow" Basically they can enter as many alternating colors and numbers as they like (with any numbers being in ascending order). The code splits the string into its components and loops through the numbers, checking the cell value against each one. When a threshold is passed it applies the relevant color (as long as the value is below the next threshold if there is one). I can send you an example workbook if you want to follow up on this method. Tim timjwilliams gmail dot com "Daniel" wrote in message ... I am trying to build a routine to build a set of formulas to use in VBA to loop through data and apply formatting Basically I created an array 1st col = equation to evaluate and 2nd col = formatting to be applied Then I loop through the records trying to test the equation against that row's value (in a specific col). I thought I had found the perfect function for this using the Evaluate(), but it doesn't seem to be able to handle slightly complicate equations and onlyreturns the proper value on the most simple equations. Mine is set up as follows Evaluate(Replace(aColor(0, m), "x", iLL)) = True In which my equations can be of 2 types x120 OR 80<x=119 for the first EValuate works just fine, but for the latter it does work??? What function would permit me to properly evaluate if my variable iLL meets the second type of equation or not? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
evaluate | Excel Worksheet Functions | |||
Why Evaluate? | Excel Programming | |||
How do you evaluate cell value in vba | Excel Programming | |||
Problem with Evaluate | Excel Programming | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) |