VBA Expressions

Description

User manual

Advantages

• Easy to use and integrate.
• Basic math operators: + - * / \ ^ !
• Logical expressions: & (AND), | (OR), || (XOR)
• Binary relations: <, <=, <>, >=, =, >, $ (LIKE)
• Outstanding matrix and statistical functions: CHOLESKY, MLR (Multivariate Linear Regression), FIT (Curve fitting), INVERSE, and a lot more!
• More than 100 built-in functions: Max, Sin, IRR, GAUSS, LSQRSOLVE, Switch, Iff, DateDiff, Solve, fZero, Format...
• Very flexible and powerful: variables, constants and user-defined functions (UDFs) support.
• Implied multiplication for variables, constants and functions: 5avg(2;abs(-3-7tan(5));9), 5(x) and x(2)(3) are valid expressions.
• Evaluation of arrays of expressions given as text strings, as in Java: curly brackets must be used to define arrays{{...};{...}}
• Floating point notation input support: -5E-5, (1.434E3+1000)*2/3.235E-5 are valid inputs.
• Free of external VBA dependencies: does not use dll.

The evaluation approach used is similar to the one we humans use: divide the function into sub-expressions, create a symbolic string to build an expression evaluation flow, split the sub-expressions into chunks of operations (tokens) by tokenization, evaluate all the tokens.

The module can evaluate mathematical expressions such as:

• 5*avg(2;abs(-3-7*tan(5));9)-12*pi-e+(7/sin(30)-4!)*min(cos(30);cos(150))

• min(cos(sin(30))+2^2;1)

• * GCD(1280;240;100;30*cos(0);10*DET({{sin(atn(1)*2); 0; 0}; {0; 2; 0}; {0; 0; 3}}))

*GCD is an user-defined function (UDF).

Allowed expressions must follow the following grammar:

Expression    =     ([{"("}]  SubExpr [{Operator [{"("}] SubExpr [{")"}]}] [{")"}] | {["("] ["{"] List [{";" List}] ["}"] [")"]}) 
SubExpr       =     Token [{Operator Token}]
Token         =     [{Unary}] Argument [(Operator | Function) ["("] [{Unary}] [Argument] [")"]]
Argument      =     (List | Variable | Operand | Literal)
List          =     ["{"] ["{"] SubExpr [{";" SubExpr}] ["}"] ["}"]
Unary         =     "-" | "+" | ~
Literal       =     (Operand | "'"Alphabet"'")
Operand       =     ({Digit} [Decimal] [{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]

VBA expressions uses the following precedence rules to evaluate mathematical expressions:

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.

Users can enter variables and set/assign their values for the calculations. Variable names must meet the following requirements:

1. Start with a letter.
2. End in a letter or number. "x.1", "number1", "value.a" are valid variable names.
3. A variable named "A" is distinct from another variable named "a", since variables are case-sensitive. This rule is broken by the constant PI, since PI=Pi=pi=pI.
4. The token "E" cannot be used as variable due this token is reserved for floating point computation. For example, the expression "2.5pi+3.5e" will be evaluated to ~17.3679680, but a expression like "2.5pi+3.5E" will return an error.

Users can register custom modules to expose and use their functions through the VBAcallBack module. All UDFs must have a single Variant argument that will receive an one-dimensional array of strings (one element for each function argument).

Here is a working example of UDF function creation

Sub AddingNewFunctions()
    Dim Evaluator As VBAexpressions
    Dim UDFnames() As Variant
    Dim Result As String
    
    Set Evaluator = New VBAexpressions
    UDFnames() = Array("GCD")
    With Evaluator
        .DeclareUDF UDFnames, "UserDefFunctions"                        'Declare the Greatest Common Divisor function
                                                                        'defined in the UDfunctions class module. This need
                                                                        'an instance in the VBAcallBack class module.
        ' The determinant of a diagonal matrix. It is defined
        ' as the product of the elements in its diagonal.
        ' For our case: 1*2*3=6. (Note that sin(atn(1)*2)=sin(pi/2)=1)
        .Create "GCD(1280;240;100;30*cos(0);10*DET({{sin(atn(1)*2); 0; 0}; {0; 2; 0}; {0; 0; 3}}))"
        Result = .Eval
    End With
End Sub

VBA expressions can evaluate matrix functions whose arguments are given as arrays/vectors, using a syntax like Java. The following expression will calculate, and format to percentage, the internal rate of return (IRR) of a cash flow described using a one dimensional array with 5 entries:

FORMAT(IRR({{-70000;12000;15000;18000;21000}});'Percent')

However, user-defined array functions need to take care of creating arrays from a string, the ArrayFromString method can be used for this purpose.

NOTE: The library only works on LibreOffice version 7.5 or higher and, since there is no 1-1 compatibility between VBA and LO Basic, users must be aware of certain changes required to recover some properties functionality. This applies mainly to those properties related to accessing variables, which were converted into functions to overcome the one-parameter limitation imposed by LO Basic when accessing them, as well as to other properties deprecated due to LO Basic's behaviour in handling class modules. An example of this is the VarValue property which was split into two procedures: GetVarValue and LetVarValue. The rest of the syntax is shared between the two implementations.

Credits

Inquisitive knight: new logo design. Awesome job!

Homepage: https://github.com/ws-garcia/VBA-Expressions

Repository: https://github.com/ws-garcia/VBA-Expressions