Implementing Observer Design Pattern in VBA with Events

I have "known" VBA Events for years. Once I created one application for hosting some data updating process. To automatize that process, I set a task for opening Excel workbook with Windows Scheduler. As soon as Scheduler was opening Excel workbook in the morning, Workbook_Open() method was kicking in and the magic was happening.

To be honest, for a long time I was lazy to dig deeper and also did not find any other "real" use for VBA Events, except these common Events for Excel workbook objects. I think this is not anything uncommon. If you use Google to find some practical examples, you will find a lot of examples, but they are almost always some examples with VBA User Forms. Anyway, at some point I was doing some semi-serious woodshedding with Observer Design Pattern and started to think about Events again. I started to study these a bit deeper and found out one pretty nice thing: I could create my own Events and use those in my classes. This current post is a result of that discovery.

In this post, I am opening up one possible Observer Design Pattern implementation with VBA Event mechanism. Remember to check my previous post on doing the same thing without VBA Events http://mikejuniperhill.blogspot.fi/2013/05/implementing-observer-design-pattern-in.html

First we create data source class (VBA Class Module, name = DataSource):

Option Explicit
'
Public Event dataFeedUpdate(ByVal marketData As Double)
'
Public Function getMarketDataUpdate(ByVal marketData As Double)
    '
    RaiseEvent dataFeedUpdate(marketData)
End Function
'

Then we create class for all "observers" (VBA Class Module, name = DataObserver):

Option Explicit
'
Private WithEvents source As DataSource
'
Public Function registerPublisher(ByRef dataFeed As DataSource)
    '
    Set source = dataFeed
    Debug.Print "Observer " & ObjPtr(Me) & " registered data feed"
End Function
'
Public Function unRegisterPublisher()
    '
    Set source = Nothing
    Debug.Print "Observer " & ObjPtr(Me) & " unregistered data feed"
End Function
'
Private Sub source_dataFeedUpdate(ByVal marketData As Double)
    '
    Debug.Print "Market data update from observer " & ObjPtr(Me) & ": " & marketData
End Sub
'

Then again, we create one tester program (VBA Standard Module). In tester program, I have been creating one test run, in which observers are registering/unregistering data source, which is sending data feed updates for observers:

Option Explicit
'
Public Sub tester()
    '
    ' create data source object and two observers
    Dim source As New DataSource
    Dim observer1 As New DataObserver
    Dim observer2 As New DataObserver
    '
    ' register datafeed publisher for both observers
    observer1.registerPublisher source
    observer2.registerPublisher source
    '
    ' send data feed
    source.getMarketDataUpdate 100.25
    source.getMarketDataUpdate 100.15
    '
    ' observer2 unregistering, send data feed
    observer2.unRegisterPublisher
    source.getMarketDataUpdate 100.1
    '
    ' observer1 unregistering, send data feed
    observer1.unRegisterPublisher
    source.getMarketDataUpdate 100.1
    '
    ' observer1 re-registering, send data feed
    observer1.registerPublisher source
    source.getMarketDataUpdate 100.2
    '
    ' observer2 re-registering, send data feed
    observer2.registerPublisher source
    source.getMarketDataUpdate 100.25
    '
    ' destroy datafeed and both observers
    Set observer2 = Nothing
    Set observer1 = Nothing
    Set source = Nothing
End Sub
'

Resulting Immediate Window printout (observer1 in blue, observer2 in red):
Observer 237412240 registered data feed
Observer 237411920 registered data feed
Market data update from observer 237412240: 100,25
Market data update from observer 237411920: 100,25
Market data update from observer 237412240: 100,15
Market data update from observer 237411920: 100,15
Observer 237411920 unregistered data feed
Market data update from observer 237412240: 100,1
Observer 237412240 unregistered data feed
Observer 237412240 registered data feed
Market data update from observer 237412240: 100,2
Observer 237411920 registered data feed
Market data update from observer 237412240: 100,25
Market data update from observer 237411920: 100,25

One more thing: when you create your custom event, the object (DataObserver) in which the Event-related function is going to be called, must have reference to its caller (DataSource). Otherwise calling object (DataSource) do not know, what object it should call. I am doing that "binding" in registerPublisher function. This issue was actually causing a lot of grey hairs for me, until I got the point.

This was another possible implementation of Observer DP in VBA by using Event mechanism. Nice to know also. Have a nice weekend!
-Mike

Implementing Observer Design Pattern in VBA without Events

Observer Design Pattern (DP) can be useful tool in your DP toolpack. Now, for what reason is this pattern used for? For example, looking closely to Bloomberg BBCOM API methods and its mechanism, leads me to assume strongly that the market data retrieving process has been implemented by using Observer DP. You could use this pattern, if you get some feed from somewhere and you would like to save the result of that feed to somewhere else, for example.

Anyway, In the following example below, I am presenting one possible VBA implementation of Observer DP, without using Events. First we create "source" class (VBA Class Module, name = DataSource):

Option Explicit
'
Private observers As Collection
Private data As Double
'
Public Function registerObserver(ByRef observer As DataObserver)
    '
    Debug.Print "Observer " & ObjPtr(observer) & " registered"
    observers.Add observer
End Function
'
Public Function unRegisterObserver(ByRef observer As DataObserver)
    '
    Dim i As Integer
    For i = 1 To observers.Count
        If ((ObjPtr(observers(i)) = (ObjPtr(observer)))) Then
            Debug.Print "Observer " & ObjPtr(observers(i)) & " unregistered"
            observers.Remove i
            Exit For
        End If
    Next i
End Function
'
Private Function notifyObservers()
    '
    If (observers.Count > 0) Then
        Dim i As Integer
        For i = 1 To observers.Count
            observers(i).updateMarketData (data)
        Next i
    End If
End Function
'
Public Function getMarketDataUpdate(ByVal marketData As Double)
    '
    data = marketData
    notifyObservers
End Function
'
Private Sub Class_Initialize()
    Set observers = New Collection
End Sub
'
Private Sub Class_Terminate()
    Set observers = Nothing
End Sub
'

Then we create class for "observers" (VBA Class Module, name = DataObserver):

Option Explicit
'
Private data As Double
'
Private Function displayMarketData()
    Debug.Print "Market data update from observer " & ObjPtr(Me) & ": " & data
End Function
'
Public Function updateMarketData(ByVal marketData As Double)
    data = marketData
    displayMarketData
End Function
'

Then we create tester program (VBA Standard Module). In tester program, I have been creating one test run, in which the data source is registering/unregistering observers and sending data feed for them:

Option Explicit
'
Public Sub tester()
    '
    ' create data source object and two observers
    Dim source As New DataSource
    Dim observer1 As New DataObserver
    Dim observer2 As New DataObserver
    '
    ' register both observers
    source.registerObserver observer1
    source.registerObserver observer2
    '
    ' data source sends feed to both observers
    source.getMarketDataUpdate 100.25
    source.getMarketDataUpdate 100.15
    '
    ' unregister observer2, data source sends feed to observer1
    source.unRegisterObserver observer2
    source.getMarketDataUpdate 100.1
    '
    ' unregister observer1, no data feed to any observer
    source.unRegisterObserver observer1
    source.getMarketDataUpdate 100.1
    '
    ' re-register observer1, data source sends feed to observer1
    source.registerObserver observer1
    source.getMarketDataUpdate 100.2
    '
    ' re-register observer2, data source sends feed to both observers
    source.registerObserver observer2
    source.getMarketDataUpdate 100.25
    '
    ' destroy source and observers
    Set observer2 = Nothing
    Set observer1 = Nothing
    Set source = Nothing
End Sub
'

Resulting Immediate Window printout (observer1 in blue, observer2 in red):

Observer 242968104 registered
Observer 242967944 registered
Market data update from observer 242968104: 100,25
Market data update from observer 242967944: 100,25
Market data update from observer 242968104: 100,15
Market data update from observer 242967944: 100,15
Observer 242967944 unregistered
Market data update from observer 242968104: 100,1
Observer 242968104 unregistered
Observer 242968104 registered
Market data update from observer 242968104: 100,2
Observer 242967944 registered
Market data update from observer 242968104: 100,25
Market data update from observer 242967944: 100,25

This was one possible implementation of Observer DP in a nutshell. Nice to know. Have a nice day. 
-Mike

Printing Bloomberg historical price screen with DDE

Sometimes we need to have printed paper documentations for Bloomberg pricing sources used for valuations for each and every security in our bond books. Now, imagine that there are a few hundred bonds out there and you should print these screenshots one by one: by manually inserting every required parameter in Bloomberg screen and pressing print. Frankly said, this would be more than time-expensive and tedious task, and definitely not something I would like to be involved with. However, I have to be involved. How could I outsource this process for a machine?

Fortunately, there is one approach what you can use in VBA: Dynamic Data Exchange (DDE). DDE has its problems and in general, I try to avoid using it. However, it has its rare moments exactly for things like this. Instead of doing this process manually, I have a program which reads a list of required parameters and then prints out price documentations one by one by using DDE.

The point in my example given below is, that you will get the basic idea how you could use DDE for this particular type of problem. Then, you can work for developing your own solution. You can copy-paste the following program into a new VBA Standard Module.

Note: for this program you need to have active Bloomberg licence in your machine.

Option Explicit
'
Private Const CONST_APP As String = "WINBLP"
Private Const CONST_TOPIC As String = "BBK"
'
Private channel As Long
Private executionString As String
'
Public Sub tester()
    '
    On Error GoTo errHandler
    '
    channel = DDEInitiate(CONST_APP, CONST_TOPIC)
    '
    Dim executionString As String: executionString = createExecutionString("DE0001102317", "CBBT", "D", "B")
    DDEExecute channel, executionString
    '
    DDETerminate channel
    Exit Sub
    
errHandler:
    MsgBox "Undefined error", vbCritical, "Error"
    Exit Sub
End Sub

Private Function createExecutionString(ByVal isinCode As String, _
ByVal provider As String, ByVal period As String, ByVal quoteType As String) As String
    '
    Dim executionString As String
    executionString = "<BLP-1><HOME>ID " & isinCode & "<GO>" & _
                      "HP<GO><TABR>" & _
                      provider & "<TABR>" & _
                      "<TABR><TABR><TABR><TABR>" & _
                      "<TABR><TABR><TABR>" & _
                      period & quoteType & "<GO>" & _
                      "<PRINT>"
    '
    createExecutionString = executionString
End Function
'

As you can see, the beef is inside createExecutionString function which creates executionString for DDEExecute function. When you open any Bloomberg function (say, SWPM<GO> for example), you can set up input values for all required argument fields by

1) clicking a field active with your mouse and then writing a value into a field
2) moving with keyboard TAB and then writing a value into a field

As you can see in createExecutionString function, it uses the latter approach by moving with keyboard TAB and then writing a value into a field. If you run that test program, you will see that it actually does all of this: moving with cursor in fields and writing a value. It's like that macro what you can record in your Bloomberg. 

Anyway, I hope that you would find this post to be helpful and it could help you to solve your problem. Have a great start for June!
-Mike

Cheap trick for imitating function pointer/delegate mechanism in VBA

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:

Option Explicit
'
Public Function x(ByRef p As Scripting.Dictionary) As Double
End Function
'

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:

Option Explicit
'
Public Enum VAR
    '
    a = 1
    b = 2
    n = 3
    i = 4
    integrand = 5
End Enum
'

After this, we can create algorithm interface implementation, for Midpoint rule:

Option Explicit
'
Implements IIntegrationAlgorithm
'
Private Function IIntegrationAlgorithm_x(ByRef p As Scripting.IDictionary) As Double
    '
    Dim a As Double: a = p.Item(VAR.a)
    Dim b As Double: b = p.Item(VAR.b)
    Dim n As Long: n = p.Item(VAR.n)
    Dim dx As Double: dx = (b - a) * (1 / n)
    Dim i As Long: i = p.Item(VAR.i)
    '
    IIntegrationAlgorithm_x = (0.5 * ((a + dx * i) + (a + dx * (i + 1))))
End Function
'

Then, we can create integration engine for pairing algorithm and integrand together:

Option Explicit
'
Public Function getIntegral(ByRef p As Scripting.IDictionary, _
ByRef algorithm As IIntegrationAlgorithm) As Double
    '
    Dim n As Long: n = p.Item(VAR.n)
    Dim dx As Double: dx = (p.Item(VAR.b) - p.Item(VAR.a)) * (1 / n)
    Dim integral As Double
    Dim i As Long
    '
    For i = 0 To (n - 1)
        p.Item(VAR.i) = i
        Dim x_value As Double: x_value = algorithm.x(p)
        Dim f_x As Double: f_x = evaluateExpression(p.Item(VAR.integrand), "x", x_value) * dx
        integral = integral + f_x
    Next i
    '
    getIntegral = integral
End Function
'
Private Function evaluateExpression(ByVal f_x As String, ByVal x_name As String, _
ByVal x_value As Double) As Double
    '
    f_x = Replace(f_x, x_name, Replace(CStr(x_value), ",", "."))
    evaluateExpression = Application.Evaluate(f_x)
End Function
'

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:

Option Explicit
'
Public Sub tester()
    '
    Dim parameters As New Scripting.Dictionary
 
    parameters.Add VAR.a, CDbl(2)
    parameters.Add VAR.b, CDbl(4)
    parameters.Add VAR.n, CLng(100)
    parameters.Add VAR.integrand, CStr("((x-(Sin(2*x)*0.5))*0.5)+(x^4/4)")
    '
    Dim algorithm As IIntegrationAlgorithm
    Set algorithm = New Midpoint
    '
    Dim engine As New Univariate_IntegrationEngine
    Dim integral As Double: integral = engine.getIntegral(parameters, algorithm)
    Debug.Print integral
    '
    Set engine = Nothing
    Set algorithm = Nothing
    Set parameters = Nothing
End Sub
'

The whole example program is presented in a frame below here:

' VBA STANDARD MODULE
Option Explicit
'
Public Enum VAR
    '
    a = 1
    b = 2
    n = 3
    i = 4
    integrand = 5
End Enum
'
'
'
' VBA STANDARD MODULE
Option Explicit
'
Public Sub tester()
    '
    Dim parameters As New Scripting.Dictionary
    '
    parameters.Add VAR.a, CDbl(2)
    parameters.Add VAR.b, CDbl(4)
    parameters.Add VAR.n, CLng(100)
    parameters.Add VAR.integrand, CStr("((x-(Sin(2*x)*0.5))*0.5)+(x^4/4)")
    '
    Dim algorithm As IIntegrationAlgorithm
    Set algorithm = New Midpoint
    '
    Dim engine As New Univariate_IntegrationEngine
    Dim integral As Double: integral = engine.getIntegral(parameters, algorithm)
    Debug.Print integral
    '
    Set engine = Nothing
    Set algorithm = Nothing
    Set parameters = Nothing
End Sub
'
'
'
' VBA CLASS MODULE (NAME = IIntegrationAlgorithm)
Option Explicit
'
Public Function x(ByRef p As Scripting.Dictionary) As Double
End Function
'
'
'
' VBA CLASS MODULE (NAME = Midpoint)
Option Explicit
'
Implements IIntegrationAlgorithm
'
Private Function IIntegrationAlgorithm_x(ByRef p As Scripting.IDictionary) As Double
    '
    Dim a As Double: a = p.Item(VAR.a)
    Dim b As Double: b = p.Item(VAR.b)
    Dim n As Long: n = p.Item(VAR.n)
    Dim dx As Double: dx = (b - a) * (1 / n)
    Dim i As Long: i = p.Item(VAR.i)
    '
    IIntegrationAlgorithm_x = (0.5 * ((a + dx * i) + (a + dx * (i + 1))))
End Function
'
'
'
' VBA CLASS MODULE (NAME = Univariate_IntegrationEngine)
Option Explicit
'
Public Function getIntegral(ByRef p As Scripting.IDictionary, _
ByRef algorithm As IIntegrationAlgorithm) As Double
    '
    Dim n As Long: n = p.Item(VAR.n)
    Dim dx As Double: dx = (p.Item(VAR.b) - p.Item(VAR.a)) * (1 / n)
    Dim integral As Double
    Dim i As Long
    '
    For i = 0 To (n - 1)
        p.Item(VAR.i) = i
        Dim x_value As Double: x_value = algorithm.x(p)
        Dim f_x As Double: f_x = evaluateExpression(p.Item(VAR.integrand), "x", x_value) * dx
        integral = integral + f_x
    Next i
    '
    getIntegral = integral
End Function
'
Private Function evaluateExpression(ByVal f_x As String, ByVal x_name As String, _
ByVal x_value As Double) As Double
    '
    f_x = Replace(f_x, x_name, Replace(CStr(x_value), ",", "."))
    evaluateExpression = Application.Evaluate(f_x)
End Function
'

Some afterthoughts

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.
-Mike

Implementing simple pricer with Visitor Design Pattern in VBA

In this short post, we are using Visitor Design Pattern for implementing simple pricer design in VBA. In essence, Visitor enables us to separate data and algorithms. Instrument knows all its parameters for a specific financial instrument and Pricer knows its specific pricing algorithm for a specific financial instrument. Somehow, we need to pair these two components together. Visitor enables that.

Instrument

Instrument class is there only for setting, holding and distributing all instrument-related parameters inside parameter wrapper. In essence, it is only having setter and getter methods. If you do not have any idea what is going on with parameter wrapper, you can read the following post http://mikejuniperhill.blogspot.fi/2013/05/handling-parameters-dynamically-with.html

IPricer

This is a public interface, which has only one method - visit, which takes in Instrument object as an argument. All concrete Pricers are implemented from this interface. Pricer implementation has specific pricing algorithm for some specific type of financial instrument, which has some specific input parameters. With the mechanism used in Visitor, a Pricer has an access to parameter wrapper of Instrument and hereby, it can use all instrument-related parameters for its specific pricing algorithm.

Example

First, enumerator for the needed parameter wrapper is given below. Note, that you could add any number of enumerated constants, as long as those constants are unique:

Option Explicit

'

Public Enum PRM

    '

    s = 1 'spot

    y = 2 'yield

    m = 3 'maturity

    p = 4 'price

End Enum

' 

So, let us go through one simple example program, where we are pricing a simple non-cashflow-paying forward with cost-of-carry-method. First, we create a set of needed parameters into parameter wrapper. Remember to reference Microsoft Scripting Runtime library from VB editor:

Option Explicit

'

Sub tester()

    '

    Dim parameters As New Scripting.Dictionary

    parameters.Add PRM.s, CDbl(100)

    parameters.Add PRM.y, CDbl(0.02)

    parameters.Add PRM.m, CDbl(1.25)

    '

End Sub

'

After this, we create desired Pricer implementation, which has a specific pricing algorithm for a simple forward:

Option Explicit

'

Sub tester()

    '

    Dim parameters As New Scripting.Dictionary

    parameters.Add PRM.s, CDbl(100)

    parameters.Add PRM.y, CDbl(0.02)

    parameters.Add PRM.m, CDbl(1.25)

    '

    Dim pricer As IPricer

    Set pricer = New CostOfCarry

    '

End Sub

'

Next, we create the actual Instrument and feed our parameter wrapper for it. Then, we set up desired Pricer implementation by using accept function of Instrument object. Inside this accept function, Pricer’s visit function is then going to get called. In this visit function, we are giving Instrument itself as an argument for Pricer. Effectively, Pricer now has an access into parameter wrapper of Instrument and it can use all forward parameters for its specific pricing algorithm for a simple forward. Finally, Pricer saves the result of the pricing algorithm directly back to parameter wrapper, from which we can get the result of pricing. Finally, we release all objects involved:

Option Explicit

'

Sub tester()

    '

    Dim parameters As New Scripting.Dictionary

    parameters.Add PRM.s, CDbl(100)

    parameters.Add PRM.y, CDbl(0.02)

    parameters.Add PRM.m, CDbl(1.25)

    '

    Dim pricer As IPricer

    Set pricer = New CostOfCarry

    '

    Dim forward As New Instrument

    forward.setParameters parameters

    forward.accept pricer

    Debug.Print forward.getParameters.Item(PRM.p)

    '

    Set forward = Nothing

    Set pricer = Nothing

    Set parameters = Nothing

End Sub

'

All Modules and Classes involved are given here below.

' STANDARD VBA MODULE
Option Explicit
'
Sub tester()
    '
    Dim parameters As New Scripting.Dictionary
    parameters.Add PRM.s, CDbl(100)
    parameters.Add PRM.y, CDbl(0.02)
    parameters.Add PRM.m, CDbl(1.25)
    '
    Dim pricer As IPricer
    Set pricer = New CostOfCarry
    '
    Dim forward As New Instrument
    forward.setParameters parameters
    forward.accept pricer
    Debug.Print forward.getParameters.Item(PRM.p)
    '
    Set forward = Nothing
    Set pricer = Nothing
    Set parameters = Nothing
End Sub
'
'
' STANDARD VBA MODULE
Option Explicit
'
Public Enum PRM
    '
    s = 1 'spot
    y = 2 'yield
    m = 3 'maturity
    p = 4 'price
End Enum
'
'
' VBA CLASS MODULE (NAME = IPricer)
Option Explicit
'
Public Function visit(ByRef security As Instrument)
End Function
'
'
'
Option Explicit
'
' VBA CLASS MODULE (NAME = CostOfCarry)
Implements IPricer
'
Private Function IPricer_visit(ByRef security As Instrument)
    '
    Dim s As Double: s = security.getParameters.Item(PRM.s)
    Dim y As Double: y = security.getParameters.Item(PRM.y)
    Dim m As Double: m = security.getParameters.Item(PRM.m)
    security.getParameters.Item(PRM.p) = s * Exp(y * m)
End Function
'
'
' VBA CLASS MODULE (NAME = Instrument)
Option Explicit
'
Private p As Scripting.Dictionary
'
Public Function accept(ByRef pricer As IPricer)
    pricer.visit Me
End Function
'
Public Function getParameters() As Scripting.IDictionary
    Set getParameters = p
End Function
'
Public Function setParameters(ByRef parameters As Scripting.IDictionary)
    Set p = parameters
End Function
'

The purpose of this posting was to open up one possible approach for implementing Visitor pattern in VBA. Visitor itself enables us to separate data and algorithms, which enables us to create easily extendable designs. For example, if we ever need any new pricing algorithm for that forward in our example, we just need to plug-in a new Pricer implementation for a forward into our existing design.

Moreover, since all instrument-related parameters are managed inside that dynamic parameter wrapper, there is no need to modify Instrument object in any way. The only thing what changes for specific instrument, is the content of that parameter wrapper. Note, that we can also store objects inside that parameter wrapper. This means, that we could store Term Structure object, which contains the complete valuation curve, as a one Instrument-related parameter in that wrapper, for example.

I hope that you have got some kind of an idea about my Visitor pattern implementation and what is going on in that program. Personally, I find this scheme of separating data and algorithms to be extremely valuable.
-Mike

VBA wrapper for ADODB objects

Currently, for all Excel/VBA database-related working, I have been using Microsoft ADO (ActiveX Data Object library). It's already a bit old technology already and its enhanced brother ADO.NET is very unfortunately available only for .NET. If ADO is completely new topic for you, there is a lot of information available in Google. You should get yourself familiar with the following ADO objects and their corresponding important methods and properties to get started:

Connection (open, close, state)
Recordset (open, close, state, fields)

With these methods, properties and some good working example, you should have enough information to understand and learn to work with ADO connection and recordset.

In this post, I will open up my VBA wrapper for Microsoft ADO. There is a couple of things you should be aware. ADO is technically speaking dll library, what should be referenced before you can use it. Now, you have two options:

Early binding

You can reference it in a "standard way": (VB editor -- Tools -- Option -- Microsoft ActiveX Data Objects 6.X Library). This is called early binding. Now, with this approach, you have to do this referencing every time you import your custom VBA wrapper class into any new VBA project. The upside is, that all ADO object methods and properties are available in your intellisense. When you use early binding, you create your connection object like this:

Dim cn As New ADODB.Connection

Late binding

In this case, you do not need to do any dll referencing in your VB editor. You are doing it implicitly in your program. The only (AFAIK) notably downside is, that all different object methods and properties are not available in your intellisense anymore. When you use late binding, you create your connection object like this:

Dim cn As Object: Set cn = CreateObject(“ADODB.Connection”)

VBA ADO Wrapper interface

Example wrapper has only one public interface function "cGetRecordset", which returns an object for its caller (ADODB recordset) and takes in four arguments.

cUserID - your personal database username (can be empty string)
cPassword - your personal database password  (can be empty string)
cSqlQueryString - working SQL string
cDSN - connection string

I think that all the other arguments, except connection string should be self-explainable. In a connection string, we define the protocol, what we are using for getting an access to a database. Different database types are requiring different protocols for accessing them. Moreover, in a connection string we define data source from where the data is going to be retrieved. For working examples, the following website is an excellent reference for different connection strings available for numerous different databases: http://connectionstrings.com/

Below is my example tester program and wrapper class for retrieving data from MS Access database. Note, that I am using late-binding, in order to avoid any dll referencing when I want to use the wrapper in my new VBA projects. Note also, that the wrapper returns an object, having a type of ADODB recordset. You have to know how to "disassemble" data from ADO recordset back to program-level. Example is given in a tester program (NxM matrix).

Now, that's all I wanted to share about Microsoft ADO this time. Personally, I find it to be flexible tool for all database-related work in Excel/VBA. Have a nice day!
-Mike

' TESTER PROGRAM (STANDARD VBA MODULE)
'
Option Explicit
'
Sub tester()
    '
    On Error GoTo errorHandler
    '
    Dim data As New ADO_wrapper
    Dim sqlQueryString As String: sqlQueryString = "SELECT * FROM bonds"
    Dim connectionString As String: connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mka\temp\bondPosition.accdb;"
    '
    Dim result As Object
    Set result = data.cGetRecordset(connectionString, sqlQueryString)
    '
    ' Do your things with data here
    Dim i As Long, j As Long
    For i = 1 To result.RecordCount
        For j = 1 To result.Fields.Count
            Debug.Print result.Fields(j - 1)
        Next j
        result.movenext
    Next i
    '
    Set result = Nothing
    Set data = Nothing
    Exit Sub
errorHandler:
    Debug.Print Err.Source & ", " & Err.Description
End Sub
'

' ADO WRAPPER (VBA CLASS MODULE)
'
Option Explicit
'
Private Const CONST_AD_USE_CLIENT = 3
Private Const CONST_AD_OPEN_STATIC = 3
Private Const CONST_AD_LOCK_READONLY = 1
'
Private cUserID As String
Private cPassword As String
Private cDSN As String
Private cSqlQueryString As String
'
Private cConnection As Object 'ADODB.Connection
Private cRecordset As Object 'ADODB.Recordset
'
Public Function cGetRecordset(ByVal DSN As String, ByVal sqlQueryString As String, _
Optional ByVal userID As String, Optional ByVal password As String) As Object 'ADODB.Recordset
    '
    cUserID = userID
    cPassword = password
    cDSN = DSN
    cSqlQueryString = sqlQueryString
    '
    cOpenConnection
    cOpenRecordset
    '
    Set cGetRecordset = cRecordset
End Function
'
Private Function cOpenConnection()
    '
    Set cConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    cConnection.Open cDSN, cUserID, cPassword
End Function
'
Private Function cCloseConnection()
    '
    cConnection.Close
    Set cConnection = Nothing
End Function
'
Private Function cOpenRecordset()
    '
    Set cRecordset = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
    cRecordset.CursorLocation = CONST_AD_USE_CLIENT
    cRecordset.Open cSqlQueryString, cConnection, CONST_AD_OPEN_STATIC, CONST_AD_LOCK_READONLY
End Function
'
Private Function cCloseRecordset()
    '
    cRecordset.Close
    Set cRecordset = Nothing
End Function
'
Private Sub Class_Terminate()
    '
    If Not (cRecordset Is Nothing) Then Set cRecordset = Nothing
    If Not (cConnection Is Nothing) Then Set cConnection = Nothing
End Sub
'