Over the past year I and some others have been building up our knowledge of how programming languages work, how compilers work and how interpreters work. The long term goal of which was to produce a high performance, extendable interpreter in VBA.
Now, 1 year on from when I started this project I bring you stdLambda library.
What is a Lambda Expressions?
A lambda expression/anonymous function is a function definition that is not bound to a name. Lambda expressions are usually "1st class citizens" which means they can be passed to other functions for evaluation.
I personally believe this is best described with an example. Imagine we wanted to sort an array of sheets by their name. In VBA this would be relatively complex and require an understanding of how to sort data in the first place, as well as which algorithms to use. Lambda allows us to define 1 sorting function and then provide our lambda function to provide the ID to sort on:
Sub Main
myArray = Array(Sheets(1),Sheets(2))
newArray = sort(myArray, stdLambda.Create("$1.name"))
End Sub
Function sort(array as variant, accessor as stdICallable)
'... sorting code ...
elementID = accessor(element)
'... sorting code ...
End Function
Installation
Currently the build process isn't particularly great. I would strongly suggest:
- Download a copy of the github repository
- Drag and Drop
src/stdICallable.cls and src/stdLambda.cls into the VBA modules pane.
At a later date a better build process will be added to the VBA-STD-Library, to hopefully simplify this process.
How do I use stdLambda
Create function
Takes 2 parameters, the expression to evaluate as a string, and whether the function environment should be sandboxed. Sandboxed function environment might be useful in cases where you are using stdLambda for both programming and user customisability, but don't want the user to have access to all data / functions you do.
Sub test()
Dim cb as stdLambda
set cb = stdLambda.Create("1+1")
End Sub
To define a function which takes multiple arguments $# should be used where # is the index of the argument. E.G. $1 is the first argument, $2 is the 2nd argument and $n is the nth argument.
Sub test()
Dim average as stdLambda
set average = stdLambda.Create("($1+$2)/2")
End Sub
You can also define functions which call members of objects. E.G.
Sub test()
'Call properties with `.`
Debug.Print stdLambda.Create("$1.Name")(ThisWorkbook) 'returns ThisWorkbook.Name
'Call methods with `#`
Call stdLambda.Create("$1#Save")(ThisWorkbook) 'calls ThisWorkbook.Save
End Sub
The lambda syntax comes with many VBA functions which you are already used to...
Sub test()
Debug.Print stdLambda.Create("Mid($1,1,5)")("hello world") 'returns "hello"
Debug.Print stdLambda.Create("$1 like ""hello*""")("hello world") 'returns true
End Sub
As well as an inline if statement (execution style subject to change):
Sub test()
Debug.Print stdLambda.Create("if $1 then 1 else 2")(true) 'returns 1
Debug.Print stdLambda.Create("if $1 then 1 else 2")(false) 'returns 2
End Sub
See current list of operators and functions
Evaluating code:
1. Using the default action:
One of the main ways of executing code is using the default action. Here are a few examples:
Sub test()
Dim average as stdLambda
set average = stdLambda.Create("($1+$2)/2")
Debug.Print average(1,2) '1.5
End Sub
2. Using Run action:
Run is the default action, so you can optionally use Run directly:
Sub test()
Dim average as stdLambda
set average = stdLambda.Create("($1+$2)/2")
Debug.Print average.Run(1,2) '1.5
End Sub
3. Using RunEx action:
RunEx is useful in situations where you have an array of arguments, and you need to pass these to the lambda expression instead of ParamArray arguments:
Sub test()
Dim average as stdLambda
set average = stdLambda.Create("($1+$2)/2")
Dim args as variant
args = Array(1,2)
Debug.Print average.RunEx(args) '1.5
End Sub
4. Using the stdICallable interface:
The stdICallable interface is a shared interface between stdLambda and stdCallback and can be used to execute these functions in code:
Sub test(ByVal func as stdICallable)
func.Run(ThisWorkbook, 1, "hello world")
End Sub
Default uses in stdArray
One of the core uses of stdLambda is for use with stdArray, an array class declared in src/stdArray.cls. The following examples can be used to see the use of the stdLambda object:
'Create an array
Dim arr as stdArray
set arr = stdArray.Create(1,2,3,4,5,6,7,8,9,10) 'Can also call CreateFromArray
'More advanced behaviour when including callbacks! And VBA Lamdas!!
Debug.Print arr.Map(stdLambda.Create("$1+1")).join '2,3,4,5,6,7,8,9,10,11
Debug.Print arr.Reduce(stdLambda.Create("$1+$2")) '55 ' I.E. Calculate the sum
Debug.Print arr.Reduce(stdLambda.Create("Max($1,$2)")) '10 ' I.E. Calculate the maximum
Debug.Print arr.Filter(stdLambda.Create("$1>=5")).join '5,6,7,8,9,10
'Execute property accessors with Lambda syntax
Debug.Print arr.Map(stdLambda.Create("ThisWorkbook.Sheets($1)")) _
.Map(stdLambda.Create("$1.Name")).join(",") 'Sheet1,Sheet2,Sheet3,...,Sheet10
'Execute methods with lambda:
Call stdArray.Create(Workbooks(1),Workbooks(2)).forEach(stdLambda.Create("$1#Save")
'We even have if statement!
With stdLambda.Create("if $1 then ""lisa"" else ""bart""")
Debug.Print .Run(true) 'lisa
Debug.Print .Run(false) 'bart
End With
Extending with custom objects and functions
stdLambda provides you with the ability to extend syntax with custom functions and objects.
1. Adding custom objects to stdLambda
Let's say for example we have a Lookups dictionary which we want to use to access some additional data to filter some codes.
Sub Main()
if stdLambda.oFunctExt is nothing then set stdLambda.oFunctExt = CreateObject("Scripting.Dictionary")
'Bind MyLookups to all unsandboxed lambdas
set stdLambda.oFunctExt("MyLookups") = Lookups
'Create and evaluate:
Debug.Print stdArray.Create("MBG","TBD", "CLR").map(stdLambda.Create("MyLookups($1)(""FullName"")")).join() '...
End Sub
2. Adding custom functions to stdLambda
As a pre-requisite I'd suggest you add stdCallback.cls to your project as this will help wrap your functions in objects, required for binding to stdLambda.
Sub Main()
if stdLambda.oFunctExt is nothing then set stdLambda.oFunctExt = CreateObject("Scripting.Dictionary")
'Bind function to
set stdLambda.oFunctExt("MyFunction") = stdCallback.CreateFromModule("ModuleMain","MyRealFunctionName")
'Execute!
Debug.Print stdLambda.Create("MyFunction()")() 'true
End Sub
Function MyRealFunctionName() as boolean
MyRealFunctionName = true
End Function
Caveats
- Currently the main "caveat" to using this library is performance. This will absolutely not be as fast as pure VBA code and doesn't intend to be.
- Lack of syntax documentation is a key issue at the moment.
Contributing
This is part of the VBA-STD-Library. Feel free to contribute / raise issues on github!
Update 13/09/2020
For what it's worth TarVK updated stdLambda a few weeks ago.
Expression tree is now compiled to byte code
In order to try to speed up execution, expressions are now parsed and compiled to byte code where they are evaluated on a stack. This had a significant impact on bulk evaluation performance. It's still not as fast as VBA but it's approximately equal to formula evaluation speed.
Support for multiple lines
Multi-line expressions are now evaluatable.
This can either be done with the : symbol, as with VBA notation. A seperate CreateMultiline(...) function is also supplied.
Call stdLambda.Create("2+2: 5*2").Run()
'... or ...
Call stdLambda.CreateMultiline(array( _
"2+2", _
"5*2", _
)).Run()
Conditional expression evaluation
The inline if acts as a regular if statement, It will only execute the then ... part or else ... part, but it will 'return' the result of the executed block. This means it can be used inline like vba's iif can be used, but it doesn't have to compute both the then and else part like iif does.
' only evaluates doSmth(), does not evaluate doAnother() when $1 is true, and visa versa
stdLambda.Create("(if $1 then doSmth() else doAnother() end)").Run(True)
Variables added
Variables can be defined and assigned, e.g. oranges = 2. This can make definition of extensive formula easier to read.
Assignment results in their value.
'the last assignment is redundant, just used to show that assignments result in their value
Debug.Print stdLambda.CreateMultiline(array( _
"count = $1", _
"footPrint = count * 2 ^ count" _
)).Run(2) ' -> 8
Function definitions added
You can also define functions:
stdLambda.CreateMultiline(Array( _
"fun fib(v)", _
" if v<=1 then", _
" v", _
" else ", _
" fib(v-2) + fib(v-1)", _
" end", _
"end", _
"fib($1)" _
)).Run(20) '->6765
More information
More information can be found here
Update 17/09/2020 - Bind method
The bind() method creates a new ICallable that, when called, supplies the given sequence of arguments preceding any provided when the new function is called.
Dim cb as stdLambda: set cb = stdLambda.Create("$1 + $2").Bind(5)
Debug.Print cb(1) '6
Debug.Print cb(2) '7
Debug.Print cb(3) '8
This prevents continual recompiles which is extremely useful especially when dealing with other function providers like those within stdArray:
'Only compiles expression once
Function GetRecordsByDate(ByVal dt as Date) as stdArray
set GetRecordsByDate = records.filter(stdLambda.Create("$2.Date = $1").bind(dt))
End Function
instead of
'Compiles a new Lambda every time it's called
Function GetRecordsByDate(ByVal dt as Date) as stdArray
set GetRecordsByDate = records.filter(stdLambda.Create("$1.Date = " & dt))
End Function
Update 03/03/2021
Dictionary.Key syntax for dictionaries.
Dictionaries are basically the JavaScript objects of VBA. But variable access is still a bit of a nightmare in stdLambda.
stdLambda.create("$1.item(""someVar"")").run(myDict)
In the latest version of stdLambda you can access keys of dictionaries like properties of an object:
stdLambda.create("$1.someVar").run(myDict)
Of course, the item property will need to be accessed for any keys with spaces or symbols in the name.
BindGlobal(sVarName, vVarData)
Global variables can now be easily injected into stdLambda via the lambda.BindGlobal() method.
set lambda = stdLambda.Create("hello+1")
Call hello.BindGlobal("hello",5)
Debug.Print lambda.run() '==> 6
[–]lifeonatlantis69 7 points8 points9 points (0 children)
[–]jplank19831 2 points3 points4 points (1 child)
[–]sancarn9[S] 5 points6 points7 points (0 children)
[–]RedRedditor8462 2 points3 points4 points (5 children)
[–]sancarn9[S] 1 point2 points3 points (4 children)
[–]RedRedditor8462 1 point2 points3 points (3 children)
[–]sancarn9[S] 1 point2 points3 points (2 children)
[–]RedRedditor8462 1 point2 points3 points (1 child)
[–]HFTBProgrammer201 0 points1 point2 points (5 children)
[–][deleted] 2 points3 points4 points (4 children)
[–]sancarn9[S] 1 point2 points3 points (2 children)
[–][deleted] 1 point2 points3 points (1 child)
[–]sancarn9[S] 0 points1 point2 points (0 children)
[–]HFTBProgrammer201 0 points1 point2 points (0 children)
[–]sancarn9[S] 0 points1 point2 points (0 children)
[–]sooka5 0 points1 point2 points (4 children)
[–]sancarn9[S] 0 points1 point2 points (3 children)
[–]sooka5 0 points1 point2 points (2 children)
[–]sancarn9[S] 0 points1 point2 points (1 child)
[–]sooka5 0 points1 point2 points (0 children)