Simply add a [WorksheetFunction] attribute!

ManagedXLL is not about writing add-ins, but exporting plain .NET code for use on Excel worksheets. The add-in is already provided by the ManagedXLL run-time. All you have to do is to add [WorksheetFunction] attributes as markers to your code. It's really that easy. Click the link to visit our step-by-step tutorial.

/// <summary>...
[WorksheetFunction("XLL.NormInv")]
// example: =XLL.NormInv(0.05,3.0)
public static double NormInv( double probability, 
    [Optional,DefaultValue(0.0)] double mean, 
    [Optional,DefaultValue(1.0)] double sigma)
{
    if( sigma <= 0.0 )
        throw new ArgumentOutOfRangeException( 
            "sigma", sigma, "sigma must be >0");
 
    return mean + NormInv(probability)*sigma;
}

Leverage existing code and classes.

ManagedXLL can export static functions, methods, constructors, properties and even operators for use with Excel (click for full example code).

/// <summary>...
public class MyMatrix
{
    internal double[,] values;
 
    /// <summary>...
    [WorksheetFunction]
    public MyMatrix(double[,] values)
    {
        this.values = values;
    }
 
    /// <summary>...
    [WorksheetFunction]
    public static MyMatrix operator+(
        MyMatrix a, MyMatrix b)
    {
        // see Tutorial for full sample
    }
}

Worksheet functions - user friendly and robust

Metadata (type information, attributes) and XMLDOC comments are used to create the information for the Paste Function Wizard. ManagedXLL also allows to disable long-running functions while the wizard is open and to automatically detect and process user abort requests (ESC key).

User documentation always up-to-date.

A help page containing a function summary, parameter descriptions, default values, general remarks and Excel specific notes is automaticaly generated from metadata (type information, attributes) and XMLDOC comments:

Throw and catch Excel errors as managed exceptions

Exceptions thrown by your code are returned as a matching Excel error together with a more verbose message in the Debug Console. The translation of exceptions can be customized through exception filters. Even errors in SDK callbacks are translated into exceptions and can be handled by your code or left to the ManagedXLL run-time for further processing (i.e. uncalculated cell exceptions).

/// <summary>...
[WorksheetFunction("XLL.NormInv")]
// example: =XLL.NormInv(0.05,3.0)
public static double NormInv( double probability, 
    [Optional,DefaultValue(0.0)] double mean, 
    [Optional,DefaultValue(1.0)] double sigma)
{
    if( sigma <= 0.0 )
        throw new ArgumentOutOfRangeException( 
            "sigma", sigma, "sigma must be >0");
 
    return mean + NormInv(probability)*sigma;
}

Full compatibility to the Excel SDK / C API

To take advantage of special Excel features (eg. worksheet references) that do not have an equivalent in the .NET framework, managed equivalents to the SDK types are provided that encapsulate the native implementation details and provide additional functionality (e.g. XlOper or ExcelRange).

/// <summary>...
[WorksheetFunction("WhatIs")]
// Example: =WhatIs(A1:C3)
public static String WhatIs(XlOper input)
{
    if( input.IsMissing ) 
        throw new ExcelException(Excel.Error.Value,
            "No argument supplied.");
    else if (input.IsReference)
        return input.AsReference().AsText();
    else if( input.IsArray ) return "an array";
    else if( input.IsError ) return "an error";
    else if( input.IsString ) return "a string";
    else if( input.IsBoolean ) return "a boolean";
    else if( input.IsDouble ) return "a double";
    else return "nothing!";
}

No more crashes or memory leaks

ManagedXLL performs marshaling, conversion and memory management as required. The implementation uses optimal allocation strategies for different parameter and return types. A sophisticated object handler works hand-in-hand with the .NET garbage collector to prevent leakage of memory, which is especially important for long-running Excel sessions (24x7).

Update cells from worksheet functions

ManagedXLL enables your code to update cell values or any other workbook properties from within a worksheet function. Our clients use this for database access, keeping a history of values or decoupling expensive recalculations from frequent RTD updates. One of the samples provided is the Highlight function that you can see in action below:

ManagedXLL does not only handle the often unexpected behaviour of the Excel SDK, but also fixes some bugs (e.g. RTD updates pushing the Excel window to the front) and provides useable workarounds (e.g. to pass whole columns). .

Get rid of support nuisances

Your add-ins can be accessed from a central location (usually a network share) without packaging your application, running a local setup routine or requiring Administrator access. Application.RegisterXLL is fully supported.

Forget about the Windows registry

Your add-ins do not require any setup procedure at all and the ManagedXLL run-time never touches the registry. Even RTD servers can be used without registering which opens up a wealth of new possibilities.

Plagued by security paranoid admins?

Unless explicitly forbidden by security policies, ManagedXLL add-ins can manage their own security settings and run from a network share. Publisher certificates are supported to grant the required permissions in locked-down environments.

Deploy with existing libraries

Copying files to the Excel application folder or the WINDOWS directory is not necessary. Native DLLs are automatically searched in and loaded from the add-in folder, even if they reside on a network share or mapped network drive.

[ DllImport("BLAS32.DLL") ]
public static extern void DGEMV(
    ref char trans, ref int m, ref int n, 
    ref double alpha, ref double a, ref int lda,
    ref double x, ref int incx, 
    ref double beta, ref double y, ref int incy);

Your Quants will love this

Native C or C++ code in external DLLs can make use of the ManagedXLL environment for logging and debugging purposes. Simply redirect "stdout" and "stderr" to the Debug Console with a single configuration file setting.

// ...

if( !connection )
    cerr << "Could not connect: " << reason;

// ...

Package add-ins from Visual Studio

ManagedXLL provides merge modules to create Microsoft Installer (MSI) based deployment packages. 98% of our customers choose to distribute their add-ins through a network share, though.

Minimize conflicts with other add-ins

ManagedXLL runs in a separate AppDomain to avoid conflicts with other managed components. The AppDomain uses a private, add-in specific configuration file to configure ManagedXLL and the .NET environment. It follows the standard XML configuration file format and can include your settings as well (e.g. logging or database connections).

Add new menu items in seconds

All menus (menubar or context) are defined in the configuration file using a simple XML syntax, with menu entries being fully localizable.

<menu title="CreditLib">

  <subMenu title="Data Connections">
    <command title="Connect!"
      macroName="CL.Connect"
    />
  </subMenu>

  <!-- separator -->
  <command title="-"/>

  <command
    title="About..."
    macroName="CL.About"
  />

</menu>

Present your work

The look and content of generated help pages can be adapted to match your corporate identity (CI). ManagedXLL supports custom CSS stylesheets and XSLT processing to cater a wide range of possibilities, including localization and merging with externally maintained documentation.

Don't bother with type conversion

Support for all "primitive" .NET types, e.g. single and double precision floating point values, Decimal (fixed point), signed and unsigned integers with 8, 16, 32 or 64 bits, Boolean, Char and String.

Marshalers for well-known types already included

Use .NET built-in types as parameter and return values - even from worksheet functions. Also note how we assign a function to a built-in category.

/// <summary>...
[
    WorksheetFunction("AddTimeSpan"), 
    ExcelCategory(ExcelCategory.DateAndTime)
]
// Example: =AddTimeSpan(NOW(), "01:30")
public static DateTime AddTimeSpan( 
    DateTime dt, 
    TimeSpan ts)
{
    return dt + ts;
}

Enums on the worksheet - more useful than ever

Any Enumeration type used by your code is automatically available from the worksheet as a function (TypeName.MemberName()) or can be passed literally as the member name. In the following example, a function using the System.DayOfWeek enumeration is exported to Excel:

/// <summary>...
[
    WorksheetFunction("IsDayOfWeek"),
    ExcelCategory(ExcelCategory.DateAndTime)
]
// Example: =IsDayOfWeek(NOW(),"Sunday")
public static bool IsDayOfWeek(
    DateTime dt, 
    DayOfWeek dayOfWeek)
{
    return dt.DayOfWeek == dayOfWeek;
}

All parameters can have a default value, either using a native language construct in VB.NET or the [Optional,DefaultValue] attributes available in almost any other language. Optional parameters and their default values are automatically documented and show up in the Function Wizard. ManagedXLL also supports the C# params syntax to pass a variable number of arguments.

/// <summary>...
[WorksheetFunction("XLL.NormInv")]
// example: =XLL.NormInv(0.05,3.0)
public static double NormInv( double probability, 
    [Optional,DefaultValue(0.0)] double mean, 
    [Optional,DefaultValue(1.0)] double sigma)
{
    if( sigma <= 0.0 )
        throw new ArgumentOutOfRangeException( 
            "sigma", sigma, "sigma must be >0");
 
    return mean + NormInv(probability)*sigma;
}

Provide access to the most complex functions

Scalar function parameters can be grouped and passed as a (name, value) array from Excel, enabling you to call those 30+ parameter functions everybody loves... This feature is also useful in combination with default values, as it allows to define selected extra parameters in a spreadsheet range while keeping the rest of the parameter list manageable.

Use objects in your spreadsheet

For any object returned to the worksheet that does not have a built-in or custom marshaler, an object handle is created. ManagedXLL tracks those handle and makes sure that objects are freed when they are no longer needed and recreated when the workbook is reopened.

Object handles can significantly increase performance and reduce development and maintenance costs by removing calculations and interim results from worksheets.

Separate types and representation

Leverage existing code to be easily called from the worksheet with custom marshalers, automatically converting spreadsheet data to an object or returning a representation of your object suitable for display in Excel. Or plug your own object and cache handlers!

For the following example, we assume that custom marshalers for Calendar and Period have been defined previously. The first parameter passed from Excel is the instance ("This"), which can be either an existing object or the name of a "well-known" calendar:

/// <summary>...
[WorksheetFunction]
// Ex: =Calendar.Advance("London",TODAY(),"3m")
public DateTime Advance(
    DateTime date,
    Period period,
    [Optional, DefaultValue(Roll.Following)] 
    Roll convention)
{
    // ...
}

RTD Servers - not only for market data

In ManagedXLL, a simple [RtdServer] attribute is all it takes to start using real-time functionality. Combined with worksheet functions, RTD servers can be used to create real-time objects or asynchronous calculations to replace volatile functions. ManagedXLL also includes samples on how to add "OnRtd" events to existing servers.

Work in restricted environments

ManagedXLL supports the usage of publisher certificates for registration-free RTD servers. Optionally, a fully-trusted Microsoft certificate can be presented to the user.

Improved RTD development and usage

RTD servers registered through [RtdServer] attributes or the configuration file can be suspended. All updates received are buffered and published to Excel when RTD servers are resumed.

It is also possible to completely restart RTD servers without closing Excel or the workbook, speeding up the RTD development cycle.

Managed code can be used to create wrapper functions around existing RTD servers. RTD wrappers can both simplify the parameter list and return data types (e.g. arrays or objects) that are otherwise not directly available through Excel's standard RTD function. The following example shows how to wrap the Bloomberg server:

/// <summary>...
[WorksheetFunction("BB")]
public static XlOper BB(
    string ticker, 
    [Optional,DefaultValue("LAST_PRICE")]
    string field)
{
    return Excel.RTD( "BLOOMBERG.RTD",
        null, ticker, field);
}

Track managed objects in real-time

With a combination of worksheet functions and a managed RTD server, object properties can be displayed on the spreadsheet and updated in real-time whenever a certain event is triggered. Choosing the IRtObject as in the following example is not mandatory.

/// <summary>...
// Example: =Asset.NPV(RtObject(handle))
public abstract class Asset : IRtObject
{
    // ...
 
    public event EventHandler Changed;
 
    public abstract double NPV
    {
        [WorksheetFunction]
        get;
    }
}

Control if and when recalculations are done

Real-time data sometimes updates faster than your spreadsheets can handle or come in batches that each trigger a recalculation where instead you would prefer only one. With the CopyRangeIf function, you now have full control over your recalculation without resorting to "manual" updates. Besides guaranteeing a minimum or maximum period between updates, a condition determines if and when an update is performed. Example conditions include a "significant" price change or updates to all assets in a watched portfolio.

Use RTD servers on legacy systems

ManagedXLL includes example code on how to make IRtdServer based feeds available on Excel 97/2000. Performance is currently not on par with later Excel versions, but it can be used to provide backwards compatibility when needed.

Row or column vector?

ManagedXLL can usually make a good guess and automatically transpose a returned vector to match the input range where you entered the array function. For example, the following function can be entered in both a column or row vector:

/// <summary>...
[WorksheetFunction("RandomVector")]
public static double[] RandomVector( 
    [Optional,DefaultValue(1)] int count )
{
    Random random = new Random();
    double[] values = new double[count];
 
    for( int i=0; i<count; i++)
        values[i] = random.NextDouble();
 
    return values;
}

Built-in Array Marshaling

Marshaling and memory management is automatically handled for one and two-dimensional arrays of "primitive" types. There is even support for arrays of arrays (also known as "jagged arrays").

/// <summary>...
[WorksheetFunction("MARKOWITZ")]
// example: =MARKOWITZ(A1:J10,A11:J11,0.05)
public static double[] Markowitz( 
    double[,] covar,
    double[] means, 
    double ret)
{
    // function body left out
}

No limits on arrays

Arrays of any size can be returned as object handles. This is usually done automatically whenever you return an array to a function that has not been entered with Ctrl-Shift-Return.

All functions that accept arrays also accept array handles.

Handle free-formatted inputs

ManagedXLL can automatically pre-process input data to eliminate empty rows/columns or those that contain #N/A values. It's also possible to provide default values for empty cells, or to require minimum/maximum array dimensions.

Background calculations have never been easier

A simple [AsyncWorksheetFunction] is all it needs to have Excel execute your code on a background thread. The user interface stays accessible, and ManagedXLL provides the synchronization necessary.

Multithreading has never been easier

Use multi-threading techniques to e.g. distribute calculations on multiple CPUs. ManagedXLL automatically assures that any SDK callbacks to Excel are made on the correct thread (as otherwise Excel will just crash).

Query web services in parallel

Calling sequentially into web services usually results in poor performance due to high latency. With ManagedXLL, simply execute a defineable number of web service calls in parallel. In the image below, you see queries to the Google "doSuggest" web service which suggest the correct spelling for a search term:

Background tasks are either controlled automatically or through your own logic, enabling adaption to a wide range of requirements. Examples for grid computing tasks like the distributed Pi calculation shown below are included with the ManagedXLL distribution.

Stay in control

All asynchronous activity (updates, web service or grid computing tasks) can be controlled by the user although it's usually not necessary to do so.