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

331–340 of 421 posts

Re: Why do people still use VBA?

#331

Post by sonicanatidae »

wiseowise wrote: post #58
donatj wrote: post #50

Earlier quoted context omitted.

This. My friend automated his whole job in Excel. He supposedly can do a days work in fifteen minutes and then just hang out. Their computers are super locked down, can’t install anything, can’t go to any non-whitelisted sites, but they have Excel.

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 know one case where someone did something like this.

We both worked at a tox lab and there are masses of numbers to be reviewed. He strung together 8-10 steps to transform, massage, etc. the data for presentation to mgmt, accounting, etc.

What he found was that most of the time, it all ran fine, but when it didn't he had to spend some of that saved time troubleshooting an issue.

They also added more to his plate, since he no longer needed XX hours to accomplish the data push.

In the end, he was more clever than the last person, but didn't have the 7.75 hours of free time that's often touted.

It may exist, but it's rarer.

Re: Why do people still use VBA?

#332

Post by blincoln »

mcbishop wrote: post #318
blincoln wrote: post #304

Earlier quoted context omitted.

Counterpoint: VBA is an awful language, other than its access to/control of Excel, Word, etc. It's full of bizarre quirks, like control characters in code that are localized.[1][2] Want your code to run on non-English installations? Better dynamically build all of the strings that are passed to that type of function using placeholders like Application.International(xlDecimalSeparator), making your code much less read…

There are quirks in JavaScript too right? When I hit a VBA quirk, I write a rectifying function around that quirky functionality. I use the custom function going forward, and never deal with that quirk again. I agree that there are a lot of quirks, and the Excel object model is byzantine. Relying on vanilla VBA / object model isn't a good idea. But, with some investment, one can be very effective in VBA. The syntax i…

FWIW, I think JavaScript is an awful language as well, just for different reasons than I dislike VBA.

VBA in my experience has too many quirks that can't be wrapped in a less-quirky general purpose function. For example, I was just working in Word and was reminded that as soon as tables come into the mix, the order of text in the document is no longer linear in terms of numeric range values. E.g. text might have a greater numeric offset value in the document than text that visually appears after it, if the first text is inside a table. I've had Word VBA get confused about this, and extend a search loop outside the range I gave it to search within and start returning content in other parts of the document. Why would I trust a language like that for anything important?

MS should really have just gone forward with a .NET replacement, IMO. C# is one of the best things they've ever invented.

Re: Why do people still use VBA?

#333

Post by Twirrim »

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.

It's there, it works. VBA is a very accessible and straightforward language to code in and iterate with. No faffing about with installing external dependencies and library hell, no compilation phase.

It's really no surprise that VBA remains invaluable to businesses. I've worked with product managers that use VBA to perform absolutely jaw dropping levels of complicated business analysis, even in environments where they have access to other tools and languages, mature build processes etc, because it's the right tool for the job they have at hand.

Re: Why do people still use VBA?

#334

Post by jampekka »

specialist wrote: post #319
jampekka wrote: post #269

Earlier quoted context omitted.

Jupyter style notebooks are already becoming the next VBA in some fields. And this is not a good thing.

Why? The usual suspects? Lack of version control? Hard to deploy (reproducibly)?

Yes and yes. But the larger and more fundamental problems are the mixing of the program logic and the state and inability to make the code composable or modular. Problems in version control and deployment/reproducibility almost necessarily follow from these.

These are probably not impossible to solve for notebook-style, but there are not many efforts to solve them or they are not even acknowledged as problems.

Edit: There is Pluto for Julia that attempts to solve the state-problem. I have not used it in practice though; I've given up on Julia, in large part because Julia community tends to be even actively hostile towards "stateless" development.

Re: Why do people still use VBA?

#335

Post by theonlybutlet »

_boffin_ wrote: post #320
theonlybutlet wrote: post #290

Earlier quoted context omitted.

This is the interesting thing. I haven't played with it myself but seems MS is trying that with their new Beta python script function, but their implementation is just crazy, completely handicapped. Can't see it being a true replacement for VBA in excel.

I haven't investigated the python implementation at all since i no longer work with VBA and or Excel in that fashion anymore. How have they handicapped it?

The code is executed remotely on Microsoft's servers (I can see many organizations just turning this feature off for all staff). I'm open to correction, can't see it in any of the demos, but the code it seems is also entered within a cell, it's not clear whether the output can manipulate/overwrite pre-existing cells as a result or it needs to have its own separate output.

Re: Why do people still use VBA?

#336

Post by pillefitz »

usrbinbash wrote: post #202
f1shy wrote: post #187

Earlier quoted context omitted.

If IT people would only understand they are giving a service for the rest of the company… and not the way around.

> they are giving a service for the rest of the company That is very true. And part of that service is to ensure that things run smoothly, securely and according to industry standards. How well would an IT guy provide that service if he were to let some unvetted, undocumented script hacked together by someone who isn't a professional software engineer, run its merry way across the production database?

Don't give access to a DB, the same way you wouldn't give access to any other external system. Instead you ask what is needed and provide a restricted REST API.

You come off as condescending and remind me of why I (ex dev who joined our business department) dislike our IT so much and do my best to encourage shadow IT where I can, while keeping sane best practices around CI/CD, security and testing.

I'm so fed up seeing working Excel solutions cobbled together over 2 weeks, that served business well over years with 0 incidents, get replaced by shitty cloud apps that cost millions to build.

Re: Why do people still use VBA?

#337

Post by julian_t »

toolslive wrote: post #82
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.

I've seen the following at least twice: some department manager (marketeers typically have a nack for this) needs something, can't or won't bother the development team and starts off with "how difficult can it be" and before you know it they've written a few hundred lines of VBA, which serves their needs. But then, the next phase starts: that scripts gets copied over (because Jim wanted to run it too) and modified (J…

Oh yes, been there and seen that. Guys on a trading desk at Some Bank wanted an app, so the IT Dept said "fill in this form, so we can set an agenda for a meeting to discuss how we're going to approach defining the requirements..."

One of them had Excel, Access and played with VBA, and in a couple of weekends had come up with a monstrosity that did just what they wanted. It lasted for years as a major part of their work toolbox until someone wrote a proper app for them in C#.

Re: Why do people still use VBA?

#338

Post by clausok »

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 Caltech comp sci data analyst who was a Python superstar. The VBA was for pulling data from a database, putting it on a sheet, building some formulas, and some pretty formatting. There were even a few userforms!

I teased him, "VBA? What else are you guys using over there? A cotton gin and a steam shovel?"

I was startled to hear him heap praise upon Excel and VBA instead of the usual complaints.

He said something that stuck with me, "Excel makes it easy to understand the dependency structure that is implied by computations. If I had done this in Python, I'd be answering questions about it all day long."

Re: Why do people still use VBA?

#339

Post by mcbishop »

blincoln wrote: post #332
mcbishop wrote: post #318

Earlier quoted context omitted.

There are quirks in JavaScript too right? When I hit a VBA quirk, I write a rectifying function around that quirky functionality. I use the custom function going forward, and never deal with that quirk again. I agree that there are a lot of quirks, and the Excel object model is byzantine. Relying on vanilla VBA / object model isn't a good idea. But, with some investment, one can be very effective in VBA. The syntax i…

FWIW, I think JavaScript is an awful language as well, just for different reasons than I dislike VBA. VBA in my experience has too many quirks that can't be wrapped in a less-quirky general purpose function. For example, I was just working in Word and was reminded that as soon as tables come into the mix, the order of text in the document is no longer linear in terms of numeric range values. E.g. text might have a gr…

I hear you. Wrapping stuff in less-quirky functions... is a slippery slope to building a framework with a new object model (on top of the current object model). With that said, I love the hell out of VBA.

Re: Why do people still use VBA?

#340

Post by aksss »

BobaFloutist wrote: post #303
j33zusjuice wrote: post #156

Earlier quoted context omitted.

What you call monkey hammer is actually the “golden hammer,” or “law of the instrument.” Idk if that matters to you, but it’s an already defined thing. https://en.m.wikipedia.org/wiki/Law_of_the_instrument

I'm pretty sure they were referring to the established aphorism "When all you have is a hammer, everything looks like a nail."

I think it's one and the same, no? Your aphorism is literally cited in the first paragraph of the "Law of the instrument" linked above.
Post reply on HN