Functional Interfaces ... in VBA

    "... those who are not averse to staring at an amateur publicly blaming a fool, let them observe how I prove that Java and Visual Basic are twins separated at birth, and C ++ they are not even a distant relative."

    Bruce McKinney “Die Hard Basic Basic”


    A constant interest in functional programming approaches currently leads to the fact that traditional programming languages ​​are actively acquiring functional means. And although pure functional languages ​​are still not very popular, the functionality is firmly established in languages ​​such as C ++, Java, JavaScript, Python, and others. The VBA language has been popular with a fairly large audience of Microsoft Office users for many years, however this language contains practically no functional tools.

    Let's try to fill this gap - I propose a complete (although perhaps not perfect) implementation of functional interfaces implemented by VBA. Implementation can serve as the basis for subsequent refinements and improvements.

    Functional Arguments Problem

    The first problem we will face along this path is the problem of passing functional arguments to a function or method. The VBA language does not contain appropriate tools (the AddressOf operator serves only to pass addresses to the Windows API functions and is not completely safe to use). The same can be said about the well-known method of calling functions by pointer (G. Magdanurov Visual Basic in practice St. Petersburg: “BHV Petersburg”, 2008). Let's not take risks - we use only standard language features and standard libraries in the implementation.

    Unfortunately, here the PLO does not help much. To transfer a functional object into a procedure or function, VBA offers a standard opportunity - to wrap the necessary functionality with an object shell (create an object, one of the methods of which will be the necessary functionality). An object can be passed as a parameter. This approach is workable, but very heavy - for each necessary functionality you will have to create your own class and an object of this class.

    There is another way, which is much simpler and does not require the creation of separate classes for each functionality.

    Suppose you want to pass an anonymous function to a certain proc procedure that increments its argument by one. This function can be written as follows:

    x -> x+1

    This notation of assigning anonymous functions has now almost become the “de facto standard." The only way to pass such a function to a parameter is to use a string representation:


    here a and b are ordinary parameters, and the third parameter is an unnamed function, which is very clear and differs little from entries in popular programming languages.

    To use an anonymous function defined in this way, you must first bring it to the standard form of the VBA function. This performs the following utility procedure:

    Private Function prepCode(Code As String) As String
             k% = InStr(Code, "->")
             parms$ = Trim$(Left$(Code, k% - 1))
             body$ = Mid$(Code, k% + 2)
             If Left$(parms$, 1) <> "(" Then parms$ = "(" + parms$ + ")"
             If InStr(body$, "self") = 0 Then body$ = ";self=" & body$ & ";"
             body$ = Replace(body$, ";", vbCrLf)
             prepCode = "function self" & parms & vbCrLf & body & _ 
                                 vbCrLf & "end function"
    End Function

    The function selects a list of parameters and the calculation body, and then forms a function called self. For our case, the self function will have the following form:

    function self(x)
    End function

    Obviously, in accordance with the VBA syntax, this function will do exactly what the anonymous function should have done - increase the value of its argument by 1. True, this function is not yet a VBA function, but only a line containing the specified code. In order to turn a string into a function, you can use the standard Microsoft library “Msscript.ocx”. This COM library allows you to execute arbitrary VBA code specified in string form. To do this, do the following:

    - Create a ScriptControl object
    - Call the language installation method (VBScript);
    - Call the function loading method;
    - Call the eval method to make the call.

    It all looks something like this:

    Set locEv=new ScriptControl
    locEv.Language = "VBScript"
    locEv.AddCode prepCode(“x->x+1”)

    After executing this code, the value of the variable r will be 6.

    Here three points should be made:

    • The body of an anonymous function can contain several lines. Individual statements in this case end with a semicolon. From the final code, the characters “;” are excluded. A multi-line body allows you to implement very advanced functionality in anonymous functions;
    • The fact that the anonymous function “in reality” has the name “self” gives an unexpected bonus - an anonymous function can be recursive.
    • Since the ScriptControl object supports two languages ​​- VBScript and Jscript, the anonymous function can be (theoretically) written in Jscript (those who wish can try it).

    Next, an object implementation model will be described.

    Object model

    The basis of the model are objects of two types: Container and Generator. The Container object is a repository of an array of arbitrary sizes, the Generator object, as the name implies, implements a general form generator.

    Both objects implement the aIter interface, which is described in more detail below. The interface includes 19 functions:

    Method name ParametersResult
    isGen - Returns True if the object is a generator.
    isCont -Returns True if the object is a container.
    getCont -For a container returns a local array, for a generator returns Empty
    getNext - Returns the following value
    hasNext - Returns True if the following value is present
    InitiniVal As Variant, lambda As String = "", emptyC As Boolean = False iniVal - initial value;
    lambda - anonymous function for
    emptyC generator - when set to True, an empty container is created
    Take n as integer Returns a container containing n consecutive values ​​obtained from the source object
    Filter lambda as string Returns the object obtained by filtering the original according to the unnamed lambda function
    Map lambda as string Returns the object obtained by mapping the original according to the unnamed lambda function
    Reduce acc As Variant, lambda As String, Returns the result of the convolution of the current object with the initial battery value acc and the folding function specified by the lambda parameter
    takeWhile n As Integer,
    lambda As String
    Returns a container containing n (or less) consecutive values ​​satisfying the predicate specified by the unnamed lambda function
    dropWhile n As Integer,
    lambda As String
    Returns a container containing n (or less) consecutive values ​​obtained from the original after skipping values ​​that satisfy the predicate specified by lambda.
    zip iter As aIter,
    n As Integer = 10
    It accepts a container or generator, and returns a container containing pairs of values ​​- from the base container and from the container container. The default result size is ten.
    zipWith iter As aIter,
    lambda As String,
    n As Integer = 10
    Takes a container and an anonymous function of two arguments. Returns a container containing the results of applying the specified function to successive pairs - one value from the base container, the other from the parameter container.
    Size - For a container, returns the number of elements
    summa - Sum of container values
    production - Product of container values
    maximum - The maximum value in the container
    minimum - The minimum value in the container

    For a generator object, a number of methods are not directly implemented - you must first select a certain number of values ​​in a container. When trying to call an unrealized method for the generator, an error is generated with the code 666. Next, several examples of using the described interfaces will be considered.


    Printing Fibonacci consecutive numbers:
    Sub Test_1() 
    Dim fibGen As aIter
        Set fibGen = New Generator
        fibGen.Init Array(1, 0), "(c,p)->c+p"
        For i% = 1 To 50
            Debug.Print fibGen.getNext()
        Next i%
    End Sub

    Here a generator is created with initial values ​​0 and 1 and a generating function corresponding to the Fibonacci sequence. Next, the first 50 numbers are printed in a loop.
    Map and filter:

    Sub Test_2() 
    Dim co As aIter
    Dim Z As aIter
    Dim w As aIter
        Set co = New Container
        co.Init frange(1, 100)
        Set Z ="x -> 1.0/x"). _
                     take(20).filter(" x -> (x>0.3) or (x<=0.1)")
        iii% = 1
        Do While Z.hasNext()
           Debug.Print iii%; " "; Z.getNext()
           iii% = iii% + 1
    End Sub

    A container is created and initialized by a numerical sequence from the range from 1 to 100. Next, the numbers with map are replaced by the inverse. Of these, the twenty first are taken. Then this population is filtered and numbers larger than 0.3 or less than 0.1 are selected from it. The result is returned in the container, the composition of which is printed.
    Using convolution:

    Sub Test_4() 
    Dim co As aIter
        Set co = New Container
        co.Init frange(1, 100)
        v = co.reduce(0, "(acc,x)->acc+x")
        Debug.Print v
        v = co.reduce(1, "(acc,x)->acc*x")
        Debug.Print v
    End Sub

    Here, using convolution, the sum and product of numbers from 1 to 100 is considered.

    Sub Test_5() 
    Dim co1 As aIter
    Dim co2 As aIter
    Dim co3 As aIter
        Set co1 = New Generator
        co1.Init Array(123456789), "x -> INT(x/10)"
        Set co2 = co1.takeWhile(100, "x -> x > 0")
        Set co3 ="x -> x mod 10")
        Debug.Print co3.maximun
        Debug.Print co3.minimum
        Debug.Print co3.summa
        Debug.Print co3.production
    End Sub

    In this example, the generator co1 is constructed, sequentially dividing the original number by degrees 10. Then the quotients are selected until zero appears. After that, the resulting list of quotients is displayed by the function of taking the remainder of the division by 10. The result is a list of digits of the number. The list is summarized, he calculated the maximum, minimum and product.


    The proposed approach is quite workable and can be successfully applied to solve everyday tasks of a VBA-programmer in a functional style. Why are we worse than the Javists?

    Download examples here.

    Good luck !!!

    Also popular now: