VSTO Arrives

(on my CV!)

I deployed my first commercial VSTO add-in last week. (I have done loads of meddling over the years of course, especially in the early versions)

As per my previous advice I use VS2008 and it was targeting Excel 2007.

It was a rewrite of a VBA add-in, I chose VSTO, mainly because it needed to listen for Excel events and this is notoriously flaky in VBA.

I have always said I like the idea of VSTO and the technology. My biggest gripe has always been deployment, and the significant list of prerequisites.

As this was a homogeneous corporate environment which met the prereqs, there weren’t really any downsides.

Given the choice between working in Visual Studio or working in the VBAIDE (whose own mother even, wouldn’t love it) I would always choose VS. Given the choice between a C language and a BASIC language I would always choose the C one.

So with no obvious downside (apart from fighting C# into working with a COM OM of course), and the joy of VS2008 and C# it was a no brainer.

So here is the scoop:

Events in VSTO are easier than VBA because you don’t need to faff around with a class wrapper. I spose you could just use the thisworkbook module in VBA, but I think thats a bit misleading.

Because VS2008 has mega intellisense you only have to type the first couple of letters and you are away.

C#/Excel is a bit more painful than VBA, but the VSTO guys have the ‘VSTO Power toys’ (google it)(or bing it) which eases then pain. For example styles(x) in VBA has to be Styles.item(x) in C# (can’t exactly remember which collection it was, but it took me ages to work out what the compiler was complaining about). As I understand it the power toys are an unsupported version of some of whats coming in 2010, the biggie being that you can call lots of Excel methods without loads of object.missings.

C# has some nice language features which if you can use make up for the OM niggles.

And finally deployment is a veritable piece of pish. Publish and forget. And with ClickOnce the add-in phones home at an interval you decide to check for updates, if it finds them it silently installs them. There is no, repeat NO security faffing required any more, what a relief.

This was my first brush with VSTO since version 1 using VS2003? I think, and Office 2003. At that stage I think I declared it unfit for use and abandoned it. As did many others in fact.

It’s hard to put into words just how far it has come since then, I’m still not convinced for Codematic web stuff that is offered to everyone and anyone but for corporate developments I think VSTO is the way to go, and the sooner the better. The alternative to join .net and Excel is a shimmed COM add-in which I wouldn’t really call a clean solution.

A quick scoot on Jobserve suggests Excel/VBA/C# is a pretty strong skillset right now, and I think it will get stronger and stronger. I am starting to see specific requests for VSTO and ClickOnce too.

I’ll do another post about the dynamics of the jobmarket because I think there are some big changes afoot.

Are you using VSTO? If not, are you planning to? and when? C# or VB?

cheers

Simon

Advertisements

17 Responses to “VSTO Arrives”

  1. Mathias Says:

    Glad you enjoyed it! I have been working with Excel + VSTO since a while now, and once you have spent a few hours using Visual Studio, there is no way you can go back to the VBA IDE without grinding your teeth… And things should just keep getting better with .NET 4.0, which eases the pains of getting C# to talk with Excel Interop, and Visual Studio 2010.
    And… I really hope you are right with the hotness of the Excel + VBA + .Net / C# skillset!
    Mathias

  2. teylyn Says:

    Great review. I’ve been using parts of VS 2008 in a project management role, TFS with templates for agile development and the like, and, yes, it’s a great interface.

    For the big unwashed masses, though, who today only have VBA at their (more or less competent) command, what is the way forward? I am not a developer. I can just about find my way through a piece of VBA code and make changes to tweak what I require it to do.

    For me, C# is a musical notation key, and I’d rather play E flat, for that matter, so, going forward, where will all the lay people play who just want a quick macro to move records from Sheet1 to Sheet2. Will we all have to buy VS licences? Would you even recommend spending time on learning VBA just now? Is there a market for VBA legacy software?

    Excel support forums and newsgroups are brimming with requests for “simple” VBA macros. Will all that be covered with VSTO only, going ahead?

    What do others think? Is VBA still a viable alternative or should we all jump on the VSTO train?

  3. Mathias Says:

    @teylyn: first, I really wish Microsoft made VSTO available in Visual Studio Express, their free edition of Visual Studio. This would at least give people a chance to try it out without having to shell out the price of the professional edition.
    Then, I believe VBA is still a viable alternative, in that it has its own space – and it is there to stay. As far as I understand it, VSTO is not intended as a replacement for VBA, but is aimed at professional developers. VBA is great if you want small-scale extensibility of a spreadsheet, VSTO is where you want to go if you want to build something that is larger scale, and become part of a corporate application. I can’t think of anything that VBA does, that can’t be done with VSTO, but while power users can pick up VBA, the same can’t be said for VSTO, which has a much steeper learning curve, because it requires a deeper understanding of .Net and object-oriented development.

  4. Dennis Wallentin Says:

    Simon,

    So how did You deploy the solution? Via ClickOnce or via Windows Installer?

    Kind regards,
    Dennis

  5. Simon Says:

    Dennis, ClickOnce so far, but we are still juggling.

    Mathias, totally agree on VSTO being in the Express editions, that would really get things moving I think.

    Teylyn, there is a choice in the way forward – stay close to the business with Excel/VBA, or move more technical with VSTO. VBA has still got lots of life, VSTO is currently going to work best in large enterprises I reckon.

  6. Doug Glancy Says:

    Simon,

    Please say a bit more about the prerequisites. I still haven’t deployed a VSTO .exe anywhere except on my own work computer (developed at home). It went out and found the 3.5 framework and installed it happily enough. What kinds of prereqs were you dealing with?

    Also I didn’t know about the notorious flakiness of events in VBA. I’ve written quite a bit of code based on both application-level and workbook events and haven’t noticed problems. I’d be interested to know more.

    • Steve Says:

      Doug
      Ja, I have not encountered problems with events either
      The only things I can say are:

      I hardly ever use events anymore, no need, just use basic events like button clicks etc (the user has to push something after all)

      in excel it can be a bit tricky turning calc on and off in code to avoid cascading events being fired (main reason to avoid events)

      up to 2003 the object browser had the Forms object model for simple GUI
      this is no longer the case in 2007 – getting events on the controls to interact with excel again is quite tricky (cause its another layer over the worksheet)

      saying this however I stopped using forms in xl so long ago I can’t remember when — why use a GUI when u have a worksheet — why bother with the effort — just drop the controls on the worksheet :)

      if anything I would say events in C# are worse than VBA, its the primary reason for creating memory leaks in C# managed code and the average C# person does not understand why

      Cheers

      Stephen

  7. Dennis Wallentin Says:

    Doug,

    Prerequisites:
    VSTO runtime; either version 2.5 (for Excel 2003) or version 3.0 (for Excel 2007.
    Primary Interop Assembly: either version 2003 or version 2007.

    You can control if any solution should check that they are available via the commands:
    1. Project > Project’s name Properties
    2. Tab Publishing > Button Prerequisites…

    Kind regards;
    Dennis

    For those of You who have 2nd edition of PED You should read chapters 24-26

  8. Steve Says:

    Mathias said it best.

    Microsoft does not like VBA but it will stick around. (Macro viruses)

    Medium level users will revolt if the can’t record macros or write simple code.

    Advanced coders will sigh every time they have to do VBA again and then just get on with it

    VSTO resolves 3 main problems encountered with VBA
    1. Security / Viruses – it has its own runtime which acts as a proxy that forwards instructions to the windows message pump (VBA uses the message pump directly, thats why viruses are such a problem)

    2. Threading, the VSTO runtime proxy can use either the STA or MTA COM threading model, so threading is possible in VSTO

    3. Versioning, VSTO resolves this whereas in VBA how many copies of the workbook do u have?

    VSTO also exposes the NET framework object model, so you can have advanced forms and controls if you want say with excel

    Here comes the problems:

    VSTO has a very steep learning curve. Ok so you know the say excel object model from the VBA days, but the NET framework takes years to really get into.

    Learning the C# language is the easy bit – the framework is the hard bit

    the other thing is that you need to use 3 components (at least in my case) to use C# well, these are:

    Visual Studio (I use 2008 and 2010 professional)

    .NET Reflector (a must use really — it really opens everything up)

    Windows Debugger (allows you to to snapshot actual memory because yes managed code leaks a lot)

    With VBA you are in a comfort zone, working directly in the office app and not worried much about anything else)

    In C# is welcome to the big bad world, u can do a lot more but need to understand a lot more

    I don’t see VSTO in VS Express – express was a freebie to target students who were using Java etc

    VSTO is a specialized application so won’t happen in express

    One funny thing about VBA re Access – no VSTO here (because access has its own Jet engine, so its SQL and VBA only with Access)

    Stephen

  9. Steve Says:

    re my last comment about VSTO and Access above

    I know that there’s no VSTO with Access…what I can’t remember is if there is a PIA for Access?

    Feeling tooooo lazzzzy to check :))

    Which leads me to this point

    I like the new NO PIA approach in VS 2010

    PIA’s are basically COM signature interfaces, so if you select the NO PIA option in your build then the required sigs (not the entire PIA) are embedded directly into the exe or dll build)

    Which means that the target machine does not require a heavy PIA install

    Cheers

    Stephen

  10. Simon Says:

    VSTO will have to be free to have a chance of breaking into VBA’s (free with Office) 99.99% office automisation share.
    Its already come down from being a fully priced version of VS to being a free component of VS2008. Maybe it will be called VSTA, but at some point there will have a to be a free with Office version. (IMO of course)

  11. Charles Says:

    @Steve,

    I suspect the VBA virus story is just for scaring the kids and selling AV software.
    When was the last time a VBA virus was found in the wild?

  12. Steve Says:

    Hi Simon

    Have you noticed that its not really called VSTO anymore?

    We call it that but MS seems to change it with every release of VS
    In VS 2010 its a real mouthful…so much so that I forget

    The new MS buzz term is OBA (Office Business Application)

    I think this is a problem, if the name keeps changing, now days you see the term “managed Add-Ins” in job adverts more often than VSTO

    Some interesting facts about VS Express
    The downloads passed 10 million a few years back (can’t remember exact date)
    Of the downloads 2 / 3 was VB.NET and 1 / 3 was C#

    however…

    At the more advanced (in practice) level its the other way around 2/3 C#

    I suspect that a lot of students and VB6 and VBA people downloaded VB.NET

    The ones that managed to make the leap (many would eventually just give up I think) would then move over to C# (this is what I did VBA to VB.NET to C#)

    I still think that MS only wants to use express to target students and coders that program other languages to stop the waves of Java developers

    C#’s primary competition is Java

    I would then think that VBA would also serve as an entry level to the office apps

    Finally you have VSTO, a much more specialised concept, so no I don’t really see a watered down version in express any time soon (MS have’nt really even finalized the approach even in VS 2010 professional)

    Cheers

  13. Dennis Wallentin Says:

    A light version of VSTO could possible be part of the VS Express version. A new free version will be included in Excel 15 which will also replace VBA.

    Just for the record; OBA (Office Business Application) is based on VSTO technology and is promoted by MSFT as a new concept towards enterprises. For any native Office developer the OBA concept itself is familiar but instead of VSTO we use VBA.

    Personally I have never seen any VBA virus and consider it to be some “true” created at MSFT’s marketing department. What is true is that VBA have a weak security.

    Kind regards,
    Dennis

  14. Simon Says:

    Steve no one here is going to fall for that macros virus bullshit, have you ever seen one?
    My work pc got infected in 1996, thats the last I heard of them that wasn’t ‘please please move to .net’ scaremongering.

    OBAs and LOBs are attempts to put a structure around some pretty fluid business processes.

    • Steve Says:

      Yes I have
      In late 1998 in my very first job in the UK after I left Africa
      It was an excel macro that brought the entire network down
      No one in the entire building could use excel for 3 weeks

      The rumour was that it was a former employee who was fired,
      this was his parting gift – never proved however.

      The point that I was trying to make was that VBA uses the windows message pump directly, so if a macro virus gets in its a big problem
      The have fixed the symptons but not the cause (AV, digital signing, allowing macros to run, etc, etc)

      With VSTO you have another layer of protection, the VSTO runtime, so you can have strongly named assemblies and other security measures, then only after all that has been satisfied will the proxy runtime pass the instructions over to the message pump

      So its better for corporate size solutions etc etc

      Cheers

  15. Mathias Says:

    @Steve, re: learning C#. Reflector is absolutely awesome, it’s a must have for any .Net developer. To that list I would add Subversion + Tortoise (version control).
    I agree that C#, the language, is not the hard part. On the other hand, I think that the major hurdle is to learn object-oriented design. In my opinion, the framework is not that difficult. It’s huge, yes, and some parts are very complex, but typically you will use only a subset of the framework. But when I started working with VB.Net, coming from VBA, I initially wrote code which looked a whole lot like VBA. It worked, but it wasn’t object-oriented, with the problems that go with it (untestable, hard to grow and maintain). It took me a good year until I got the hang of it.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: