OK, enough with the back story, lets get down to business.

In this post we are going to look at the simplest way to use Excel DNA. This methods is ideal if you want to write User Defined Functions (UDF), or want to copy a UDF from VBA to DNA for a quick and easy* performance improvement.

In this example we are going to use the XML file to hold our code, so there is no need to have an IDE installed. In following instalments we will look at how to use a IDE to write a compliled DLL and use that with Excel DNA.

First things first, we need to get hold of the Excel DNA files. Download the most up to date version of Excel DNA from the Excel DNA codeplex site here:

http://exceldna.codeplex.com/

Once you have down loaded the files, extract them to a new folder, called something like “XL DNA Tests”, there should be 2 folders in there, like this:

Open the distribution folder, find and copy the XLL file called “ExcelDNA.xll”, now paste that back in to your root folder, it should look like this:

Next create a new text file (use the short cut menu, right click>>New>>Text Document). Rename that text file to “MyFirstDNAAddin.DNA”, then remane the XLL file to the same name, but keep the .xll extention, i.e “MyFirstDNAAddin.xll”, it should all look like this:

Note that the 2 files have the same name. This is very important as it ties the code we are going to write in the DNA file with the XLL. When we load the XLL, it will be looking for code in a file with the same name as itself . Notice that for this to work the XLL and the DNA file must be on the same path – i.e. in the same folder, at the same level, like they are in this example.

Now time for the code. Open the DNA file (double click the icon it will open with notepad) and copy and paste the following code into the DNA file.

<DnaLibrary>
  <![CDATA[
     Public Module Mod1
        Function MyFirstFunction(byVal YourText As string)as string
          MyFirstFunction = YourText & ",...Hello World!"
        End Function
      End Module
   ]]>
</DnaLibrary>

Close the file, saving the changes and then double click the XLL file. This will open Excel, enable macros if required. With a new workbook open, open the function wizard, and find the category called “MyFirstDNAAddin”…

Select that category, and you will see your new function in the functions list, like this:

Click OK, and enter some text in the argument box, then hit OK, your function will appear in the active cell, something like this:

And that’s really all there is to it. To share this new function all you need to do is send out the XLL file and the DNA file to the end user, get them to save the file to their PC, then get them to add the addin to Excel via the addins manager in the Tools menu, henceforth the function will always be available to that user on that PC.

OK lets take a step back and look at what we just did.

The first 2 lines of code in the text file are:

<DnaLibrary>
  <![CDATA[

These are just XML element tags, they are used by Excel DNA to help it read in your code, they are not part of the function.

The next line opens a new Module and gives it a name “Mod1”, later this module is closed with “End Module”

Function MyFirstFunction(byVal YourText As string)as string
      'Other code here
End Module

The actual code that does the work are these three lines…

Function MyFirstFunction(byVal YourText As string)as string
      MyFirstFunction = YourText & ",...Hello World!"
End Function

“Function” Declares a new function, which we call “MyFirstFunction”, a bracket, then an argument is defined, another bracket, then the return type of the function. The next line returns the result to the function, which is the text you passed in the Argument “YourText” and the string, “,…Hello World!”. Finally "End Function" closes the function.

Then

]]>
</DnaLibrary>

Are just the closing parts for the XML trees.

Very simple stuff if you’ve written VBA I’m sure you’ll agree. In the next post will look at taking a VBA function and porting it over to Excel DNA, in real live so to speak…

*Generally VBA code transfers quite well in to VB.Net code, some times you need to change the syntax a  little, some times you need to use a dot net library etc., start with something small and simple and work up, you’ll soon get the hang of dot Net.