XLL Add-ins and Add-in Express 2010

As I’ve mentioned in one of my previous posts, Excel has become one of my favourite reporting tools in the last few years. It is also the primary tool for anyone doing a data clean-up or conversion project.

One requirement I’ve picked up with the data migration projects I’ve worked on, is that even though you can build in a number of checks using standard Excel formulas, there are a number of scenarios where you need to validate the data against certain business rules i.e. category should be in the list of valid values or dates to fall within accepted periods.

I know you’re asking what is an XLL? An XLL is a DLL which Excel can open directly. They are mostly used to create User-Defined Functions (UDF) in Excel, however they work much faster. XLL interfaces have been available for C and C++ developers since Excel 4 in 1992. Fortunately for us Add-in Express hides this complexity for .Net developers by providing us with an XLL Add-in project template.

Using Add-in Express 2010 and User-Defined Functions in an XLL add-in, I’ll discuss how you can easily build your own Excel formulas to check custom rules and validations. We’ll use a scenario of a data migration, where you need to check whether dates specified were not on days the business was closed.  We’ll also write a simple formula to calculate fuel consumption.

Start by opening Visual Studio and creating a new Add-in Express XLL Add-in project.

In the XLLModule.cs code, you’ll see a region named Define your UDFs in this section, within this region is a function to demonstrate how to handle all parameter types available for UDFs. Go ahead and uncomment the code and test the function.  Below is a table that illustrates the pairing between a value that is passed to an UDF and the arguments’ .NET Type:

		Excel Type
		Double	String	Boolean	Empty	Error
	Single, Double, Int32, UInt32, Int16, Byte, Sbyte	Will try to cast #NUM on error	#VALUE	#VALUE	0	#VALUE
	String	#VALUE	String Value	#VALUE	String.Empty(“”)	#VALUE
	Boolean	#VALUE	#VALUE	Boolean Value i.e. TRUE or FALSE	False	#VALUE
	DateTime	Excel’s DateTime is a Double internally. Excel Services converts any Excel Double into a .NET DateTime when the argument type is DateTime	#VALUE	#VALUE	#VALUE	#VALUE
	Object	Double	Object is a reference to the string	Boolean	Null	#VALUE

For example, if you have a Double argument in your UDF function, and you pass a String value to it from Excel, the result of the UDF function will be #VALUE. Another example is when you pass an empty cell value into a UDF function that accepts a Boolean value, the result will be False.

As for arrays, only arrays of type Object are supported, in C# this means support for object[] and object[,] arguments.

First, let’s create a function that reads a list of dates from a text file and compares it to a date value passed as an argument. The code looks like this:

public static bool IsBusinessClosure(DateTime dateValue,
string filePath)
{
bool returnValue = false;
List closureDates = LoadDates(filePath);
if (closureDates.Contains(dateValue))
{
  returnValue = true;
}
returnreturnValue;
}

The LoadDates method reads a text file, which path is specified as an argument and returns a generic list of DateTime values.

Next, switch over to the design view of the XLLModule, and add an Excel Function Category by right clicking on the design surface and selecting it from the context menu. Select the newly added Excel Function Category and change the CategoryName property to Business Date Calculations. Click on the ellipses (…) button in the FunctionDescriptors property. Add a new Function by clicking on its toolbar button. Select IsBusinessClosure from the list of functions in the FunctionName property and change its Description property to Check a date against a specified list of business closure dates.

While you have the IsBusinessClosure function selected, add a parameter by clicking the toolbar button. Change the Description property to Date that needs to be checked against list and select dateValue from the ParameterName property’s list. Add another parameter and change its Description property to Full path to text (.txt) file with list of business closure days and selectfilePath from the ParameterName property’s list. Underneath an example of the controls collection:

Build and Register your ADX project and open a new Excel workbook. Create a list of dates and add a formula in a cell by clicking on the Insert Function button. In the category dropdown list you’ll notice the Business Date Calculations category and the IsBusinessClosure function in the list of functions

Test your function by creating a list of dates and using the formula to determine if the date was a business closure or not, the formula text should be

 =IsBusinessClosure(A1,”C:\Dev\BusinessClosures.txt”)

Next, let’s do a function that will calculate fuel consumption. Our code for the function:

public static double KMperLitre(double startKMs, double endKMs, double litres)
{
double returnValue = (endKMs - startKMs) / litres;
return returnValue;
}

After you build and register your project, start Excel and type the formula

=KMperLitre(B2,C2,D2)

Yes, I know what you’re going to say, these examples are pretty easy to do directly in Excel. But consider the possibilities if you need a library of specified mathematical formulas or business specific rules or calculations.

So until next time, keep coding!

You may also be interested in:

Developing Excel XLL add-ins step-by-step
Excel UDF tips