Whats so good about VBA?

A (very valid) question from Johan.

Here is why I think VBA is so great, but first lets try and define VBA.

A tightly integrated application automation system that allows professionals from non software development disciplines to enhance their organisations use of the host application. With justifiable levels of training costs and tooling costs.

And secondly, lets add some context – we don’t develop in a vacuum so we should not review our tools in one.

Its 2008, VBA was integrated into Excel in v5 in 1995, last notable update was 97 really when we got the current IDE. Prior to VBA many orgs has dipped a toe in the macro waters with XLM and Lotus macros. Many organisations have over 10 years worth of live VBA code. And coders with 10 or more years experience solving that orgs problems with that toolset.

Software vendors desperate to sell us their latest version have almost convinced us that ‘legacy’ means ‘old useless shite that should be thrown out’. But legacy also means ‘something of value handed down from ancestors’. What better legacy than tested, proven, working code, no matter how out of fashion the language might be? or how ‘not to your style’ the code might be?

I’ll be frank, I don’t much care for BASIC derived languages, I prefer C based ones. VBA has a few language features missing, and a few backwards compatibility ones I’d happily see removed. It also has the odd inconsistency, and a few awkward twists (If not (x is nothing) then... springs to mind). VBA is more, much more than a language though, its a complete system in partnership with the host app, with history.

‘Do you like the whole VBA story or not?’ boils down to do you savour proven, working code? or do you like new stuff? Do you like safe and certain? or do you like unnecessary risk and waste?

VBA has a major entry barrier for someone from a user background (I mean business here, but also science, engineering etc – anything except sw dev I guess). But once over that barrier is a gentle learning curve through to extremely high levels of productivity. Object Oriented approaches represent an even bigger barrier to that target group in my opinion, with limited payback in that problem domain.

Excel is a functional tool, its users understand functions – they write them in almost every cell. Very few use classes, objects make no sense to Excel users. Classes are considered an advanced technique in Excel VBA, not because they are difficult, because they are an unusual way for a spreadsheeter to go about solving the sorts of problems they solve with spreadsheets.

Spreadsheeters email their spreadsheets around, this approach is absolutely proven to work in (and out of) organisations of all sizes all over the world in all industries. VBA supports this zero deployment in almost all cases. Most spreadsheet users have never heard the expression ‘deployment’ that is how ‘zero touch’ VBA is. (many have never heard of testing either but that is a separate discussion)

The Excel COM object model is an ideal target for VBA, optional parameters, parameterised properties etc are all fine. Unlike C# for example, which is from a whole other era, designed from the ground up to solve a whole other type of problem. Should Excel get a C#/manged code friendly object model then that would even things up a little. But by then there might be 15 or 20 years (or more??) of VBA legacy code to compete with.

Whats so good about VBA?, is like saying whats so good about driving on the left? honestly? it would be better for a whole host of reasons if we swapped and got in line with all the new world countries. But who, when, how and where could this be done? and who will pay, and when would they get payback (hint: never ever ever).

Whats so good about VBA? its here, now, in widespread use. Perhaps the correct question is what should an organisation that is already heavily invested in VBA (most are) do now in 2008?

Of course theres lots to not like about it too, but thats a topic for another post. The point of this post is that I don’t see anything currently better than VBA for most of the stuff that VBA is used for, and for the folks that use it.

Maybe the VBA argument comes down to: SW devs will always hate it because its an impure bastardisation of a beginners language. Business users will prefer it to the other available options because they can leverage existing investments, so it represents the best value for money.

Try this test: ribbon v commandbars? .net v VBA? either you like new stuff or you like existing proven stuff.

What have I missed? anything to add? anything you disagree with?

cheers

Simon

Advertisements

37 Responses to “Whats so good about VBA?”

  1. Johan Nordberg Says:

    Simon, even though I’m probably the most ribbon and vsto friendly reader of your blog (together with Dennis I guess.) I agree with you alot! Really! I do like VBA. I use it alot every single day at work. It’s amazing how much you can do with the Excel OM and the VBA toolset. That the codes lives inside the document is really great! Probably the one of the best things compared to vsto solutions.

    But I have a few “but”s:

    * Every valid point you make could be just as valid for a good calculator. Excel has a bigger learning curve. It’s more complex. Requires more hardware. Is new and unproven (compared to a calculator or pen and paper) But that doesn’t mean that Excel isn’t excellent and worth using. I would still use Excel for rather simple calculation that a calculator could do faster. And hey, the calculator even prints in real time!

    * Legacy also has a downside. 10 years of ad hoc changes mean 8 years of code that probably could and should be removed. It’s just that noone knows what the code does so noone dares to change it. This makes these tools bloated and really hard to maintain. Legacy is the definition for the expression “If it works – don’t touch it”.

    * .Net isn’t new. Neither is VSTO. As you said in an earlier post, it’s been around for more than 7 years. Office 2003 has had support for it for more than 5 years. How long does it take to make it existing proven stuff? Telnet has been around longer than then web. It’s dead simple with just text and some ansi codes if you’re really fancy. But that doesn’t make it better than html, css and modern web technoligies. Table based html designs is simpler and more widely used than css based design. But that doesn’t makes it better. Simple and widely used isn’t the same as better. 10 million people can be wrong!

    The more comments I read on your blog, the more sure I am that Excel development has two completly seperate targets. It’s the group that you describe and it’s professional developers. The first group will probably always prefer VBA because it’s simple, you can record macros and make small changes to the code. Professional developers that solve problems for other will probably prefer a richer programming language and a better IDE.

    Does vsto really have to suck just because vba solves problems?

    // Johan

  2. jonpeltier Says:

    Simon – In truth, Windows VBA was upgraded from 5 to 6 in Excel 2000, which makes it more modern than both Excel 97 and all flavors of Mac Excel.

    Johan – VSTO doesn’t suck because VBA solves problems. The point is that for people well versed in VBA, VSTO represents a lot of added overhead and a deployment barrier that is difficult to get around and difficult to justify because VBA plus Excel functionality can do so much.

  3. tom Says:

    Well said, Simon.

    I’m a professional developer, love new stuff, love new ideas. Will use, and have used, everything from SAP to .NET to EC2/S3 in order to solve business problems. But, for a certain type of problem (mainly the ad-hoc/fringe/”we’ve no money at the moment” problems so inherent in every-day business) nothing (yet!) comes near the truly powerful combination of Excel and VBA; the optimal intersection of business process and programming worlds.

  4. Biggus Dickus Says:

    “Whats so good about VBA?, is like saying whats so good about driving on the left? honestly? it would be better for a whole host of reasons if we swapped and got in line with all the new world countries. But who, when, how and where could this be done? and who will pay, and when would they get payback (hint: never ever ever).”

    Good analogy (and distinctively British methinks ;-)).

    It should always be remembered that VBA is first and foremost a “Macro Language”. That means it automates a Host application. VSTO on the other hand resides outside the application and therefore adds complexity to the standard development, automation and deployment of Excel models – which are what this is all about. There is too much influence on this debate IMHO by those who just like to “Code” as opposed to those who Design and use Spreadsheets.

    VSTO was built for “Profesisonal Developers” while VBA is for “Professional Spreadsheet Developers” – which in my opinion is an important skill and one which must get more prevelant, not less, as corporations want to include Excel data in their SharePoint solutions. First and foremost the Excel Models have to be useful to the people who care and feed them. In a perfect world they would then export data or Worksheets for use by SP. This will require better Spreadsheet developers and a better use of “macro automation”. To me that means VBA running on the client and does not need or justify the overhead of VSTO.

    Dick

  5. MacroMan Says:

    nice article Simon.

    I’d like to preface that C based languages are more elegant, and I enjoy using them. But…

    I just wanted to reiterate the obvious, VBA is a SCRIPTING language. Excel devs are not creating stand alone apps. OO languages are over kill for Excel.

  6. Simon Says:

    Johan, all good points as usual, I don’t think VSTO sucks at all. I am looking forward to getting a project where that seems to be the best solution. I nearly had one a few months ago but they got a cheap .net dev in to do it.

    All your buts are totally valid, and pretty much the opposite side of the same coin.

    I think we would all like a few language features (non breaking please), and a modern IDE (pretty please), anyone who spends a lot of time using a tool appreciates quality. Perhaps we will get a VSTA type editor that works with existing VBA code? in Excel 14??

    Here are my defs:
    a. Professional dev – paid to write code
    b. Professional business dev – paid to solve business problems often using code.(I solved one once by getting the prospective customer to speak to their data providers to change the format of submitted data -problem solved, zero code (zero fee too sadly)).

    a. get to refactor and polish their code because they work to SLAs and pad their estimates, and deal with projects in weeks/months.

    b. work under ultra pressure to solve tactical issues in timescales of hours/days/weeks, pressure to deliver ASAP often leaves a mess, the business is prepared to bear this cost/risk in return for early delivery. business never see any value in tidying up.

    I think Tom describes the VBA sweet spot best – the intersection of bus proc and programming.
    I too love new stuff I have dabbled in tons of different stuff within business and software and outside. I don’t like pointless breaking changes and the waste they represent, at all! (anyone think of an example?? ;-)

    Jon I didn’t see the 2k change as notable, but I guess you are right it did tidy a few things up, and gave us COM add-ins.

  7. Harlan Grove Says:

    You way, way, way underestimate Lotus 123 macros with the line ‘Prior to VBA many orgs has dipped a toe in the macro waters with XLM and Lotus macros.’ In some companies, like the one I work for now (about 8.5 years) and the two I worked for before that, there were at least hundreds of thousands of lines of 123 macro code and thousands of custom menus and dialogs in widespread use. Where I work now, 123 models were in DAILY use up to late 2005. I was involved in the transition to Excel, and there were some things that had to be abandoned because even VBA can’t do some things 123 macros could (there’s no close VBA or XLM equivalents for 123’s {?} or {look} macro statements).

    Remember, 123 helped launch the PC revolution, not Excel.

  8. Charles Says:

    Simon,

    One point you missed is performance: currently going through Interop is a killer if performance matters.

    I suspect that most .NET/VSTO developers dont know how to use Excel properly (after all it takes a long time to develop good Excel skills), but on the other hand most Excel/VBA developers dont know how to use .NET at all, so there tends to be a high level of incomprehension in any dialogue. Another consequence of this incomprehension is that people feel justified in using the technology they know.

    I am sure its been said before, but if the requirement is to build a standalone app that happens to use a bit of Excel then .Net is a better solution, but if the requirement is to build an additional tool/capability into Excel then either C or VBA is usually a better option at the moment.

    I don’t see this situation changing except where there is some compelling reason (excel Server/Sharepoint/discontinuance of VBA/ better performance and integration from .net with Excel …)

    History has shown us that useful business applications frequently have a lifetime measured in decades rather than years.

  9. Harlan Grove Says:

    Now that I’ve read Simon’s whole article and the responses, let me repeat themes others have mentioned. First, VBA is a scripting language, VSTO isn’t even it it’s claimed to be. Next, there’s a big difference between models or applications that are calculation-intensive with some database connectivity and/or file I/O requirements and applications in which customized UIs are a major share of the development effort.

    Spreadsheets specifically and uniquely among ‘productivity’ software are already programming languages by virtue of their formulas. VBA supplements Excel’s inherent functionality in a manner not too different from how classic macros supplemented 123’s. VBA as a structured programming language (with some OO syntax) and the VBE are it’s biggest strengths relative to other scriptable spreadsheets. And it’s built-in, which makes it much handier for deployment AND for coding precisely because it’s possible to record macros. If you’re unsure of the value of that, lurk in the microsoft.public.excel.programming newsgroup and see all the postings by .Net programmers who know next to nothing about the Excel OM so are unable to use it effectively.

    That said, Johan’s perspective probably represents the future. From what I’ve seen, very few under 30s are going to be able to figure out existing spreadsheet models, and none of them are likely to extend the art. Excel on client machines is becoming a grid control or a document preparation component. The serious stuff is moving slowly but surely to servers. I expect spreadsheet application development as I know it now to become extinct in 15 years. Some client spreadsheet models may live on for years after that as some Clipper and FoxPro apps still live on today, but the species is doomed.

  10. Biggus Dickus Says:

    Harlan:

    ” expect spreadsheet application development as I know it now to become extinct in 15 years. Some client spreadsheet models may live on for years after that as some Clipper and FoxPro apps still live on today, but the species is doomed.”

    I very respectfully disagree.

    Not only is this unlikely, but it doesn’t make sense to go that route. there will ALWAYS be a need for the functionality of a spreadsheet. Before VisiCalc you used paper ledgers (I know I did ;-) and there can be no “After Spreadsheets”. How is that possible?

    There is always going to be GREAT value to having an “electronic ledger” to do free-form or ad-hoc calculations and presentation. Add the capability to connect to the external “data” structures and this technology will ALWAYS be valuable. This technology will get better and there’s no reason why the work people do in them won’t get better and better as skills get passed on from user to user.

    I think people like us here should take the attitude that “Spreadsheets are Forever !!” , because they MUST be forever. This is a HUGE opportunity for Microsoft as long as they don’t get bored (like Lotus Development Corp did) with the technology.

    Dick

  11. Simon Says:

    Harlan fair point on 123, that sentence didn’t quite work out the way I wanted. I have never used Lotus on Windows, so I don’t know what version my 123 experience ended 4? (the first one one that had : for formatting stuff)

    From what you are saying customisation/development whatever is important for 123 users too. Is it a spreadsheet thing then? and if so where does that leave OOo calc?, which much as I love it, I don’t think is strong in ease of programmability.

    I’m with Dick, I don’t see the spreadsheet/integrated coding application going away any time soon. At the very least it will continues as the bleeding edge prototyping tool to allow business users to codify their logic. In the SOX world they may get migrated to something more robust sooner, but I think they will be around for a long time.

    I would love to see something that is better than spreadsheets at doing spreadsheet stuff, maybe http://www.resolversystems.com/ is it??

  12. Harlan Grove Says:

    I foresee SharePoint and Excel Services becoming the center of Excel development in the coming years (while I don’t like much of what Microsoft does, I believe they know what they’re doing pushing SharePoint and Excel Services as hard as they are). I also see SOX curtailing individual department spreadsheet development. In other words, I see trends towards recentralizing development (before it gets offshored to Asia).

    I do see a continuing role for spreadsheets, but precisely as electronic ledgers once again. That is, departmental spreadsheet applications dumbed down almost to the level they could be implemented in Works. I don’t like it, but that’s what the trends I see lead me to believe.

  13. Johan Nordberg Says:

    Charlies: When I first ready your comment “I suspect that most .NET/VSTO developers dont know how to use Excel properly (after all it takes a long time to develop good Excel skills), but on the other hand most Excel/VBA developers dont know how to use .NET at all” I got kind of pissed off. As bad as it is of me to sometimes dismiss VBA lovers programming skills, it’s as bad of you to dismis .net developers excel skills.

    I was like.. Hey.. WTF. What do yoiu know about my Excel skills? And I was all ready for battle. :)

    But then I realized that you’re more right than I would like you to be. We’ve been trying find more Excel devs for the place I work, but the biggest problem is that good excel nerds knows nothing about .net, think they know vba, but their code isn’t worth calling code. And since noone use VBA stand alone and .net devs all use asp.net, winforms and don’t care about Office it’s really hard to find the right people.

    The only guy I even know about in Sweden that would fit our dream description would be Dennis, but I think he has better things to do than work for us. :)

    Maybe it’s time to go back to web development. :)

  14. Simon Says:

    Johan I would estimate there are about 20 people in the world who know Excel VBA and .net, who are active on-line. and I don’t think you could get competent without being active on-line.

    Sadly employers havent registered yet this is a rare and valuable skill, hence I got undercut by some .net-only jockey who didn’t know Excel/VBA on a recent migration bid. That was a day rate though, so overall the client will pay more because they will be slower on the Excel/VBA comprehension.

    Good luck with the recruitment, at least an Excel/VBA dev might see some .net experience as a step up/forward, .net folks would probably see Excel/VBA as beneath them.

  15. Mike Staunton Says:

    For me, VBA is not even a scripting language – the UDF code that I write with for statements and loops could be written in Fortran – I’m not interested in automating spreadsheet moves and formulas, I’d far rather just do the calculations in VBA and return just the numerical answer (whether that’s an option value using a binomial tree with 262,000 time steps or a fast fourier transform with 2,000,000 points – admittedly the latter took 10 minutes but the answer was accurate to the eighth decimal place)

    And, Simon, you forget another key advantage of VBA over VSTA/VSTO whatever – it comes free with every copy of Excel

  16. Charles Says:

    Mike,

    slightly offtopic but have you looked at Ian Smith’s VBA stats functions, which seem to be both faster and more accurate than the native excel equivalents?
    http://members.aol.com/iandjmsmith/iansNApage.htm

    Johan,

    Yes, its likely that most VBA users have no professional programming training/skills at all, but if Simon is only out in his Excel+ .Net numbers by an order of magnitude or two then I would bet there are more Excel/VBA programmers out there who can write professional level code than .Net programmers with professional level Excel/VBA skills (but I am afraid that both numbers are small).

  17. Simon Says:

    Charles
    And even if this rare .net + Excel breed exists they won’t want to touch Excel with a barge pole. Silverlight (or Sharepoint??) only I reckon.
    here is what .net devs are interested in:
    http://www.developerday.co.uk/ddd/agendaddd6lineup.asp

  18. Johan Nordberg Says:

    Well. I like both Excel and .Net. So does Dennis. So we do exists.
    We’ve looked alot for talented Excel VBA devs with deep Excel knowledge. .Net hasn’t been a requirement. But the problems is that even though there are some people with good knowledge about Excel, they claim to know how to program VBA but 9/10 times that’s not the case.

    And even if they can solve a problem with code, it’s quite different to write code that a machine can understand and write code that a human can understand.

    I’ll probably be banned from this site by saying this, but I would say it’s alot easier to teach a good programmer Excel than it is for to teach an Excel Guru to write good code.

    And I also think that Office will be alot more interesting for professional developers if there are good development tools and languages. I think the .net story is the key to make office development as sexy as [fill in cool stuff here].

  19. Harlan Grove Says:

    Johan’s experience isn’t mine. It’s quite difficult to teach people who learned procedural programming first how to use functional programming languages, like spreadsheet formulas. The reverse has been less true, at least for those who understand how to develop spreadsheets well. That said, one thing spreadsheet developers often don’t understand is how to work on multiple developer projects – they’re often clueless about how changes they make could fubar other peoples code or formulas.

  20. Simon Says:

    Johan – not banned, but…

    I’d say attitude would be a key factor as much as technical background.

    An Excel guru already has 2 or 3 diverse skills – functional spreadsheet stuff, procedural VBA, and maybe BASIC style semi object orientation. (4 if you count the business/science/engineering skill they probably have).

    A .net dev will probably mainly have OO skills which will block them from using Excels powerful native functionality effectively, and drive their designs into convoluted contrived quasi OO style. As Harlan says they will probably have better project/lifecycle skills though.

    Also bear in mind the claims (by MS for example) that .net is easier and more productive than everything before. Excel/VBA is older, and apparently harder, so experts in that should have no bother transferring to .net. Those spoon fed .net devs will struggle with the harsh real world of 1990’s technology.

    So sorry I have to disagree again but I think cross training a decent Excel/VBA dev to code to your style would be easier than getting a .net dev comfortable with Excels massive object model. especially if they have no idea what range.sort means on a sheet.

    I hope you are right about tools etc encouraging more devs, but we’ve had VS based COM add-ins and automation exe projects since VS5 or 6 (or before?) and i don’t see mainstream devs queuing up.

  21. MacroMan Says:

    amen Simon.

  22. jonpeltier Says:

    @Simon – Range.Sort? Why not extract the range values into an array, throw QSort or something at it, and ddrop it back into the worksheet?

  23. Simon Says:

    @Jon – array? why not extract the value into a custom collection of hand written objects, perhaps from a handwritten class called CCell, Cell, or MyCell? Then implement a ‘sort’ method in your collection class…

    You think I’m being sarcastic (I am) but thats exactly what the last .net dev I worked with did. His code was beautiful (Johan would love it) but sadly all utterly pointless, reinventing the wheel. He even had UML class diagrams.

  24. Johan Nordberg Says:

    “utterly pointless, reinventing the wheel”. That’s exacly my point! You constantly reinvent the wheel with VBA to achieve basic stuff.

    I don’t mean reinventing stuff that’s already in Excel. I do agree 100% that knowing Excel as a program is the key for any Excel developer. And I do agree that many devs probably would create their own function to get the second largest value in a range, because they didn’t know it’s already builtin.

    If you Google some tricky problem, you very often come across a solution using Win32 api calls. This is far from simple if you don’t have background on Win32 programming which I don’t think many Excel devs have. I know I don’t.

    So what’s a dev to do? Copy the code and use it even though you don’t really understand it? That’s probably what most of us do, but it doesn’t feel right. Atleast not to me. In .net most of these thing is just there and part of the runtime.

    Well.. It’s kind of a pointless discussion. It’s like discussing osx vs. windows, nokia vs. sony ericsson. It’s not even about new vs. old.

    Maybe a better question would be: Do you wan’t Office to be an attractive development platform? If Yes, what do you think is the best way to attract new talents to Excel and Excel automation?

    Time will tell…

  25. Simon Says:

    Johan
    Google plus cut and paste is a very pragmatic approach, and still leaves you with trivial deployment. If the .net stuff was that accessible it might have taken off more for desktop dev.
    I’m gonna nick your last question for a post, its not tm’ed is it?

  26. Johan Nordberg Says:

    No, I was actually hoping that it would be a new post with fresh comments.

    We keep coming back to, and I think we agree too, that the deployment differences is one of the best things with VBA and one of the worst things with VSTO. Maybe that’s the key. Atleast one of the really big ones…

  27. Stephane Rodriguez Says:

    On reinventing the wheel, what are the odds that, should Microsoft have fixed their proprietary algorithms (dates, number formatting, …) in the file format to support ISO standards, that it would make it possible to work with .NET code ?

    That Microsoft refused to do so in Office 2007, chose to keep all the legacy stuff, chose to not do the heavy lifting that they owe to their customers, should be enough to understand the situation in which we are.

    Office 2009 is going to fix it? Probably not. It’s not in their DNA to do big things. See, Office only supports one platform (Windows), and even that is subject to discussion : lack of 64-bit, VBA development stopped so can’t be used on server products. And so on. Compare this with cross platforms alternatives such as OpenOffice : are not those guys doing the heavy lifting?
    What on earth makes it people buy Microsoft Office licenses?

  28. Simon Says:

    Stephane
    I see you got quoted in ZDNet:
    http://news.zdnet.co.uk/software/0,1000000121,39292894,00.htm

    re heavy lifting, even if they had done all the overloads so C# could cope with Excel optional params it would have been something.

    Office 2009 – I think the OOXML stuff will push it back to Office 2010.

    Licenses – fear, ignorance and habit

  29. jonpeltier Says:

    @Simon – No, I know what you mean. I was adding a little comic relief.

    @Johan – “You constantly reinvent the wheel with VBA to achieve basic stuff.” I don’t get this. How can I do something in VBA that requires a Windows API call? I use APIs all the time, and I don’t care if it doesn’t “feel right”. If it works, it does “feel right”. I already think that Excel isn’t a bad development platform (not having much experience with other platforms). You just sometimes need to rely on certain add-ins to enhance features. Add-ins like Word, and PowerPoint, and even the Windows API. (Okay, I’m joking about calling everything an add-in, but I treat them this way, because they are useful enhancements to projects I write.)

  30. Simon Says:

    Jon, I know – I was trying to continue it…

  31. Charles Says:

    Interesting thread on users of VSTO in Finance over at Wilmott
    http://www.wilmott.com/messageview.cfm?catid=10&threadid=59041

    does not seem like anyone is using it.

  32. Andy Foreman Says:

    I stumbled across this blog when doing research for a number of clients on VSTO vs VBA. Interesting discussions, but seems like most participants are programmer/developers and the debate seems to be way too techie-style when something vital is being missed.

    I consult for CPAs, Actuaries and Benefits experts. These are people who live and die by their spreadsheets (and some PowerPoint!) and are all pretty good at working with VBA code. To them, VSTO is thus far a complete and total nightmare to the point where I have been hired now to (#1) fully analyze VSTO, and (#2) find possible alternatives.

    Microsoft seems as blissfully unaware as ever that there is a rather large end-user segment that survives on NOT being programmers, but being sharp enough to learn and use VBA. To these folks VBA is a tool, nothing more. They dont want to have to get a Computer Science degree (which thus far it seems VSTO requires) or spend thousands to pay outside consultant to help them tweek their spreadsheets (or any other Office doc).

    Consider this – suppose you are a gardener. You plant flowers using your trowel and for years you have been happy, and business has been good and healthy. Now Microsoft comes along and says they have something new to replace your trowel. They then present you a backhoe. A backhoe is a complex, very powerful, with lots of features tool. But in this case, its the WORST tool to use. Ever try to dig a small shallow hole with a backhoe???

    You see the point. My clients are howling that MS does not get it – as usual – and they simply deliver massively complex, poorly documented, backhoes, where simplicity and elegance would do the best job. Its once again a case of needing Microsoft to deliver a skateboard, and you get the Space Shuttle.

    Ive spent the last two weeks trying to learn and work with VSTO. Its terrible!!! The documentation is lousy, the version confusion abysmal, and in the end (the typical MS coup d’grace) the Microsoft sample code does not even work!!!

    Next week I am meeting with 3 clients to answer the question “Should we invest the time and energy into VSTO, or seek alternatives?”

    At this point, I would have to tell them to avoid VSTO like the plague.

  33. Harlan Grove Says:

    Poor analogy, trowel vs backhoe.

    Better analogy would be people who use a pickup truck for their job and choose to maintain the truck’s engine themselves. VBA is a 1958 Ford engine for which you need a full set of socket wrenches and maybe a hammer. VSTO is a 2009 Ford engine for which you need a year’s pay to buy all the electronic diagnostic gear.

    If you use the additional stuff for the 2009 Ford, your engine will run more efficiently. But if your day job isn’t being a mechanic, is it worth the time & effort learning to use it?

    Actually this is where my analogy breaks down. VSTO solutions even when used well aren’t necessarily more efficient than pure VBA. And the analogy breaks down further because truck engines are a lot more standardized than the different .Net versions.

  34. Simon Says:

    Andy
    If you look around the blog you’ll see many of us share your view, and are not just focused on the tech.
    VSTO has its uses as does VBA. IMO VBA is generally more appropriate for people from a business or spreadsheet background. VSTO works well for all those masses of Visual studio devs targeting Office (sarcasm – there are roughly 6 worldwide.

    Microsoft has lost the plot on Office – Fact. But the question is what are you, me, us, our clients going to do about it? probably nothing – carry on as before.

    My basic tech advice would be to use a thin VBA layer to call out to Com enabled .net asemblies.

    Harlan, analogies rarely work perfectly, as you regularly point out.
    In the VBA/VSTO issue though, I don’t see VSTO as technically superior to VBA, just targeting a different (smaller)(minsicule in fact)) audience. Each tech is better than the other in certain areas.

  35. Data Manager Toolkit: Overview | Information in Schools Says:

    […] Don’t learn VBA […]

  36. What is VBA good for? … | Newton Excel Bach, not (just) an Excel Blog Says:

    […] of visitors here (thanks to a link posted by Charles Williams of FastExcel).  On a similar theme, Smurf on Spreadsheets has a post entitled “What’s so good about VBA?” that has just celebrated its fifth […]

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: