CODEWITHSUNDEEP
c#excelvbavsto
Rui Monteiro
Rui Monteiro

Reputation: 329

How to call a C# function from VBA and Excel

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

Upvotes: 0

Views: 1837

Answers (2)

Rui Monteiro
Rui Monteiro

Reputation: 329

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.

Upvotes: 2

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

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.

Upvotes: 0

Related Questions