Behavior of VBA in the workspace

User: "Hiroki"
Hiroki
Newcomer
Updated by Jirapongse

When the following VBA macro is executed line by line in debug mode,
it successfully retrieves the result date of the Reuters function in Eikon Excel and complete without issues.
However, in Workspace Excel, the cell remains at "Retrieving..." and a type mismatch error occurs.

Is this behavior specific to the validation environment?
Or will this result still be true after Workspace goes live?

Sub test()

Dim lastRow As Long

lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


Application.Calculation = xlCalculationManual


ThisWorkbook.Worksheets("Sheet1").Cells(lastRow + 1, 1).Value = "=RtGet(""IDN"",""JPY1MFSRF=ISDA"",""VALUE_DT1"")"


DoEvents

ThisWorkbook.Worksheets("Sheet1").Calculate

DoEvents


Dim res As Date

res = ThisWorkbook.Worksheets("Sheet1").Cells(lastRow + 1, 1).Value


Application.Calculation = xlCalculationAutomatic

End Sub

Find more posts tagged with

excel
vba
#technology
Sort by:
1 - 1 of 11
    User: "Jirapongse"
    Jirapongse
    ✭✭✭✭✭

    @Hiroki

    Thank you for reaching out to us.

    I tested it with the simple code, as shown below. 

    Sub Test()
    
    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = "=RtGet(""IDN"",""JPY1MFSRF=ISDA"",""VALUE_DT1"")"
    
    ThisWorkbook.Worksheets("Sheet1").Cells(2, 1).Value = "=RtGet(""IDN"",""JPY="",""BID"")"
    
    ThisWorkbook.Worksheets("Sheet1").Cells(3, 1).Value = "=RtGet(""IDN"",""JPY="",""ASK"")"
    
End Sub

    Then, I ran this macro in Excel and it could display data properly.

    image.png image.png
    test.zip
Quick Links