How to use Excel VBA macros and COM add-ins in one solution

The Microsoft Excel Office Object model is a varied and very mature framework and allows you to not only create VBA macros and functions but also COM add-ins. Of course creating these add-ins is made even simpler with the help of Add-in Express.

But, did you know you can use your VBA macros and functions from within your Excel add-in and vice versa? It is a great way for developers to re-use the code they’ve invested a lot of hours in creating. It also gives users of your add-in the ability to write VBA macros to enhance your Excel add-in even further.

In this article we’ll take a look at how you can access your Excel Add-in objects and functions from within a VBA routine as well as how to access VBA functions from within your Excel COM Add-in.

• Creating the Excel COM add-in
• Accessing Excel add-in objects, properties and methods from VBA
• Invoking built-in Excel ribbon buttons from VBA
• Accessing Excel VBA routines from an Excel Add-in
• Data exchange between VBA and Excel add-ins

Creating the Excel COM Add-in

Start by creating a new ADX COM Add-in project in Visual Studio.

In the next steps of the “New Microsoft Office COM Add-in” wizard, select your programming language (C#, VB.NET and C++.net are supported) and the minimum version of Office (2000 – 2013) that your Excel add-in will support.

Finally, select Microsoft Excel from the list of supported applications.

Accessing Excel Add-in objects, properties and methods from VBA

Once the new project wizard has finished, switch to the code view of the AddinModule.cs class. Next, we need to add a function which we can invoke from VBA. I’ve created a simple method that accepts the row and column number of a cell whose value you want to set as well as the actual value. Note that the method should be declared as public:

C# code sample

public void SetCellValue(int row, int col, string value)
{
    Worksheet sheet = null;
    Range range = null;
 
    try
    {
        sheet = (Worksheet)ExcelApp.ActiveSheet;
        range = sheet.Cells[row,col] as Range;
        range.Value = value;                
    }
    finally
    {
        if (sheet != null) Marshal.ReleaseComObject(sheet);
        if (range != null) Marshal.ReleaseComObject(range);
    }
}

Build and register the project. Next, we need to create a new Excel worksheet and create a new VBA function to invoke our SetCellValue method.

In order to add the VBA function click on the Visual basic button on the Developer tab. This will open up the “Visual Basic for Application” editor window and will look very familiar especially if you’ve worked with Visual Basic 6 or earlier.

Double-click on the ThisWorkbook item and add the following code:

VBA code samples

Sub SetCellValue()
    Dim addin As COMAddIn
    Dim adxModule As Object
 
    Set addin = Application.COMAddIns.Item("ExcelVBA.AddinModule")
    Set adxModule = addin.Object
 
    Call adxModule.SetCellValue(5, 7, "The new Cell Value")
End Sub

The ComAddins collection expects the ProgId of your add-in. To find this, look at the top of the AddinModule.cs class to find it:

[GuidAttribute("E20004F4-7BFB-4843-B1D0-80DDED745AF1"), ProgId("ExcelVBA.AddinModule")]
public class AddinModule : AddinExpress.MSO.ADXAddinModule

Invoking built-in Excel ribbon buttons from VBA

If, for example, you would like to display the Format Cells Font dialog window from VBA, this is easily achieved using the ExecuteMso method:

VBA code sample

Sub ShowFontDialog()
    Application.CommandBars.ExecuteMso("FormatCellsFontDialog")
End Sub

This will display the built-in dialog:

Accessing Excel VBA routines from an Excel Add-in

If you invested a lot of time developing and perfecting your VBA functions, there is no need for you to throw it out and rewrite them if you’re developing an Excel add-in.

For example, the following VBA function will return decimal degrees when you pass in GPS coordinates consisting of degrees, minutes and seconds.

VBA code samples

Function ConvertToDecimalDegrees(GPSCoords As String) As Double
    Dim degrees As Double
    Dim minutes As Double
    Dim seconds As Double
    degrees = Val(Left(GPSCoords, InStr(1, GPSCoords, "°") - 1))
    minutes = Val(Mid(GPSCoords, InStr(1, GPSCoords, "°") + 2, _
              InStr(1, GPSCoords, "'") - InStr(1, GPSCoords, _
              "°") - 2)) / 60
    seconds = Val(Mid(GPSCoords, InStr(1, GPSCoords, "'") + _
            2, Len(GPSCoords) - InStr(1, GPSCoords, "'") - 2)) _
            / 3600
    ConvertToDecimalDegrees = degrees + minutes + seconds
End Function

This function, when invoked from within an Excel sheet, can be called like this:

=ConvertToDecimalDegrees("25°47'11.83""S")

And it will return the GPS coordinate in decimal degrees e.g. 25,117175

To use the same ConvertToDecimalDegrees function from within an Excel Add-in, you can use the Run method of the Application object:

C# code sample

private void showDecimalDegreesRibbonButton_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    try
    {
        string degrees = ExcelApp.Run(
            "ConvertToDecimalDegrees", "25°47'11.83\"S").ToString();
        MessageBox.Show(String.Format(
            "GPS coordinates 25°47'11.83\"S is {0} in degrees", degrees));
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

The run method accepts 31 parameters, the first should be the VBA function name, and the rest of the parameters should contain any parameter values required by the VBA function.

The Excel worksheet must contain the function or you will receive the following exception message:

Cannot run the macro ‘ConvertToDecimalDegrees’. The macro may not be available in this workbook or all macros may be disabled.

Data exchange between VBA and Excel add-ins

When passing data between your Excel COM addin and VBA functions, you’ll need to keep an eye on how VBA interprets different types of data.

For example returning a string array from a function in an add-in requires you to not explicitly return an array of string values, but rather an object as illustrated below:

C# code sample

public object CitiesinZA()
{
    string[] cities = new string[] { "Johannesburg", "Pretoria", "Cape Town" };
    return (object)cities;
}

Calling the method above and looping through the result in VBA would require the following code:

VBA code sample

Sub GetCities()
    Dim addin As COMAddIn
    Dim adxModule As Object
 
    Set addin = Application.COMAddIns.Item("ExcelVBA.AddinModule")
    Set adxModule = addin.Object
 
    cities = adxModule.CitiesinZA()
    Dim city As Object
    For Each city In cities
        MsgBox city
    Next city
End Sub

Returning primitive data types are by far less complicated. Consider the following two methods, one returns a string whereas the other an integer.

C# code sample

public string GetNameString()
{
    return "Joe Blogs";
}
 
public int EasySum(int number1, int number2)
{
    return number1 + number2;
}

Invoking these two methods and accepting their return values in VBA can be accomplished using the following VBA code:

VBA code sample

Sub GetNameAndNumber()
    Dim addin As COMAddIn
    Dim adxModule As Object
 
    Set addin = Application.COMAddIns.Item("ExcelVBA.AddinModule")
    Set adxModule = addin.Object
 
    MsgBox(adxModule.GetNameString())
 
    MsgBox(adxModule.EasySum(10, 20))
End Sub

Thank you for reading. Until next time, keep coding!

You may also be interested in:

• Creating COM add-ins for Excel 2013 – 2007. Customizing Excel Ribbon
• Excel add-in development in Visual Studio