How to expose a C# class to a VBA module in a docu

Question

This is a hypothetical situation.

I would like to find out if it's possible to expose a C# class to VBA in a document-level add-in.

Here's an SSCCE:

In VS PRO 2012 I have started a new project, Selected Office -> Excel 2010 Workbook. (make sure you select .Net framework ver 4)

I have added a DateTimePicker control to Sheet1.

I can set/get the .Value property off the DateTimePicker control within the C# solution without a problem.

While debugging: In VBA, the .Value property is not exposed. (tried .OLEFormat.Object.Value)

Not all properties can be exposed to VBA because the ActiveX control DateTimePicker is wrapped by MSForms so Excel recognizes it (compatibility).

I need to be able to grab the actual value of the wrapped control from VBA, but I am not sure how go about it (whether it's possible or not)...

I know that the control itself supports events but this is not the path I want to take. I want to be able to grab the static/current value off a control.

This is what I would like to be able to do:

• Add a class to my C# solution

• Expose it, so it's recreatable from VBA like Dim obj as new MyExposedClass

• then have MyExposedClass store reference to the DateTimePicker as it appears in C# (all properties available)

• then I can define a function GetValue(string controlName) which returns the Value from C# POV

So I found this solution + (this one)that seems to work with an application-level add-in but it does not work with a document-level add-in.

When I debug my solution and open VBA's Object Browser I can see that references are automatically added to Microsoft Visual Studio 2008 Tools for Office Execution Engine 9.0 Type Library but I don't think I can add an extra class to it...

When I open the references in VBE there are no extra references added into the project but in the /debug folder of my solution there is a ExcelWorkbook1.dll so how it that even attached to the solution?

So my question is:

How can I expose a class in a document-level add-in for Excel using C# to extend the range of properties accessible by default on .Net controls?

Update:

This is the closest I got so far but it only allows you to expose the host item like Worksheet, Workbook, Chart etc. It allows you to call the methods though so I am going to investigate this further and come back with some feedback

Calling Code in Document-Level Customizations from VBA

How to: Expose Code to VBA in a Visual C# Project

Walkthrough: Calling Code from VBA in a Visual C# Project

Answer 1

You would need to create a public interface to expose the class to VBA, this works for me as a document level addin.

1. Open a new Excel workbook and copy the following into a MODULE

   Sub CallVSTOMethod()
   Dim dt As Date
   Dim VSTOSheet1 As DocLevelAddin.Sheet1
       Set VSTOSheet1 = GetManagedClass(Sheet1)
       dt = VSTOSheet1.GetDatePickerVal
   End Sub
   

2. Save Excel as "TestProj.xlsm" and close.

3. Open VS, new project, Excel 20xx Workbook and name the project "DocLevelAddin"
4. In the wizard, select copy an existing document and select the newly created workbook "TestProj.xlsm"

5. On the Excel Sheet1 add the DateTimePicker control to the sheet from wihin VS, double click to create a ValueChanged event and update the code in Sheet1.cs to read

   private DateTime dtVal;
   private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
   {
       dtVal = dateTimePicker1.Value;
   }
   

6. Still in Sheet1.cs, add a public method to return dtVal

   public DateTime GetDatePickerVal()
   {
       return dtVal;
   }
   

7. Also add the following to Sheet1.cs

   protected override object GetAutomationObject()
   {
       return this;
   }
   

8. Above public partial class Sheet1 in Sheet1.cs add the following

   [System.Runtime.InteropServices.ComVisible(true)]
   [System.Runtime.InteropServices.ClassInterface( 
       System.Runtime.InteropServices.ClassInterfaceType.None)]
   

9. Now you need to create a public interface for the method. In Sheet1.cs right click select Refactor, Extract Interface and check the public method GetDatePickerVal

10. Make the interface public and COM visible

    [System.Runtime.InteropServices.ComVisible(true)]
    public interface ISheet1
    {
        DateTime GetDatePickerVal();
    }
    

11. Double click Sheet1.cs so the Excel sheet is visible. Select any cell to open the properties window and change property ReferenceAssemblyFromVbaProject = true

12. In Excel you may need to goto Trust Centre Settings and add the VS Solution folder and sub folders as trusted location

13. Run the project and the code in the Excel MODULE will return the dateTimepicker through the exposed GetDatePickerVal method.

Sheet1.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace DocLevelAddin
{
    [System.Runtime.InteropServices.ComVisible(true)]
    [System.Runtime.InteropServices.ClassInterface(
        System.Runtime.InteropServices.ClassInterfaceType.None)]
    public partial class Sheet1 : DocLevelAddin.ISheet1
    {
        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
        }

        private void Sheet1_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.dateTimePicker1.ValueChanged += new System.EventHandler(this.dateTimePicker1_ValueChanged);
            this.Startup += new System.EventHandler(this.Sheet1_Startup);
            this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);

        }

        #endregion

        private DateTime dtVal;
        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            dtVal = dateTimePicker1.Value;
        }

        public DateTime GetDatePickerVal()
        {
            return dtVal;
        }

        protected override object GetAutomationObject()
        {
            return this;
        }

    }
}

ISheet1.cs:

using System;
namespace DocLevelAddin
{
    [System.Runtime.InteropServices.ComVisible(true)]
    public interface ISheet1
    {
        DateTime GetDatePickerVal();
    }
}