title: Passing Json string to Json.NET in C# and get it back as VBA Object
body: <p>until now, I have been using the vba-json parser from github. Due to the appearance of large JSON-Files, I realized, that the vba-json parser needs a very long time, because it reads the json strings character by character and transforms them into a vba collection.</p>
  
  <p>Now, I researched a bit in the internet and found JSON.NET, which appears to be a very fast json parser. To use this in Excel-VBA, I installed Visual Studio and created a VSTO-Project.</p>
  
  <p>I succeeded in passing the json-string from the vba-code to Visual Studio. The Json-String is then deserialized into a C#-Object. Now, when I return the C#-Object back to Excel VBA, I get an error message stating </p>
  
  <blockquote>
    <p>Error 424 Object required</p>
  </blockquote>
  
  <p>My JSON-File I want to work with in Excel-VBA:</p>
  
  <pre><code>[
    {
      "rootarray": [
        {
          "id": "11234",
          "price": 2.99
        },
        {
          "id": "5324",
          "price": 32.99
        },
        {
          "id": "23",
          "price": 5.99
        }
     ],
     "name" : "Shop Nr. 22"
    }
  ]
  </code></pre>
  
  <p>My VBA-Code:</p>
  
  <pre><code>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)
  End Sub
  </code></pre>
  
  <p>And my C#-Code:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Xml.Linq;
  using Excel = Microsoft.Office.Interop.Excel;
  using Office = Microsoft.Office.Core;
  using Microsoft.Office.Tools.Excel;
  using System.Runtime.InteropServices;
  using Newtonsoft.Json;
  using Object = System.Collections.ObjectModel;
  
  
  namespace ExcelAddIn2
  {
  
      [System.Runtime.InteropServices.ComVisible(true)]
      public class CodeKlasse
      {
          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) =&gt; JsonConvert.DeserializeObject&lt;Welcome[]&gt;(json, Converter.Settings);
      }
  
      public class Converter
      {
          public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
          {
              MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
              DateParseHandling = DateParseHandling.None,
          };
      }
  
          public Welcome[] CSharpToVBA(string jsontext)
          {
              try
              {
                  var jPerson = JsonConvert.DeserializeObject&lt;Welcome[]&gt;(jsontext, Converter.Settings);
                  return jPerson;
  
              }
              catch (Exception ex)
              {
                  System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
                  var failure = JsonConvert.DeserializeObject&lt;Welcome[]&gt;(string.Empty);
                  return failure;
              }
          }
  
  
      }
  
      public partial class ThisAddIn
      {
          private CodeKlasse codeKlasse;
  
          protected override object RequestComAddInAutomationService()
          {
              codeKlasse = new CodeKlasse();
              return codeKlasse;
          }
          private void ThisAddIn_Startup(object sender, System.EventArgs e)
          {
          }
  
          private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
          {
          }
  
          #region Von VSTO generierter Code
  
          /// &lt;summary&gt;
          /// Erforderliche Methode für die Designerunterstützung.
          /// Der Inhalt der Methode darf nicht mit dem Code-Editor geändert werden.
          /// &lt;/summary&gt;
          private void InternalStartup()
          {
              this.Startup += new System.EventHandler(ThisAddIn_Startup);
              this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
          }
  
          #endregion
      }
  }
  </code></pre>
  
  <p>Apparently, Excel can't work with the C# Object. How do I do this. I want the C# Object transformed into a vba object, so that when I write in Excel-VBA:</p>
  
  <p><code>?solution(0)("rootarray")(2)("price")</code></p>
  
  <p>I get the value:</p>
  
  <p><code>5.99</code></p>
  
  <p>I hope anyone can help me? I would appreciate it a lot.
  Thank you.</p>
  
  <p>Greetings.</p>