Excel VBA调用Json.NET返回C#对象时遇Error 424问题求助

你遇到的核心问题是：VBA无法直接识别C#自定义类的数组对象，因为COM互操作需要使用VBA原生支持的集合类型（比如ArrayList、Hashtable）才能像集合/字典那样通过索引和键访问。下面是具体的解决方案：

关键思路 #

将C#反序列化得到的Welcome[]对象，转换为VBA能直接识别的嵌套集合结构：

• 外层用ArrayList对应JSON的顶级数组
• 每个Welcome对象转换为Hashtable，包含name字符串和rootarray对应的ArrayList
• 每个Rootarray对象转换为Hashtable，包含id和price字段

修改后的C#代码 #

首先更新CodeKlasse中的CSharpToVBA方法，添加类型转换逻辑：

using System;
using System.Collections; // 引入ArrayList和Hashtable所在的命名空间
using System.Runtime.InteropServices;
using Newtonsoft.Json;

namespace ExcelAddIn2
{
    [ComVisible(true)]
    public class CodeKlasse
    {
        // 保留你原有的Welcome、Rootarray、Converter类定义不变
        public partial class Welcome
        {
            [JsonProperty("rootarray")]
            public Rootarray[] Rootarray { get; set; }
            [JsonProperty("name")]
            public string Name { get; set; }
        }
        public partial class Rootarray
        {
            [JsonProperty("id")]
            public string Id { get; set; }
            [JsonProperty("price")]
            public double Price { get; set; }
        }
        public partial class Welcome
        {
            public static Welcome[] FromJson(string json) => JsonConvert.DeserializeObject<Welcome[]>(json, Converter.Settings);
        }
        public class Converter
        {
            public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
            {
                MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
                DateParseHandling = DateParseHandling.None,
            };
        }

        // 修改后的CSharpToVBA方法，返回ArrayList类型
        public ArrayList CSharpToVBA(string jsontext)
        {
            try
            {
                var welcomeList = JsonConvert.DeserializeObject<Welcome[]>(jsontext, Converter.Settings);
                var vbaCompatibleList = new ArrayList();

                foreach (var welcome in welcomeList)
                {
                    // 创建存储Welcome对象的Hashtable
                    var welcomeHash = new Hashtable();
                    welcomeHash["name"] = welcome.Name;

                    // 转换rootarray为ArrayList
                    var rootArrayList = new ArrayList();
                    foreach (var item in welcome.Rootarray)
                    {
                        var itemHash = new Hashtable();
                        itemHash["id"] = item.Id;
                        itemHash["price"] = item.Price;
                        rootArrayList.Add(itemHash);
                    }
                    welcomeHash["rootarray"] = rootArrayList;

                    vbaCompatibleList.Add(welcomeHash);
                }

                return vbaCompatibleList;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
                return new ArrayList(); // 返回空集合避免报错
            }
        }
    }

    // 保留ThisAddIn类的原有代码不变
    public partial class ThisAddIn
    {
        private CodeKlasse codeKlasse;
        protected override object RequestComAddInAutomationService()
        {
            codeKlasse = new CodeKlasse();
            return codeKlasse;
        }
        private void ThisAddIn_Startup(object sender, EventArgs e) {}
        private void ThisAddIn_Shutdown(object sender, EventArgs e) {}
        #region VSTO生成的代码
        private void InternalStartup()
        {
            this.Startup += new EventHandler(ThisAddIn_Startup);
            this.Shutdown += new EventHandler(ThisAddIn_Shutdown);
        }
        #endregion
    }
}

验证VBA代码 #

更新你的VBA代码，添加测试逻辑来验证访问：

Sub NetTest(strJson As String)
    Dim addIn As COMAddIn
    Dim objekt As Object
    Set addIn = Application.COMAddIns("ExcelAddIn2")
    Set objekt = addIn.Object
    
    Dim solution As Object
    Set solution = objekt.CSharpToVBA(strJson)
    
    ' 测试访问预期值5.99
    Dim priceValue As Double
    priceValue = solution(0)("rootarray")(2)("price")
    MsgBox "预期价格：5.99，实际获取：" & priceValue, vbInformation
End Sub

' 测试调用示例
Sub TestJson()
    Dim testJson As String
    testJson = "[ { ""rootarray"": [ { ""id"": ""11234"", ""price"": 2.99 }, { ""id"": ""5324"", ""price"": 32.99 }, { ""id"": ""23"", ""price"": 5.99 } ], ""name"" : ""Shop Nr. 22"" } ]"
    Call NetTest(testJson)
End Sub

为什么原来的代码会报错？ #

你的原代码直接返回C#自定义类的数组Welcome[]，虽然标记了ComVisible(true)，但VBA无法直接将其识别为可索引的集合对象：

• VBA需要对象实现IEnumerable或它熟悉的集合接口（比如ArrayList的接口）
• 自定义类的属性无法直接通过键名（如("rootarray")）访问，而Hashtable天然支持键值对访问

通过转换为ArrayList和Hashtable，我们把C#对象映射成了VBA原生支持的结构，这样就能像你预期的那样通过solution(0)("rootarray")(2)("price")访问值了。

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