The end result of this small example project will be a simple C# program, which reads data from named Excel ranges, uses this data in C# program and finally returns calculation results back to named Excel ranges. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.
Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.
STEP ONE : C# function
Create a new C# Class project "ExcelInterface". Rename created default "Class1.cs" to be "ExcelInterface.cs". For the class ExcelInterface.cs, copy the code in the box below and replace the existing code with this one.
Since we might need to use Windows Forms object in our program (MessageBox in Catch block), we need to create reference to System.Windows.Forms library (Project - Add reference - .NET - System.Windows.Forms). After this, build solution and save it.
You'll see a lot of errors at this point, but do not mind - those will disappear completely, as we create reference to Excel-DNA library next.
As we see, the only static class method execute (void) is not returning any value. Instead, it creates Excel application, reads data from named Excel ranges and after performing some calculations (discount factor df from r and t) or modifications with the data (multiplying matrix items with the value of pi), it writes results back to named Excel ranges. Note, that for objects typed with C# keyword dynamic, we are not going enjoy any intellisense support. This means, that you have to be comfortable with Excel Object model (Application, Workbooks, Worksheets, Range, etc).
STEP TWO: Excel-DNA
Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be False.
Add new file as text file to project (Project - Add new item - Text file) and name it to be ExcelInterface.dna. CopyPaste the following xml code into this file.
From the properties of this dna file, set Copy to Output Directory to be Copy if newer.
Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\ExcelInterface\ExcelInterface) and rename it to be ExcelInterface.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see ExcelInterface.xll file what we just pasted into this ExcelInterface folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.
Build the solution. Everything should have gone well without any errors or warnings. At this point, my \\ExcelInterface\bin\Release folder looks like the following.
At this point, we are done with C# and Excel-DNA.
STEP THREE: Excel named ranges and "trigger program" for starting C# program
Open a new Excel workbook. From Excel Name Manager (Formulas - Name Manager), set range names (_r, _t, _df and _matrix) and initial values to these ranges as shown in the picture below.
Finally, we need to have "triggering program", which will start our actual C# program. In Excel workbook, open VBA editor. Insert a new standard VBA module and copyPaste the following code into this module.
While this workbook is still open, doubleClick ExcelInterface.xll file in your \\ExcelInterface\bin\Release folder. After this, xll file content can be used by Excel and our C# function is available to be called from VBA program (Application.Run). VBA program tester will call and start C# program execute, which reads all input data, performs calculations and sends result data back to worksheet. We could also have one ActiveX button in our worksheet and then use Application.Run for starting C# program inside button event handler.
Run the program and we are done.
For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.
Thanks again for reading.