Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why do people still use VBA?

sancarn.github.io

Post reply on HN
Print view View original

371–380 of 421 posts

Re: Why do people still use VBA?

#371

Post by emj »

clausok wrote: post #338

I've been surprised to see many pro devs using Excel/VBA as a secondary tool. One example: a couple years ago I was working with a big hedge fund and one of their data analysts sent me an Excel model he had built and I was tickled to see the .xlsm extension (i.e., VBA code on board). "Ahh ha", I thought, "Let's see what these macro-recording cowboys have been up to." There was a lot of VBA inside, all written by this…

Well I agree that excel is a superb interface for many things and it helps people to understand data, to a certain degree. On the flip side; they are accustomed to the data model and when things get a bit complicated they tend to not ask questions, perhaps blaming themselves. There are things like this 3GB Excel/VBA pension forecast model from Sweden, with an 38 page user manual as well. Which does not really use Excel that well: https://www.pensionsmyndigheten.se/statistik-och-rapporter/p...

Re: Why do people still use VBA?

#372

Post by dalyons »

usrbinbash wrote: post #353
dalyons wrote: post #316

Earlier quoted context omitted.

The real answer is "ok, that is a bad idea for XYZ reasons, what problem are you trying to solve? is there another way we can help you solve for it? Maybe a cheap replica would work for you?" And look, i have nothing to go off but the justifications and choice of words in your replies. But in my experience this attitude of "high priest protecting the gates of production from barbarians(company staff)" is strongly cor…

> The real answer is "ok, that is a bad idea for XYZ reasons, what problem are you trying to solve? That's the answer that I give immediately after the "No." Look, I get what you are saying. I am not trying to keep people away from the capabilities they need to improve how the whole show works. The problem is, what people in my business "guard" are often complex, critical systems, which themselves don't always meet t…

Thanks for responding. You sound like you're on the right side of things - enabling change and innovation when its sane and possible. Sorry for assuming the otherwise from your previous replies.

Re: Why do people still use VBA?

#373

Post by jojobas »

ethbr1 wrote: post #176
jojobas wrote: post #153

Earlier quoted context omitted.

It's not so much central planners deciding who can have what, but rather a natural monopoly. You don't want your water/electricity depend on a family-run shop that can just shut down, and neither you want your purchasing department to hinge on that guy from logistics who can just quit, leaving behind his magic incomprehensible spreadsheets.

We decided that the water/electricity utility doesn't get to control how you use the amount you consume. Modern IT is more like if your water utility had final say over which faucet you installed and how you used it.

You can use as much water/power as the pipes/wires allow through, but you don't get to have petrol pipes, beer pipes or milkshake pipes laid to your property, neither you get to choose 160V DC or 430V 400Hz electricity, however useful all of these things are.

Re: Why do people still use VBA?

#374

Post by username135 »

throwaway154 wrote: post #2

Corps have a dev environment sitting right in Excel that doesn't need special (management, management's management, adding to a registrar of projects, budgeting or project manager assigned, etc) approval for non-stock software. The stack's Excel, plus Sharepoint if you're really looking for a networked data store that also has a web interface. From that end-user direction, solutions emerge. And they're in VBA.

SharePoint lists and tables work very well with Access and Excel. Linking them all together is trivial but appears to others as if youve created a magic kingdom of data. Ive gotten far in my career using these oft laughed at tools.

Re: Why do people still use VBA?

#375

Post by c0nsumer »

jimbokun wrote: post #351
c0nsumer wrote: post #283

Earlier quoted context omitted.

That's a great way to go, but unfortunately the reality is often the folks go off and write stuff in the non-supported way. The business gets dependent on it, and then years later -- because IT is there to support the company overall -- IT gets stuck supporting it even if long ago they said they wouldn't. Sure, you can have an internal political fight, but it only goes so far when everyone there is supposed to be wor…

True, but that's kind of like Twitter having to go back and rewrite their Ruby services in Scala down the line. Getting a viable Minimum Viable Product is all important. If a non-developer can hack that together in Excel + VBA, more power to them. Going back and rewriting it in a Proper Programming Language after the fact is an acceptable cost, once you have something solving an actual business need.

The issue I've most seen is that it never gets rewritten because the business side sees it as sufficient and just uses it. The techy/IT/programming folks never even hear about it until it breaks.

Most of these things are sheets which perform perfectly fine as-is, with their issues being around long-term maintenance. (Routine platform upgrade break the app, but the platform owners had no idea about the app until it broke for the users. The app didn't really even have an owner anymore because IT was never involved to assign it an IT owner and the author is long gone...)

Yes, it's the old-as-time problem of misaligned interests, but it's the reality in most corporate/enterprise IT and is a strong reason for prohibitions that may seem stubborn to devs.

Re: Why do people still use VBA?

#376

Post by xnx »

kqr wrote: post #85
wiseowise wrote: post #58

Earlier quoted context omitted.

I always read those “X automated their job, finishes it 15 minutes and then does whatever” and wonder how true are they? How could it be that nobody notices or cares?

I'm sure it does happen; there are a surprising number of duct-taping jobs where a person is hired to fill in a systemic/organisational/processual gap with manual labour. Those are often very good targets for automation. There are also the other stories we don't hear: One of my first jobs involved a very repetitive software task that got boring quickly. I spent four weeks trying to automate it, but eventually had to…

A familiar experience! https://xkcd.com/1319/

Re: Why do people still use VBA?

#377

Post by sancarn »

clausok wrote: post #338

I've been surprised to see many pro devs using Excel/VBA as a secondary tool. One example: a couple years ago I was working with a big hedge fund and one of their data analysts sent me an Excel model he had built and I was tickled to see the .xlsm extension (i.e., VBA code on board). "Ahh ha", I thought, "Let's see what these macro-recording cowboys have been up to." There was a lot of VBA inside, all written by this…

VBA does have it's issues (https://sancarn.github.io/vba-articles/issues-with-vba.html) but it's far from the worst tool out there... E.G. PowerAutomate

VB6 has a pretty big community, and https://twinbasic.com/ has really helped unify VBA and VB6 communities as of late. So it might have a little of a resergence in the dev community.

Re: Why do people still use VBA?

#378

Post by sancarn »

username135 wrote: post #374
throwaway154 wrote: post #2

Corps have a dev environment sitting right in Excel that doesn't need special (management, management's management, adding to a registrar of projects, budgeting or project manager assigned, etc) approval for non-stock software. The stack's Excel, plus Sharepoint if you're really looking for a networked data store that also has a web interface. From that end-user direction, solutions emerge. And they're in VBA.

SharePoint lists and tables work very well with Access and Excel. Linking them all together is trivial but appears to others as if youve created a magic kingdom of data. Ive gotten far in my career using these oft laughed at tools.

I do agree, but they do have their limitations. Can't update sharepoint lists from Excel if you have more than 5000 records, can't update sharepoint lists generally unless you use VBA hacks (or use REST API and somehow get VBA to authenticate). I'm not certain whether you can update in bulk using access honestly, I'd be interested in knowing though... Even using REST API has it's own limitations.

Generally I update them with client-ran JavaScript. I love sharepoint lists for their ease of use to users, but the limitations are pretty rubbish if ever you want to do anything programatically, unless you can figure out how to authenticate (and/or use a library which handles that for you).

Re: Why do people still use VBA?

#379

Post by clausok »

sancarn wrote: post #377
clausok wrote: post #338

I've been surprised to see many pro devs using Excel/VBA as a secondary tool. One example: a couple years ago I was working with a big hedge fund and one of their data analysts sent me an Excel model he had built and I was tickled to see the .xlsm extension (i.e., VBA code on board). "Ahh ha", I thought, "Let's see what these macro-recording cowboys have been up to." There was a lot of VBA inside, all written by this…

VBA does have it's issues ( https://sancarn.github.io/vba-articles/issues-with-vba.html ) but it's far from the worst tool out there... E.G. PowerAutomate VB6 has a pretty big community, and https://twinbasic.com/ has really helped unify VBA and VB6 communities as of late. So it might have a little of a resergence in the dev community.

I'm always stunned to see the level of VB6 expertise that remains in the world even two decades after Microsoft left it for dead.

Just look at the effort and knowhow that went into this VBA function that resolves the local file system path from the https url of workbooks synced to OneDrive/SharePoint:

https://gist.github.com/guwidoe/038398b6be1b16c458365716a921...

Re: Why do people still use VBA?

#380

Post by sancarn »

arethuza wrote: post #168
sanitycheck wrote: post #145

Earlier quoted context omitted.

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…

"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…

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.

Post reply on HN