In the last post I went through the steps for creating a class library for use with ManagedXLL to create an Excel add-in. We very briefly encountered the ManagedXLL way of marking methods for use in Excel.

Now I’m going to delve a little deeper into what we can do with ManagedXLL. This will not be comprehensive, but will highlight some of the key features of ManagedXLL.

ExcelAddInEvent

We can mark a method with this attribute to carry out a task when the add-in is loaded (AddInEvent.Open), allowing us to carry out specific functionality, such as maybe getting an SSO token for the user or the likes. We can also use mark a method to be called when an add-in is being closed, i.e. when a workbook is closed, for example to allow us to save data to the cloud using AddInEvent.Close. We can also use AddInEvent.Add and AddInEvent.Remove to carry out a task when the add-in is added to the add-in manager or removed from it.

[ExcelAddInEvent(AddInEvent.Open)]
public static void OnLoaded()
{
}

A couple of subclasses exist to handle Open and Close, named ExcelAutoOpen and ExcelAutoClose.

Excel Object

Before moving onto more attributes, let’s quickly look at the ManagedXLL Excel object. It includes methods to interact with Excel from our code, such as displaying a message box in Excel or getting at the sheets collection and so on, for example

Excel.MessageBox("You are not authorised to connect to the selected service", Excel.MessageBoxType.ErrorOK, "MyExcelAddin");

WorksheetFunction

The WorksheetFunction attribute is used to tell Excel, not only the name of the function (as seen by Excel users), but also how Excel should use the method in the sense of how it should display exceptions and whether to detect the user aborting of the method.

[WorksheetFunction("CalculateFee", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
public static double CalculateFee()
{
}

ExcelDescription

We can mark methods, arguments and return values with the ExcelDescription attribute, allowing us to document what the various arguments and/or return values of a method are as well as what the method itself does.

[ExcelDescription("Gets a string based upon the supplied index")]
[return: ExcelDescription("Returns the selected string")]
public static string DoSomething([ExcelDescription("The Index")] int index)
{
}

ExcelHidden

We can mark methods and hidden to hide them from the function wizard using the ExcelHidden attribute, this doesn’t stop us using the method, it just doesn’t appear in the function wizard

[WorksheetFunction("MyName", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
[ExcelDescription("Returns a string with the name of the add-in.")]
[return: ExcelDescription("A string representing the name of the add-in")]
[ExcelHidden]
public static string Name()
{
}

ExcelCategory

The ExcelCategory is used on a class and allows us to create a category for groups of functions – this aids the function wizard in grouping the methods. You can create multiple classes and give them the same category or group together specific functionality into their own categories.

[ExcelCategory("My Date & Time Functions")]
public static class DateTimeFunctions
{
}

ExcelAbbreviation

We can mark individual arguments of a method using this attribute. In essence we’re defining an alternate name for the parameter as viewed by Excel’s function wizard.

public static string Name([ExcelAbbreviation("Name")] String enterYourName)

ExcelCommand

The ExcelCommand attribute allows us to define methods that are registered as Excel macro functions which can be run from the command’s listed in the Managed.XLL.config

[ExcelCommand(".Run")]
public static void Run()
{
}

<command title="My Run Command" macroName=".Run"
    description="Runs a command" edition="Professional"/>

This will add a command to the Excel menu and when clicked will run our macro/