1

Hi Stackoverflow Users

I was recently assigned to rewrite a excel document my company uses a lot. Originally it was written in matlab, but to easier distribute it, they migrated it to excel. It was done very bad, and therefore it worked on some computers and not on others. So it was rewritten to vba. But now they want it to be able to do very complex calculations like Monte Carlo simulations. therefore we wish to write it in c#.net for performance and development reasons. the last week i have tried and tried to learn how it was best done. I found several possible solutions, but i have not found a single way to do it which meets my 2 simple demands:

  1. The user doesn't have to go to any settings and add something, if it have to be done, it need to be done in code

  2. The calculations is done at the press of a button, so i need to be able to receive the event

I have spent so much time on the web, but i don't know exactly what to search for, so it all just seems like a big wall of unexplained, complex com-magic.

So what are your suggestion? Is there any solution which meet those demands? Any suggestions, references or any other help will be deeply appreciated.

Rasmus Damgaard Nielsen
  • 1,940
  • 4
  • 18
  • 33

1 Answers1

1

Excel doesn't natively support C# in macros, so you're not going to be able to meet your first demand in a stand-alone spreadsheet. If you're willing to install an add-on into Excel, you might want to consider Excel-DNA. This would allow you to compile a .NET DLL containing the functions you want to add in to Excel.

Receiving an event from a button click is simply a case of adding a button control onto a sheet, and assigning a macro to it. This macro (e.g. a VBA function) will be run on demand when you click the button.

Alan
  • 2,962
  • 2
  • 15
  • 18
  • I am sorry, i'm have not been to stackoverflow for a very long time, so i'm not that good at asking questions. It's ok if i have to use dll, it just have to be able to be installed automaticly. But what do you think about VSTO, is that a possible solution? – Rasmus Damgaard Nielsen Oct 24 '14 at 10:51
  • Yes, VSTO is definitely a possible solution. You might want to have a look at this question which discusses the pros and cons in detail: http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects – Alan Oct 24 '14 at 11:11
  • VSTO does not give you a good approach to building UDF functions that you can call from a worksheet, while Excel-DNA is great for making UDFs (and advanced add-ins) with .NET. If you can implement your calculations as UDF functions, they are easy and flexible to use from the familiar Excel environment. – Govert Oct 24 '14 at 13:38