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:
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.
Good stuff, Ross. Will definitely give this a go at some point.
Ross,
Nice and I hope that VBA developers see a smooth way to .NET and to ExcelDNA via Your series!
Kind regards,
Dennis
[...] Excel DNA Hello World Function « Methods In Excel [...]
Thanks Chaps,
Gordon, give it a go, it’s great.
Dennis, thanks for the encouragement!
Ross
EXCELLENT START!
No more vba?
When clicking on MyFirstDNAAddin.xll, I get this error:
“The file you are trying to open is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
OS: Windows 7 64-bit, Excel 2010 64-bit.
Hi,
Excel-DNA does not currently support the 64-bit version of Excel. I’m hoping to look at it in the next few months, but it’s pretty tricky.
Note that Microsoft recommends you use, and the default install is also, the 32-bit version of Office, even on 64-bit operating systems.
Excel-DNA works fine on 32-bit Excel running under 64-bit Windows.
-Govert
Hi,
Great job, but
in this way the code is open,
can you obscure or hide the code?
Regards
PLEASE read carefully, and also read the other post on this subject, then you can answer your own questions
Thanks and good luck
Ross
No doubt, an astounding recommendation and guidance. However, as per Roberto how 2 save your code? Furthermore the TWO files requiring SAME names and SAME locations would be required by all the users of the AddIn?
No doubt, an excellent recommendation and guidance. However, as per Roberto how 2 save your code? Furthermore the TWO files requiring SAME names and SAME locations would be required by all the users of the AddIn?
Hi Faraz, you can package the files into one XLL, using the packaging tool included in the distribution, if managing 2 files is beyond you…
past that I don’t understand what you question/problem is
Ross
Hi Ross!
Sure am a novice in this regard and that’s why sure found your response to Roberto’s comment to be attracting attention as well.
Furthermore, sure would oblige if you can provide me an example of a source code for a UDF to simply refer to two different Excel cells and add them up.
Thanx again.
Hi Roberto and Faraz,
I’m the developer of Excel-DNA and I’d be happy to help you get started, and answer your questions on using the library. (Ross was kind enough to write some introductory blogs posts, but I wouldn’t want to burden him with support too!)
The best place to start looking is at the Excel-DNA Codeplex site: http://exceldna.codeplex.com. For support, I prefer questions to the Google group at http://groups.google.com/group/exceldna, where there are already thousands of questions and answers to search through.
For the simplest case of a function adding two values, check the Excel-DNA distribution – the ExcelDna.dna sample has an AddThem function that does exactly that. For more details, I’m happy to reply on the Google group with further discussion.
Cheers,
Govert
This was a great post and a fun read.
Keep ‘em coming.
Thanks.
Christopher
[...] http://www.blog.methodsinexcel.co.uk/2010/09/03/excel-da-hello-world-function/ Similar to above. 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-ish performance improvement. [...]