C# to VBA Excel: How to Pass an Array of Objects – Comprehensive Guide

c++comcom-interopexcelvba

I have a C# console application and Dll. I registered the dll as COM via interop. I used the

COM Visible property, GUID etc
Set Assembly Information, Build information to register it to COM

I referenced this dll in the VBA code.

I created a list of objects for that class in the console application, converted it to Array and then used Excel.Run to send the array of user defined objects to VBA code.

I can access properties of the array in VBA like LBound and UBound. But I am unable to access each individual object in the array.

The C# code is as follows:

using System;
using System.Runtime.InteropServices;

namespace Save_as_excel_classes
{
    [Guid("0BA8F8DE-8F0A-4D7E-9DDB-8AED42943BDA")]
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class CollClass
    {
        [ComVisible(true)]
        public string NameValue { get; set; }
    }
}

The Console App is as follows:

using Microsoft.Office.Interop.Excel;
using Save_as_excel_classes;
using System;
using System.Collections;
using System.Collections.Generic;
using _Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Application excel = new _Excel.Application();
            try
            {
                string fileName = "D:\\Book2.xlsm";
                Workbook wb;
                Worksheet ws;
                int sheetNumber = 1;
                excel.Visible = true;
                wb = excel.Workbooks.Open(fileName);
                ws = wb.Worksheets[sheetNumber];
                ws = wb.Worksheets[sheetNumber];
              
                var collVals = new List<CollClass>();
                
                collVals.Add(new CollClass() { NameValue = "ABC" });
                collVals.Add(new CollClass() { NameValue = "DEF" });
                collVals.Add(new CollClass() { NameValue = "GHI" });
                collVals.Add(new CollClass() { NameValue = "KLM" });
                CollClass[] arr = collVals.ToArray();
                excel.Run("ThisWorkbook.GetListofObjects1", arr);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
            }
        }
    }
}

The VBA side looks like this: I am just trying to set the NameValue to a cell.

Public Function GetListofObjects1(ByRef objColl() As collClass)
    MsgBox "Inside GetListofObjects function" + " Hurray!"
    Range("C3").Value = LBound(objColl)
    Range("C4").Value = UBound(objColl)
    Range("C5").Value = objColl(0).NameValue
End Function

I've searched various questions on StackOverflow and forums but I don't know what I am doing wrong. Every time I run the console application, Excel opens, I get the message box and then crashes and restarts. I also get the exception in the c# console application as follows after the LBound line and the UBound line execute. This only happens for the Range("C5").Value = objColl(0).NameValue

"System.Runtime.InteropServices.COMException: 'Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))'"

I get the proper NameValue when I send a single object instead of an array. It's just with the array that all the problems seem to occur.

This probably has a very simple solution but I've been at this for over a day and a half now and I can't seem to figure it out.

Edit:

I added another class CollClassArray to the dll that will just contain the one CollClass array. When I pass the object of CollClassArray through the Run command and try to access the array inside it I get the error: Wrong number of arguments or invalid use of property near the CollArray(0).

Public Function GetListOfObjects1(array as CollClassArray)
Dim objColl as CollClass
Set objColl = array.CollArray(0).NameValue
End Function

Best Answer

After researching a bit more, I added mscorlib as a reference in the VBA code. I used the mscorlib.ArrayList and was able to access the elements in the ArrayList.

c# code: with ArrayList

 static void Main(string[] args)
 {
     Application excel = new _Excel.Application();
     try
     {
         string fileName = "D:\\Book2.xlsm";
         dynamic wb;
         Worksheet ws;
         int sheetNumber = 1;
         excel.Visible = true;
         wb = excel.Workbooks.Open(fileName);
         ws = wb.Worksheets[sheetNumber];
         
         var collVals = new List<CollClass>();
         
         collVals.Add(new CollClass() { NameValue = "ABC", NumberValue = 2 });
         collVals.Add(new CollClass() { NameValue = "DEF", NumberValue = 4 });
         collVals.Add(new CollClass() { NameValue = "GHI", NumberValue = 6 });
         collVals.Add(new CollClass() { NameValue = "KLM", NumberValue = 8 });
         CollClass[] arr = collVals.ToArray();

        
         ArrayList list = new ArrayList();
         list.AddRange(arr);
         excel.Run("ThisWorkbook.SetC5", list);
     }
     catch (Exception ex)
     {

     }
     finally
     {
         
         excel.Quit();
         System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
     }
 }

VBA code:

Public Function setC5(coll As mscorlib.ArrayList)
    MsgBox "Inside this function"
    Dim count As Integer
    Dim coll2(), coll1 As collClass
    Dim number As Integer
    number = coll.count
    Range("C6") = number
    Set coll1 = coll(0)
    Range("C5").Value = coll1.NameValue
End Function

Related Solutions

C# – How to Perform Deep Cloning of Objects

Whereas one approach is to implement the ICloneable interface (described here, so I won't regurgitate), here's a nice deep clone object copier I found on The Code Project a while ago and incorporated it into our code. As mentioned elsewhere, it requires your objects to be serializable.

using System;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;

/// <summary>
/// Reference Article http://www.codeproject.com/KB/tips/SerializedObjectCloner.aspx
/// Provides a method for performing a deep copy of an object.
/// Binary Serialization is used to perform the copy.
/// </summary>
public static class ObjectCopier
{
    /// <summary>
    /// Perform a deep copy of the object via serialization.
    /// </summary>
    /// <typeparam name="T">The type of object being copied.</typeparam>
    /// <param name="source">The object instance to copy.</param>
    /// <returns>A deep copy of the object.</returns>
    public static T Clone<T>(T source)
    {
        if (!typeof(T).IsSerializable)
        {
            throw new ArgumentException("The type must be serializable.", nameof(source));
        }

        // Don't serialize a null object, simply return the default for that object
        if (ReferenceEquals(source, null)) return default;

        using var stream = new MemoryStream();
        IFormatter formatter = new BinaryFormatter();
        formatter.Serialize(stream, source);
        stream.Seek(0, SeekOrigin.Begin);
        return (T)formatter.Deserialize(stream);
    }
}

The idea is that it serializes your object and then deserializes it into a fresh object. The benefit is that you don't have to concern yourself about cloning everything when an object gets too complex.

In case of you prefer to use the new extension methods of C# 3.0, change the method to have the following signature:

public static T Clone<T>(this T source)
{
   // ...
}

Now the method call simply becomes objectBeingCloned.Clone();.

EDIT (January 10 2015) Thought I'd revisit this, to mention I recently started using (Newtonsoft) Json to do this, it should be lighter, and avoids the overhead of [Serializable] tags. (NB @atconway has pointed out in the comments that private members are not cloned using the JSON method)

/// <summary>
/// Perform a deep Copy of the object, using Json as a serialization method. NOTE: Private members are not cloned using this method.
/// </summary>
/// <typeparam name="T">The type of object being copied.</typeparam>
/// <param name="source">The object instance to copy.</param>
/// <returns>The copied object.</returns>
public static T CloneJson<T>(this T source)
{            
    // Don't serialize a null object, simply return the default for that object
    if (ReferenceEquals(source, null)) return default;

    // initialize inner objects individually
    // for example in default constructor some list property initialized with some values,
    // but in 'source' these items are cleaned -
    // without ObjectCreationHandling.Replace default constructor values will be added to result
    var deserializeSettings = new JsonSerializerSettings {ObjectCreationHandling = ObjectCreationHandling.Replace};

    return JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(source), deserializeSettings);
}

C# Excel Interop – How to Properly Clean Up Objects

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

Best Answer

Related Solutions

C# – How to Perform Deep Cloning of Objects

C# Excel Interop – How to Properly Clean Up Objects

Related Question