VSTO & .NET & Excel

June 7, 2010

VSTO: Upgrade and port present projects to VS 2010?

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 6:57 pm

As many of us Excel developers may be aware of (or should be aware of); VSTO is still under development and has not yet reached a mature stage although it comes closer for every new version. New features are being added frequently. The situation creates a degree of instability in the VSTO platform and is one major reason why we still might not want to base client’s solutions on VSTO.

As I write this blog entry the latest available VSTO version is version 4.0 which is shipped with VS 2010. For every new major version of VSTO one supported version of Excel tend to be dropped, i.e. the oldest supporting version is being dropped. When version 1.0 was released it target Excel 2002 and 2003. When version 2.0 was made available it target only Excel 2003. When Excel 2007 was released MSFT released VSTO 2005 SE that allowed us to work with both Excel 2003 and 2007. VSTO 3.0, shipped with VS 2008, was an exception as it target both Excel 2003 and 2007. Before 3.0 was released we had version 2.0 with VSTO 2005 SE on top of it. This wasn’t a good situation so when VS 2008 was released MSFT also decided to release version 3.0 of VSTO.

Anyway, the limited backward compatibility creates badwill for the VSTO technology. Especially among native Excel developers who, under all years, have become used to that the backward compatibility for every new version of Excel is really good. Another aspect is that for individuals, running their own micro Excel business, it’s expensive to invest in a new VS.NET license on a frequently basis. It’s also difficult to use the VSTO technology in environments with mixed versions of the Office suite.

But as we move away from Excel 2003 and older versions the situation must be improved, especially if MSFT want us to use VSTO on a regular basis. That is not to say that Excel developers should replace VBA with VSTO. Personally I have never seen that as an option, rather more VSTO as a complement to VBA. Also, bear in mind that the initially intention with VSTO was to provide .NET developers a platform to develop Office solutions on. Speaking about VBA, MSFT has now launched a new site dedicated to Office and VBA – Office VBA Developer Center. I recommend You to check it out.

A new feature has been added to VS 2010, embedded interop types, which replace the need of PIAs when targeting Excel 2007 and 2010 and when using the .NET Framework 4.0. Perhaps this is the major news in VSTO 4.0. Another major improvement is that the VSTO 4.0 runtime component is now part of the system files when Office 2010 is installed. Both these two improvements mean that we don’t need to check if the PIAs and the runtime component exist or not when deploying solutions.

The Office extensions for the .NET Framework (part of the VSTO 4.0 runtime component) are different depending on if we target version 3.5 or version 4.0 of the .NET Framework. Consequently they also behave different. Speaking about .NET Framework, if we need to target version 3.5 with a VSTO 4.0 solution then we need to be aware of the fact that .NET Framework 3.5 Client Profile is not supported. In other words, we must use the full version of .NET Framework 3.5.

For more information about the runtime’s assemblies please see Assemblies in the Visual Studio Tools for Office Runtime. For more information about the Office extensions please Visual Studio Tools for Office Runtime Overview. For an excellent write up about the VSTO 4.0 runtime component please see VSTO 2010 Runtime components explained.

Creating new VSTO solutions based on the VSTO 4.0 runtime component, targeting either Excel 2007 or Excel 2010, is straightforward. When targeting both Excel 2007 and Excel 2010 we don’t need to apply the lowest common denominator strategy as we must do in version 3.0. In other words, we can leverage all the new features introduced in Excel 2010 even when a solution target Excel 2007. I must explicit thanks the VSTO team for this improvement. As a consequence we don’t need to have the earliest targeting version of Excel when developing the solutions.

But to upgrade existing solutions to VSTO 4.0 may or may not be simple, if possible at all. Perhaps the most important information is the fact that we cannot port VSTO solutions that target Excel 2003 to VS 2010. VSTO 4.0 targets only Excel 2007 and 2010 (both 32-bit and 64-bit). For more information please see Creating an Add-in for Office 2007 and Office 2010 that “Lights Up” on Office 2010. Again a major upgrade of VSTO means that the oldest supporting version of Excel is dropped.

To develop and maintain Excel 2003 solutions we still must use VSTO 2.0 or VSTO 3.0, i.e. either VS 2005 or VS 2008. Sure, we can be upset because of this situation but this is the price we pay when using a technology that is still under rapid development. Since Excel 2003 still plays a critical role in many corporates it still must be supported. Practically it means that we must use both VS 2005/2008 and VS 2010 side by side to support Excel 2003 – 2010. What we can hope for is that a majority of all corporates will migrate from Excel 2003 to Excel 2010 within the nearest future. However, a trend among the corporates, at least in my experience, is that they want to extend the life cycles for existing Office versions.

For more information about migrating existing VSTO solutions please see Migrating Office solutions to the .NET Framework 4.

Personally I see it as a must that the next VSTO version can target Excel 2007, 2010 and the next coming Excel 15.0. If not then it’s high time that we drop VSTO.

Instead of using VSTO we can develop managed COM add-ins based on the Shared Add-in template in VS, which is my present main strategy. I still use the RAD tool Add-in Express .NET. Porting managed COM add-ins from VS 2008 to VS 2010 is workable.  For a walkthrough how to do it, i.e. using a shim to isolate the add-ins from each other, please see COM Shim Wizards for VS 2010.

To port VSTO solutions for Excel 2003 to managed COM add-ins is possible but we then must manually copy code etc between the solutions and carefully test the new solutions.

For additional information on the subject to port present VSTO solutions please see Fixing Compile and Run Time Errors after Retargeting VSTO Projects to the .NET Framework 4 and

Now that Excel comes in two versions, 32-bit and 64-bit, the good news is that if we create our VSTO solution with the VSTO 4.0 runtime in VS 2010 and we compile it for “Any CPU” then the solution will run on both the Excel platforms. Of course, if the VSTO solution calls native 64-bit APIs it requires that we set the platform target to “x64” and then it can only run with the 64-bit version of Excel 2010. For additional information please see Will your VSTO add-in run on Office 2010 64-bit? Yes, probably.

Deployment with ClickOnce has also been improved in VSTO 4.0, i.e. it’s easier to deploy. But for Excel developers that still apply the one file strategy even the simplified ClickOnce deployment is still rather complex. But again, it’s part of the price if we want to use a modern development technology like VSTO.

What can be concluded is the following:

  • It can be rather confusing! Let me know if You spot any error in the text.
  • VSTO is still under rapid development and still has not reached a mature stage.
  • We don’t need to use the lowest common denominator strategy when target both Excel 2007 and Excel 2010.
  • When targeting Excel 2007 and 2010 and also using the .NET Framework 4.0 we do not need to pay extra attention to the PIAs which decrease the burden upon deployment.
  • We can port VSTO solutions from VS 2008 to VS 2010 but not for solutions that explicit target Excel 2003.
  • Managed COM add-ins, based on the Shared Add-in template in VS, is still a very competitive alternative to VSTO solutions.

Kind regards,
Dennis

Edit note: A kind visitor (thanks Hans) pointed out that I should mention that if target Excel 2003 -2010 we can use VS 2008 to create the Excel 2003 solution which than will work in both 2007 and 2010. Of course, it requires that we apply the lowest common denominator strategy, which requires us to only use features that 2003 can handle.

Create a free website or blog at WordPress.com.