从Outlook VBA Sub中调用带set-type的Excel VBA函数

Sub CallExcelFunction()
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlModule As Object
    Dim result As Variant
    
    ' 创建Excel应用程序对象
    Set xlApp = CreateObject("Excel.Application")
    
    ' 打开Excel工作簿
    Set xlWorkbook = xlApp.Workbooks.Open("C:\Path\To\Your\Excel\File.xlsx")
    
    ' 获取Excel VBA模块
    Set xlModule = xlWorkbook.VBProject.VBComponents("Module1")
    
    ' 调用Excel VBA函数
    result = xlModule.Run("YourExcelFunction")
    
    ' 关闭Excel工作簿
    xlWorkbook.Close SaveChanges:=False
    
    ' 退出Excel应用程序
    xlApp.Quit
    
    ' 释放对象
    Set xlModule = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    
    ' 处理Excel VBA函数的返回值
    MsgBox result
End Sub

Function YourExcelFunction() As Variant
    Dim result As String
    
    ' 执行一些操作，获取结果
    result = "Hello, World!"
    
    ' 将结果赋值给函数名
    Set YourExcelFunction = result
End Function