In this post we are going to cover how to use an IDE (Integrated Development Environment) to write a DLL and use that DLL with Excel DNA. There are a few reasons we would pick this approach over just writing the code into a text/xml file.
- You get to use an IDE, and all the benefits that come with that…
- Complex code is easier to manage
- Debugging is much easier
- The DLL “protects” your IP (a bit)
- Working with userforms etc. becomes possible/much simpler
There’s other stuff as well but lets not labour the point.
OK, first which IDE to use? As far as I know all Microsoft IDE which support .Net 2 or above will work with the latest version of DNA. So that’s 2005 through to 2010, including any and all express versions. The great thing about DNA is its not compiling some oddball super specialized DLL which needs a lot of equally obscure registry settings and support files to run, its a bog standard managed DLL. But remember you will need the correct dot Net framework running on the target PC.
In this example I’m going to be using SharpDevelop [get it here]. It’s a free IDE for the dot Net framework (supporting, C#, F#, IronPython, and Boo). In many ways it’s not as polished the Express editions, but in some ways it’s better. The “Pro” editions of VS are in my opinion the finest development environments available, but they do cost a lot of money. The other monumentally good thing about SharpDevelop is that it installs and opens much quicker that any version of Visual Studio. Installing VS takes an age, opening VS 2008 (VB) express for the first time on my PC takes 30 seconds for SharpDevelop(3.2) its 4 seconds! Regardless of what you are using, the principals are the same, it might just be that the various bits are in slightly different places, just look around; you’ll find what you need in the end!
Ok, Step one (after installing the IDE, and downloading the DNA package*) is to create a new Class project. Open SharpDevelop, click File>New>>Solution:
In the dialog that pops up find the VB node, and select that (tip, you don’t need to drop into any of the child nodes), pick the “Class Library” project. Notice at the top right of the dialog there is a dropdown for picking the target framework version. Unless you have a compelling reason not too, pick “.Net Framework 2.0” – this should have the widest coverage. Finally fill out the details at the bottom and create the project.
In case you have not used an IDE before it worth us talking about what you have just done. In the world of Excel we normally create one file and that holds all our code. This is a little different, but don’t worry it’s not rocket sugary. What the IDE has just done is write to disk a few files that it will use to hold and manage the project, if you add new files, and when you build the project these file will be placed in this project folder. To start with there are 3 files and 3 sub folders. The folders are (briefly!); “Bin”, with another sub folder, this is where the output DLL will normally be written to, “Obj” with further sub folders; this is where the IDE puts the files it uses for building your project, and “Properties”, guess what goes in there! The 3 files are “XYZ”.sln, this is the solution file – this opens the project and all the other support things for this solution (for example if you had more than one project in a solution), “ZYX.vbproj”, the VB project you just created, this will also open the project, and the solution, but not any other projects in the solution, and “NewClass.vb”, this is the actual code file, like a module in VBA.
Ok, back in the IDE, you should have something that looks like this:
The first thing we need to do is add a reference to the Excel DNA integration assembly. To do this go to the Project menu, then “Add Reference”…
In the dialog that is displayed, go to the “.Net Assembly Browser” tab, and click Browse, then find your DNA folder, open the “Distribution” sub folder and select the file called “ExcelDna.Integration.dll”:
Click “Open” and the file will be added to the list of References, then click OK
Next we need to add some code to the class. At the top of the class file remove the comments and add “Imports ExcelDna.Integration”, while we’re here let’s rename the class to “Public Class MyFirstDNAAddin”. Next, in the project explore to the left of the screen, right click on the class file, pick rename, and change the name of the class file from “NewClass.vb” to something like “MyAddin.vb”
We are now ready to add some functional code. Lets Write a “Hello world” function. Add this code under the Class declaration:
Public shared Function HelloWorld (byVal sString As String) As String
Return "Hello World, from a DLL..." & sString
End Function
Next compile the project, hit F8, or Build>>Build Solution, or click one of the green down arrow buttons, shown in the red box below:
Cool, all being well, you should have just built a DLL. Notice at the bottom of the IDE there’s an “Output” window, which tells you how long the project took to compile, if there were any errors these should also be displayed at the bottom of the IDE.
Next go to your project Folder, and open the Bin folder and then the Debug folder, the DLL will be in here with two other files, the DNA integration DLL and a temp file, copy the DLL you just created – “MyFirstIDEAddin.dll”, or whatever you called it, in to a new folder,… somewhere new…
Next copy the “ExcelDNA.xll” from the DNA package (its in the distribution folder) into the same folder where your DLL now is. Then create a new text file and add the following code in it:
<DnaLibrary>
<ExternalLibrary Path=”MyFirstIDEAddIn.dll” />
</DnaLibrary>
Close the text file, saving the changes, and rename the text file to something like “HelloIDE.DNA”, finally change the name of the XLL to “HelloIDE.xll”, and double click it.
Excel should open, enable macros if needed, and open anew workbook. If you now go to the function wizard, your function should be in there, and you can use it as you would any other function.
Remember you need to distribute the three files for this to work on another PC, that’s the DNA file, the DLL file and the XLL. If you only want to send out 1 file you can use the packager, we will cover this in another post.
* download Excel DNA from codeplex, unzip the files, and copy them to some suitable location, “C:\ExcelDNA\\ExcelDna-0.27”, for example would be a good place.
Superb walkthrough Ross, really clear.
I may never get a chance to try this stuff out now as I’ve got a new role that is one step away from the coal face, but could really see my stuff going this way in the future if I was sticking in my old job.
Well done with the new job Gordon, and thanks for the feedback, I was a bit worried about this post because it came together over a number of days, in the down time between doing actual work, so its good to know it fits together!
Ta
Ross
Thanks!!!! One question though: how did you create the UDF cathegory “HelloIDE”? Is it automatic?
btw, watch out for the 2nd screenshot.
Hi cmenem,
Yes automagicly, it is possible to change/manage this through tags – I’ll show that at some stage of look in the more sample folder…
oh and thanks for the heads up!
Sure. Please, for the next post, try to interact a little with the OM.
I love these DNA posts, keep em coming :-)
Thanks for the feedback Peder, good to know people find it interesting… I’ll keep going as much as I can, just got a bit busier here, so I’m gonna have to work a bit harder! ;-(
Ross,
You made it again! High time to take DNA seriously and take the opportunity to explore SharpDevelop at the same time.
Great work Ross and I suggest that when You have finished the DNA series You compile a new blog entry, as a summary, that gives us access to them all.
Thanks and all the best,
Dennis
Thanks for the support Dennis, I will defiantly so a summary/link post when I’m done
Thanks
Ross
Ross
Thanks for this post…Its a great help. SharpDevlop looks good.
Quick question….If I have to create a “serious” UDF in SharpDevelop + DNA – do we get access to the Excels object model or directly on do we need to set references somewhere.
I Sam, this is the topic of my next post, I’ll try and get it out today
Thanks
Ross
[...] First create a new DNA solution in SharpDevlope, and import the Excel DNA Integration DLL, as we did before, etc, etc, etc. (See last post) [...]
I am uncertain why you referenced and imported ExcelDna.Integration, since the code does not seem to use this library.
I also noticed that when I compiled for .NET 4.0 I got no error, but the function did not appear in Excel. When I targeted .NET 2.0, it worked just fine.
Hi Dan,
That’s a good point about not using the DNA integration lib in this function, I’m not sure if it would work if you did not add a reference to the library – I will try that at some point, most of the time we will use the reference for something though.
As for the version issues, are you using the lasted version on DNA, 0.27 is the only one that works with dot Net 4.
Permit me another comment: Works OK with .NET 3.5. Your example ports to F# effortlessly as well.
I was a bit shocked to find that you can mix an external dll reference and C# script (presumably VB too) in the same *.dna file.
Referencing ExcelDna.Integration is not necessary if you are only defining a free-standing function which does not interact with Excel objects.
Not sure about the framework issue then, might be worth having a look at the google group for that one…
Thanks for clearing up the lib referance question, makes sense I guess.
Thanks
Ross
Thanks for great tool!
Would it possible to add some help to UDF on ExcelDNA?
(ie, UDF description, parameter’s help like Intelisence)
hi, i’m getting this error using your dll method:
There was an error while processing C:\Documents and Settings\john\My Documents\Coding\Office Add-ins\MyDNA2\MyDNA2.dna:
There is an error in XML document (2, 23).
Invalid character in the given encoding. Line 2, position 23.
Software:
ExcelDna-0.28
Excel 2007
Visual Studio Express 2010
Window XP
.NET 4.0 installed (and prior versions)
Any idea?
thanks for an awesome post!
tried again, using SharpDevelop. Followed your instructions to the letter. Still getting same error (see my comment above).
Hi Johny,
If you copy-and-paste the ExternalLibrary example from the blog post above into a text editor, you might get ‘smart’ quotes around the Path attribute. XML only allows single or double regular quotes, not the fancy-looking ‘smart’ quotes. Just retype the quotes in your .dna text file.
-Govert
Ross,
You can’t imagine how excited I am about this new technology after reading your posts. Please keep writing…
I’ve been toying with VB.Net for some time. This series has taught me more in an hour than every other site I have visited. Thanks for the effort.
I too got the error on Line2 initially. I shouldn’t have been lazy & copied & pasted the code. When I manually typed it in the error disappeared.
Roy
Thanks Roy, glad it helped
Ross
[...] Writing an Excel DNA function Using an IDE [...]
I’m trying to create a sample as described, but getting this error when trying to launch it:
External library could not be registered – Path: ExcelDnaTest.dll
Error: Could not load file or assembly ‘file:///D:\projects\temp\ExcelDnaTest\bin\Debug\ExcelDnaTest.dll’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
UPD: I’m using ExcelDna 0.28 and my assembly is for .NET 4 client profile
Hi John,
Sounds like Excel has already loaded a version of the framework below 4. Do you have other .net addins running?
Try, swaping to a build with 2.0, or unloading the other addins.
If that dont work, pop over to
http://groups.google.com/group/exceldna
someone there will beable to help for sure.
Thanks for stopping by
Ross
Switching to .NET 2 helps, but it’s strange, because I have VSTO add-in built for .NET 4 and it works without any problems.
I am getting this error can some one help me
There was an error during processing of C:\NewFolder\HelloIDE.dna:
There is an error in XML document (2, 23).
Invalid character in the given encoding. Line 2, position 23.
Hi Joy,
You have a error ion the DNA file – the text file – make sure tou have copied the text correctly and all the names ect are correct, make sure ” are right for example.
Thanks
Ross
how do you enter comment or the help file for the add in function?
Public Shared Function [GG(Description="Another adding function.", HelpTopic ="GGHelp.CHM!1012")](ByVal Ma As Double, Mb as Double, Mc as Double, Md As Double)as Double
…
End Function
Thank you for the suggestion but still am getting a closing bracket error that says:
Bracketed identifier is missing closing ‘]’. (BC30034) – C:\Users\H Penton\Documents\SharpDevelop Projects\GG\GG\Mabcd.vb:12
Where does the closing bracket go?
[ExcelFunction(Description="Another adding function.", HelpTopic ="MyHelp.CHM!1012")]
public static double MyFunction(…)
Public Shared Function [GG(Description="Another adding function.", HelpTopic ="GGHelp.CHM!1012")](ByVal Ma as Double, Mb as Double, Mc as Double, Md As Double)as Double
…
End Function
Thank you for the suggestion but still am getting a closing bracket error that says:
Bracketed identifier is missing closing ‘]’. (BC30034) – C:\Users\H Penton\Documents\SharpDevelop Projects\GG\GG\Mabcd.vb:12
Where does the closing bracket go?
Sorry to necro but I’m getting a weird error no one else got. I see the function come up but get a “Can’t find project or library” error. Did I skip a step?
Have you imported the XL DNA dll? or put it in the right place?
[...] 2: Compiling a .DLL http://www.blog.methodsinexcel.co.uk/2010/09/22/writing-an-excel-dna-function-using-an-ide/ Ross McLean uses the SharpDevelop IDE; VB Express is [...]