
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.
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:
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:
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:
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:
After executing this code, the value of the variable r will be 6.
Here three points should be made:
Next, an object implementation model will be described.
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.
Printing Fibonacci consecutive numbers:
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:
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:
Here, using convolution, the sum and product of numbers from 1 to 100 is considered.
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 !!!
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:
Method name | Parameters | Result |
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 |
Init | iniVal 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.
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 !!!