Macro avec des API bloomberg

Public Sub MakeRequest(sSecList() As String, sFldList As Variant, sOverrideFields As Variant, sOverrideValues As Variant)

    Dim req As Request
    Dim nRow As Long
    
    currentrow = 16 'Start output from row 16
    Set req = refdataservice.CreateRequest("ReferenceDataRequest")
    For nRow = LBound(sSecList, 1) To UBound(sSecList, 1)
        req.GetElement("securities").AppendValue sSecList(nRow)
    Next
    
    For nRow = LBound(sFldList, 1) To UBound(sFldList, 1)
        req.GetElement("fields").AppendValue sFldList(nRow)
        Sheet1.Cells(currentrow, nRow + 2).Value = sFldList(nRow)
    Next
    
    Dim overrides As Element
    Set overrides = req.GetElement("overrides")
    
    For nRow = LBound(sOverrideFields, 1) To UBound(sOverrideFields, 1)
      Dim override  As Element
      Set override = overrides.AppendElment()
      override.SetElement "fieldId", sOverrideFields(nRow)
      override.SetElement "value", sOverrideValues(nRow)
    Next
    
    session.SendRequest req
    
    currentrow = currentrow + 1
    
    Dim eventObj As blpapicomLib2.Event
    Do
        Set eventObj = session.NextEvent() ' this will block code execution until the next event is received
        If eventObj.EventType = PARTIAL_RESPONSE Or eventObj.EventType = RESPONSE Then
            Dim it As blpapicomLib2.MessageIterator
            Set it = eventObj.CreateMessageIterator()
            
            Do While it.Next()
                Dim msg As Message
                Set msg = it.Message
                
                Dim numSecurities As Integer
                numSecurities = msg.GetElement("securityData").NumValues
                
                Dim i As Integer
                For i = 0 To numSecurities - 1
                    Dim security As Element
                    Set security = msg.GetElement("securityData").GetValue(i)
                    Sheet1.Cells(currentrow, 1).Value = security.GetElement("security").Value
                    Dim fields As Element
                    Set fields = security.GetElement("fieldData")
                    Dim numFields As Integer
                    numFields = fields.NumElements
                    
                    Dim fldplace As Integer
                    Dim a As Integer
                    For a = 0 To numFields - 1
                        fldplace = a
                        Dim field As Element
                        Set field = fields.GetElement(a)
                        Do While (Sheet1.Cells(16, fldplace + 2).Value <> field.Name)
                         fldplace = fldplace + 1
                        Loop
                        Sheet1.Cells(currentrow, fldplace + 2).Value = field.Value
                    Next
                    currentrow = currentrow + 1
                Next
            Loop
            
            If eventObj.EventType = RESPONSE Then Exit Do
            
        End If
    Loop
End Sub