Object Oriented VBA: Delegates

In .NET, a Delegate is a reference type. A reference type points to something – typically an object. A Delegate, however, points to a method. In VB.NET methods are either Functions or Sub procedures. In C#.NET methods are methods – they either return a value or they don’t (ie. return value type is “void”). I’ll come to discuss VBA shortly, but for now I want to illustrate the use of delegates in VB.NET as an aide to their understanding.

Module Main

     Private Delegate Function MyDelegate(ByVal x As Double) As Double

     Sub Main()

          Dim f As MyDelegate

          f = AddressOf Squared

          f = AddressOf Cubed

     End Sub

     Private Function Squared(ByVal d As Double) As Double
          Return d * d
     End Function

     Private Function Cubed(ByVal e As Double) As Double
          Return e * e * e
     End Function

End Module

Firstly, I specify the delegate “MyDelegate”. Crucially, MyDelegate will only point to a Function that takes a single input parameter of type Double and returns a Double. It will only point to methods which have these characteristics. For example, if you try to point it to a Sub procedure that takes a single parameter of type Double, your code won’t compile.

In Main(), I then declare a reference, “f”, of type “MyDelegate”. I then point “f” to the method “Squared” and, on the next line, I invoke the method with the input value “2” and, in doing so, the delegate’s return value is passed in to a message box.

The next two lines are the same as the previous two with the exception that I re-point f at a different method, “Cubed”.

When you run the code you get two message boxes: one with “4” appearing and the other with “8”.

Again, this example is very simple. In practice, the methods pointed to (Squared, Cubed) could be highly complex, each with multiple lines of code involving many different objects. They could exist in different classes – or even different assemblies. The point to remember is that a Delegate is simply a pointer. It can point to any method that matches its definition (parameter list + return type).

In .NET delegates are generally used to support Events, callback procedures and indirect calls to class methods. Visual Basic for Applications (VBA) does not offer Delegates but the preceding sentence offers a clue to mimicking their behaviour. If you’ve understood the above VB.NET you’ll know that a Delegate is simply a pointer to a method with a wrapper to check parameters and return type. Ignoring this latter aspect and concentrating on the former, can you guess what VBA feature allows us to (generically) call a method? If you guessed “CallByName” you’re right.

CallByName allows you, the VBA Developer, to call a particular method of a specified object (eg. class) by passing in these values at run-time. CallByName also allows you to pass in arguments for the called method’s parameter list. I’ll illustrate its use with a problem I had at a previous client.

Sometimes, my work requires that I step out of full developer mode and take on a more quantitative role. One recent situation involved implementing the Newton-Raphson algorithm to find the implied volatility of an Option contract. You don’t need to know what all this means, you just need to know that finding the implied volatility is an iterative process that involves:

(i) taking an initial guess at the value of the implied volatility that you think gives rise to the (observed) market value of the Option,
(ii) passing the guessed value into a function that calculates the Option’s value based on that guess,
(iii) comparing the calculated value with the (observed) market value,
(iv) examining the size of the difference between the two values, and
(v) repeating the process with a new guess until the difference is so small as to be acceptable.

The final guessed value is the implied volatility. Newton-Raphson is an algorithm for determining the next guess at each iteration. It is very efficient.

While I initially coded the algorithm to explicitly point to the Calculate method (ie. Function) of a Pricer class I had defined and instantiated, I wanted to turn the code into something generic that could point to any method of any class. CallByName allowed me to do this. So instead of:

fh = Pricer.Calculate(x1 + h)

I used:

fh = CallByName(objClassInstance, strMethodName, VbMethod, x1 + h)

and passed in the object reference (“objClassInstance”) and name of the method (“strMethodName”) at run time.

While not a true delegate (for there are no conditions on matching the input parameter list and return type), like so many things in VBA, CallByName is a convenient workaround to achieve the same or similar functionality.



    • Turns out that class was originally named “Delegate”. It takes a string, generates an “anonymous method” out of it, and runs it on demand. See it in action on Code Review Stack Exchange, where it’s used to implement predicates for a LINQ-like “Where” method on a custom “List” class: http://codereview.stackexchange.com/q/66706/23788

      Dim accumulator As Delegate
      Set accumulator = Delegate.Create(“(work,value) => value & “” “” & work”)

      Debug.Print LinqEnumerable.FromList(List.Create(“the”, “quick”, “brown”, “fox”)) _

      Produces this output:

      fox brown quick the

      I think this is about as far close to an actual “delegate” as VBA can get.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: