C# is a powerful language. It is simple to write and is typically compiled through Visual Studio.
Excel is the top data analysis and calculation tool - but defining new functionality is most commonly accomplished through User Defined Functions (UDFs) in VBA (Visual Basic for Applications). VBA can be used to create both functions (to calculate results) and macros (to automate processes). This article focuses on adding functionality.
There are two main ways of adding C# functions in excel. One is through an add-in. This creates a seamless integration into excel by exposing functions through various adornments. However, this also increases the number of references required by the C# project - and does not grantee backwards and forwards Excel compatibility.
The second option is Component Object Model (COM): a binary-interface standard for software components introduced by Microsoft in 1993. COM is language agnostic - and allows the re-use of objects without no knowledge of their implementation. Lets's see an example of C# being exposed to COM.
using System;
using System.Runtime.InteropServices;
namespace ExcelTest
{
/// <summary>
/// Some handy math functions
/// </summary>
[ClassInterface(ClassInterfaceType.None)]
[Guid("00000000-0000-0000-0000-000000000000")] // Make your own UUID, don't use this
[ProgId("ExcelTest.Math")]
public class Math
{
/// <summary>
/// Adds two numbers
/// </summary>
/// <param name="a">The first number to add</param>
/// <param name="b">The second number to add</param>
/// <returns>The sum of the two numbers</returns>
public double Add(double a, double b) {
return a + b;
}
}
}
In order to expose this functionality, the assembly has to have "Make assembly COM-Visible" enabled and the Build has to "Register for COM interop" within the project settings. Build the project. Visual studio will register the assembly for COM, so it should be accessible from anywhere on your PC. Now, this can be easily incorporated into excel.
Open excel and create a xlsm (macro enabled) file. Open the visual basic editor (alt+f11). Create a new module, and try the following code.
' filename: CSMath.bas
' ----------------------------------------------------
' Adds two numbers and returns their sum (using c sharp)
'
' a - The first number to add
' b - The second number to add
' ----------------------------------------------------
Function CSAdd(a As Double, b As Double) As Double
Dim o: Set o = CreateObject("ExcelTest.Math")
CSAdd = o.Add(a, b)
End Function
Yes, adding two numbers is the simplest example in the world, but it shows the point. You can now go to any cell and use the CSAdd function - which will call the C# code.
Enter
=CSAdd(1.2,3.4)
into a cell and hit enter.
The result should be 4.6.