my  block

where uzairs roam

That heading describes a problem I hope you never have to tackle. We’ve got a full-fledged exotics trading system (call it XX) built in Excel with all sorts of fancy bells and whistles. For some reason that escapes me, XX has never been split into a thin front-end sheet backed by an XLA containing all the business logic.

The system is used by a lot of users all over the place for all sorts of things, including, notably, for running risk batches, which of course are crucial for trading. Recently we’d been trying to increase the complexity of batches, so that users could basically tweak all sorts of configuration settings and perform any transformations of market data they may wish to do before running the valuations. There are various ways of doing this, but the most powerful way would be to offer a way to load or inject custom batch logic written in VBA, so that anyone could whip up a custom batch and debug it easily.

In this scenario, the the user would simply specify a text file containing the VBA logic when launching XX in Excel. XX would then run the custom logic and then run the batch. Here’s an example of something we might want to do (‘XX’-prefixed functions are calls to the trading system):

Sub BatchLogic()
    Dim transformations As New Collection
    Call transformations.Add(0.01, "SpotBump")

    Call XXLoadMarketData
    Call XXTransformMarketData(transformations)
End Sub

It sounds a bit hairy given that VBA doesn’t have an evaluate function to execute arbitrary VBA code (you can evaluate worksheet functions, but you don’t have first-class functions like Lisp), but I’m learning you can do practically anything with the VBE if you’re willing to mess around for a bit. The key references, as always, are the Pearson Consulting website and the Erlandsen Consulting website. It took me three tries to get something that worked like I wanted.

My first try was to add a module called ‘Batches’ in the XX VBA project and try to dynimically add a procedure to it and then call it. The code in Batches looked something like this:

Sub InjectAndRunBatchLogic(filename As String)
    Dim text As Collection

    ' Load VBA from file
    text = ReadFileAsCollection(filename)

    ' Add to XX
    Call AddProcedureToModule("Batches", text)

    'Call BatchLogic
    Application.Run ("BatchLogic")
End Sub

The call to BatchLogic is commented out in favour of Application.Run since the function will not exist in XX except when running a custom batch (and will therefore cause compilation problems during normal development).

Unfortunately, it turns out that VBA compiles the module when it is used, so that dynamically modifying the module will either result in BatchLogic not being found (because it didn’t exist when InjectAndRunBatchLogic was called) or Excel crashing (if you try to put a stub BatchLogic in Batches and then delete it before creating it from the text file). Of course, once InjectAndRunBatchLogic has finished running and BatchLogic exists in the module, one can call it. That’s not much use though, because I don’t have the option of restarting the VBA project flow.

The next thing I tried was to dynamically add a new module to XX and then add the new batch procedure to this new module. The idea was that the new module wouldn’t be compiled until BatchLogic was called. Here’s what the code looked like:

Sub InjectAndRunBatchLogic(filename As String)
    Dim text As Collection

    ' Load VBA from file
    text = ReadFileAsCollection(filename)

    ' Add a temporary module to XX
    Call AddModuleToProject("Temporary")

    ' Add to XX
    Call AddProcedureToModule("Temporary", text)

    Application.Run ("BatchLogic")
End Sub

This ended up working decently well, until I password-protected XX’s VBA project so that I could test it the way users use it. Turns out you can’t add modules to password-protected VBA projects. They can’t even add modules to themselves! There’s a workaround using SendKeys that lets you unlock the project, but every reference to the method that I’ve seen warns that it’s flaky at best.

So, back to the drawing board. Since the problem was XX being password-protected, how about creating a new workbook that isn’t password-protected and adding the module there? Worked like a charm. Here’s the code (bit more explicit this time):

Sub InjectAndRunBatchLogic(filename As String)
    Dim wbk As Workbook

    Application.ScreenUpdating = False

    ' Create new workbook
    Set wbk = Workbooks.Add

    With wbk.VBProject
        ' Add module to new workbook (default name: 'Module1')
        .VBComponents.Add (vbext_ct_StdModule)

        ' Add the procedure from the batch file
        .VBComponents("Module1").CodeModule.AddFromFile(filename)

        ' Add reference that points to XX
        .References.AddFromFile(ThisWorkbook.VBProject.filename)
    End With

    ThisWorkbook.Activate
    Application.ScreenUpdating = True

    Application.Run ("'" & wbk.Name & "'!BatchLogic")

    ' Get rid of the new workbook
    wbk.Close (False)
End Sub

You can ignore the ScreenUpdating and workbook activation code — that’s just there to make sure that XX is what remains visible to the user. Since the batch logic will now end up in a separate workbook, you can see I am adding a reference to XX so that it is able to call XX functions. This is actually a fairly nice solution because one can add arbitrary references to the new workbook without having to add them to XX.

So, boys and girls, that’s how that worked out. It’s always fascinating to me how powerful and flexible an environment VBA provides…but of course, that’s also the number one reason for abusing VBA to do things that really should be done in other places.