在Excel VBA宏完成之前，Bloomberg数据不会填充 [英] Bloomberg data doesn't populate until Excel VBA macro finishes

问题描述

我正在PC上运行一个空白的Excel 2007工作簿中的宏，具有彭博许可证。宏将Bloomberg函数插入到sheet1中，以拉伸收益率曲线数据。一些附加功能的结果取决于第一个功能的完成和正确显示Bberg数据。当我完成程序时，它只显示＃N / A请求数据。 。 而不是查询的结果，不管我走多么慢。因为一些功能依赖于字符串和数字字段结果的填充，程序会在该代码中遇到运行时错误。当我停止调试 - 完全结束运行程序 - 所有应该填充的Bberg值将出现。我想在程序仍然运行时出现这些值。

I'm running a macro in a blank Excel 2007 workbook on a PC with a Bloomberg license. The macro inserts Bloomberg functions into sheet1 that pull yield curve data. Some additional functions' results are dependent on the first functions finishing and correctly displaying the Bberg data. When I step through the program it only displays '#N/A Requesting Data . . .' instead of the results of the query, no matter how slowly I go. Because some of the functions are dependent on string and numeric field results being populated, the program hits a run-time error at that code. When I stop debugging -- fully ending running the program -- all the Bberg values that should have populated then appear. I want these values to appear while the program is still running.

我已经尝试使用DoEvents和Application.OnTime（）的组合来将控制权返回到操作系统并让程序等待很长一段时间进行数据更新，但都没有工作。任何想法都会有所帮助。我的代码如下 wb 是一个全球级的工作簿，而ws1 是一个全球级的工作表。

I've tried using a combination of DoEvents and Application.OnTime() to return control to the operating system and to get the program to wait for a long time for the data update, but neither worked. Any ideas would be helpful. My code is below. wb is a global-level workbook and ws1 is a global level worksheet.

Public Sub Run_Me（） / p>

Public Sub Run_Me()

'Application.DisplayAlerts = False
'Application.ScreenUpdating = False

Call Populate_Me
Call Format_Me

'Application.DisplayAlerts = True
'Application.ScreenUpdating = True

End Sub

Private Sub Populate_Me（）

Private Sub Populate_Me()

Dim lRow_PM As Integer
Dim xlCalc As XlCalculation

Set wb = ThisWorkbook
Set ws1 = wb.Sheets(1)

'clear out any values from previous day
If wb.Sheets(ws1.Name).Range("A1").Value <> "" Then
    wb.Sheets(ws1.Name).Select
    Selection.ClearContents
End If


xlCalc = Application.Calculation
Application.Calculation = xlCalculationAutomatic

Range("A1").Value = "F5"
Range("B1").Value = "Term"
Range("C1").Value = "PX LAST"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_MEMBERS"",""cols=1;rows=15"")"
BloombergUI.RefreshAllStaticData

Range("B2").Select
ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_TERMS"",""cols=1;rows=15"")"
BloombergUI.RefreshAllStaticData

Application.OnTime Now + TimeValue("00:00:10"), "HardCode"

'******more code*******'
End Sub

Sub HardCode（）

Sub HardCode()

Range("C2").Select
ActiveCell.FormulaR1C1 = "=BDP($A2,C$1)"
BloombergUI.RefreshAllStaticData

End Sub

推荐答案

要解决这个问题的方法是把你想要的彭博社的数据揪成不同的子后运行的所有潜艇等。每当您打电话给彭博信息时，都必须这样做。如果您在Application.OnTime Now + TimeValue（00:00:15）之后的master子句中调用另一个子句，那么它将失败 - 您必须将所有子元素放在新的master子节点中。

A way to get around this issue is to put all subs, etc that you want to run after pulling the bloomberg data into a different sub. You must do this each time you call Bloomberg information. If you call another sub in the "master" sub after the Application.OnTime Now +TimeValue("00:00:15"), it will fail- you must put all subs following into a new master sub.

例如：
而不是

For example: Instead of

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "OtherSub1"
'This will cause the Bloomberg Data to not refresh until OtherSub2 and 3 have run
OtherSub2
OtherSub3
End Sub

应该是

It should be

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "Master2"
End Sub

Sub Master2()
OtherSub1
OtherSub2
OtherSub3
End Sub

希望有帮助

这篇关于在Excel VBA宏完成之前，Bloomberg数据不会填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！