Using Bloomberg in VBA

Do you ever have the need to access a Bloomberg function from VBA? Let's say you're writing a VBA dialog box, and you need access to a bit of market data. Sure, you can write a formula using BLP and read it off the sheet, but that's rather inflexible.

The way to do it is by using the Bloomberg.Data COM Control.

Public Sub SimpleBBTest()
    Dim objDataControl As Object
    Set objDataControl = CreateObject("Bloomberg.Data")
    If objDataControl Is Nothing Then
        Debug.Print "Cannot instantiate " _
               & "BLP_DATA_CTRLLib.BlpData"
        Exit Sub
    End If
    Dim arrayFields, vtResult
    arrayFields = Array("PX_LAST")
    objDataControl.subscribe "222388209 CUSIP Pfd", _
         1, arrayFields, Nothing, Nothing, vtResult, False
    Debug.Print "The current price for CFC B Pfd is " _
         & vtResult(0, 0)
End Sub

Run the code,
and you'll see the following in the Immediate window:

The current price for CFC B Pfd is 15.9199

Please note that both input and output to the Bloomberg Data Control's subscribe funtion takes arrays of strings.

And what if you need to use Bloomberg's over-rides?

Public Sub BloombergVbaTest()
    Dim objDataControl As Object
    Set objDataControl = CreateObject("Bloomberg.Data")
    If objDataControl Is Nothing Then
        Debug.Print "Cannot instantiate BLP_DATA_CTRLLib.BlpData"
        Exit Sub
    End If
    Dim arrayFields, overrideFields, overrideValues, vtResult
    arrayFields = Array("CDS_FLAT_SPREAD")
    overrideFields = Array("SW_EFF_DT", _
        "MATURITY", "SW_SPREAD", "CDS_QUOTED_PRICE", "CDS_RR")
    Dim upfront As Double, upfrontStr As String
    upfront = 43
    upfrontStr = "" & (100 - upfront)
    overrideValues = Array("20081224", _
        "20140320", "500", upfrontStr)
    objDataControl.subscribe "SPV6037W Corp cusip", _
        1, arrayFields, overrideFields, _
        overrideValues, vtResult, False
    Debug.Print "The flat rate equivalent " _
        & "for RDN with 43% upfront and 500 bps running is " _
        & vtResult(0, 0)
End Sub

This should give you something like this:

The flat rate equivalent for RDN with 43% upfront and 500 bps running is 2689.178