For pretty much as long as I can remember I have wondered why people in organisations persist in doing things in Excel and VBA that would be sooo much better in almost any other technology. We have discussed many of the reasons in the past but I got hit by a new one the other day:
The corporate process for requesting the right tools for the job is so long winded, painful and uncertain that no one bothers. Its quicker, easier and less pain to build an Excel monster instead.
To me this is a disaster, a dereliction of duty by IT, and just an all round FAIL.
IT should be proactively monitoring the markets and trialling interesting looking software and services with a view to proposing them to the business. Not sitting back, blocking all the interesting websites and file shares waiting to obstruct any other attempts to get the job done. (Hardly the work of a Strategic Business Partner is it?)
I have been through these processes a few times, some stuff like getting your Office install upgraded to Pro to get Access is normally a few clicks, an approval email and a cross charge. Getting approval to deploy some VSTO components and some C++ dlls at one place was a bit harder, including a half hour interview on the benefits and risks. I totally don’t mind needing to justify this stuff, but in many cases IT should already have proactively tested and approved it, imo. (or in the case of C++ understand they already have it all over)
I was looking at these processes for not so standard stuff at a few places that publish the detail, and sure enough it can be 6-8 weeks to get approval for an install. That’s ok if its bill and bobs virus ridden utility, but if its a part of what should be core infrastructure then you’ve already missed the boat.
So if you are in IT here are some tips
Developers need developer tools, they can and will develop without proper ones, but it wont be pretty – and it will be your fault for not providing the right tools.
Users need intelligent flexible access to complex data. If you don’t have the right tools, (Think OLAP/BI), then it is your fault when they do it badly in Excel.
just sayin…
What is the most long winded, laborious, misguided IT process you have had to go through? was it for software approval?
cheers
Simon
Smurf on Spreadsheets 
Wednesday, 29th June, 2011 at 4:15 pm |
I hear you man. There is a way to work this to your advantage though. Excel is a great prototyping tool. You get a front row seat with the people that actually make money for the company. My technique is to push things into C++ during this process. When you are getting the right numbers the IT guys come around and want that in their system, but they don’t want Excel. Now you can just hand them a C++ library they can link to and get identical results.
Be sure to charge them a lot of money for solving a problem they are now ready to pay up for.
BTW, the latest iteration of the tool I use for this is at http://nxll.codeplex.com. You can even use it to create purdy documentation now. The haircuts really seem to go for that sort of thing.
Wednesday, 29th June, 2011 at 5:06 pm |
Yup, been there. I’m (only) engaged by operating depts, to turn their Excel processes into a ‘centralised data model’ after demo’ing the idea. IT hate it because I become a hero and the users then want to know why IT hadn’t thought of it first :) Anyway, getting a SQL Server has often been the hurdle. Now I’m trying to push my VBA code patterns into VSTO Add-Ins I expect resistance much like Simon describes.
Wednesday, 29th June, 2011 at 5:54 pm |
Keith, sounds like a good approach, I keep getting dragged into VBA though which is a bit more of a dead end than C++. My last dev job only had Office, no VS, no local admin rights… kinda limiting.
Hiran I meant to say bin VSTO and go straight to ExcelDNA for wrapping your VB.net stuff. Or you could always look at Keiths excellent toolset. I’ll email yer.
Wednesday, 29th June, 2011 at 6:15 pm |
Bring in your laptop with VC++ 2010 Express and a USB stick. :-) You can do pretty much everything that VBA does if you know about the aptly named MACROFUN.HLP.
Wednesday, 29th June, 2011 at 7:13 pm |
I’m working on a project in VBA that has already, even before I started working on it, been slated to be done via the web. I guess they want it out there now so they’re having me work on it. It’s lots of fun even if it shouldn’t be done in Excel VBA.
This is my first consulting project too, which makes it even more fun. It’s amazing what Excel can do.
Wednesday, 29th June, 2011 at 8:23 pm |
Not sure I understand your point here Simon. People “persist” in using Excel because for many jobs it is simply the easiest and quickest tool to get something done. I can query an Access database or whip up some charts pretty easily. Plus the learning curve is relatively low. That’s exactly why (in spite of what IT wants) everyday users are still using Excel and Access.
What sort of tool would you suggest for these types of tasks?
Wednesday, 29th June, 2011 at 9:41 pm |
Simon, (thanks for tips, and I’ll be calling on you Keith)
I’m on JP on this. The reason why my clients go with the Excel application solution is because (1) IT (for years) never showed an interest (2) the workbooks already exist and the backend db makes a huge difference and is a small step from where they are already and (3) they can get it done without IT approval and (4) I’m just a fun guy! oh and (5) IT never find out until it is out there and BIG and too late to object to. IOW pretty much what JP said. Plus (6) they already see a successful working model before we even start – IT can’t do that without some dev. budget in uncharted territory that will take several months perhaps with significant risk of failure.
Thursday, 30th June, 2011 at 9:38 am |
I recently did a user needs survey about lots of IT stuff, but this came up, we classed it under the term “Small Apps”.
Why do people use Office solutions? It’s what they have and know, but also because IT supported solutions are too expensive and take too long to get written, and updated. This latter issue is just as important as the first one these days. For example I spoke with a number of IT directors who could not except that central IT would do a worst job, slower and at a higher cost than they could go out and get some PHP written to solve the same problem.
Ultimately what these “power users” want is central IT to manage the backups, and group policies and for them to be able to develop against this robust infrastructure. I think it’s getting better, SharePoint, and cloud should help…
But its bending the will of central IT… still which is a shame.
Thursday, 30th June, 2011 at 1:53 pm |
An high level manager told a group of us that IT is like a battleship. It takes three years to build (literally) and three miles to turn around. He didn’t know that there was an IT person in the group. She didn’t say a word, just blushed and looked down. As a VBA developer I would like to think I am like a seal team on a Zodiac boat, in and out very quickly. I can provide a solution in a couple of weeks and same day fixes. IT is not real fond of me but the users are and they sign my paycheck. To be fair if you crossing an ocean you want to do it in a battleship and the seal team needs the support provided by the battleship. The optimum solution is a good relationship between IT and the VBA developer.
Thursday, 30th June, 2011 at 2:25 pm |
JP my point is about all the things that Excel/VBA isn’t great at.
Thursday, 30th June, 2011 at 3:02 pm |
Simon and all, 6 months ago I started researching ExtJS javascript library as a possible avenue for rearchitecting (some) Excel VBA applications, the ones where I use ‘centralised data’. IT perceived ExtJS route as ‘not amateur like Excel is’.
http://www.sencha.com
However, IT will still be the gate-keeper. So a non-starter where I’m concerned. And a whole host of issues such as formulas (why re-invent the wheel?).
Saturday, 2nd July, 2011 at 1:01 pm |
I often find that the most tedious process in organisational IT is that of getting access to shared network folders that all too often contain the critical information you need to get done. In pretty much every organisation I’ve worked in you put a request through to the IT support team for access to a shared resource, which then gets bounced back to you as you have to email the ‘owner’ of the resource (usually some jumped up manager who wants to know every reason why you should have access to their drive). Once you’ve convinced the ‘owner’ you do need access they then have to email the IT helpdesk themselves and wait it out whilst it reaches the top of the long queue of calls.
It really does wind me up.
Matt
Wednesday, 6th July, 2011 at 11:09 am |
Absolutely Matt, it can be a big problem which is then compounded by people diverting this process so others can work on the data in the meantime. Whilst a form is sat in someone’s inbox others are busy bypassing the process and scattering copies of spreadsheets and data extracts across drives, directories and via email.
Wednesday, 6th July, 2011 at 11:12 am
Sorry, I’m jennifer. First time replying through wordpress and have changed my profile name by mistake. Hopefully this will appear correctly
Wednesday, 6th July, 2011 at 11:25 am
Hi Jennifer and Matt
I had exactly this issue recently. In the end I did most of the work under a test account that had the required access. whilst logging onto a different machine with my own credentials to check my email. The two accounts had so little common access I ended up saving stuff to a pen drive to share between the two.
Sunday, 10th July, 2011 at 9:27 pm
I’m doing a lot of business process / CRM stuff at the moment, and in a lot of cases I find that business critical data is held in spreadsheets, and/or process progress is documented by updating these (fill in the date when you have done the next stage, type of thing).
One thing more dangerous than a spreadsheet is a copy of a spreadsheet (as you say, sometimes deliberately emailed to get around some data access problem, or simply out of ignorance). You now have two or more “versions of reality”.
Just about the only thing worse than this is a printout of a spreadsheet which is out of date the moment it came off the printer but will be referred to for days to come instead of looking at the live one.
(wave to Jennifer – good to see you in York last month!)
Tuesday, 5th July, 2011 at 7:11 pm |
Where I work, the knowledgeable and noisy few can get access to databases . . . through intranet/browser applications. Once fetched, data must then be saved in Excel 5 XLS or CSV formats. Direct access to company databases from Excel? NEVER!!
As for other tools, there are now so many of us using R either on company machines (unapproved) or on our own machines at home (outside IT control) that IT has given up trying to prevent us doing so. Not for the faint-hearted, but occasionally rebellion against IT works.
Thursday, 7th July, 2011 at 6:24 am |
@Harlan,
I recently completed a project that uses a excel template as a front end to update the SAP database (Oracle)
It reads and writes to the database
But I really don’t see the point in allowing read access to Queries/Views if not to the tables directly
Thursday, 7th July, 2011 at 6:25 am |
OOps Typo
But I really don’t see the point in NOT allowing read access to Queries/Views if not to the tables directly