VBA刷新未按正确顺序运行的彭博数据 [英] VBA to refresh Bloomberg data not running in proper order

问题描述

我的目的是更新彭博数据，并使用不同的行情收录机进行一些计算.但是似乎VBA会运行所有计算而无需等待数据更新. 这是代码:

my purpose is to update Bloomberg data and do some calculatations with different tickers. But it seems that VBA will run all the calculations without waiting the data to be updated. Here is the code:

Application.Calculation = xlCalculationAutomatic

For i = 1 To 3

    Call Worksheets("Sheet1").Range("data1").Select 'the cells "data1" contains the function =BDH(ticker, field, start date, end date) to get the information from Bloomberg'

    Call Application.Run("RefreshCurrentSelection")

    Worksheets("sheet1").Range("d3").Value = Worksheets("sheet1").Range("sum") 'the cells "sum" takes the sum of all BB info'     

有人知道如何解决吗?

推荐答案

您必须在检查和刷新之间进行拆分.

You have to split it up between a check and a refresh.

Sub RefreshData()

Application.Calculation = xlCalculationAutomatic

Worksheets("Sheet1").Range("A1:A4").Select 'the cells "data1" contains the function =BDH(ticker, field, start date, end date) to get the information from Bloomberg'

Application.Run "RefreshCurrentSelection"

'Check to see if it filled
Call Check_API

End Sub

Sub Check_API()

If Application.WorksheetFunction.CountIfs(Range("A1:A4"), "#N/A Requesting Data...") > 0 Then
    'Check every 3 seconds
     Application.OnTime Now + TimeValue("00:00:03"), "Check_API"
Else

    'What to do after API filled
     Worksheets("sheet1").Range("D3").Value = Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range("A1:A4")) 'the cells "sum" takes the sum of all BB info'
End If

End Sub

此外，您可以执行以下操作，而不是专注于选择:

Also, instead of focusing on the selection, you can do:

根据默认选项设置刷新:

Refresh based on default option setting:

      Application.Run "RefreshData"

刷新当前选择:

      Application.Run "RefreshCurrentSelection"

刷新当前工作表:

      Application.Run "RefreshEntireWorksheet"

刷新当前工作簿:

      Application.Run "RefreshEntireWorkbook"

刷新所有工作簿:

      Application.Run "RefreshAllWorkbooks"

如果您有兴趣.更好的选择是实现v3 COM API类，该类可以在Bloomberg的带有VBA示例的SDK中找到.

If you were interested. Still the better option is implementing the v3 COM API class that can be found in Bloomberg's SDK with VBA examples.

这篇关于VBA刷新未按正确顺序运行的彭博数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！