Excel Developer conf

Last Saturday a few of us Excel developers sat down and discussed many of the issues facing us and our projects.

Many of the topics have also been discussed here too.

One common point was the .net/office disconnect. Not necessarily the technology, but the cultural gap seemed to be a factor.

When I got back home I had the latest VSJ in my in-box with an ad for DevWeek. This is possibly the biggest .net developer training event in the UK. There is not one single session touching VSTO or COM add-ins, or anything to do with Office in fact. There is plenty of Silverlight fluff, of course.

I know Dennis and Johan, and others I’m sure, are making .net and Office work from a business POV.

But here are the factors I’m personally considering:

  • Discussions at the event and in the pub suggesting .net has low appeal
  • Stephanes comment about lack of control of .net framework version and
  • Ross’ work to test that

Based on all of that I intend to completely ignore .net/VSTO for 2008. I’ll still tinker with C# for glue code etc, but I don’t even think I’ll bother looking much further at VS2008 VSTO. If we get VSTA in Excel 14 then I may invest some effort during the beta, but otherwise I’ll pass I think. (And even if VSTA is there, it will be a version 1.0 (or less!) technology which holds very little appeal.)

The load order issue makes code reliant on more recent frameworks more likely to fail than stuff that targets older frameworks. This just isn’t something I want to get tangled up in. [Edit: This is nowhere near as bad as I understood it to be – see Erics comment]. [note to self, trust but check before relying on stuff from the intarweb!]

I actually think that looking at Delphi will be a better investment of time for most Office oriented devs, but I’m personally going to focus more on C++ and xlls.

Most of my xll demo on Sat was in VS6.0, and Stephane has also raised concerns about deployment with all versions since then so it looks very much like I’m going to be stuck firmly in the nineties with my VB6, VBA, and VC++6.0.

I can’t help wondering if the Visual Studio team have lost the plot a little. Or maybe I(/we) just havent kept up and have missed the benefits of the deployment pain we now face?

Cheers

Simon

Advertisements

21 Responses to “Excel Developer conf”

  1. Dennis Wallentin Says:

    Simon,

    If I understand it correctly .NET is developed in one part of MSFT and Office suite in developed in another part. This has already a negative impact on the development to integrate .NET & Office.

    Another difficult aspect is that the PIAs are controlled by the same part of MSFT where .NET is developed in. It exist some issues with the PIAs but appears to have no priority.

    From my point of view Excel and in particular the VBE is outdated and therefore is also a major source for the gap between .NET & Excel.

    All in all, MSFT needs to put some efforts to improve the integration between .NET & Excel.

    However, it’s too easy to just claim that .NET is the bad guy here in this context and therefore should be dropped. I totally disagree because I find the online Excel community to be too much focused on Excel only. .NET was released 2002 and very little has actually been discussed within the Excel community 2002 – 2007.

    I also believe that many are afraid of .NET because it’s much like a ‘black box’ for them and it does not exist an easy way to learn it.

    I fully trust that MSFT actually will improve .NET & VSTO platform as well as Excel. The questions are only when and how!

    Kind regards,
    Dennis

  2. Simon Says:

    Dennis
    Good point on the PIA’s I hadn’t thought about them.
    I don’t see .net as the bad guy, if anything I’m frustrated that the Excel team havent embraced it further/properly. But I also wonder why?
    You are right the Excel world has largely ignored .net, and also the VS world has largely ignored Excel.
    I don’t think VS devs will ever care about Excel, but the Excel world may show some interest in .net if/when the deployment matches the ease of workbook deployment.
    I’m sure things will get better – my concern is will it be too little too late?
    Everyday most organisations are investing further and further in Excel/VBA solutions. That means .net must get more and more compelling as the hurdle is getting higher daily.

  3. Harlan Grove Says:

    I’ll ask what may seem like ignorant questions.

    1. If a .Net developer were creating an application that didn’t perform many numerical calculations and didn’t result in a report, why should Excel even enter into the distant periphery of their awareness?

    2. I can use WSH Dictionary and regular expression objects from VBA, and VBA presumably provides garbage collected memory allocation. If the bulk of the applications I’m developing involve worksheet formulas, a bit of ADO or DAO, and some simple UI embellishments, what compelling functionality would I be missing by avoiding .Net?

  4. Eric H Carter Says:

    I posted this comment on Ross’s blog–thought I’d post here as well:

    In Excel 2007, it will always load the latest CLR available on the machine, regardless of COM shims. In Excel 2003 it can be an isue if a COM shim locks to an older version of the .NET framework–for this reason it is advised that COM shims where they exist always load the latest framework.

    Add-ins in .NET that you write will work with the version of the CLR you built them with (e.g. 2.0) and they will work on newer CLRs (e.g. 3.0).

    When an add-in is installed on a box, it is assumed that the add-in installer will ensure that at least the CLR that it was built with so the add-in will run. So if an add-in was written against CLR 2.0 it should also bootstrap 2.0. That way, with 2.0 installed on the machine, Office will start loading that version (the latest version on the machine) and all add-ins written against all versions through 2.0 will load properly. The bummer for VSTO 1 and VSTO 2 developers was we didn’t make it very easy for you to write those installers–you had to figure out how to bootstrap the CLR you built the Add-in against on your own.

    In VSTO 3 the good news is we make the creation of installers that bootstrap the right CLR really easy to create through our new support for ClickOnce. The bad news is that these features work for Excel 2007 and not 2003, so you still have to work through some of the same difficulties if your are doing Excel 2003 development.

    Hope this helps–if you need more help or have additional questions, feel free to contact me through my contact form on my blog.

    Eric

  5. tfsjohan Says:

    Harlan: You are right that you can create pretty much the same things with Plain Old VBA, MSXML3 and MDAC 2.6, which is pretty much what you can be sure of to exist on any Windows XP Office 2003 machine. The Visual Basic Scripting Runtime has support for FileSystemObject and Dictionaries and the Visual Basic Regular Expression library has support for Regexps. What else can you need?

    Well. Maybe I don’t need so much more technologies, but I do like to work more effective. The System.Data namespace in .Net is so much better than MDAC 2.6. Just to name one feature is named parameters for queries. In Access and MDAC 2.6 you need to have your parameters in the correct order. Yikes!

    Another thing is creating Windows Forms. Comparing the forms designer in VBA and .Net is.. Well.. Not comparable. At all. And add to that the plethora of .net controls that are really useful, just naming the date picker for one. I know, you can add an ocx for that in vba, but then you have the whole deployment in vba as well, so… Then there’s the fact that VBA cant create UserControls that can be reused.

    .net has alot of really good language features that speed up your work and make you do more with less typing.

    However, I do write alot of VBA code still. And I think it’s a pretty cool challenge to recreate the rich user experience that .Net offers using just native VBA och UserForms. You can create really cool UI:s, but it requires more work for the same result and it’s alot harder to maintain than it would be in .net.

    As I written before in some comments, I really think that VSTA would be the best thing. The simplicity of VBA, but the power of .net and Visual Studio.

    I’ve never looked at Delphi. Why should I use it?

    // Johan

  6. Dennis Wallentin Says:

    Simon,

    >>I’m sure things will get better –
    >>my concern is will it be too little too late?

    Whenever MSFT release a new Office version it does not bring so much business usefulness as we may expect or want. So there will probably a time lag between the de facto version in use and the latest version from Redmond.

    The latest version of Office will always work best with the latest available of .NET.

    So I believe the answer is yes.

    Harlan,
    I can agree that the interface .NET & Excel is not the hottest area for the .NET developers group.

    As we know there exist a great number of serverbased middlewares that can create Excel reports. VSTO is just an additional tool among all other.

    It also exist some great spreadsheet components with powerful calculation engines which also reduce the need of Excel. In addition, it also exist some inexpensive components like OWC and Office Framer control.

    The same can be said for the group of Excel developers with .NET BUT only as long as the business needs can be supported for the customers. And as long as the customers accept the poor UI native Excel provides us with. The later will perhaps be the most important driver to adapt .NET.

    Eric,
    In my opinion VSTO 3.0 is so much better then any previously version so the VSTO team have made a great work with it. I would say that the closer integration between VSTO & VBA is one of the best improvements together with the setup. However, the badwill that versions 1.0/2.0/2.5 caused will take time to recover from…

    My strategy to solve the CLR version problem is to use the version previously the latest available and always save it on a local disk. However, the early release of VS 2008 left me behind with the use of version 2.0. But I will soon update my solutions to use 3.0.

    Kind regards,
    Dennis

  7. Simon Says:

    Eric
    Thanks for the clarification. VSTO 3.0 and Excel 2007 does now seem to be an effective proposition. As Dennis says though, a few of us are still recovering from fingers burnt with prior versions.

    If demand in the marketplace picks up (I am not as proactive as Dennis!) I’ll certainly keep an eye on it.

    Johan, Delphi is billed as having the power of C++ with the ease of use of VB. The underlying language is Pascal not basic though.

  8. Stephane Rodriguez Says:

    @Eric,

    Perhaps I need some clarification and things I find confusing to say the least,

    “In Excel 2007, it will always load the latest CLR available on the machine, regardless of COM shims. In Excel 2003 it can be an issue”. ==> This seems to say that this is a breaking change. Great, you had Excel developers forced to comply with the Ribbon/addin UI SDK problems, and now loading scheme on top of that.

    What do you mean by “always load the latest” ? Aren’t you assuming that there is no override rule in Excel.exe.config? I think any ISV out there may require their customer to do those hacks just to get their add-ins to work. And that screws other add-ins loaded in the list, especially if they haven’t been tested for that CLR version. Everytime Microsoft ships a new CLR version (and that is often), this scenario becomes de facto for all add-ins as soon as the customer installs this version of the CLR (pushed through Windows Update, or some software totally unrelated to Office) : instant death.

    This logic screams for an out-of-process COM shim mechanism that would fix matters for anyone willing to guarantee it works. You would trade performance (and that in and of itself can be argued), but that would work. It would also allow side-by-side CLR to work as well, not worry about “latest”/”config file”/”where in the loaded list” matters.

    As an aside, why isn’t Microsoft Office installing one version of .NET that would guarantee that it’s there ?

    “Add-ins in .NET that you write will work with the version of the CLR you built them with (e.g. 2.0) and they will work on newer CLRs (e.g. 3.0).” ==> This implies CLR versions are backwards compatible. It simply is not the case. Take .NET 1.x versus .NET 2.0 as a simple example of that.

    That Visual Studio 2008 can target any CLR version, not just .NET 3.5 is in direct contradiction with the “always latest” loading kind of spirit.

  9. Dennis Wallentin Says:

    I still use Delphi 5.0 as I can create small exe with it. The key is that I don’t need to ship it with xxx MB systemfiles. It’s pretty slick to just send an exe file around 20 KB (especially these days).

    Kind regards,
    Dennis

  10. Harlan Grove Says:

    I infer from much of the discussions about .Net that it’s not reliably backward compatible. Seems like that should have been a design goal.

    I use a particular Excel/.Net application on a daily basis. The .Net bit adds a front-end with a standard menu UI, displays a few (< 10) standard dialogs in response to some of these menu commands (standard dialogs in the sense that VBA would have displayed pretty much the same Save As dialog, etc.), and provides an XML database interface for queries that could have been handled adequately by ADO. In this case, .Net provides nothing that Excel/VBA couldn’t.

    If 80% or more of a particular application’s functionality is provided by Excel itself without VBA or .Net or whatever, then it makes much more sense to use VBA for the remaining bits. If less than 50% of functionality would be provided by Excel, why bother using Excel at all. In the 50-80% range, opinions may differ.

  11. Simon Says:

    Harlan – that Excel/code mix issue is my view too. I like the technology but I wonder if there is a viable market for it.

  12. Stephane Rodriguez Says:

    @Harlan,

    True, especially when you have customers doing crazy things sometimes. Here’s one from a customer I had : he wanted to use Excel 2007 files because as you know you can store one million rows now. To make matters even more convincing that Excel 2007 was useless for that, he told me that the data had no formatting, and that the file had no chart and other things. I tried very hard, but couldn’t not make him use a CSV file instead of an Excel 2007 file. I simply hope this guy is not very high in the ranks of who decides to buy what in his company.

  13. Johan Nordberg Says:

    I have a question for you: If using Visual Studio would make the development time 75% of the time needed for the same functionality developed in VBA, would you think VSTO would be a better solution?

    You talk alot of market demand and what your clients would think. But in my experience clients care about a) budget and b) features. If I can develop more features in less time, I’m certain that my clients prefer to install .net framework, vsto runtime and the pia:s. This fall I’ve done four VSTO project for four different clients, two Outlook addins, one Word template and one big Excel solution with both an addin and two templates. None of these clients have had anything at all to say about installing the extra bits.

    For the VBA projects we’ve done this fall, all of the clients requested that we do real msi install packages, so what’s the real difference?

    I really don’t think we need to be afraid of suggesting new technology to clients. As long as we deliver the best solution for the money, they will continue to be happy.

    // Johan

  14. Harlan Grove Says:

    The problem is that for simpler add-on features, VSTO is unlikely to achieve any time savings over VBA, at least not VBA with a code library from which to draw frequently used procedures, types, classes and forms.

    This gets back to the point I was trying to make: if 80% of functionality is in the spreadsheet itself, then the code isn’t doing much. In that scenario, .Net would be grossly excessive. Ergo, if the bulk of the models one develops fall into this category (mostly spreadsheet formulas with a modest file system interface and customized printout), there’s little if any benefit to .Net.

    If the features your customers want are outside the spreadsheet, you may be right about the benefits of .Net. But if the features are spreadsheet ones, it’s difficult for me at least to see how .Net would be useful.

    Examples sometimes help in discussions like this. Consider needing to print a few discontiguous ranges on the same printout page. The VBA approach would be to paste pictures of the ranges into a newly created blank worksheet, print that worksheet then delete it. Would the equivalent in .Net be easier?

  15. Dennis Wallentin Says:

    Harlan,

    I do understand what You’re trying to say and I can also agree with You although not fully agree.

    We can actually interact with Excel objects model on the .NET platform. This is valid for both managed Shared add-ins / VSTO application level solutions & add-ins and VSTO workbook solutions. For me and Johan it should be as easy as we do it with VBA.

    The latest update of VSTO has made it possible to interact with VBA in a better way than previously. In that way we can leverage both VBA and & VSTO at the same time (i e a kind of perfect world).

    The Pareto rule (80 % / 20 %) is valid also for all kind of softwares solutions so this rule can be applied for Excel solutions like You say.

    But there may be other aspects that dictate the terms of development:

    # Customers requirements as well as any existing policies
    # Security and protection are two important aspects for a growing number of corporates.
    # Knowledge and skill when it comes to tools to use

    Windows Vista is shipped with .NET Framework and it wouldn’t surprise me if VSTO runtime will be added to Vista or later version. Should we consider .NET Framework to be an overhead on Vista?

    Kind regards,
    Dennis

  16. Marcus Says:

    “installing the extra bits”
    Hi Johan – that’s probably going to depend on the client. I’ve done work for smaller clients who didn’t care what technology was used provided it solved the problem within a given budget. Many larger clients do care and are very restrictive.

    One major reason, I believe, why VBA solutions still enjoy so much favour is there independence of installing frameworks or involving the IT department.

    Also, many IT departments don’t want any non-standard (i.e. standard for them) technologies installed as it gives them one more technology they have to support (should that developer get hit by a bus).

    I’d actually like to get my teeth into a decent .net project, but the opportunity and a welcoming environment haven’t materialised. Harlan has a good point regarding the usage of Excel functionality. More and more of the Excel apps I’m involved in are really database applications (poorly implemented in Excel) which require migration to a RDBMS – .Net would provide a very suitable interface to this,

    Regards – Marcus

  17. tfsjohan Says:

    Harlan: For a really simple project, as the one you describe I would use VBA. No contest. But we seldom do such small projects.

    As Marcus says, more and more projects involve databases. And if I need to distribute an Access database and possible some OCX controls, like the Calendar control as well as the Excel solution I still need to create a setup project or atleast some sort of cmd-deployment file.

    I get frustrated everytime I need to do some sort of OO-programming and is stuck in VBA land. The code gets messy and you end up using crazy optional variant arguments and type checking or copies of the same method with different names instead of using a simple method overload. Or a really basic thing as subclassing objects?

    Most my projects also involve some sort of XML config file. Just the simplicity of using the XML serializer in .Net saves me from writing and maintaining alot of code. Sure I’ve done the same in VBA many many times, but it is more code that takes time from creating “real” stuff.

    And then we have the forms designer………

    But maybe Harlan has hit the nail on the head. Maybe we can’t compare the pros and cons different technologies as long as we don’t put them in the right context. I don’t think I’d ever use VSTO for creating a simple macro. But most of the projects I do is about 60-200 hours.

    // Johan

  18. MacroMan Says:

    …but business users create simple macros all the time. Connecting to a database, or any other kind of data source, can be considered a simple macro. Most projects with Excel VBA are simple, it really depends on the amount of data the project uitlizes. That’s when you decide if you want to use Excel or build a stand alone app.

    I created a reporting app that connects to a database and reports on p&l for a trading portfolio. The department did not want IT involved because it takes too long.
    We would have to set a meeting with a Business Analyst, the BA creates a spec, a programmer develops the spreadsheet, then it goes thru QA for testing. The developer usually has zero finance knowledge, sometimes BAs knows nothing also. The department would have been going back and forth with the developer or BA on business requirements.

    I finished this reporting tool in Excel VBA within two weeks. After a few days part of the project was already being used while I finished the rest. Since the department is not part of IT, we would not be allowed to have Visual Studio. If anything, include VSTA along with VBA in office. If a proffesional developer gets involved from an IT department, he might as well build a stand alone app, not a spreadsheet.

  19. Simon Says:

    Johan
    I’d suggest using C# against Excel would add about 20% to the time needed to develop a reasonable system – not reduce by 25%.

    And when you consider the free availability of source code and other resources VBA for all its language deficiencies holds its own pretty well.

    If determined to shoe horn an OO design into an Excel app (I’m not convinced that makes much sense btw) then maybe C# would catch up a bit.

    No idea on VB.net, that is probably significantly better than C# for Excel interaction but weaker on resource availability.

    I suspect our different clients give us very different constraints to work within. Like Marcus I couldn’t mandate a .net framework change, or even a COM registration often. I need some clients like yours I reckon!

    MacroMan – I agree, totally.

  20. Jon Peltier Says:

    “And when you consider the free availability of source code and other resources VBA for all its language deficiencies holds its own pretty well.”

    With all of the VBA and VB6 web sites available, there is no end to the copy-and-paste programming you can do. It’s not what you know, it’s what you can find.

  21. tfsjohan Says:

    Simon: I’ve actually switched to VB.Net for Office solutions. With all the ByRef and Optional madness of the Office object modell, C# is real pain.

    It’s beyond me how so many method use ByRef arguments to return more than one value, instead of using a object och struct (Type in VB). To me it’s just bad design. So the only reasonable way to comply to the bad design, VB is much easier to use then C#.

    Jon: Regarding copy-paste-programming. VB.Net is basically compatible with VBA or VB6. I’ve converted a few Word VBA templates to .Net and just copied the code and made a few changes. The biggest change was how Properties are written and that the object modell in MSXML3 is quite different from the System.XML namespace. And if I google something that have something to do with the Excel object modell, that’s no difference in VBA, VB6 or .Net. So in that case everything is stealable anyway…

    // Johan

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: