Using the COM interface

Using the COM interface in VBA

All functions available within Quantlab may be called from any COM-aware program such as Microsoft Excel or Microsoft Visual Basic. This includes not only the built-in library of financial functions but also user-written functions implemented in Qlang (and saved as a library file), as well as compiled functions implemented as a Dynamic Link Library (DLL).

Note

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

Generating the tlb file

Before you try to access your own library functions and DLL:s you need to generate a .tlb-file (type library file). Using this file Excel (or Visual Basic) knows enough about your functions to include them in the Object Browser. To produce such a file from Quantlab, proceed as follows:

Install the x86 and/or the x64 version(s) of the com dll and the exe plus a suitable tlb-file (ql_com.tlb) next to quantlab-64.exe in a quantlab installation.

Note that the tlb file can in some circumstances already be created and installed in the root catalog of the installation. If so, you can proceed to the registration of the tlb below.

Generate and administer the tlb file using a command promt set to the current Quantlab working directory. In general, it should be enough to run the command window as user (using the -U option below.) If that does not work you might need to run the command promt with administrator privileges in order to register to tlb file.

‘ql_com_tool [-U] -t [-f]’ updates/generates a tlb-file and registers it. ‘-U’ means registration per-user, and ‘-f’ forces the creation of a tlb file if one is not present or is invalid.

‘ql_com_tool [-U] -r’ registers the com library dll and tlb.

‘ql_com_tool [-U] -u’ unregisters the com library and tlb.

This results in a ql_com.tlb file being generated in your Quantlab folder. The file includes information about all functions currently available in your library files and DLL:s. Note that you need to generate a new .tlb file if you update your library files (or DLL:s) and want the new functions to be available via the COM-library.

Setting Environment PATH

Before starting Excel to run the com library, the environment path must point to where you have placed the ql_com.dll file. This in order for excel to find it in the list of available libraries in Tools | References in the VBA editor.

To set the path, open the environment variables on your PC and extend the list of paths to include your local path. Either user or system path must be set.

Register the qlab31-64.dll

When using the Qlang libraries from Excel/VBA for the first time, the qlab31-64.dll needs to be registered first. Ensure that the user / system path is pointing to the folder where you have the dll (see above). Then run the command prompt using the -r option. Run preferably using the -U (user) option so you do not have to be administrator on your pc. Example below:

Running Excel and setting references in VBA Editor

Now you can start Microsoft Excel or Visual Basic, start the Visual Basic Editor and open References under the Tools menu. Use the Browse button to find the generated ql_com.tlb file and verify that ‘Quantlab COM Library’ is included in the list and marked as active. You only have to do this once - every time you update the .tlb file it is enough to close and reopen Excel. Make sure that Excel or any program that uses the .tlb file is closed when you generate a new file - otherwise Quantlab will be unable to overwrite the existing file.

Use the Object Browser and browse the ‘ql30’ library to examine what functions are available. To get more help on each function, use the function browser in Quantlab.

COM function overloading

Note that COM does not allow for overloaded functions to be exported. To get around this, it is possible to publish a user-defined name to the COM interface.

By using the key-word: option(com_name: <string>) directly after the function name, an alternate name of the function will be published to the COM interface. Example:

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

Debugging Qlang from Excel / COM

Since Quantlab version 4107 it is possible to debug session’s VBA/COM in Qlang code directly in the Qlang debugger / editor.

First a COM process aware Quantlab session must be started using the same Quantlab as is used when running the EXCEL/COM. A debug session is started by running the -Z -c command at the command line. See example below:

This opens a Debugger with a “cproxy” process thread started.

This process listens for COM processes that initialize Quantlab libraries. Multiple Excel/VBA/COM sessions can be started from different environments such as Excel VBA or .NET.

We can now set break-points and debug in Quantlab as normal.

Note

Note! that the Quantlab Editor / Debugger will be in “lock-down” mode. It will not allow for any changes or re-compiles of the Qlang code during the debugging session.

Example of VBA project (qltest_com.xls)

Here is an example of how to calculate so called tail yields using QLang functions in VBA. The example file is found in Quantlab\examples\COM. The code corresponds to the case study Calculating tail rates (Chapter 8.9 in Quantlab User Manual).

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