Continue after reuters has retrieved data

Question

Thomson Reuters Eikon used:

I need to get data from reuters regarding fx spot prices. This is easily done with a reuters formula. However, I need to use this data in a VBA script. Part of the VBA script inserts the formula, reuters then retrieves data but this can take a while (10-20 seconds).

How do i make the VBA script pause until reuters has retrieved the data? (in the first 10 seconds the cell of the reuters data says: 'retrieving....'

Range("B2").value = Application.Evaluate("RtGet(" & IDN & "," & RWAL & "," & MIDPRICE & "," & TYPENUM & ")")

This code is now used to insert the formula where RWAL is the exchange rate identifier (such as EUR=)

Answer 1

We need to use application.run to use the RtGet function in code as its really a worksheet function. Because its a worksheet function, the data will not be retrieved while code is running.

To get round this we use Application.RTD.RefreshData to pull the data from the RTD server

Function getField(ric As String, field As String) As String
    Data = Application.Run("RtGet", "IDN", ric, field)
    Do While VBA.Left(CStr(Data), 3) = "Ret" 'ie loop while it is retrieving data
        Application.RTD.RefreshData
        DoEvents
        Data = Application.Run("RtGet", "IDN", ric, field)
        Debug.Print Data
    Loop
    getField = Data
End Function

Public Sub testGetField()
    Dim test
    test = getField("EUR=", "BID")
End Sub