[sanitycheck]

I was talking about this with a friend the other week... I think what IT depts really need to do is let people go crazy with Excel/VBA, but write a script to monitor activity on xls files on the network over the long term.

If there's an xls which has been in regular use for more than 18 months, and it contains macros, then it can be assumed it performs some important role and should be properly documented and checked and could also be rewritten in a "real" language and officially supported. Set up a meeting with whoever made it, and whoever's touched it most. Approach it more like "we're improving your cool thing" than "we're taking away your toys".

[civilized]

No one at my company would ever let IT take over their Excel/VBA processes.

The moment IT touches your stuff, your job transforms from solving problems to writing emails and having meetings.

Any change, no matter how trivial, takes dozens of emails, dozens of meetings, and half a year to orchestrate.

If IT wants to help solve more business problems, it needs to fundamentally change its self-concept and purpose away from "prevent hypothetical bad things from happening at all costs" and move it towards "solve more business problems".

[analog31]

This is vastly underestimated. The difference between coding and managing coding.

[civilized]

Working with IT isn't even managing coding. Management implies power to hold someone accountable, while working with IT an extended exercise in nagging and supplicating an organization which is completely unresponsive and unaccountable in its outcomes and methods.

You might as well become an immigration lawyer and spend all day begging the government to explain why your latest M-10582-9DJVA-V isn't being processed in the normal time frame, even though it was stamped in triplicate and sent by Certified Mail with a full-color copy of every identification document you own.

The best way to deal with IT is to avoid depending on it ever in the slightest way. If you give it an inch, it will take a mile.

[gremlinunderway]

You're actually on to a really important thing that IT depts misunderstand about Excel/VBA monstrosities.

They exist because they work. You want UX or business analysis? You literally just got that done for you for free if you run into a Excel/VBA application. The hardest part of dev is figuring out requirements, so stop looking at these as toys and start realizing that shadow IT exists because of a gap in development. Full stop. You can argue all day long that you're working your assess off, and you do great products, but the existence of these apps is empirical proof that IT has missed the boat on developing something of importance.

Use that.

[lelanthran]

It might be sobering for "real" devs to find out that more "real apps written in real languages" go unused than apps in VBA.

[pjacotg]

Completely agree. I've always thought that Excel/VBA shows you where all the short comings are with your in house systems.

[arethuza]

"let people go crazy with Excel/VBA"

Many years ago a company I worked for used to send out a spreadsheet to its suppliers which they would complete with the products they offered and then when it was received back there was a button in the spreadsheet that would automatically upload the data to a central database.

When I first saw this I was curious how it worked and did a bit of investigation - turns out there was VBA behind the button that established the database connection and uploaded the data. What was amusing was that the user had hardcoded the database connection string including username and password. Of course this wouldn't work outside of the firewall - but I'd be careful about letting people get too crazy.

[bjornasm]

I have this great new product called "DELETE FROM products WHERE provider != 'mycompany'"

[xen2xen1]

Little Bobby tables!

[sancarn]

To be honest, not hard coding a connection string is quite tough too. It really isn't an easy problem to solve. And especially when every piece of software out there connects to data in different ways.

The reality of the situation is with proper IT support, there could be compiled Excel Addins which provide API connections to core systems such that proper authentication also takes place. But that requires a first step by IT. Either that or authentication via a web server to get a temporary connection string. Either way, it requires prior infrastructure.

[jimbokun]

Or, you know, just ask people what they're using Excel for, and seeing if there's a way you can help them improve on it.