如何将获取SharePoint List的VBA Sub改写为工作表可调用的UDF函数

核心限制说明 #

你原代码直接改Function失效的原因是：Excel工作表调用的UDF默认禁止修改当前实例的工作簿结构、创建新对象等操作，ListObjects.Add属于被限制的操作范畴，因此需要用独立的隐藏Excel实例来执行这部分逻辑，规避UDF的权限限制。

可直接调用的UDF完整代码 #

Public Function GetSharePointListData() As Variant
    Dim hiddenApp As Excel.Application
    Dim objWorkbook As Workbook
    Dim objWorksheet As Worksheet
    Dim objList As ListObject
    Dim strSite As String, strList As String, strView As String
    
    ' 配置SharePoint参数
    strSite = "mycompany.sharepoint.com/sites/Department"
    strList = "{XXXXXXXX-4620-44B2-9C99-B5C0A854D5C5}"
    strView = "{XXXXXXXX-05B2-76+A-BC15-C7A68FEC6C30}"
    strSite = "http://" & strSite & "/_vti_bin"
    
    ' 创建独立隐藏Excel实例执行操作，规避UDF操作限制
    Set hiddenApp = New Excel.Application
    hiddenApp.Visible = False
    hiddenApp.DisplayAlerts = False
    
    On Error GoTo Cleanup ' 异常处理避免后台进程残留
    Set objWorkbook = hiddenApp.Workbooks.Add
    Set objWorksheet = objWorkbook.Worksheets.Add
    
    Set objList = objWorksheet.ListObjects.Add(xlSrcExternal, Array(strSite, strList, strView), False, , objWorksheet.Range("A1"))
    GetSharePointListData = objWorksheet.Range("A1").CurrentRegion.Value
    
Cleanup:
    ' 释放资源，避免后台Excel进程残留
    objWorkbook.Close SaveChanges:=False
    hiddenApp.Quit
    Set objList = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    Set hiddenApp = Nothing
    
    ' 异常时返回错误值
    If Err.Number <> 0 Then
        GetSharePointListData = CVErr(xlErrValue)
    End If
End Function

使用说明 #

• 该函数可直接在工作表中作为数组公式调用：如果使用Excel 365，选中任意空白单元格输入公式后直接回车即可自动溢出展示全量数据；旧版本Excel需要选中和返回数据行数/列数匹配的区域，输入公式后按Ctrl+Shift+Enter确认。
• 你也可以根据需求修改函数，将站点地址、列表ID、视图ID设为入参，实现不同列表数据的灵活拉取。
• 运行设备需要提前配置好对应SharePoint站点的访问权限，否则会返回错误值。

内容的提问来源于stack exchange，提问作者R.T.