c# - 如何将 C#.net 字典传递给 VBA

Question

我正在尝试将字典从 c# 传递到 vba，但发生此错误

System.Runtime.InteropServices.COMException: 'Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))'

我也尝试将我的字符串列表转换为 vb 集合，但发生了同样的错误。
这是我的代码

private void btn_macrorunner_Click(object sender, EventArgs e)
{
    Dictionary<string, string> xlist_dictvb = new Dictionary<string, string>();
//xlist_names has been properly initialized and already has some items
    xlist_names.ForEach(x => xlist_dictvb.Add(x, x));

    ExcelFire(xlist_dictvb);
}

private void ExcelFire(Dictionary<string, string> pcoll = null)
{
    //~~> Define your Excel Objects
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook;

    //~~> Start Excel and open the workbook.
    xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Hutanginamo\Documents\xlkalat\0928ExcelFile.xlsm");

    if(pcoll != null)
    {
        //calls Sub ShowDict(pdict1 As Dictionary) 
        xlApp.Run("ShowDict", pcoll); //WHERE ERROR OCCURS
    }

    //~~> Clean-up: Close the workbook
    xlWorkBook.Close(false);

    //~~> Quit the Excel Application
    xlApp.Quit();

    //~~> Clean Up
    ReleaseObject(xlApp);
    ReleaseObject(xlWorkBook);
}

这是我的 vba 代码

Sub ShowDict(pdict1 As Dictionary)
   Dim x As Variant

   For Each x In pdict1.keys
        MsgBox (pdict1.Item(x))
   Next
End Sub

Answer 1

为了实现你想要的，你必须通过正确的字典。 C#字典不能用于 VBA像这样。实现您想要的最简单的方法是使用 VBA C# 中的字典然后通过它。

请参阅此示例。

C#代码

Scripting.Dictionary dict = new Scripting.Dictionary();
dict.Add("Apples", 50);
dict.Add("Oranges", 60);

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;

// ~~> Opens an existing Workbook. Change path and filename as applicable
xlWorkBook = xlApp.Workbooks.Open("C:\\Sample.xlsm");

// ~~> Display Excel
xlApp.Visible = true;
xlApp.Run("ShowDict", dict);

VBA代码

Sub ShowDict(pdict1 As Dictionary)
   Dim x As Variant

   For Each x In pdict1.keys
        MsgBox (pdict1.Item(x))
   Next
End Sub

C# 中的引用



VBA 中的引用