Getting started with ExcelDna

Do this first:

• Get ExcelDna: Version 0.20 on CodePlex , unzip in a convenient directory.

• The .NET 2.0 runtime must be installed. The .NET Framework Version 2.0 Redistributable Package is available from Microsoft.

• Macros security in Excel must not be 'Very High' or 'High' (if set to Medium -- it will prompt whether to enable each macro library). To use the .NET macros you will have to 'Enable' at the prompt.

1. Create a user-defined function in Visual Basic

• Make a copy of ExcelDna.xll in a convenient directory, calling the copy Test1.xll.

• Create a new text file, called Test1.dna (the same prefix as the .xll file), with contents:

  	<DnaLibrary>
  	<![CDATA[
  
  		Public Module MyFunctions
  
  			Function AddThem(x, y)
  				AddThem = x + y
  			End Function
  
  		End Module
  	]]>
  	</DnaLibrary>
  

• Load Test1.xll in Excel (either File->Open or Tools->Add-Ins and Browse).

• You should be prompted whether to Enable Macros, click Enable.
• Enter =AddThem(4,2) into a cell - you should get 6.
• There should also be an entry for AddThem in the function wizard, under the category Test1.

Troubleshooting

• If you are not prompted to Enable Macros and nothing else happens, your security level is probably on High. Set it to Medium.
• If you get a message indicating that the .xll file is not recognized (and even opening it as text), you might not have the .NET Framework 2.0 installed. Install it.
• If Excel crashes with an unhandled exception, an access violation or some other horrible error, either during loading or when running the function, please let me know. This shouldn't happen, and I would like to know if it does.
• If a form appears with the title 'ExcelDna Error Display' then there were some errors trying to compile the code in the .dna file. Check that you have put the right code into the .dna file.
• If Excel prompts for Enabling Macros, and then the function does not work and does not appear in the function wizard, you might not have the right filename for the .dna file. The prefix should be the same as the .xll file and it should be in the same directory.
• Otherwise, if something goes wrong, let me know.

2. Creating a user-defined function in C#

• Change the contents of Test1.dna to:

	<DnaLibrary Language="CS">
	<![CDATA[

		using ExcelDna.Integration;
			
		public class MyFunctions
		{
			[ExcelFunction(Description="Joins a string to a number", Category="Useful functions")]
			public static string JoinThem(string str, double val)
			{
				return str + val;
			}
		}
	]]>
	</DnaLibrary> 

• Reload the .xll, either from File->Open or in Tools->Add-Ins.
• Check with the formula =JoinThem("abc", 123)
• If the first example worked, this one should too.

3. Making the functions from a compiled library available

ExcelDna can also load any compiled .NET library. Public static functions with a compatible signature are exported to Excel.

• Create a file called TestLib.cs containing the following code:

  using ExcelDna.Integration;
  			
  public class MyFunctions
  {
  	[ExcelFunction(Description="Multiplies two numbers", Category="Useful functions")]
  	public static double MultiplyThem(double x, double y)
  	{
  		return x * y;
  	}
  }
  

• Compile TestLib.cs to TestLib.dll: from the command-line: c:\windows\microsoft.net\framework\v2.0.50727\csc.exe /target:library /reference:ExcelDna.Integration.dll TestLib.cs

• Modify Test1.dna to contain:

  	<DnaLibrary>
  		<ExternalLibrary Path="TestLib.dll" />
  	</DnaLibrary>

• Reload the .xll and check =MultiplyThem(2,3)

• The ExcelDna project is hosted at CodePlex.
• More info, questions and discussion can be found on the main ExcelDna discussion forum: the exceldna Google Group.

Comments

[M.Grieser]

I have a question...

Once I created the Test1.xll and the Test1.dna (which worked just fine), what exactly needs to be distributed with the Excel Workbook for users to access the .xll functions?

[Govert]

You need to give the users Test1.xll and Test1.dna. However, they must also have the .Net 2.0 runtime installed, have the macro security relaxed and maybe fix the version loading issue described above. But the only ExcelDna bit that you need is the .xll file, to which you add your .dna and any .dlls you create.

[R. Sarkissian]

Can you create an object in the C++ code, and expose its properties and methods in Excel?

R. Sarkissian

[govert]

Currently objects can not be exposed to Excel directly from managed code, only static methods. Of course you can wrap your object methods by creating corresponding static methods, as you would when creating an .xll in C/C++. I plan to add some built-in object support to be compatible with the Excel Services UdfClass/UdfMethod declarations. Properly doing object references and object tracking for user-defined classes might come later.

[N. Teofilov]

Greet stuff! Wow!
Thank you!

I have two questions:

1. How to create a menu with c#?

2. How to add description to the Add-In from c#?

[Andrew Roberts]

Can I add the XLL to my existing c# project and call the Excel functions from some c++ code?

Regards

[jake.]

Any plans to support separate AppDomain with private .config file - without this deployment becomes problematic.

Thanks.

[jake.]

btw i forget to mention that i have developed software using ExcelDna and i believe is a quality product. it just needs a few touches on the deployment side before i can really leverage it in a production environment. support for object handles would also be awesome.

[Javier Medina]

Would a sample of code to use a dll created with VB code be possible? I'm too neophite to get it on my own!

[govert]

Hi Javier,
To make a .dll out of your VB.NET code, use vbc.exe in the command line. You might need to translate some of the command line arguments - just check the help for vbc.
You are welcome to send me e-mail if you still have problems.
Regards, Govert