While developing any Microsoft Office add-in (Excel, Word, Outlook, Powerpoint, Access, etc.), you will eventually encounter performance problems and often it will be difficult to see what is causing the poor performance. One way to diagnose poor performance is to pause the code at random with Visual Studio and hopefully Visual Studio will stop inside the slow function. The odds are higher that it will stop in the slowest function because that’s what’s taking up the most time. The other more exact option is to use a profiler, even if your budget is zero you can still do it for free. SlimTune which you can get here: http://code.google.com/p/slimtune/ is fantastic for profiling C# add-ins (it supports .net 4.0). Figuring out how to use it wasn’t immediately obvious, so let me show you how to do it (I’m also writing this in case I forget).

I posted an article to codeproject that shows how to create a dynamic view model for WPF that supports enums. It makes it easy to use enums in the model and create list boxes in the UI to allow users to select the value. You can  find the article here.

Managing COM resources in an Office add-in is very important since ignoring it means Excel might not even close with your add-in installed. Ironically C++ is a bit easier this way because Visual Studio can generate wrapper classes for you that take care of releasing the COM objects as long as they are created on the stack or deleted explicitly. You might think that the .Net garbage collector takes care of the objects but it doesn’t entirely, it just frees up the proxy object not the COM resource.

There are two ways to change cell values in an Excel spreadsheet: the wrong way and the fast way. The wrong way takes the form of a loop where each cell is individually accessed in a for loop. Each time you assign a value, your add-in makes a COM call into Excel. Calling COM object methods from C# or anything for that matter, is a slow process. It might be 10ms or so (just a guess) but if you were looping through 200k cells that becomes 2000 seconds of COM calls.