Skip to content

stdLambda Class

Jump to bottom
Sancarn edited this page Dec 11, 2020 · 4 revisions

Lambda Expressions for VBA

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 citezens" 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:

  1. Download a copy of the github repository
  2. Drag and Drop stdICallable and stdLambda 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()
    Debug.Print stdLambda.Create("$1.Name")(ThisWorkbook)  'returns ThisWorkbook.Name
    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
Clone this wiki locally