评估Excel VBA布尔条件(不是公式) [英] Evaluate Excel VBA boolean condition (not a formula)

问题描述

我在单元格 A1 中有以下文字

I have text in a cell A1 as below

((True And False Or True) And (True And (Not True))) And (False Or True)

我需要评估此文本，然后使用 VBA代码将布尔结果(True/False)放入另一个单元格 B1 .

I need to evaluate this text and put Boolean result (True / False) in another cell B1 using VBA code.

我尝试使用Evaluate函数，它不起作用.

I tried to use Evaluate function, it is not working.

当我阅读此单元格时，它总是返回字符串类型，并在双引号中加上双引号.因此，将其视为字符串，而不评估布尔表达式.

When I read this cell, it always return string type with double quote enclose in both side. Hence it is treated as string and not evaluating the Boolean expression.

"((True And True Or True) And (True And (True))) And (True Or True)"

我想用这种方式写，但是没有用

I want to write this way, but it is not working

If Range("A1").Value = True Then
    Range("B1").Value = True
Else
    Range("B1").Value = False
End If

我也尝试将其存储在布尔变量中

I tried to store in Boolean variable also

Dim Result as Boolean
Result = CBool(Range("A1").Value) 

因为它是字符串，所以在尝试使用CBool​​进行转换时遇到类型不匹配的情况.

as it is string, I am getting type mismatch when I tried to convert using CBool.

推荐答案

这是您可以使用VBA .Evaluate进行的操作:

This is what you can do with VBA .Evaluate:

Option Explicit

Public Sub TestMe()

    Dim cell01  As Range
    Dim cell02  As Range

    Set cell01 = Range("A1")
    Set cell02 = Range("A2")

    Range("A1") = "1+2+3+4+5"
    Range("A2") = "TRUE and FALSE"

    Debug.Print Evaluate(CStr(cell01))
    'Debug.Print CBool(cell02) - this will be an error!
    Debug.Print Evaluate(CBool("True") And CBool("False"))

    Debug.Print Evaluate("=AND(TRUE,FALSE)")
    Debug.Print Evaluate("=AND(TRUE,TRUE)")
    Debug.Print Evaluate("=OR(TRUE,TRUE)")

End Sub

如果要解析TRUE and FALSE事物(在我的回答中有评论)，请尝试从中构建公式并对其进行评估.

If you want to parse the TRUE and FALSE thing (commented in my answer), try to build a formula out of it and to evaluate it.

例如TRUE AND FALSE，应翻译为=AND(TRUE,FALSE).由于它是Excel公式，因此VBA可以轻松对其进行评估.翻译不是一件容易的事，而是一件有趣的事.

E.g., TRUE AND FALSE, should be translated to =AND(TRUE,FALSE). This gets evaluated easily by VBA as it is an Excel Formula. The translation is not a trivial task, but an interesting one.

这篇关于评估Excel VBA布尔条件(不是公式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！