JavaScript as an Excel scripting language via ExcelDNA

Update June 2010: Also, see JavaScript as an Excel scripting language via JSDB

Developing .NET DLLs that are to be used within an Excel VBA add-in is relatively easy to do. But the overhead of the COM managed interfaces can be a serious performance bottleneck if the .NET managed functions are called from within a tight loop. The alternative is to create a C++ XLL and then to call out to .NET, and that’s exactly what the ExcelDNA add-in does.

ExcelDNA allows you to expose any .NET DLL’s public static methods as Excel UDFs, but not only that, by inserting code into the add-in’s associated .dna file you can ‘script’ a UDF without compiling it into a DLL (via the magic of CodeDomProviders). Both C# and VB.net are supported as ‘scripting’ languages but in fact any .NET language that exposes a CodeDomProvider class can be used. I thought to myself, how cool would it be to use JavaScript as an Excel scripting language!

To use JavaScript (in the guise of JScript.NET) with ExcelDNA you’ll need the AssemblyQualifiedName of the Microsoft.JScript.JScriptCodeProvider type. Set the Project Language=”Microsoft.JScript.JScriptCodeProvider, Microsoft.JScript, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” within the .dna file and that’s it, code away in JavaScript. The code snippet below also includes a C# UDF to check if a Type is a sub-class of CodeDomProvider.


<DnaLibrary Name="My Test AddIn">
<Project Language="Microsoft.JScript.JScriptCodeProvider, Microsoft.JScript, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">
<![CDATA[

import ExcelDna.Integration;
public class myFunctions
{
ExcelFunctionAttribute(Description="Checks if a 1 to 3 digit number", Category="JScript REGEX example", IsMacroType=true)
public static function  ValidateStuff(str)
{
var regex=/^\\d{1,3}$/;
if (regex.test(str))
{
return "VERY GOOD: it's a 1 to 3 digit number";
}
else
{
return "NOT GOOD: Must be a number with between 1 and 3 digits";
}
}
}

]]>
</Project>
<Project Language="CS">
<![CDATA[
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using System.Xml.Serialization;

using ExcelDna.Integration;

public class Testing123
{
[ExcelFunction(Description="Check if type inherits CodeDomProvider", Category="Useful functions")]
public static string Test11(string str)
{
string str2;
str2="Testing if ...";
try
{
Type t = Type.GetType(str,true,true);

if (t.IsSubclassOf(typeof(CodeDomProvider)))
{
ConstructorInfo ci = t.GetConstructor(new Type[] {} );
CodeDomProvider p = (CodeDomProvider)ci.Invoke(new object[] { });
str2 = p.GetType().AssemblyQualifiedName;
}

}
catch (Exception e)
{
str2=e.ToString();
}
return str2;
}
[ExcelFunction(Description="Joins a string to a number", Category="Useful functions")]
public static string DoJoinThem(string str, double val)
{
return str + val;
}

}

]]>
</Project>
</DnaLibrary>

Advertisements

4 responses to “JavaScript as an Excel scripting language via ExcelDNA

  1. Pingback: Python the new VBA ? « Gobán Saor

  2. Pingback: Python to replace VB6 … « Gobán Saor

  3. Pingback: JavaScript as an Excel scripting language via JSDB « Gobán Saor

  4. Pingback: Steam Powered PowerPivot | Gobán Saor