VBA更新Excel Bloomberg BDH函数日期范围时历史数据未刷新

使用Excel VBA更新Bloomberg BDH函数的结束日期时遇到异常：该函数用于获取国库券历史数据，需每日更新结束日期。手动输入日期时所有数据均可正常刷新，但通过VBA更新后，仅A7单元格更新，A8-A100的历史日期区域未更新。

Excel公式（A7单元格）：

=@BDH(B1,B6:C6,B2,B3,"Dir=V","Dts=S","Sort=D","Quote=C","QtTyp=P","Days=T",CONCATENATE("Per=c",B4),"DtFmt=D","UseDPDF=Y","cols=3;rows=262")

尝试的VBA代码（第一种）：

Range("B3").Select
ActiveCell.FormulaR1C1 = "2/15/2024"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3/1/2024"
Range("B4").Select


    wksheet2.Cells(3, 2).Select

    wksheet2.Cells(7, 1).Select
        ActiveCell.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Cells.Find(What:="=", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate


Application.Run "RefreshData"
Application.Run "RefreshCurrentSelection"
Application.Run "RefreshEntireWorksheet"
Application.Run "RefreshEntireWorkbook"
Application.Run "RefreshAllWorkbooks"
Application.Run "RefreshAllStaticData"

    Application.Calculate
    ActiveSheet.Calculate


    Sleep 15000

尝试的VBA代码（第二种）：

Range("B3").Select
ActiveCell.FormulaR1C1 = "2/15/2024"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3/1/2024"
Range("B4").Select
    
    'Range("A7").Select
    'ActiveCell.Formula2="@BDH(B1,B6:C6,B2,B3,"Dir=V","Dts=S","Sort=D","Quote=C","QtTyp=P","Days=T",CONCATENATE("Per=c",B4),"DtFmt=D","UseDPDF=Y","cols=3;rows=262")"
        Range("A7").Select
    Selection.Copy
    ActiveSheet.Paste
    
    wksheet2.Cells(7, 1).Select
        ActiveCell.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Cells.Find(What:="=", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate


Application.Run "RefreshData"
Application.Run "RefreshCurrentSelection"
Application.Run "RefreshEntireWorksheet"
Application.Run "RefreshEntireWorkbook"
Application.Run "RefreshAllWorkbooks"
Application.Run "RefreshAllStaticData"

    Application.Calculate
    ActiveSheet.Calculate
    
    Application.OnTime (Now + TimeValue("00:00:15")), "InitiateProcess"

核心错误点 #

1. 无效的日期赋值与格式问题
   连续两次给B3赋值是冗余操作，且用FormulaR1C1赋值字符串日期可能导致Bloomberg插件无法正确识别格式，应该直接用日期类型赋值。

2. Select/Activate操作混乱
   大量使用Select和Activate会导致代码在切换工作表时执行上下文错误，比如选中wksheet2的单元格后，后续ActiveSheet.Paste可能在其他工作表执行，破坏刷新逻辑。

3. Bloomberg刷新方法滥用
   一次性调用所有刷新宏会导致执行冲突，反而无法触发BDH函数的完整区域扩展。

4. @运算符限制动态扩展
   公式中的@是隐式交集运算符，会限制公式仅返回单个单元格结果，手动刷新时Bloomberg插件的自动扩展特性被触发，但VBA更新后未触发该逻辑。

5. 等待逻辑不匹配实际加载耗时
   固定时长的Sleep或OnTime无法保证Bloomberg数据加载完成，应该等待计算状态结束。

修正后的VBA代码示例 #

Sub UpdateBDHEndDate()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("wksheet2") '替换为实际工作表名
    
    '1. 用日期类型更新结束日期，避免格式识别问题
    targetSheet.Range("B3").Value = DateSerial(2024, 3, 1) '也可使用Date获取当日日期
    
    '2. 移除@运算符，使用Formula2支持动态数组溢出
    Dim bdhFormula As String
    bdhFormula = "=BDH(B1,B6:C6,B2,B3,""Dir=V"",""Dts=S"",""Sort=D"",""Quote=C"",""QtTyp=P"",""Days=T"",CONCATENATE(""Per=c"",B4),""DtFmt=D"",""UseDPDF=Y"",""cols=3;rows=262"")"
    targetSheet.Range("A7").Formula2 = bdhFormula
    
    '3. 精准触发目标区域的Bloomberg刷新
    targetSheet.Range("A7").Select
    Application.Run "RefreshCurrentSelection"
    
    '4. 等待计算完成，确保数据加载完毕
    Do While Application.CalculationState = xlCalculating
        DoEvents
    Loop
    
    '5. 强制触发目标区域的计算，确保数据扩展
    targetSheet.Range("A7").Resize(262, 3).Calculate
End Sub

额外注意事项 #

• 确保已加载Bloomberg插件，且VBA编辑器中已勾选Bloomberg Office Tools引用（工具→引用）。
• 开启Bloomberg插件的自动扩展功能：在Bloomberg菜单的设置中，勾选“自动调整静态数据区域”选项。

内容的提问来源于stack exchange，提问作者solarissf