Whats so good about VBA 2

  • Autonomy
  • self determination
  • power
  • Freedom from the tyranny of IS departments
  • Trojan horse

Call it what you like, VBA gives the power to the people, without having to beg to the power crazed people in the IS department for permission to do your job.

I missed this one off the other list, Marcus reminded me in a comment.

I think this point is getting more and more important, as the IS department, full of SOX fuelled bravado, is moving in for the kill. They are desperate to boss the business, and are most likely seeking retribution for decades of brutal staff cuts.

I have another post in the pipeline about this battle, but for now I thinks fair to say, Excel/VBA is one of the key tools in the battle. I would say Access, Excel and VBA are some of the key targets of the job prevention department.

In a way I think we are lucky that Office itself is so dependant on VBA that Microsoft do not recommend fully disabling it via policy. If that barrier were to be lifted, perhaps by implementing sumif.xla etc as VSTO add-ins? then things might change.

You may have noticed that most of what I reckon is great about VBA has nothing to do with language structure. It’s more to do with external factors, many of which are outside the current direct control of MS.



10 Responses to “Whats so good about VBA 2”

  1. MacroMan Says:

    This is the biggie. I hope MS decision makers read this blog. As bright as you all are in MS, there is no way for any of you to know the politics between technology and business departments.

    As a business user within a non-technology department, in a financial company, I am NOT allowed to have Visual Studio on my PC. There are many financial analysts that use VBA. We use it for building simple macros to pricing models that value complex financial instruments. VBA is what makes Excel great.

  2. Marcus Says:

    You wont believe how far business units will go to avoid dealing with their own IT dept (or maybe you will).


    At one mortgage provider I developed an add-in to capture key business metrics (KPI’s) as it was ”descoped” (is that a word) from their official system. It was an Excel add-in using a dynamic menu (as per previous thread) with an Access back-end. We had around – wait for it – 130 users. However they we’re all concurrent – there’s was an average of 8 concurrent users: connect-execute query-disconnect.

    It took me 18 months to convince to business to migrate to SQL Server. When they asked their IT department for an estimate to migrate to a web interface with SQL Server back end, the business baulked at the price and project duration. They ended up retaining the Excel add-in interface and simply migrated the data to SQL Server. We also retained all the QueryDefs in Access, as the IT dev team quoted 3 months to convert them all to Stored Procedures. So the add-in connected to the mde and the mde linked through to SQL Server.

    In another case (another financial institution) the business unit had a team of about 10 people whose primary role was to generate reports in Excel using data extracted from SQL Server and the GL (Hyperion). Most of this entailed – you guessed it – lots of copying and pasting. There were some monster, 150 mb spreadsheets. Rather than centralising the data (text files, Access, whatever) and creating Pivot Tables from external data, great wads of data was copied into each Excel report generated.

    The IT dept offered to build a reporting solution at a cost of two years salary of 10 copy-and-pasters. The business declined. One reason was budgetary – they preferred to pay 10 salaries out of their operational budget (OpEx) rather than request a capital budget for the project. It also means their OpEx budget would have been reduced post project. The other reason was distrust for the IT dept as a result of prior botched projects. The third reason was a sense control over the process. (The “process” was the ever prevalent ‘wet finger in the wind’ approach).

    In a business where MS Access was officially verboten – there were several hundred of them scattered across the network. I also counted over 7,500 spreadsheets.

    You’re on to something MacroMan. For all it’s inherent benefits, .Net solutions still enforce a dividing line between business and IT. If MS are unable to provide a technology base which allows the business to retain it’s sense of control over destiny, many businesses will seek a vendor who will.

    Cheers – Marcus

  3. dermot Says:

    Amen! This is indeed the biggie, I believe.

    I worked in this kind of IT regime for 15 years, and without VBA, I would have got nothing done.

  4. Ross Says:

    What if we’re wrong!!!!

    What if, instead of getting VSTO/VS/VSTA banned, IT deparmewnts turned round and said, ok, if you can pass this test, we’ll give it to you to use?

    That just might happen you know! Business’s aren’t run by IT departments! – I think :-)

  5. Simon Says:

    I’ve worked at places where getting dev tools was not possible because I worked in the wrong department. Experience, ability, job requirements, all irrelevant whilst any numpty from IS can get anything they can spell.

    And I’ve worked places where anyone could have VS just for the effort of filling in the form and accepting the cross charge.

    I’ll post more about why I doubt many IS depts will not do that (think job security).

  6. Marcus Says:

    There’s a scene in the movie My Big Fat Greek Wedding, where the daughter complains to her mother and her father’s proclamation that he is the head of the family. Yes, replies the mother, but the wife is the neck and she can turn the head whichever way she wants.

    This sort of represents the relationship between the business unit and the IT department, except each one thinks that the other is the head. Each thinks the other is the cause of the problems whereas in reality the business couldn’t function in a modern world without the IT dept and the IT dept wouldn’t exist to begin with if it wasn’t for the business.

    My experience has been more akin to Simon’s (most of my dev background has been working in the business rather than the IT dept). Their attitude to giving this business developer the keys to visual studio verged on letting the kids play with a chain saw when they can barely handle scissors. The only time co-operation was forthcoming was for those covert, “under the radar” projects.

    Side note: I’ll need to recant the comment about the 150 mb spreadsheet monsters. I’ve just been given the task of migrating a process to SQL Server which includes a reporting spreadsheet weighing in at 262 mb.

    Regards – Marcus

  7. Biggus Dickus Says:

    “Call it what you like, VBA gives the power to the people, without having to beg to the power crazed people in the IS department for permission to do your job.”

    Oh yes !! That says it all. Of course it varies form company to company but that is a fact and the bigger the coporation the worse it gets.

    After working for BIG corporations for 20+ years I am seriously considering moving down into the medium and small (say it ain’t so!!) biz markets in order to survive. In the past I used to be able to fly under the radar but now Corporate IT has brought in the lawyers and the SOX Nazis and have designed a radar that goes right down to the ground ;-) …..

    I have an endless stream of examples as others have listed. It is getting harder and harder to do things because:

    1. on one hand IT adds so much wasted time to the process and
    2. on the other hand they see what we do as “lower” than them, so they don’t appreciate the complexity of the business process that we must migrate through and expect everything we do to be a “quick and dirty” and therefore cheap… That’s the biggest threat – devaluing our work to the point where it isn’t worth our while to get the business.


  8. MacroMan Says:

    One simple reason why IT departments don’t want business departments to have access to VS and other IT tools: job security. If they allowed other departments to have these tools then there will be less demand for the IT structure. Other departments will start to hire IT people themselves. There will always be a wall between IT and business. This is fine, I’m sure the trading desk would not want IT people making trades.

    Marcus you’re correct. I see users using alternative spreadsheet apps that comes with their own scripting language, if future Excel versions don’t come with VBA.

  9. Charles Says:

    I was asked by a manager at one of my customers if I could help automate a process. Turned out to need Lotus Notes development (thats what the customer was using), which is not in my skillset so I could not help.

    He decided to do it with the IT department.

    2 years later I was discussing the developement of another Excel-RDB based tool with him, and I asked how the Lotus Notes project was going: “We are still working with IT on developing the spec” he replied.

  10. Harlan Grove Says:

    Job security isn’t quite it. Succession planning is one big point in the IT/IS department’s favor. While a non-IT/IS department might have someone who can write VSTO, it’s very unlikely they’d have two. So if that one person gets hit by the proverbial bus or takes a better job elsewhere, then what?

    Maybe one person in a department is a lot better at model building than the others in that department, but as long as there are many who can understand what the one person does, there’s no succession risk – the risk of having to ask IT/IS for help maintaining departmental applications.

    IT/IS departments buy Microsoft software, not individual departments. So who is Microsoft going to try to impress? Whose job security is Microsoft going to try to bolster? Whose needs is Microsoft going to take into account when designing their software ‘stack’?

    We’re business developers, so we should know this simple rule: FOLLOW THE MONEY.

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 )

Connecting to %s

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

%d bloggers like this: