Using the COM Interface (Excel / VBA)

Overview

All functions available within Quantlab may be called from any COM-aware program such as Microsoft Excel or Microsoft Visual Basic. This includes the built-in library of financial functions, user-written QLang functions, and compiled QLL functions.

Note: When using 32-bit VBA/Excel you should run ql_com_tool.exe. For 64-bit VBA/Excel you run ql_com_tool-64.exe. This holds true regardless which version of Quantlab you are using!

Setup

Generating the tlb File

Before accessing your functions from Excel, generate a .tlb file (type library file) so Excel knows about your functions:

ql_com_tool [-U] -t [-f]    # Generate/register tlb (-U = per-user, -f = force)
ql_com_tool [-U] -r          # Register com library dll and tlb
ql_com_tool [-U] -u          # Unregister

This generates ql_com.tlb in your Quantlab folder. Regenerate whenever you update library files or DLLs.

Setting Environment PATH

The PATH must include the folder containing ql_com.dll for Excel to find it in Tools | References.

Register the qlab31-64.dll

First-time setup – register the Quantlab COM library:

ql_com_tool -U -r

Setting References in VBA Editor

In the VBA Editor: Tools -> References -> Browse to ql_com.tlb. Verify that “Quantlab COM Library” is checked. Browse the “ql30” library in the Object Browser to see available functions.

COM Function Overloading

COM does not support function overloading. Use option(com_name: 'alias') in QLang to publish alternate names:

number my_new_func(number x, number y)
    option(com_name: 'my_new_func2')
{
    return x * y;
}

VBA Example – Tail Yields

Calculate instrument maturities and tail yields using QLang functions from Excel VBA. The example file is in Quantlab\examples\COM\:

Function mat(c As String, d As Date) As Variant
    On Error GoTo err:
        Dim out() As Date
        Dim iv() As ql.instrument
        Dim cv As ql.curve

        Set app = CreateObject("QLang.Application")
        Set cv = app.db_curve(c, d)
        iv = cv.instruments()

        ReDim out(UBound(iv))
        For i = 0 To UBound(iv)
            out(i) = iv(i).maturity
        Next

        mat = out
        Exit Function
err:
    MsgBox "Error" & Str(err.Number) & ": " & err.Description
End Function

Function tail_graph(curvename As String, tradedate As Date) As Variant
    On Error GoTo err:
        Dim fr As ql.fit_result

        Set app = CreateObject("QLang.Application")
        Set fr = app.bootstrap(app.db_curve(curvename, tradedate))

        Dim m() As Date
        m = mat(curvename, tradedate)

        Dim out() As Double
        ReDim out(UBound(m) - 1)

        For i = 0 To UBound(out)
            out(i) = fr.zero_rate_dc(tradedate, m(i), m(i + 1),
                     "simple", "ACT360") * 100
        Next

        tail_graph = out
        Exit Function
err:
    MsgBox "Error" & Str(err.Number) & ": " & err.Description
End Function

Debugging QLang from Excel / COM

Since Quantlab version 4107, you can debug VBA/COM sessions directly in the QLang debugger.

Start a COM debug session:

C:\Algorithmica\Quantlab> quantlab-64.exe -Z -c

This opens a Debugger with a “cproxy_server_com” thread. Multiple Excel/VBA sessions can connect.

Note: The Quantlab Editor/Debugger will be in “lock-down” mode during the debugging session.