在Excel中使用标准布尔语法评估多个布尔项的方法是什么

Question

在Excel中，不能使用Application.Evaluate函数来处理诸如"真实和真实"；。它必须写为"=和(真、真("；。这带来了一个问题，即您不能对人类可读的布尔术语使用标准语法，也不能让Excel VBA进行评估。我有一些相当长的布尔项字符串，它们可以具有动态关系集，例如"；(真和(真或假((或假或假"；。用Excel格式写这篇文章会让人困惑，开发一个可以解释任何术语组合的解析工具可能比这里更值得做。

有人知道有什么方法可以简化这一点，或者有人知道已经编写的工具或代码可以弥补这一差距吗？

Answer 1

在查看了BigBen提供的一些相关帖子后，我决定只编写解决方案。

我相信它可以从一些效率优化中受益，而且它不考虑XOR等扩展布尔逻辑。它只处理and、OR和not。但这比我在其他地方能找到的要多。

该代码中的方法是将整个字符串分解为一层字符串(由括号确定(，然后使用AND和OR的操作顺序处理每一层逻辑。接下来，它用一个结果true或false替换每个一层项，然后继续到下一层，直到到达逻辑的顶层。函数返回一个字符串，以便它可以调用自己，并将结果无缝地输入到原始字符串求值中。

Public Function EvaluateBooleanString(BooleanString As String) As String
Dim StrPos As Integer
Dim TermStartPos As Integer
Dim TermEndPos As Integer
Dim DepthCounter As Integer
Dim SubBool As String
Dim OrArray() As String
Dim AndArray() As String
Dim OrCounter As Integer
Dim AndCounter As Integer
Dim AndArgs As String
Dim OrArgs As String
TermStartPos = 0
TermEndPos = 0
For StrPos = 1 To Len(BooleanString)
If Mid(BooleanString, StrPos, 1) = "(" Then
DepthCounter = DepthCounter + 1
If DepthCounter = 1 Then TermStartPos = StrPos
End If
If Mid(BooleanString, StrPos, 1) = ")" Then
If DepthCounter = 1 Then
TermEndPos = StrPos
SubBool = EvaluateBooleanString(Mid(BooleanString, TermStartPos + 1, (TermEndPos - TermStartPos) - 1))
BooleanString = Left(BooleanString, TermStartPos - 1) & SubBool & Right(BooleanString, Len(BooleanString) - TermEndPos)
TermStartPos = 0
TermEndPos = 0
StrPos = 1
End If
DepthCounter = DepthCounter - 1
End If
Next StrPos
BooleanString = Replace(BooleanString, "OR", "|", , , vbTextCompare)
BooleanString = Replace(BooleanString, "AND", "&", , , vbTextCompare)
OrArray = Split(BooleanString, "|")
For OrCounter = LBound(OrArray) To UBound(OrArray)
AndArray = Split(OrArray(OrCounter), "&")
If LBound(AndArray) < UBound(AndArray) Then
For AndCounter = LBound(AndArray) To UBound(AndArray)
AndArgs = AndArgs & AndArray(AndCounter) & ","
Next AndCounter
AndArgs = Left(AndArgs, Len(AndArgs) - 1)
Else
AndArgs = AndArray(0)
End If
AndArgs = Replace(AndArgs, "not true", "false", , , vbTextCompare)
AndArgs = Replace(AndArgs, "not false", "true", , , vbTextCompare)
OrArray(OrCounter) = Application.Evaluate("=AND(" & AndArgs & ")")
AndArgs = ""
OrArgs = OrArgs & OrArray(OrCounter) & ","
Next OrCounter
OrArgs = Left(OrArgs, Len(OrArgs) - 1)
OrArgs = Replace(OrArgs, "not true", "false", , , vbTextCompare)
OrArgs = Replace(OrArgs, "not false", "true", , , vbTextCompare)
If LBound(OrArray) < UBound(OrArray) Then
EvaluateBooleanString = Application.Evaluate("=OR(" & OrArgs & ")")
Else
EvaluateBooleanString = OrArgs
End If
End Function