如何开发可填充多单元格的Excel插件，实现类似Bloomberg BDH函数功能

Excel 2019不支持动态数组溢出，且UDF（自定义函数）本身受Excel限制，计算过程中无法修改调用单元格之外的区域，因此采用「UDF触发请求+计算完成后置写入」的逻辑实现功能：UDF先返回首个值/占位符，同时将需要写入的数据和目标区域信息存入队列，待Excel全局计算完成后，在SheetCalculate事件回调中写入下方的多行数据。

自定义BDH函数定义 #

Imports Microsoft.Office.Tools.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class BDHFunctions
    ' 待写入任务队列，存储调用单元格标识、待写入数据、数据行数
    Private Shared pendingWriteTasks As New Dictionary(Of String, Tuple(Of Object(,), Integer))

    ' 暴露给Excel单元格调用的BDH函数
    Public Function BDH(secId As String, field As String, startDate As String, endDate As String) As String
        Dim callerCell As Excel.Range = CType(Globals.ThisAddIn.Application.Caller, Excel.Range)
        Dim cellKey As String = $"{callerCell.Worksheet.Name}!{callerCell.Address}"

        ' 替换为你的实际数据源请求逻辑，返回二维数组：第一列是日期，第二列是观测值
        Dim timeSeriesData = GetTimeSeriesData(secId, field, startDate, endDate)
        Dim rowCount As Integer = timeSeriesData.GetLength(0)

        ' 将写入任务加入队列
        If pendingWriteTasks.ContainsKey(cellKey) Then
            pendingWriteTasks(cellKey) = Tuple.Create(timeSeriesData, rowCount)
        Else
            pendingWriteTasks.Add(cellKey, Tuple.Create(timeSeriesData, rowCount))
        End If

        ' 调用单元格优先返回首个值，避免显示错误
        If rowCount > 0 Then
            Return timeSeriesData(0, 1).ToString()
        Else
            Return "无匹配数据"
        End If
    End Function

    ' 模拟时间序列数据拉取，替换为实际的数据源接口请求
    Private Function GetTimeSeriesData(secId As String, field As String, startDate As String, endDate As String) As Object(,)
        Dim mockData(2, 1) As Object
        mockData(0, 0) = Date.Parse("2024-01-01") : mockData(0, 1) = 100.25
        mockData(1, 0) = Date.Parse("2024-01-02") : mockData(1, 1) = 101.36
        mockData(2, 0) = Date.Parse("2024-01-03") : mockData(2, 1) = 99.87
        Return mockData
    End Function

    ' 工作表计算完成后执行待写入任务
    Public Shared Sub ExecutePendingTasks(sh As Object)
        Dim ws As Excel.Worksheet = CType(sh, Excel.Worksheet)
        Dim completedTaskKeys As New List(Of String)

        For Each task In pendingWriteTasks
            Dim parts = task.Key.Split("!")
            If parts(0) <> ws.Name Then Continue For

            Dim callerCell As Excel.Range = ws.Range(parts(1))
            Dim data = task.Value.Item1
            Dim rowCount = task.Value.Item2

            ' 写入范围为调用单元格向下扩展rowCount行、2列
            Dim targetRange As Excel.Range = callerCell.Resize(rowCount, 2)
            targetRange.Value = data
            ' 设置日期列格式
            targetRange.Columns(1).NumberFormat = "yyyy-mm-dd"

            completedTaskKeys.Add(task.Key)
        Next

        ' 清空已完成的任务
        For Each key In completedTaskKeys
            pendingWriteTasks.Remove(key)
        Next
    End Sub
End Class

事件绑定 #

在ThisAddIn.vb的启动事件中绑定工作表计算回调：

Private Sub ThisAddIn_Startup() Handles Me.Startup
    AddHandler Application.SheetCalculate, AddressOf BDHFunctions.ExecutePendingTasks
End Sub

• 需开启项目的COM可见性：右键项目→属性→生成→勾选「为COM互操作注册」
• 若数据量较大，可在数据拉取层加异步逻辑，避免阻塞Excel主进程
• 可根据需求扩展BDH函数参数，对齐彭博BDH的频率、对齐方式、币种等可选参数

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