Preface

                This guide will tell you everything you need to know to use ExHale to easily and quickly develop User Defined Functions (UDFs) in C# which can be used within Excel.  In order to follow the guide users need a version of Microsoft Visual C# Express or higher and Microsoft Excel 97 or later.

The ExHale Platform

                Before we start developing a UDF we should first be aware of how ExHale works.  ExHale takes the pain out of developing a .Net add-in be it in C#, VB.Net or even IronPython, it does this by consuming a class library (.dll) and presenting it to Excel, converting all the native calls and data into a managed representation and vice versa. 

                ExHale requires an up to date version of the C++ runtime libraries and .Net 2.0 or later.  Before we start writing any code, its a good idea to test ExHale on the development machine.  To do this, simply extract the ExHale.xll and ExHaleCore.dll files to a folder somewhere on the local machines hard drive.  Then add a reference to ExHale.xll in Excel, to do this, press Alt + T,I this brings up the Add-Ins dialogue box:

Click Browse and navigate to the folder where you extracted ExHale.  Select ExHale.XLL.  You should see the following entry appear in the Add-Ins list:

If Excel returns an error, or states that ExHale is not a valid addin, ensure you have the C++ runtime libraries installed (available here) that you have permission to execute the files and that they are not corrupt.

Upon clicking OK the ExHale splash screen will appear, signifying it has been successfully loaded into Excel, now we can start the development.

Hello ExHale

                To use ExHale, you now simply need to create a Class Library to encompass your custom logic.  This guide is assuming you are using Visual Studio 2008 or C# Express 2008.  If you do not have either installed you can download C# Express Edition for free direct from Microsoft here.

<o:p></o:p>

Begin by creating a new class library project, we shall call it ExHaleExample1:

Next we need to add a reference to ExHaleCore.DLL

Next we need to add a reference to ExHaleCore.DLL

Now that we have referenced ExHaleCore.DLL we can bring in the following namespace declaration:

<o:p> </o:p>

<o:p> </o:p>

Armed with this we can now go about creating our own ExHale UDF function.  ExHale uses an Aspect Oriented Programming design methodology to allow developers great flexibility in how their functions should be presented to Excel.

<o:p> </o:p>

ExHale will search any target class library for any methods that are marked with the [ExHaleUDF] attribute.  If the owner class is defined as static, a reference to the static instance is used, if not ExHale automatically instantiates the class, as such it must have a public parameter less constructor (in C# classes have this by default).

<o:p> </o:p>

The cells in a worksheet can contain any kind of data, to represent this, we use the ExOper type, anyone who has used the Excel 97 SDK will find this type somewhat familiar.  It allows for representation of many types of data, from strings to nested ranges of ExOpers.

To be a valid UDF a value must be returned, this value must be of the type ExOper, as such we can create a function such as the one bellow:

Build the project, and copy the output DLL to the same folder you extracted ExHale too, restart Excel.

Once you have restarted Excel you should see the following entry in the function explorer

(Alt +I,F), you will find a new category “ExHale UDFs”:

Armed with this we can go about using our newly created function in a spreadsheet:

Refinements

                Whilst ExHale has allowed us to very easily create a rough and ready example, there are a few more simple steps we can take to help ensure a superior user experience.

<o:p></o:p>

First we can control the category the function names appear in, and add a description:<o:p> </o:p>

<o:p></o:p>

This makes for a much better appearance in the function explorer:

We can also do the same for each function argument, providing useful information for the user:

<o:p> </o:p>

<o:p> </o:p>

Produces the following in the function Arguments screen:

Developers can take advantage of the function help text to allow them to provide much more intuitive ExHale libraries than they could via COM Automation.

Example 2

In the same archive as the Beta ExHale and this document, you will find a second example project of a fully working Black-Scholes pricer.