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”

Introduction

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:

r=proc(a,b,”x->x+1”)

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)
     self=x+1
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”)
r=locEv.eval(“self(5)”)

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:


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.

Examples

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 = co.map("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
    Loop
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 = co2.map("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.

conclusions

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 !!!