Excel中Bloomberg Add-In的BDP函数无法随下拉列表刷新的解决咨询

问题背景 #

在Excel中使用Bloomberg Add-In的BDP函数，需实现以下自动化流程：切换下拉列表选项时，让依赖该选项的含BDP函数的单元格自动刷新，随后导出工作表为PDF，循环处理下拉列表所有选项。目前常规Excel函数刷新正常，但BDP函数切换下拉选项后无法自动刷新。

原VBA代码：

Option Explicit
Sub printPDF()
    Dim ws As Worksheet
    Dim validationCell As Range
    Dim validationFormula As String
    Dim saveFolder As String
    Dim pdfName As String 
    Dim item As Variant
    Dim dropdownValues() As Variant
        
    Set ws = ActiveSheet
    Set validationCell = ws.Range("AF1") '下拉列表位置
    validationFormula = validationCell.Validation.Formula1
    
    '存储路径
    saveFolder = ws.Range("A1").Value
    If Right(saveFolder, 1) <> "\" Then
        saveFolder = saveFolder & "\" 
    End If
    
    If Left(validationFormula, 1) = "=" Then
        Dim listRange As Range
        Set listRange = ws.Evaluate(Mid(validationFormula, 2))
        ReDim dropdownValues(listRange.Cells.CountLarge - 1)
        Dim i As Long
        i = 0 
        Dim cell As Range
        For Each cell In listRange
            dropdownValues(i) = cell.Value
            i = i + 1
        Next cell
    End If
    
    If UBound(dropdownValues) >= 0 Then
        validationCell.Value = dropdownValues(0)
    End If
    
    Application.Calculation = xlCalculationAutomatic
    For Each item In dropdownValues
        '切换下拉选项
        validationCell.Value = item
        Application.Calculate
        '等待计算完成
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop
        
        'PDF文件名
        pdfName = ws.Range("AF2").Value & "_" & Format(Now, "yyyymndd_hhamss")
        '导出为PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFolder & pdfName & ".pdf", quality:=xlQualityStandard
    Next item
    MsgBox "完成"
    Shell "explorer.exe """ & saveFolder & """", vbNormalFocus
End Sub

解决方案 #

BDP函数属于Bloomberg Add-In的外部数据函数，普通Excel计算命令无法触发其刷新，需针对性调整代码：

1. 调用Bloomberg专属刷新方法 #

替换原代码中的Application.Calculate，使用Bloomberg Add-In提供的刷新命令：

• Application.Run("RefreshAllStaticData")：刷新所有静态Bloomberg数据（包括BDP）
• 若只需刷新特定区域，可使用Application.Run("RefreshSelectedData", ws.UsedRange)（指定目标区域）

2. 优化等待逻辑，确保数据刷新完成 #

Bloomberg数据刷新依赖网络，仅检查Excel计算状态不够，需添加额外等待机制：

• 可使用Application.Wait Now + TimeValue("00:00:02")添加固定延时（根据网络情况调整时长）
• 或循环检查Bloomberg的刷新状态，直到完成

修改后的完整代码 #

Option Explicit
Sub printPDF()
    Dim ws As Worksheet
    Dim validationCell As Range
    Dim validationFormula As String
    Dim saveFolder As String
    Dim pdfName As String 
    Dim item As Variant
    Dim dropdownValues() As Variant
        
    Set ws = ActiveSheet
    Set validationCell = ws.Range("AF1") '下拉列表位置
    validationFormula = validationCell.Validation.Formula1
    
    '存储路径
    saveFolder = ws.Range("A1").Value
    If Right(saveFolder, 1) <> "\" Then
        saveFolder = saveFolder & "\" 
    End If
    
    If Left(validationFormula, 1) = "=" Then
        Dim listRange As Range
        Set listRange = ws.Evaluate(Mid(validationFormula, 2))
        ReDim dropdownValues(listRange.Cells.CountLarge - 1)
        Dim i As Long
        i = 0 
        Dim cell As Range
        For Each cell In listRange
            dropdownValues(i) = cell.Value
            i = i + 1
        Next cell
    End If
    
    If UBound(dropdownValues) >= 0 Then
        validationCell.Value = dropdownValues(0)
    End If
    
    Application.Calculation = xlCalculationAutomatic
    For Each item In dropdownValues
        '切换下拉选项
        validationCell.Value = item
        
        ' 触发Bloomberg数据刷新
        Application.Run("RefreshAllStaticData")
        
        ' 等待Excel计算完成 + 额外等待Bloomberg数据加载
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop
        ' 根据网络情况调整延时，确保数据完全加载
        Application.Wait Now + TimeValue("00:00:03")
        
        'PDF文件名
        pdfName = ws.Range("AF2").Value & "_" & Format(Now, "yyyymndd_hhamss")
        '导出为PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFolder & pdfName & ".pdf", quality:=xlQualityStandard
    Next item
    MsgBox "完成"
    Shell "explorer.exe """ & saveFolder & """", vbNormalFocus
End Sub

额外注意事项 #

• 确保Bloomberg Add-In已正确加载且处于登录状态
• 若刷新仍不稳定，可尝试在代码开头添加Application.ScreenUpdating = False，结尾恢复为True，减少界面干扰
• 延时时长可根据实际网络速度调整，避免过短导致数据未加载完成

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