Let us think for a moment a situation, in which we would like to use a numerical algorithm in our VBA program. A good example could be something like numerical integration algorithm. How should we implement that?
If we think this problem for a moment, we can find two variable components in this problem: integration algorithm itself (Midpoint rule, Simpson's rule, etc), and integrand function (probability density function to be integrated). I could also create one integration engine class for pairing algorithm and integrand together. With algorithm part, we could easily let our design to be flexible by using Strategy Design Pattern (DP). We could create an common interface for algorithm, which is going to be implemented by all possible concrete integration algorithms what we would like to use. In our program, we would then give a concrete interface implementation as argument for our integration engine.
Then comes the most interesting part of this scheme: how should we handle the integrand part? One option would be to set up a separate function library module for all possible integrand functions. Sounds inviting, but how could we tell to our integration engine, what is the function we would like to use for integration? Well, in C++ we have a function pointer mechanism and in C# we have delegate/lambda/anonymous functions available for this purpose. Now, do we have anything similar in Visual Basic? AFAIK, there is not any corresponding mechanism in VB.
However, there is one "sleazy" trick to imitate this function pointer/delegate mechanism in VBA: Application.Evaluate function. If you have never heard about this, you can find some information in here: http://msdn.microsoft.com/en-us/library/office/ff193019.aspx
Numerical integration example
In this example, I will implement a simple numerical integration program. First, we create an interface for all numerical integration algorithms:
Once again, I am using "parameters-wrapped-inside-dictionary" approach for handling parameters. Remember to reference Microsoft Scripting Runtime library in your VB editor. If you do not have any idea, what I am telling here, you can read my posting http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html
In my example program, we need the following enumerator:
After this, we can create algorithm interface implementation, for Midpoint rule:
Then, we can create integration engine for pairing algorithm and integrand together:
Integration engine takes in two arguments: algorithm interface implementation and parameter wrapper. Our integrand function (string) is one of the items in parameter wrapper. Now, integration engine is existing only for pairing algorithm with data, hosting a loop and converting our string-expression function into a value with a given x-value. Function evaluateExpression is "converting" our integrand function into a value with a given x value.
Finally, the actual tester program is presented below:
The whole example program is presented in a frame below here:
Now, I am fully aware that this is one of those "VBA cheap tricks" and needless to say, we cannot even compare this "mechanism" with function pointer or function delegate mechanism. Application.Evaluate is just parsing a string expression into a function expression and returns a result for that expression. However, for some rare special cases in VBA, this trick works and can solve some otherwise quite complex design problems. The different discussion could be, how expensive this approach is. I do not know yet.
Anyway, Have a great day again. Maybe you can come with some completely new insights and uses for Application.Evaluate function. Let me know.