C# 調用 Excel VBA 宏（三）：三類宏調用完整示例

VBA 宏定義

Sub GetTime()           ' 無參無返
    Sheet1.Cells(1,1) = Now
End Sub

Sub GetTime2(title As String) ' 有參無返
    Sheet1.Cells(2,1) = title & " : " & Now
End Sub

Function GetTime3(title As String) As String ' 有參有返
    GetTime3 = title & " : " & Now
End Function

統一調用封裝類

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

public class ExcelMacroHelper
{
    public void RunExcelMacro(string filePath, string macroName, object[] args, out object result, bool visible)
    {
        Excel.Application app = null;
        Excel._Workbook wb = null;
        try
        {
            app = new Excel.Application { Visible = visible };
            wb = app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            // Run 返回宏的返回值（Function），Sub 返回 null
            result = app.Run(macroName, args);
        }
        finally
        {
            wb?.Close(false, Missing.Value, Missing.Value);
            app?.Quit();
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(app);
            GC.Collect(); GC.WaitForPendingFinalizers();
        }
    }
}

調用示例

var helper = new ExcelMacroHelper();
object ret;

// 1. 無參宏，顯示 Excel
helper.RunExcelMacro(@"E:\test.xls", "GetTime", null, out ret, true);

// 2. 有參宏，隱藏 Excel
helper.RunExcelMacro(@"E:\test.xls", "GetTime2", new object[] { "現在時刻" }, out ret, false);

// 3. 有參有返宏，顯示 Excel
helper.RunExcelMacro(@"E:\test.xls", "GetTime3", new object[] { "現在時刻" }, out ret, true);
MessageBox.Show(ret?.ToString()); // 顯示返回字符串

關鍵點

• Application.Run(macroName, args) 支持 Sub/Function，參數按位置匹配。
• visible 控制 Excel 進程是否顯示 UI，服務端建議 false。
• 必須引用 Microsoft Excel 11.0/14.0/15.0/16.0 Object Library。
• 宏安全性：信任中心 → 宏設置 → 勾選「信任對 VBA 項目模型的訪問」。