How to call a C# function from VBA and Excel

Question

I installed VSTO on Visual Studio 2019 and I started writing a simple script like this:

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;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

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

        private double ReturnMagicNumber()
        {
            return 123;
        }


        #region VSTO 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.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

I would like to know how to make ReturnMagicNumber() function available in Excel as an User Function and also how can I call it from a VBA Macro?

Thanks

Is [ExcelDNA](https://www.nuget.org/packages/ExcelDna.AddIn/#:~:text=Used%20By%20%20%20%20Package%20%20,Fluent%20ribbon%20bui%20...%20%20%205.0K%20) an option? — Mathieu Guindon, May 08 '21 at 05:54
Does this answer your question? [Create new Excel Function (User Defined Function) using C#](https://stackoverflow.com/questions/49687851/create-new-excel-function-user-defined-function-using-c-sharp) — Eugene Astafiev, May 08 '21 at 13:25
It's a pretty simple thing I'm trying to accomplish here. If VSTO doesn't do this by itself I don't understand why even exists... — Rui Monteiro, May 08 '21 at 17:13

score 1 · Answer 1 · answered May 08 '21 at 23:08

Ok I got it working without the need of Excel-DNA.

For the solution I read this here and here, and the code is the following:

The C# code on VS 2019 is the following:

using System;
using System.Data;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;


namespace MagicNumberAddIn
{

    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface INumberGetter
    {
        double ExcelReturnNumber();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class NumberGetter : INumberGetter
    {
        public double ExcelReturnNumber()
        {
            return 123.0;
        }
    }


    public partial class MagicNumber
    {

        private NumberGetter myAddIn;

        protected override object RequestComAddInAutomationService()
        {
            if (myAddIn == null)
                myAddIn = new NumberGetter();

            return myAddIn;
        }


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

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

        #region VSTO 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.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Then the code on VBA is this:

Public Function GetMagicNumber() As Variant

    Dim addin As Office.COMAddIn
    Dim automationObject As Object
    Dim returnNumber As Double

    Set addin = Application.COMAddIns("MagicNumberAddIn")
    Set automationObject = addin.Object

    returnNumber = automationObject.ExcelReturnNumber()
    
    GetMagicNumber = returnNumber

End Function

This works for me because what I want is to be able to use C# Add-ins inside VBA in order to get Multitasking and Asynchronous Functions in VBA.

score 0 · Answer 2 · answered May 08 '21 at 13:30

0

VSTO doesn't provide anything for developing user-defined functions for Excel. You need to create such projects on your own - it can be an automation add-in or XLL one. Both approaches are well described in the Using C# .NET User Defined Functions (UDF) in Excel article.

If your choice is XLL add-ins you may consider using Excel-DNA which simplifies development a lot.

answered May 08 '21 at 13:30

Eugene Astafiev

47,483
3
24
45

I don't see anywhere how can I call a function from VBA written in Excel-DNA C#. Many books about VSTO are from 2007 and none afterwards, so I'm starting to guess that VSTO isn't developed like other stuff in Visual Studio. – Rui Monteiro May 08 '21 at 17:17
The question was about developing user-defined Excel functions with VSTO. You may ask Excel DNA developers directly if that is your choice. – Eugene Astafiev May 09 '21 at 08:22
Hi, I already found the solution and posted here. However I've a question, what is is called this `[ComVisible(true)]`? I don't find this kind of code in any book, do you know what this line of code is in C# syntax? – Rui Monteiro May 09 '21 at 19:07

How to call a C# function from VBA and Excel

2 Answers2