1

I am building an application which must interpret a set of instructions set by the user, instructions which will not be known until runtime.

I am achieving this by using the Evaluate function, as so:

Evaluate("5>4")

Which returns True, as one would expect. My question is how do I evaluate the following statement:

5 is greater than 4, but less than 10

I will be substituting the numbers with variables, of course.

I realise I could split the string into an array of two parts and test individually, but there must be a way of passing a single argument to test.

Jiminy Cricket
  • 1,377
  • 2
  • 15
  • 24
  • maybe like this? `Evaluate("10>5>4")` – gizlmo Feb 24 '17 at 09:48
  • That doesn't work because Evaluate("10>10>4") also returns True – Jiminy Cricket Feb 24 '17 at 09:49
  • I'm sure there's a reason why you can't use the and operator, can I ask what it is? – Preston Feb 24 '17 at 09:51
  • @tompreston I've just tried Evaluate(5>4 and 5<10) which does work, but the user will be entering as a string in a field. How will i convert into this formula because the follow does not work Evaluate("5>4" and "5<10") – Jiminy Cricket Feb 24 '17 at 09:56
  • try this: Debug.Print Evaluate("10>4") And Evaluate("4>2") – Preston Feb 24 '17 at 09:58
  • Thanks Tom, this does work, but as mentioned in the question, I want to avoid splitting the string into an array and creating separate evaluations if possible. Perhaps I will need to – Jiminy Cricket Feb 24 '17 at 09:59
  • 1
    Oh god, missed that part- D'oh! Good luck all the same – Preston Feb 24 '17 at 10:01
  • How many operators are allowed in your expressions? You should limit yourslef to a very simple grammar, otherwise, you should write your own expression evaluator, that's a huge task. – A.S.H Feb 24 '17 at 10:16
  • Just two. I was part of the way through writing my own evaluator, then I discovered the Evaluate function. I can write a simplified evaluator using the Evaluate function, i think this is what i'll end up doing – Jiminy Cricket Feb 24 '17 at 10:22

1 Answers1

2

Application.Evaluate evaluates Formulas so AND(5>A4,5<10) or (5>A4)*(5<10) (results in 0 or 1)

Another alternative could be ScriptControl Eval, but it can't access Excel addresses like Evaluate

With CreateObject("ScriptControl")
    .Language = "VBScript"              ' either VBScript or JScript
    Debug.Print .Eval("5>4 and 5<10")
End With
Slai
  • 22,144
  • 5
  • 45
  • 53