Evaluate Equation in VBA

[Daniel]

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

x>120
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

 

[Charles Williams]

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

 

[Peter T]

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

 

[Tim Williams]

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