0

Trying to make the following work:

Sub Test()

    Dim condition As String
    Dim comparison As String

    condition = "({0} > {1} And ({1} = 2 Or {2} <> 3))"

    Dim variable As String
    variable = StringFormat(condition, 3, 2, 4)

    Debug.Print variable
    Dim result As Variant
    result = Evaluate(variable)
    Debug.Print result

End Sub

Public Function StringFormat(mask As String, ParamArray tokens()) As String

    Dim i As Long
    For i = LBound(tokens) To UBound(tokens)
        mask = Replace$(mask, "{" & i & "}", tokens(i))
    Next
    StringFormat = mask

End Function

and somehow this is what I got in the immediate window:

enter image description here

which is rather ok, but when I try to evaluate the first line I get the result I am expecting:

enter image description here Changing the condition to condition = "({0} > {1})" works.

Any idea how to evaluate this: ({0} > {1} And ({1} = 2 Or {2} <> 3)) with the Evaluate() method?

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    It's probably helpful to note it's `Error 2015` in en-us Excel. – GSerg Jan 04 '20 at 20:19
  • @GSerg - yeah, I have translated the "Wahr" to "True", but I am so used with "Fehler", that I consider it English already :) – Vityata Jan 04 '20 at 20:23
  • 1
    `Evaluate` is supposed to evaluate Excel formulas, not VBA expressions. In Excel formulas, [`And`](https://support.office.com/en-us/article/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9) and [`Or`](https://support.office.com/en-us/article/or-function-7d17ad14-8700-4281-b308-00b131e22af0) are functions and need to be called as such. `And(3 > 2, Or(2 = 2, 4 <> 3))`. – GSerg Jan 04 '20 at 20:23
  • 2
    `evaluate(”and(3>2,or(2=2, 4<>3))”)` – Tim Williams Jan 04 '20 at 20:24
  • @TimWilliams - the `And()` in front will probably fix it. Anyway, is there a way to call the `?` from the immediate window as a VBA code? – Vityata Jan 04 '20 at 20:26
  • @Vityata `Debug.?` – GSerg Jan 04 '20 at 20:27
  • @GSerg - my idea is to get the result from the second screenshot somehow without changing the the string. – Vityata Jan 04 '20 at 20:29
  • Possible duplicate of [Evaluate Excel VBA boolean condition (not a formula)](https://stackoverflow.com/q/47283839/11683) – GSerg Jan 04 '20 at 20:31
  • 1
    Also https://stackoverflow.com/q/51264509/11683. – GSerg Jan 04 '20 at 20:33
  • Place a button on a worksheet (Ribbon - Developer - Insert - Forms Controls - Button). Go to Immediate window, type `? TypeName(Evaluate(1))`. Surprized? ;) – omegastripes Jan 04 '20 at 20:49
  • I don't think you can achieve that result without changing the string as it simply isn't the right syntax to be evaluated. So : `Evaluate("AND(3>2,OR(2=2,4<>3))")` is probably your goto. And small sidenote, but isn't `Replace$` old fashioned? Or does the dollar sign still serve a purpose? – JvdV Jan 04 '20 at 21:04
  • 1
    @JvdV The dollar sign [does serve a purpose](https://stackoverflow.com/a/36555495/11683) still, but not for `Replace`, as `Replace`, unlike the other string functions, only has the strongly typed version. – GSerg Jan 04 '20 at 21:33
  • Replace also has Replace$ - is above comment saying they're effectively one and the same? @GSerg – QHarr Jan 04 '20 at 23:12
  • 1
    @QHarr [There isn't](https://i.stack.imgur.com/YJaWV.png) `Replace$`. The compiler allows you to add the `$` because here it's just a type specifier. – GSerg Jan 05 '20 at 06:51
  • @GSerg Oh damn... thank you for clarifying. :-) – QHarr Jan 05 '20 at 07:52

0 Answers0