I have found whilst working in financial institutions that I am continually called upon to integrate Bloomberg market data into spreadsheets using VBA in Excel. While the Bloomberg API returns data in an array, I tend to work with recordsets when dealing with large sets of data. Because of this I have some standard code which I reuse in all my Bloomberg projects to fairly efficiently convert bloomberg results arrays into recordsets.

ADODB.Recordsets have advantages over arrays for storing larger volumes of data, such as in built sorting and searching methods, and while converting an array into a recordset may seem like a waste of time it can make working with large volumes of Bloomberg data a lot easier.

Implementing this function is very simple, you simply loop through the array and write the relevent values to the recordset after jigging about with the format of the original array.

Here is the code complete with an example of retrieving data from the Bloomberg API:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Function: BBArrayToRecordset
' Required Reference to: Microsoft ActiveX DataObjects
' Author: Matthew Rathbone
'
' Description: This function takes a results array as output by the Bloomberg API and converts it
' into a ADODB.recordset
'
' Copyright: Matthew Rathbone: Free for commercial and non-commercial use.
' Links to www.matthewrathbone.com are always appreciated
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function BBArrayToRecordset(fieldList As Variant, dataArray As Variant) As ADODB.Recordset

    'declare our return object
    Dim returnSet As ADODB.Recordset
   
    ' string to hold our list of fields
    Dim fieldString As String
   
    'string to hold our list of records
    Dim recordString As String
   
    Dim rDataArray As Variant
    ReDim rDataArray(0 To UBound(dataArray, 2))
   
    'convert the fields array to a string
    fieldString = Strings.Join(fieldList, ", ")
   
    'construct our return object
    Set returnSet = New ADODB.Recordset
   
    ' lets make our recordset! Looping and adding fields
    For i = 0 To UBound(fieldList, 1)
        returnSet.Fields.Append fieldList(i), adVarChar, 500
    Next
   
    'open the recordset
    returnSet.Open
        ' lets fill our recordset!
        For i = 0 To UBound(dataArray, 1)
            For ii = 0 To UBound(dataArray, 2)
                rDataArray(ii) = dataArray(i, ii)
            Next
            returnSet.AddNew fieldList, rDataArray
        Next
       
   
    'return the recordset
    Set BBArrayToRecordset = returnSet
   
End Function

'Example of it's use
'THIS EXAMPLE REQUIRES THE BLOOMBERG DATA TYPE LIBRARY REFERENCE
Public Sub exampleExecution()

    Dim oBlp                    As BlpData
    Dim aResult                 As Variant
    Dim aTickerList             As Variant
    Dim aFieldList              As Variant
    Dim exampleRecordset        As ADODB.Recordset
   
    'defining the securities we want data for
    ReDim aTickerList(0 To 1)
    aTickerList(0) = "AMGN EQUITY"
    aTickerList(1) = "GOOG EQUITY"
   
    'defining the fields we want to retrieve
    ReDim aFieldList(0 To 1)
    aFieldList(0) = "NAME"
    aFieldList(1) = "PX_CLOSE"
   
        'initialize our bloomberg data object
        Set oBlp = New BlpData
       
        'we want a single subscription to bloomberg (will not fire events ongoing instead populates our result array)
        With oBlp
            .Timeout = 40000
            .SubscriptionMode = ByRequest
            .Subscribe aTickerList, 3, aFieldList, , , aResult
        End With
   
    Set oBlp = Nothing
    Set exampleRecordset = BBArrayToRecordset(aFieldList, aResult)
       
   
End Sub

Link to the actual code module