Hey everyone, just a quick question. I've been developing an Excel Add-in using VSTO, and recently our server here at work began to run a lot slower. I ask about their relation, because the reports and such that I'm developing the add-in for are based on the shared server, so when developing and debugging, I'm constantly accessing them, so I'm worried I may be causing the slow down. If this is possibly the case, I'd like to do what I can to stop it; I thought I'd been pretty good about making use of Marshal.ReleaseComObject(), but is there anything else I should look for or be aware of? Or is there no way possible that I'm causing the performance decrease?

Thank you.

Hi Nyoshu

It's important to keep "general automation" and "working with Office via VSTO" separate. One of the advantages of VSTO is that it takes care of managing COM objects for you. So possibly many of the things you've been reading do not apply when working in a VSTO project. I say "possibly" because it really does depend on what you're doing.

As far as everything within the instance of Excel goes: opening, saving, closing workbooks; writing to cell ranges - all those things do not require you to explicitly manage the COM objects. VSTO is taking care of that for you. The only thing you need to do is set things to "null" ("Nothing", if you use VB) when you want to release them.

One example, based on the details you give: in order to work with the running instance of Excel from within your add-in:
      Excel.Application xlApp = Globals.ThisAddin.Application

You do not need GetActiveObject because your code is already running in the Excel instance. So there's nothing for you to instantiate - and nothing for you to release. Excel is running, your add-in is in Excel, so you have direct access to the application instance.

It could help you to read through the documentation and examples on MSDN for VSTO, just to get a "feel" for "best practice" coding VSTO projects. As I mentioned, a lot of the "dirty work" a non-VSTO developer has to take into account is taken care of for you by the VSTO tool.

---

Cindy Meister, VSTO/Word MVP

Hi Nyoshu

I think you need to provide more information on exactly what your Add-in is doing, especially in relation to the server. How is your add-in accessing the server?

Also, generally an Add-in wouldn't require Marshal.ReleaseComObject. Can you be more specific about the circumstances for which you're using that?

---

Cindy Meister, VSTO/Word MVP

Cindy,

My Add-in is, of course, created in VSTO. It does a number of things, but to be brief: User can create tasks, teams, and personnel, to be drag-and-dropped into assignments on a treeview. This treeview is then saved and serialized into an XML document for future retrieval. Also, based on this treeview, a spreadsheet is generated with spreadsheets for each Team, headers for each personnel, and subheaders under each personnel. Also, any necessary formatting. It prompts the user to save the spreadsheet, if wanted, for future use, under a filename that is also typed in prior. I then have a menu that holds and populates all the tasks, per personnel, per team. The people who create those tasks can simply click on the person/task to put the necessary information in, and then hit a button to both print, and add these tasks to the original spreadsheet under the person. These tasks, per person who creates them, are saved in a file labeled 'Tasks<shortdatestring>'. It also runs reports based on the spreadsheet, and can sync any information added from other users when it's only open by one person (not a shared file).

It accesses the server because every XML file is on this server. Also, all the templates and worksheets used and saved are on this server. Basically, everything the add-in uses is on the server. From my research, which I've been doing for quite a while, I was under the impression that everytime I created a com object to access Excel, I should clean up using marshal.releasecomobject. For instance, to use the open version of Excel, I used 'Marshal.GetActiveObject(Excel.Application)'. Now, due to the fact that I'm constantly using this application, that's the only one I don't release. Otherwise, anytime I assign a range, worksheet, book, etc, to a variable, I release it. Was I incorrect in this?

Thanks for your help, Cindy!

Hi Nyoshu

It's important to keep "general automation" and "working with Office via VSTO" separate. One of the advantages of VSTO is that it takes care of managing COM objects for you. So possibly many of the things you've been reading do not apply when working in a VSTO project. I say "possibly" because it really does depend on what you're doing.

As far as everything within the instance of Excel goes: opening, saving, closing workbooks; writing to cell ranges - all those things do not require you to explicitly manage the COM objects. VSTO is taking care of that for you. The only thing you need to do is set things to "null" ("Nothing", if you use VB) when you want to release them.

One example, based on the details you give: in order to work with the running instance of Excel from within your add-in:
      Excel.Application xlApp = Globals.ThisAddin.Application

You do not need GetActiveObject because your code is already running in the Excel instance. So there's nothing for you to instantiate - and nothing for you to release. Excel is running, your add-in is in Excel, so you have direct access to the application instance.

It could help you to read through the documentation and examples on MSDN for VSTO, just to get a "feel" for "best practice" coding VSTO projects. As I mentioned, a lot of the "dirty work" a non-VSTO developer has to take into account is taken care of for you by the VSTO tool.

---

Cindy Meister, VSTO/Word MVP