Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 28. August 2008 18:22

Update, Oct 12, 2009: if you are looking for a way to install a VSTO add-in with multiple dll, I found out there was a better solution here.

I just completed my first real-life VSTO project, and I am officially a convert: I can do everything I did in VBA, using mature languages like C#, and the comfort of the Visual Studio development tools.

Everything has not been smooth, though. I struggled quite a bit initially with deployment, a problem which just does not exist with VBA. However, after some digging, I came across this great post, which provides comprehensive step-by-step guidelines on setting up an add-in project for Office 2003.

At that point, I thought my issues were over, and I just cruised along, happily coding in C#. And then I decided that I would extract the logic of my calculation engine in a separate dll, which I would reference in my Excel add-in as a “satellite assembly” – and had a bad surprise. On my development machine, everything worked beautifully, and when I ran the installer on a clean machine, it installed my add-in without any complaint (The satellite dll was even added to the add-in folder), but somehow, the add-in did not run. No error message, no indication of a problem, but where I expected my dll to perform calculations, nothing happened.

Before getting into how I resolved the problem, a quick word on why I thought this would be a good idea to separate the business logic in its own dll. I had two motivations to do that: reuse, and testability.

There is nothing wrong per se in keeping all your code in the add-in project. However, imagine you build a calculation engine which could be re-used in other projects. If all your code is in the add-in project, you would have to copy/paste the code file into the other project, and to painfully change namespace and references all over the place, to integrate it in the new project. This is very unpleasant – and you will have to do it every time you create a new project. On top of this, if you end up finding a bug in your code (this happens, even to the best of us), you will have to manually change the code in all projects. By contrast, if your business logic is nicely separated in a dll, the only thing you need to do is to reference that dll in any project that uses it, and you are done; and if you find a bug, you need to fix it in one place only, and re-reference the updated dll, and you are done. No code duplication, minimal manual work: much better.

The other issue is testability. I am a unit-test fanatic, and like to build tests as I go, adding tests hand in hand with code, and leveraging the refactoring tools of Visual Studio. I also like to separate the tests from the project itself, so that I don’t have to ship my tests with my product. To do that, I typically add a second project to the solution, which contains only unit tests, and references the main project. The problem here is that because of the technology behind VSTO projects, you cannot reference the VSTO project in your unit test project. You have to do one of two things: referencing the add-in dll, or building the tests in your main project, i.e. shipping them included with your product. And if you reference the dll, you lose all Visual Studio refactoring support, and your whole test-driven development cycle becomes very painful. That’s not good.

So what can you do about it? It took me some time to figure it out, but the solution is actually relatively easy. The post mentioned earlier has a small-print caveat:

This article makes the following assumptions about the project that you will deploy:
•    There is only one customization assembly; there are no other referenced or satellite assemblies deployed with the solution.

The issue is that in order for your add-in to work with your “satellite assembly”, i.e. your dll, you need to explicitly grant security trust to that dll as well. How do you go about that?
The procedure is fairly simple, and follows the same general lines described to grant security to the add-in assembly. I assume that you already have set your project up so that you have the add-in project, the SetSecurity project, and your add-in deployment project in place.

I assume also that you have referenced a dll in your add-in project – in my case, the AddInEngine dll.

Right-click on the deployment project (in my case, ExcelAddInDemoSetup), and select View > Custom Actions. If you followed the guidelines provided by the post I reference, you should see 4 “folders” Install, Commit, Rollback and Uninstall, each of them containing one item “Primary output from SetSecurity (Active)”.

Right-click “Custom Actions” > Add Custom Action, and select “Application Folder” in the combo box; Click “Add Output”, “SetSecurity” and “Primary Output”. At that point, you should see that each of the 4 folders now contains 2  “Primary output from SetSecurity (Active)”. I recommend that you rename the ones that have just been added to something like “Primary output from SetSecurity for AddInEngine (Active)”, so that you know what's what.

The procedure now is identical to the one you followed to grant security to the add-in dll itself. The CustomActionData field for the original Custom Action granting security to the add-in dll looked like this:

/assemblyName="ExcelAddInDemo.dll" /targetDir="[TARGETDIR]\" /solutionCodeGroupName="MyCompany.ExcelAddInDemo" /solutionCodeGroupDescription="Code group for ExcelAddInDemo" /assemblyCodeGroupName="ExcelAddInDemo" /assemblyCodeGroupDescription="Code group for ExcelAddInDemo" /allUsers=[ALLUSERS]

In the Custom action you just added, simply replace “ExcelAddInDemo.dll” by the name of your satellite assembly, so that your CustomActionData field looks something like:

/assemblyName="AddInEngine.dll" /targetDir="[TARGETDIR]\" /solutionCodeGroupName=" MyCompany.AddInEngine" /solutionCodeGroupDescription="Code group for AddInEngine" /assemblyCodeGroupName="AddInEngine" /assemblyCodeGroupDescription="Code group for AddInEngine" /allUsers=[ALLUSERS]

Do the same substitution in the Rollback and Uninstall; at that point, you should see something like this, and you are set to go!


If you have multiple dlls referenced, you will have to grant security to each of them individually, which is a bit tedious. Hopefully, I can find a way to automate that process down the road…

kick it on DotNetKicks.com

Comments

12/7/2008 2:14:18 AM #

Kishore

Hi

I'm using VS 2008 + office excel 2003 for development.
I can not able to create setup for my code. It is working fine and loading in debug mode, but setup is not working, I'm getting hanged due to this. my client is chasing for this.

But I can not able to solve my problem, can you help me

Kishore India | Reply

3/4/2009 5:56:11 PM #

Mathias

Hi Kishore,

Sorry but I just noticed your comment now. Did you manage to resolve your issue? Deploying a VSTO add-in can be challenging, and I found this walk-through very helpful:
msdn.microsoft.com/en-us/library/bb332051.aspx
Hope this helps.

Mathias | Reply

9/16/2009 1:01:47 AM #

Michiel

If you are someone really getting the problems (like me)
And dont have the following items in you prerequisites list:
      Microsoft Office 2003 Primary Interop Assemblies
      2007 Microsoft Office Primary Interop Assemblies

You can add them in the way shown in this link:
blog.developers.ie/.../2427.aspx

Michiel Netherlands | Reply

9/26/2009 10:54:10 AM #

trackback

VSTO add-in with multiple assemblies

VSTO add-in with multiple assemblies

Clear Lines Blog | Reply

10/3/2009 10:50:02 PM #

Domain

How to show an Explorer Toolbar of my VSTO Outlook add-in for any given folders?

Domain United States | Reply

10/5/2009 4:46:30 PM #

Mathias

I wish I could help, but I have never worked with Outlook, and I am not familiar with the object model... You might want to check out this blog; I know Robin Shahan has been working on Outlook add-ins with VSTO, maybe you'll find answers there!
http://robindotnet.wordpress.com/
Good luck,
Mathias

Mathias United States | Reply

2/8/2010 8:50:43 AM #

Michael

Hi Mathias,

I have created an Excel 2007 VSTO 3.0 document level add-in and wanted to unit test it.

My add-in creates a Ribbon Tab which calls methods contained  behind the worksheets. Mainly formating cells/sheets, checking value, removing columns.

How did you do the excel portion of unit testing?
Did you implement a host adapter for testing? If so, can you give me any pointers how to implement one? I already looked at the samples from the SDK but they were "over-my-head" Smile.

Michael United States | Reply

2/11/2010 9:34:00 PM #

mathias

Hi Michael,

this is a great question - thank you! Like you, I found unit testing VSTO projects challenging, and I don't have a perfect solution yet. The issues are somewhat similar to the problems of testing against a database: the dependency is expensive to instantiate, requires clean-up between tests...
From the description of the problem, the solution that comes to mind is mocking, but I can't honestly say that's what I have done so far. My approach has been to have as little functionality dependent on Excel as possible, and to write integration-style tests for a few methods I use all the time, like read from/writes to worksheets, using test files. It worked well because my recent projects relied very little on Excel events.
I just started a series on building a complete add-in, and I will take that opportunity to look deeper into the question. Out of curiosity, can you please indicate what SDK examples related to unit testing you looked into?
Hope this helps,
Mathias

mathias United States | Reply

2/13/2011 4:57:04 AM #

trackback

Unit testing VSTO projects

Unit testing VSTO projects

Clear Lines Blog | Reply

7/7/2012 11:29:27 AM #

pingback

Pingback from cutebeachvacationideas.sims2dl.com

Volvo hit with $1.5 million fine for recall delay | Cute Beach Vacation Ideas

cutebeachvacationideas.sims2dl.com | Reply

7/23/2012 2:36:17 AM #

Olutoye Akinjiola

I have a similar problem. I completed my first workbook project. A simple example from MSDN. the clickonce, cd/dvd  runs without any issues on my developmetal computer. For the life of me, I cannot get it to run on any other computer.

Olutoye Akinjiola United States | Reply

12/20/2012 4:20:24 PM #

pingback

Pingback from siteduct.com

Perl Script Installation Service

siteduct.com | Reply

2/10/2013 9:51:03 PM #

pingback

Pingback from sitefixing.com

Website Repair

sitefixing.com | Reply

10/5/2013 3:34:14 AM #

pingback

Pingback from alexatrafficracer.org

Extended Living Spaces

alexatrafficracer.org | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS