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

Does this answer your question? Create new Excel Function (User Defined Function) using C# — Eugene Astafiev
– Eugene Astafiev, Commented May 8, 2021 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
– Rui Monteiro, Commented May 8, 2021 at 17:13

Rui Monteiro · Accepted Answer · 2021-05-08 23:08:13Z

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.

Eugene Astafiev · Accepted Answer · 2021-05-08 13:30:50Z

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 8, 2021 at 13:30

Eugene Astafiev

49.7k3 gold badges28 silver badges50 bronze badges

3 Comments

Rui Monteiro Over a year ago

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.

Eugene Astafiev Over a year ago

The question was about developing user-defined Excel functions with VSTO. You may ask Excel DNA developers directly if that is your choice.

Rui Monteiro Over a year ago

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?

Collectives™ on Stack Overflow

How to call a C# function from VBA and Excel

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related