VBA 数学表达式计算器
一个用于评估 VBA 字符串中的数学表达式的类模块
引言
对 CSV Interface 的自然演进，需要集成一种数学表达式求值器，以便能够过滤大量记录的 CSV 文件。然而，这种特殊的实用工具在 VBA 语言中开发得很少，现有的替代方案不够灵活且难以集成，还有一些无法正确评估某些极端情况。这种限制促使我开发一个满足必要要求并且同时能够以最少代码量集成到目标库中的求值器。
追求的目标
该实用工具需要满足以下要求
- 该工具必须能够接收文本字符串作为输入，并返回通过执行表达式中包含的算术、逻辑和二元关系运算所获得的结果。
- 用户必须能够定义变量，并在解析表达式后决定每次求值时使用的值。
- 必须提供内置函数。
- 用户必须能够在运行时注册并使用自己的函数，而无需向已开发的工具添加代码。
- 在每个已解析的表达式中找到的变量以及与它们相关的值必须暴露给用户。
- 该工具应避免使用硬编码的表达式，因此变量值应作为文本字符串分配。
道路
求值数学表达式的传统方法涉及将中缀表达式（操作数由运算符分隔）转换为后缀表达式。这样，就可以使用堆栈或解析树进行求值，从而确保数学表达式中定义的运算符的优先级。
然而，我们决定开发 VBA Expressions 作为一种能够直接求值中缀表达式的工具，尽管这极其困难，但目的是证明人类评估表达式的方式是足够且相对高效的。求值之前的分析过程包括通过以下步骤创建求值树
- 使用数学运算符将表达式细分为相关的子表达式。
- 每个子表达式被分解成标记，每个标记由 2 个由数学运算符关联的参数定义。
- 为每个子表达式创建并存储一个标记。
- 每个标记被分解成参数，这是使用的最低逻辑级别，表示为列表、变量或操作数。
- 所有标记解析完成后，求值树就完成了。
求值过程遵循以下路径
- 解析器使用求值树中的第一个标记作为入口点。
- 然后评估标记的所有参数。
- 保存标记求值的结果。
- 求值一直进行到所有标记都被求值。
- 求值结果始终对应于最后一个标记的求值。
VBA Expressions 方法使用括号作为标记表达式的入口点，因此该工具允许进行一些有趣的技巧，其中我们可以提到支持定义为文本字符串的数组，其语法与 Java 使用的类似。因此，括号是专门用于对必须作为单个标记进行求值的子表达式进行分组的字符，从而提供了一种评估高度复杂表达式的方法。
下面显示了该实用工具支持的表达式定义的语法
Expression = ([{"("}] SubExpr [{Operator [{"("}] SubExpr
[{")"}]}] [{")"}] | {["("] ["{"] List [{";" List}] ["}"] [")"]}
SubExpr = Token [{Operator Token}]
Token = [{Unary}] Argument [(Operator | Function) ["("] [{Unary}] [Argument] [")"]]
Argument = (List | Variable | Operand)
List = "{" ["{"] SubExpr [{";" SubExpr}] ["}"] "}"
Unary = "-" | "+" | ~
Operand = ({Digit} ["."] [{Digit}] ["E"("-" | "+"){Digit}] | (True | False))
Variable = Alphabet [{Decimal}] [{(Digit | Alphabet)}]
Alphabet = "A-Z" | "a-z"
Decimal = "."
Digit = "0-9"
Operator = "+" | "-" | "*" | "/" | "\" | "^" | "%" | "!" | "<" | "<=" |
"<>" | ">" | ">=" | "=" | "&" | "|" | "||"
Function = "abs" | "sin" | "cos" | "min" |...|[UDF]
所支持运算符的优先级以及运算符本身在实用工具中是硬编码的，因此，到目前为止，还无法添加新运算符。该实用工具将根据 PEMDAS 方法优先评估运算符
1. () Grouping: evaluates functions arguments as well.
2. ! - + Unary operators: exponentiation is the only operation that violates this.
Ex.: `-2 ^ 2 = -4 | (-2)^ 2 = 4.
3. ^ Exponentiation: Although Excel and Matlab evaluate nested exponentiations
from left to right, Google, mathematicians and several modern
programming languages, such as Perl, Python and Ruby, evaluate this
operation from right to left. VBA expressions also evals in Python way:
a^b^c = a^(b^c).
4. \* / % Multiplication, division, modulo: from left to right.
5. + - Addition and subtraction: from left to right.
6. < <= <> >= = > Binary relations.
7. ~ Logical negation.
8. & Logical AND.
9. || Logical XOR.
10. | Logical OR.
应注意的是，VBA 不允许使用代码反射，因此可扩展性仅限于 CallByName 函数。为了限制硬编码参数的使用，用户定义的函数必须接受单个变体类型参数，该函数将接收一个一维文本字符串数组，其元素数量与在解析给定表达式时找到的参数数量相同。
代码
VBA Expressions 所进行的求值核心在于 Parse 方法，该方法负责生成求值树。此过程的一个基本方面是区分简单表达式和复合表达式。在工具看来，简单表达式是指不使用括号且产生单个求值树的表达式。对于复合表达式，需要为表达式中存在的每个子表达式创建尽可能多的求值树。这是代码
Private Sub Parse(ByRef Expression As String)
Dim lambdaText As String
Dim meLB As Long
Dim meUB As Long
Dim meCounter As Long
Dim SimpleExpr As Boolean
Dim TreeUB As Long
Dim LbrCount As Long
Dim RbrCount As Long
On Error GoTo Parse_errHandler
InitializeErrHandler
LbrCount = CountParentheses(Expression, d_lParenthesis)
RbrCount = CountParentheses(Expression, d_rParenthesis)
If LbrCount <> RbrCount Then
Exit Sub
End If
err.Clear
SubTreeData() = GetSubTreeData(Expression)
lambdaText = SerializeSubTree(Expression, SubTreeData)
meLB = LBound(SubTreeData)
meUB = UBound(SubTreeData)
SimpleExpr = (meUB - meLB = 0 And lambdaText = SubTreeData(meUB))
TreeUB = meUB + Abs(CLng(Not SimpleExpr)) 'Upper bound for ClusterTree array
ReDim EvalTree(meLB To TreeUB)
For meCounter = meLB To TreeUB
InitBuffer EvalTree(meCounter) 'Reserve storage for tree branches/Expressions
If meCounter < TreeUB Then 'Tokenize branches
TokenizeSubExpr SubTreeData(meCounter), SubTreeData, EvalTree(meCounter)
Else
If Not SimpleExpr Then 'Tokenize main tree/lambda expression
TokenizeSubExpr lambdaText, SubTreeData, EvalTree(meCounter)
Else
TokenizeSubExpr SubTreeData(meCounter), SubTreeData, EvalTree(meCounter)
End If
End If
ShrinkBuffer EvalTree(meCounter)
Next meCounter
If ValidTree Then 'Call the validate function
GeneratedTree = True
Else
GeneratedTree = False
End If
End Sub
使用 TokenizeSubExpr 方法将每个子表达式标记化或细分为其基本组件。在这里，区分了常规子表达式、参数列表和数组。
Private Sub TokenizeSubExpr(ByRef Expression As String, _
ByRef SubExpressionsData() As String, ByRef outBuffer As ClusterTree)
Dim tmpReplacement As String
Dim ExpCopy As String
Dim tmpArgs() As String
Dim taIcounter As Long
Dim OperationIndex As Long
Dim tmpIndex As Long
tmpIndex = UBound(SubExpressionsData) + 1
OperationIndex = tmpIndex
ExpCopy = Expression
tmpReplacement = GetSubstStr(OperationIndex)
If Not ExpCopy Like "*{{*}}*" Then
Select Case InStrB(1, ExpCopy, P_SEPARATORCHAR)
Case 0 'Regular sub-expression
GetRootedTree ExpCopy, tmpReplacement, OperationIndex, outBuffer
outBuffer.CompCluster = False
Case Else 'Composite function argument
tmpArgs() = Split(ExpCopy, P_SEPARATORCHAR)
For taIcounter = LBound(tmpArgs) To UBound(tmpArgs)
GetRootedTree tmpArgs(taIcounter), tmpReplacement, _
OperationIndex, outBuffer
Next taIcounter
outBuffer.CompCluster = True
outBuffer.CompArrCluster = False
End Select
Else 'Composite array function argument
outBuffer.ClusterArrBounds = SplitArrBranch(ExpCopy, tmpArgs)
If outBuffer.ClusterArrBounds(0) <> -1 Then 'Splitting argument success
For taIcounter = LBound(tmpArgs) To UBound(tmpArgs)
GetRootedTree tmpArgs(taIcounter), tmpReplacement, OperationIndex, outBuffer
Next taIcounter
outBuffer.CompCluster = True
outBuffer.CompArrCluster = True
Else
'Todo: Code here for trap error of missing () in a composite array
'and standard input
End If
End If
End Sub
GetRootedTree 方法提取标记上的所有二元运算，直到它们被简化为单个参数。
Private Sub GetRootedTree(ByRef SubExpression As String, ByRef tmpReplacement As String, _
ByRef OperationIndex As Long, ByRef outBuffer As ClusterTree)
Dim vToken As Token
Dim switch As Boolean
Dim tmpPos As Long
Dim OperandInBundle As Boolean
Dim PrevChar As String
Do
SubExpression = ApplyLawOfSigns(SubExpression)
vToken = GetEvalToken(SubExpression)
'@--------------------------------------------------------------------
' Mask worked token
tmpPos = InStrB(1, SubExpression, vToken.DefString)
If tmpPos > 2 Then
PrevChar = MidB$(SubExpression, tmpPos - 2, 2)
OperandInBundle = (InStrB(1, op_AllItems, PrevChar))
Do While Not OperandInBundle And tmpPos > 2 'Tokens starts with a operator
'or with a null string
tmpPos = InStrB(tmpPos + 2, SubExpression, vToken.DefString)
PrevChar = MidB$(SubExpression, tmpPos - 2, 2)
OperandInBundle = (InStrB(1, op_AllItems, PrevChar))
Loop
End If
SubExpression = MidB$(SubExpression, 1, tmpPos - 1) & _
tmpReplacement & MidB$(SubExpression, tmpPos + LenB(vToken.DefString))
AppendToBuffer outBuffer, vToken 'Save to target token ClusterTree
switch = (SubExpression <> tmpReplacement)
If switch Then
OperationIndex = OperationIndex + 1
tmpReplacement = GetSubstStr(OperationIndex)
End If
Loop While switch
End Sub
当这些过程无警报地完成时，获得的结果是已准备好进行处理的求值树。正是在这一点上，Compute 方法发挥作用，该方法负责处理求值树并返回结果。求值过程相对简单，困难在于彼此关联的求值树结果之间的关系，在这种情况下，需要将先前结果与其他运算链接以计算新结果。
为了使结果的链接成为可能和可行，设计了 BaseIndex 变量，该变量指示参数是否与同一求值树的分支和/或在评估另一棵树时获得的结果相关。Compute 方法遍历每个标记的求值树，提取其操作数并在求值每个操作数后保存结果。求值数组（具有 m 行 n 列）和列表后获得的结果以输入格式存储。
Private Function Compute() As String
Dim B As Long
Dim t As Long
Dim i As Long
Dim OperationIndex As Long
Dim BaseIndex As Long
Dim PrevOP1 As String
Dim PrevOP2 As String
BaseIndex = UBound(SubTreeData) + 1
For B = LBound(EvalTree) To UBound(EvalTree) 'Loop all subexpressions
OperationIndex = BaseIndex
For t = 0 To EvalTree(B).Index 'Loop all tokens
OperationIndex = OperationIndex + 1
If Not EvalTree(B).Storage(t).ConstantToken Then 'Gallop
If P_GALLOPING_MODE Then
PrevOP1 = EvalTree(B).Storage(t).Arg1.Operand
PrevOP2 = EvalTree(B).Storage(t).Arg2.Operand
End If
GetOperands EvalTree(B).Storage(t), EvalTree(B), BaseIndex
BottomLevelEval EvalTree(B).Storage(t)
If P_GALLOPING_MODE Then
EvalTree(B).Storage(t).ConstantToken = _
(PrevOP1 = EvalTree(B).Storage(t).Arg1.Operand And _
PrevOP2 = EvalTree(B).Storage(t).Arg2.Operand)
End If
End If
Next t
If Not EvalTree(B).CompCluster Then
EvalTree(B).EvalResult = EvalTree(B).Storage(t - 1).EvalResult
Else 'The ClusterTree contains a composite function args as expression
Dim tmpResult() As String
ReDim tmpResult(0 To EvalTree(B).Index)
'@--------------------------------------------------------------------
' Loop all sub-expression tokens results
For i = 0 To EvalTree(B).Index
tmpResult(i) = EvalTree(B).Storage(i).EvalResult
Next i
If Not EvalTree(B).CompArrCluster Then 'Function Argument
EvalTree(B).EvalResult = Join$(tmpResult, P_SEPARATORCHAR)
Else 'Array function Argument
EvalTree(B).EvalResult = JoinArrFunctArg_
(tmpResult, EvalTree(B).ClusterArrBounds(0), EvalTree(B).ClusterArrBounds(1))
End If
End If
Next B
Compute = EvalTree(B - 1).EvalResult
ComputedTree = True
End Function
操作数通过 GetOperands 和 GetOperand 方法获得。
Private Sub GetOperands(ByRef CurToken As Token, ByRef CurTree As ClusterTree, _
ByRef BaseIndex As Long)
'@--------------------------------------------------------------------
' Get the first operand
GetOperand CurToken, CurToken.Arg1, CurTree, BaseIndex
'@--------------------------------------------------------------------
' Get the second operand if required
If Not CurToken.OperationToken = otNull Then
GetOperand CurToken, CurToken.Arg2, CurTree, BaseIndex
End If
End Sub
Private Sub GetOperand(ByRef CurToken As Token, ByRef CurArg As Argument, _
ByRef CurTree As ClusterTree, ByRef BaseIndex As Long)
If CurArg.Implicit Then
If CurArg.FunctionIn Then 'Implicit function
If CurArg.FactorialIn Then
If CurArg.LinkedIndex >= BaseIndex Then 'Data on current tree
CurArg.Operand = Factorial(EvalFunction_
(CurTree.Storage(CurArg.LinkedIndex - BaseIndex).EvalResult, _
CurArg.FuncName, CurArg.UDFFunctionIn))
Else 'Data is main tree
CurArg.Operand = Factorial(EvalFunction(EvalTree_
(CurArg.LinkedIndex).EvalResult, _
CurArg.FuncName, CurArg.UDFFunctionIn))
End If
Else 'Eval
If CurArg.LinkedIndex >= BaseIndex Then
CurArg.Operand = EvalFunction(CurTree.Storage_
(CurArg.LinkedIndex - BaseIndex).EvalResult, _
CurArg.FuncName, CurArg.UDFFunctionIn)
Else
CurArg.Operand = EvalFunction(EvalTree(CurArg.LinkedIndex).EvalResult, _
CurArg.FuncName, CurArg.UDFFunctionIn)
End If
End If
Else 'Return data
If CurArg.LinkedIndex >= BaseIndex Then
If CurArg.FactorialIn Then
CurArg.Operand = Factorial(CurTree.Storage_
(CurArg.LinkedIndex - BaseIndex).EvalResult)
Else
CurArg.Operand = CurTree.Storage(CurArg.LinkedIndex - BaseIndex).EvalResult
End If
Else
If CurArg.FactorialIn Then
CurArg.Operand = Factorial(EvalTree(CurArg.LinkedIndex).EvalResult)
Else
CurArg.Operand = EvalTree(CurArg.LinkedIndex).EvalResult
End If
End If
End If
If AscW(CurArg.DefString) = 45 Then
CurArg.Operand = ApplyLawOfSigns(op_minus + CurArg.Operand)
End If
Else 'Explicit function or data
If CurArg.LinkedVar > -1 Then 'Variable substitution
If CurArg.FactorialIn Then 'Operate factorials
CurArg.Operand = Factorial(ExprVariables.Storage(CurArg.LinkedVar).value)
Else
CurArg.Operand = ExprVariables.Storage(CurArg.LinkedVar).value
End If
If AscW(CurArg.DefString) = 45 Then
CurArg.Operand = ApplyLawOfSigns(op_minus + CurArg.Operand)
End If
Else
If CurArg.FactorialIn Then
CurArg.Operand = Factorial(MidB$(CurArg.DefString, 1, _
LenB(CurArg.DefString) - 2))
Else
CurArg.Operand = CurArg.DefString
End If
End If
End If
End Sub
最后，每个标记由 BottomLevelEval 方法进行求值
Private Sub BottomLevelEval(ByRef aToken As Token)
If aToken.OperationToken < 8 Then 'Arithmetic operators
Select Case aToken.OperationToken
Case OperatorToken.otSum
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) _
+ CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otDiff
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) - _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otMultiplication
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) * _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otDivision
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) / _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otPower
Dim kFctr As Double
If AscW(aToken.Arg1.DefString) = 45 Then
kFctr = -1
Else
kFctr = 1
End If
aToken.EvalResult = kFctr * CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) ^ _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otMod
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) Mod _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otIntDiv
aToken.EvalResult = Floor(CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) / _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn))
Case Else
If aToken.Logical Then
If aToken.Arg1.NegationFlagOn Then
If AscW(aToken.Arg1.Operand) <> 126 Then '"~"
aToken.EvalResult = Not CBool(aToken.Arg1.Operand)
Else
aToken.EvalResult = _
Not CBool(MidB$(aToken.Arg1.Operand, 3))
End If
Else
aToken.EvalResult = CBool(aToken.Arg1.Operand)
End If
Else
aToken.EvalResult = aToken.Arg1.Operand
End If
End Select
Else
If aToken.OperationToken < 14 Then 'Comparison operators
Select Case aToken.OperationToken
Case OperatorToken.otEqual
aToken.EvalResult = (CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) = _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn))
Case OperatorToken.otNotEqual
aToken.EvalResult = (CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) <> _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn))
Case OperatorToken.otGreaterThan
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) > _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otLessThan
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) < _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case OperatorToken.otGreaterThanOrEqual
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) >= _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
Case Else
aToken.EvalResult = CastOPtype(aToken.Arg1.Operand, _
aToken.Arg1.NegationFlagOn) <= _
CastOPtype(aToken.Arg2.Operand, _
aToken.Arg2.NegationFlagOn)
End Select
Else 'Logical operators
Dim tmpBooleans() As Boolean
Select Case aToken.OperationToken
Case OperatorToken.otLogicalAND
tmpBooleans() = GetLogicalNeg(aToken)
aToken.EvalResult = tmpBooleans(0) And tmpBooleans(1)
Case OperatorToken.otLogicalOR
tmpBooleans() = GetLogicalNeg(aToken)
aToken.EvalResult = tmpBooleans(0) Or tmpBooleans(1)
Case Else
tmpBooleans() = GetLogicalNeg(aToken)
aToken.EvalResult = tmpBooleans(0) Xor tmpBooleans(1)
End Select
End If
End If
End Sub
EvalFunction 方法负责求值解析表达式中包含的函数，如前所述，用户定义的函数（UDF）将接收一个文本字符串数组，但根据 CallByName 函数的要求，UDF 必须提供的唯一参数必须是变体类型。
Private Function EvalFunction(ByRef Argument As String, _
ByRef FunctionName As String, Optional IsUDF As Boolean = False) As String
If Not IsUDF Then
Select Case FunctionName
Case "Absolute"
EvalFunction = Absolute(Argument)
Case "ArcSin"
EvalFunction = ArcSin(Argument)
Case "ArcCos"
EvalFunction = ArcCos(Argument)
Case "ArcTan"
EvalFunction = ArcTan(Argument)
Case "Average"
EvalFunction = average(Argument)
Case "Cosin"
EvalFunction = Cosin(Argument)
Case "aCeiling"
EvalFunction = aCeiling(Argument)
Case "ExpEuler"
EvalFunction = ExpEuler(Argument)
Case "aFloor"
EvalFunction = aFloor(Argument)
Case "Gamma"
EvalFunction = tGamma(Argument)
Case "Logarithm"
EvalFunction = Logarithm(Argument)
Case "LgN"
EvalFunction = LgN(Argument)
Case "LN"
EvalFunction = LN(Argument)
Case "Max"
EvalFunction = Max(Argument)
Case "Min"
EvalFunction = Min(Argument)
Case "Percent"
EvalFunction = Percent(Argument)
Case "Power"
EvalFunction = Power(Argument)
Case "Sign"
EvalFunction = Sign(Argument)
Case "Sine"
EvalFunction = Sine(Argument)
Case "SquareRoot"
EvalFunction = SquareRoot(Argument)
Case "Tangent"
EvalFunction = Tangent(Argument)
Case Else
'Rise an error for not found function
End Select
Else
EvalFunction = EvalUDF(FunctionName, Argument)
End If
End Function
Private Function EvalUDF(ByRef UDFname As String, ByRef Expression As String) As String
Dim args As Variant
Dim tmpEval As String
Dim UDFidx As Long
UDFidx = GetCBItemIdx(UserDefFunctions, UDFname)
If UDFidx > -1 Then 'Only declared functions are called
args = SplitArgs(Expression) 'Pass a string array to UDF functions
tmpEval = CallByName(callback(UserDefFunctions.Storage(UDFidx).value), _
UDFname, VbMethod, args)
EvalUDF = tmpEval
End If
End Function
Using the Code
让我们从一个带有多个括号的表达式的求值开始
Sub SimpleMathEval()
Dim Evaluator As VBAexpressions
Set Evaluator = New VBAexpressions
With Evaluator
.Create "(((((((((((-123.456-654.321)*1.1)*2.2)*3.3)+4.4)+5.5)+_
6.6)*7.7)*8.8)+9.9)+10.10)"
If .ReadyToEval Then 'Evaluates only if the expression was successfully parsed.
.Eval
End If
End With
End Sub
现在，让我们看看如何评估一个包含变量的表达式，这些变量的值在请求评估表达式时定义。
Sub LateVariableAssignment()
Dim Evaluator As VBAexpressions
Set Evaluator = New VBAexpressions
With Evaluator
.Create "Pi.e * 5.2Pie.1 + 3.1Pie"
If .ReadyToEval Then
Debug.Print "Variables: "; .CurrentVariables 'Print the list of parsed variables
.Eval ("Pi.e=1; Pie.1=2; Pie=3") 'Late variable assignment
Debug.Print .Expression; " = "; .Result; _
"; for: "; .CurrentVarValues 'Print stored result, expression
'and values used in evaluation
End If
End With
End Sub
在调用 Eval 方法之前，也可以定义变量的值。
Sub EarlyVariableAssignment()
Dim Evaluator As VBAexpressions
Set Evaluator = New VBAexpressions
With Evaluator
.Create "Pi.e * 5.2Pie.1 + 3.1Pie"
If .ReadyToEval Then
Debug.Print "Variables: "; .CurrentVariables
.VarValue("Pi.e") = 1
.VarValue("Pie.1") = 2
.VarValue("Pie") = 3
.Eval
Debug.Print .Expression; " = "; .Result; _
"; for: "; .CurrentVarValues
End If
End With
End Sub
库中包含的三角函数可以使用弧度或度进行求值。
Sub TrigFunctions()
Dim Evaluator As VBAexpressions
Set Evaluator = New VBAexpressions
With Evaluator
.Create "asin(sin(30))"
If .ReadyToEval Then
.Degrees = True 'Eval in degrees
.Eval
End If
End With
End Sub
VBA Expressions 的一个重要特性是用户可以使用 GallopingMode 属性来避免重新评估在调用 Eval 方法之间值保持不变的那些标记。
关注点
这种方法已被证明非常灵活，因为它遵循我们在纸上评估表达式时使用的相同思维过程，通过使用括号，可以实现一些非常强大的技巧。在接受数组和列表时，利用了这一优点。
尽管选择了一些导致其他解决方案失败的极端情况，但仍需要更多的测试来确保该工具中的编程错误数量可以忽略不计。请随时提出可用于测试的表达式。
历史
- 2022 年 2 月 25 日：首次发布