More VBA wonderings

Lots of parents are quite strict about their kids diet. Many for example try to stop their kids having sweets. Imagine if you had some fairly strict rules that you considered were for the good of your kids. Then imagine that the teachers at your kids school were totally undermining your rules by handing out sugary lollies each time kids got a question right in class.

How would you feel? would go to the school and try and resolve the issue? would you move your kids to a school that respected your views more? bill them for your kids dental fees? threaten to sue them maybe?

[All rhetorical – don’t answer – its a spreadsheet blog, not a parenting one!]

Ok, now imagine that you are head of IT/IS at some big company. You are concerned about unskilled users running amok with your system resources, leaking confidential data, deleting critical data and generally causing pain network wide. You set a policy that says only users with relevant training and/or experience can access developer tools, which you classify as Visual studio, SQL server etc. That instantly gets you a big SOX tick-in-a-box.

How would you feel if you found out that every nodder in every department has access to a pretty much fully featured programming environment, that gives them full access to all resources on the network and full access to the Windows API allowing them to do anything they want within the rights of their login credentials? (which they would happily swap for a small piece of chocolate) . And via the gift of the internet, they should be able to find the free code snippets to do it? Bang goes your ‘defense in depth’ strategy. Thats a big SOX cross-in-a-box.

Would you want software policies to lock out those untrained users? would you look at removing that whole chunk of functionality? would you moan at the vendor? Change product? Threaten to sue them maybe?

Now I think VBA is superb, it really hits the sweet spot in ease of learning and rapid productivity, and ease of deployment. And I am totally convinced it has been a major factor in the ongoing success of MS Office. But I can certainly see this other side, can you? Not in all organisations for sure, but I could imagine a SOX world with rigorous VBA control (/removal). I think VBA will be in MS Office for many many years, although I wouldn’t be surprised if it become easier to disable/not install across an enterprise.

I can see how, as increased security threats force tighter and tighter lock downs, VBA could come to represent one freedom too far – like taking a bottle of water on a plane. Or driving a car with no seatbelt. I’m not talking next year, or next MS Office, but after a couple more major scandals, and a few years of debate… 

If you were a vendor getting beaten up because your product, whilst being loved by the users, was seen as a security hole by the IT department, what would you do?

I think I’d like to be able to say, ‘We no longer recommend that product (VBA) for organisations like yours, we have built security in from scratch in this product here. We recommend you move ASAP to VSTO’.

It seems to me that MS (and some of us and our custom solutions)  are caught in the cross fire in an internal domestic dispute between IT and the business. Anyone else ever get that feeling?

I think VSTO represents MS’s ‘Get out of jail free’ card. If people adopt it thats a bonus, but just it’s presence as an alternative probably protects MS from possible legal threats. I really don’t see VSTO as compelling at the moment, but in 3-5 years, and with MSOffice V next or next +1 , I think it will be the tool of choice. But that is utterly dependent on VSTO targeting the majority of MS Office installations. That means either most organisations will need to be running 2007 or newer, or that VSTO functionality is back ported (well enough) to the versions enterprises are actually using.

And when I say VSTO, I mean a slick VS .net based approach to interacting with the Excel object model, it might be called VSTA by then, or more likely some more catchy marketing bs name.

My plan is to start (re) looking at VSTO around 2009 (thats only a year and a half away), what about you? do you see VSTO as the future? do you think VBA has still got legs? Do you think it will have an impact on the market for (independent) Excel development services? Will you do or C#, or some other? Do you even think it will impact where you work?

And the big question: Assuming VSTO+MSO14 is ‘the one’, will that drive people to upgrade their MS Office to 14, or will it encourage them to stick on their current version of Office and use VBA over VSTO? What if Office 15 is the one?

And the other big question: would you support an alternative to VBA (maybe in addition to ‘classic’ VBA) that was sandboxed within that workbook, like Access macros in 2007, they can run on any security setting because they have no ‘dangerous’ features. This would do UDFs say but not enumerate files in a folder etc. Any use? Maybe based on XLM?



10 Responses to “More VBA wonderings”

  1. Harlan Grove Says:

    Everyone with a Windows box has CMD.EXE, and they very likely have Windows Script Host. Windows boxes without Office have tools every bit as dangerous as VBA.

    Access macros are cookie cutter processing blocks that make sense for Access. What would be comparable in Excel? Would security concerns be addressed sufficiently by being able to turn off Windows API calls, OLE automation or file system access separately?

  2. Charles Says:

    I plan to start developing in Office VSTO/VSTA (or whatever) whenever it more-or-less matches Office VBA in facilities/performance/cost/deployment etc.
    I dont know when that will be: Office 14/15/16 …
    My working assumption is that Office VBA will be useable and maintainable for about 10 years after that date.
    If it happens a lot sooner than that someone will make a lot of money rewriting the 100 KLOC or so of VBA code I have written which is currently in daily use.

  3. Simon Says:

    I can’t remember the last clients where I could get a DOS console. Not without a ton of form filling anyway. And Its a challenge to get local drive access too.
    I suppose I was thinking of UDFs, print macros, setting views, hiding/unhiding sheets, pasting values, etc all within the wb with the code therefore sandboxed.
    Charles, fair point, and there also need to be a market for VSTO skills/solutions.

  4. Dennis Wallentin Says:


    >>I plan to start developing in Office VSTO/VSTA (or whatever) whenever >>it more-or-less matches Office VBA in >>facilities/performance/cost/deployment etc.

    The truth is that it will never happen. ‘Whatever’ will be based on .NET platform which is, compared with VBA, so much more in all terms. I hope that MSFT realize the real issues with the new technologies.

    Kind regards,

  5. Harlan Grove Says:

    Simon: are the in-house users as locked down as outside consultants? Anyway, try holding down the Windows key (between the left [Ctrl] and [Alt] keys) and pressing R. If that brings up the Run dialog, try entering cmd. There are MANY holes in Windows. Specifically denying Read & Execute permission for CMD.EXE is necessary to prevent its use, and I’ve never seen any business PCs with that particular permission/security setting. Deleting console icons and not displaying the Start menu’s Run… command are all I’ve seen, and that’s easily bypassed.

    While I wasn’t explicit, I meant udfs and simple macros that use no outside facilities/objects. Loss of any SIMPLE facility in which to write udfs would eliminate one of the main advantages Excel has over OpenOffice for me. The other is Excel’s better array formula semantics and array constants.

  6. Simon Says:

    Harlan – everyone is locked down, most people can’t get the VBA editor (at many of my clients) (not easily anyway ;-)).
    Even calling shell.exe from vba to a get a prompt has been disabled at some places.
    some places have prnt screen disbled too, which makes screen shot documentation a challenge!
    You are right though there is normally a way round.
    More and more are disabling USB ports too, which is a major PITA. (have to email an external account and pick it up on my machine!)(or zip it across 20 floppies!)

  7. VBx - the future VBA? « Gobán Saor Says:

    […] 2nd, 2007 by gobansaor With the future of VBA being a concern for many Office professionals, some of the  MIX07 announcements around dynamic language support in Silverlight may shed some […]

  8. Marcus Says:

    “are the in-house users as locked down as outside consultants”

    Yes. I can’t recall the last time I could get access to the internet, command prompt or C:\ drive without filing a request to IT services.

    “There are MANY holes in Windows”

    Good point, Harlan. Right cick the Start button and select Explore and I’ve got direct access to the C:\ drive without IT approval.

    “Locked Down USB”

    Definitely a PITA. Also no one has access to external email services such as Hot Mail or Gmail.

    “do you think VBA has still got legs””

    Yup. I’m still working on migrating an Excel / Access based solution to a production environment. It has over 40K LoC and 800 queries. It is being migrated for regulatory compliance purposes. This is just one of thousands of these projects floating around. It would be an enormous effort for a large organisation to migrate these to alternate platforms. I’d wager that most of these organisation’s IT departments have no idea how pervasive these solutions are in the business and how dependant the business is upon them.

    I believe a lot of my work exists as the business doesn’t want to deal with their own IT department. Any environment, such as VSTO, which removes the ease at which business users can steer their own course has got a tough sell on its hands.

    In some respects I see the way the play is unfolding as an opportunity: Less people are entering this domain. Each year natural attrition removes some players. Does this mean there will be more work for less people with the natural reflection in rates? Beyond that though, do you want to be doing VBA maintenance work until you retire?

    Cheers – Marcus

  9. Biggus Dickus Says:


    “I believe a lot of my work exists as the business doesn’t want to deal with their own IT department. Any environment, such as VSTO, which removes the ease at which business users can steer their own course has got a tough sell on its hands.”

    That’s my ONLY concern with VSTO. VSTO is for “REAL” developers and as such it is completely counter to the Office dev story (primarily Excel and Access). It’s a great product but if it means I have to constantly interact with Corporate IT to get the smallest changes deployed then it’s over for Office development.

    “In some respects I see the way the play is unfolding as an opportunity: Less people are entering this domain. Each year natural attrition removes some players. Does this mean there will be more work for less people with the natural reflection in rates? Beyond that though, do you want to be doing VBA maintenance work until you retire?””

    Sobering isn’t it? Sad but likely true.

  10. Yuhong Bao Says:

    VSTO existed since Office 2003.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: