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.
Windows ships with VBScript, JScript, CMD, C#, and PowerShell right out of the box. I recall interviewing a college guy around 2018, and he tried to educate me about how Windows doesn't have a good command line / scripting / automation solution beyond command.com. I think I still said "hire" because he had other talents, but damn.
That's for sure not true. For my bigger projects (over 200 lines), I tend to use VSCode. Just having a look at the larger projects I've got up on github, I'm over 2000 lines across 3 projects - all developed in Code.
ISE does occasionally hang / crash, but it's quite rare compared to how VSCode behaves across every machine I've used it with. It really seems to be just a Powershell problem, haven't had the same issue in any other language.
When I'm really making great progress on something, having to fart around with killing and restarting the shell constantly is really disruptive. Yes, Code has better and more features, but for me the extra productivity does not overcome the crashy shell.
Nobody on the team wanted that, not even Snover, but we were making that thing in about 2005, when everyone was getting burned by email based zero days. Even though we had the Set-ExecutionPolicy thing there were boogeyman news articles immediately after the v1 release asking whether the new scripting language was...too powerful.
PowerShell with ISE is a lot better than the VBA editor in many ways but you're still in the same situation of using a long deprecated ide with an ancient version of a programming language (ISE is deprecated and if you're using the built in version of PowerShell you're stuck on the last legacy framework version from 7 years ago forever and missing a ton of improvements and fixes from newer versions of powershell)
So disappointed that MS hasn't rolled out PowerShell 7 but I guess it's easier to develop a programming language when you don't have to deal with users.
C:\Windows\Microsoft.NET\Framework64\ has both MSBuild.exe and csc.exe, but only for .NET Framework up to 4.0. I was under the impression that 4.8 was installed on Win 10 machines via Windows Update.
As I understand it, PowerShell allows you, out of the box, to write some C# code in a string, and then run it. And by C# code I mean regular classes with all the bells and whistles.
It makes the csc that comes with .NET available out of the box on pretty much any Windows system. I'm not sure how good it is at building serious programs, but it's good enough for little static void Main thingys. I doubt it's useful for the same demographic that would be using VBA, though.
JScript is deprecated, and is likely to be removed at some point too...
CMD is often blocked on many people's machines due to group policy.
PowerShell is really the only other option other than VBA, as discussed in the article. Only reason I haven't used PowerShell til now is the version was hidiously outdated and didn't even support classes... Of course with PowerShell you can evaluate C# code.
I don't understand how's Excel page with macros inside different from random exe file from security perspective? Does Excel have some kind of excellent sandbox implementation, so it's safe to run random macros on the work machine?
No, it's never safe to run random macros. Macros arguably used to be the biggest initial attack vector for threat actors. Maybe still are. Microsoft built a ton of mitigation around it (macros are only allowed in docs with special extensions, macros must be activated by the user, only signed macros will be activated, mark of the web, etc.).
It's not any different just untenable. IT departments tend to block all threat-vectors, with the exception of excel macros as even the most basic user use macros in their day to day job.
A better diffentiating factor would be who developed the macro. If it's built in house by someone merely using it to make their lives easier it's doubtful they inserted malicious code. I guess ideally IT should review the code.
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.
That was pretty much my first job, I strongly believe this still happens every day around the planet :)
I wouldn't want to go back to that 30kloc of VBA though!
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?
My understanding is he basically gets paid to put data into easily automated categories, and the company is soulless and has no ambition for automating anything.
I have a few colleagues that told me they have a job like that. Not done in 15 minutes but 2 hours, then they goof off for the next 6 hours.
There are two reasons:
1. They have a specific job with a specific set of duties (think sysadmins, or administrative duties) in a large company or in a state beurocracy.
2. They would rather go home or do something more but they are not permitted: they have metered time in the office and other people would and do shut them down on any initiatives.
To me, a workplace like that is like a kafkaesque nightmare but they seem to be fine with it, or rather, have accepted it. It lets them focus on other things in life outside of work.
> they seem to be fine with it, or rather, have accepted it.
i mean, i would imagine some people want to see purpose in their jobs, while others are just treating it as a job and whatever happens with the output of the job is of no consequence. And this is esp. true of gov't jobs, but by no means do the gov't have a monopoly on such inefficiencies.
But my opinion is that there's something systemic that is preventing these jobs from being competed on and efficiencies eked out.
Indeed, but there seems to be no incentive to do it. In government jobs nobody cares. In large companies nobody cares either, these are just operating costs. That is, until money is short, but then they either cut whole departments or sites.
The problem is actually in the work culture, where other coworkers would prevent another worker from becoming too efficient and proactive. So, nothing changes.
Since WFH became more common, it is easier than ever to automate anything that you have to reproduce. If my workload is light, I will often try to automate boring tasks so I can have more "free" time to expand my knowledge, refactor parts of codebases I find terrible to work with, or occasionally give myself some time to mentally rest (cook dinner early, watch something interesting on YouTube, browse HN, etc).
I started my career like this, with a boring job where I inherited a gigantic excel with a few macros. Every day I had to download via ftp millions of logs from high speed trains from all over france (the logs themselves were retrieved manually via a serial cable on each train by maintenance guys every few days). I would then run a few macros that would do a bunch of geoloc calculation, spit out results in 2 tables, one for "pretty sure results" and the other one with "not enough data", and spend the rest of the day looking a google earth screenshots and comparing lat/long and using my brain to do basic visual "puzzles".
I spent a few days improving the macros but I felt limited so I learned python in a few months and created a piece of software based on graph theory that would do almost everything I was doing looking at google earth and bam, job automated.
When I went to see my manager to ask for more to do, he saw the potential but let me sit on my ass a few month because I was a contractor and the job was done, and then pushed hard to get me formally hired to be trained and work in embedded C on high speed trains ! Life changing carrer move, would do it again.
Seeing the environment (SNCF contractor, I guess), kudos to your manager, they really went the extra mile with you and it's not what usually happens at all!
Yup, after 10 years I had to quit because of the too-low pay, but I am super grateful because I wouldn't be where I am today if they hadn't bet on me like that !
In one of my first jobs I was a contractor for the government. There was blatant corruption (or inefficiency, depending on how one sees it); I was employed full time, but the daily amount of actual work to perform, took around one hour.
Although I wasn't in the condition of automating the time required down to N minutes, I can see how this dynamic plays - essentially, BigCo with dysfunctional management, where efficiency doesn't really matter.
I've known plenty of devs who have managers who don't understand the effort required to do their job, and who have automated a lot of it, but they rarely goof off. If you're capable of that you're rarely the goofing off type. They've always been people who help others a lot, write high quality code, do things that are extra to their job (running guilds, sitting on steering groups, etc). Maybe I've been lucky.
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 declare failure[1] and then I had to explain to my boss why I was a month behind on my work that was due in a couple of weeks[2].
I imagine that for every "automated my job and now I can do it in 15 minutes" story there are 15 stories of "I automated my job and now I work just as hard maintaining the automation" and another 50 stories of the "I tried automating my job but failed" kind. Only the first one gets re-told.
[1]: Mainly due to hardware quirks I didn't have the experience and skill to work around.
[2]: This is not a story about how automating something is bad; it's a story about the bad decisions one makes when one is inexperienced!
The automation trap I keep seeming to hit is where I can only output garbage because the input is garbage. And people around me say "well it's obvious this user wrote their name incorrectly and you should have fixed it when you copied it", which would be fair if not for the fact the precludes a script just copying it for you.
I spent the first couple of years at my first job like this. Without going through much detail, back in 2006 I had to pull raw network performance data from some 7 elements every hour, then use a tool to convert to CSVs, then load into Excel, perform preliminary analysis, then email the Excel files to another team along with any alarming conclusions if any.
A few weeks into the job I completely automated these in python and all I had to do was turn my laptop on in the morning, then off in the evening, and I was done.
You'd be surprised. My wife had an accounting job, and when either of the other two people were out for the day, she had to cover. She could manually do their whole day worth of work in 45 minutes.
They were slower, but they also chit chatted with half the office, went to lunch, etc.
Their jobs consisted of pulling some data from here or there, entering it into excel, sending a few emails, entering some data into another system, printing some checks. All stuff that's easy to automate (you'd probably need more than Excel in this case)
100% true. Teams have team-sized work queues. Individuals with unique roles have individual work queues. Benefiting from a faster individual requires solving a similarly large coordination problem across the company for a smaller payoff.
In one of his memoirs, the science fiction author Arthur C. Clarke recalled his days as a young man working for the British bureaucracy (something to do with teacher pensions, as I recall). His particular job involved consolidating huge lists of figures into reports. He observed that the numbers in the reports were rounded to two significant figures, well within the accuracy of his slide rule, and started using the slide rule to do all his work.
He could finish his daily quota before lunch and take every afternoon off.
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 feels contradictory to talk about these super locked down environments when "lock down Excel macros" in my view comes first if you're trying to secure an environment. I deal with before dealing with local administrator access such is the prevalence of it being exploited.
I know a small business owner that says one of his top security threats is Microsoft Word and Microsoft Excel docs attached to emails that try to infect / phish credentials. He has fully disabled all macros on all regular employee computers. He said that it is a real battle. Sometimes I miss the good old days (15+ years ago) when the Internet was a less threatening place!
Plus Visual Basic is a very powerful language on its own. Given an environment such as Excel Macros, its power can be unleashed and utilized to a great extent and that's what power users in many enterprises do.
This is quite reminiscent of the good old "emacs operating system" paradigm just applied to a different context!
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 (Jane has a different VBA version) and expanded (now it does "THIS!" too).
Now it's a 1500 line kludge and they want to unload it, ie pass it over to development for maintenance.
But isn't that how it's supposed to work for these LOB type applications? Users prototype a solution and the developers then change it into a proper real world application. Alternative approaches are usually worse.
Look at it another way: that script kludge is a prototype, a dangerous one of course, that embodies the functional requirement better than what the user could express. Understand its deep meaning (what the user meant to do and and not what they settled on considering their technical limitations) and you are ready to rewrite it into a proper implementation. We frequently stumbled upon this situations and we like them, because a well used kludge that reaches its breaking point has buy-in from all stakeholders for a well-budgeted industrialization !
Yes. And the entire thing is done in half the time it would take for the development team's managers to get through whatever bullshit agile scrum epoch meetings to ultimately deny the request because it's not worth their time - or worse, approve the request, and get you waiting a year for The Project Done Right.
> Now it's a 1500 line kludge and they want to unload it, ie pass it over to development for maintenance.
... and THAT should be considered a GOOD THING!
It means you've got a tried and true business case for the application, the requirements capture has already been done, you've got an instant user-base and a very clear bar to jump over. Of course, the application must be able to outperform the old application in every way, or else questions will be raised.
I think it's important to point out that the inception of these excel VBA monstrosities is innocent and pragmatic. An SME has a job to do, they're doing their job, but have a need for a custom tool to help do their job.
It is ALMOST NEVER the case that they should drop what they're doing and engage a SW development team to go through a lengthy VERY expensive process with uncertain outcomes-- all the while still having to do their job. It's much more pragmatic, in many cases, to tackle the problem piece by piece, as need arises, with little spreadsheets, scripts and little databases.
I think complaining about VBA monstrosities is wrong-headed. They should be, in a way, embraced as a starting point for devs-- hopefully BEFORE they become mission-critical to the company, however.
You are absolutely right that the VBA prototype should be seen as a blessing. But no matter how you approach an IT development request - upfront or after the VBA prototype is created - the problem is always the same. IT wants a very, very long time to create something, or allow for the slightest change once created. And lots and lots of emails and meetings before any functionality even might become available (of course, complete failure is a very real possibility).
There is no way for the IT customer to negotiate this "correctly". It always leads to the same result.
The problem is IT exists to administer computer systems, not to help business people create or maintain software. This brings the wrong mentality and skillset.
What my old team did (at a major Fortune 100 no less) was a bit unconventional.
They embedded a technical developer into a business team, and had that individual write the "kludgy" business apps that needed quick automation for throwaway tasks or for data processing standup. The dev has access to more than VBA, specifically, Python, GitHub, the ability to spin up what amounts to VPS's in the cloud with access to all of the database infra. All tools are shared with the rest of the company through a tech sharing program that is being heavily promoted across teams, and of course hosted in a repo, often with docs or a website if possible.
This "fills the gap" of dev latency for small dev tasks that don't necessitate pulling in an entire IT team. I don't really understand why this isn't more popular. The business team this individual was hired onto was over-the-moon when this occurred because they were doing absurd things like copy-pasting and hand-modifying JSON payloads many times a day and simply lacked the skillset to fix the problem, due to the issues you described. These issues were immediately resolved in under a month for hundreds of man-hours saved.
Just give business teams a tech resource that's well-trained and understands proper dev for on-demand work that doesn't justify the agile scrum whatever nonsense, and you won't end up with a forest of Excel macros.
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#.
Worth also pointing out that sometimes when you're in a corporate dystopian hell hole do not expect to be able to actually request or install software on your device. What is there is what you have and trying to get it changed is an exercise in taking on the bureaucracy. It's not worth it. Many people have tried and failed.
Back in the dark ages, we had a horrible reporting engine in Word VBA that pulled report definitions off a fileshare and cut and pasted bits of templates together and then printed them. Literally there was a computer in the office the IT team hadn't taken back because the guy had quit and we logged it in as one of us and ran that .doc all day to do numerous engineering reports. This was quicker and cheaper than filing a PO for the reporting option on the CAD/CAM software which would have taken at least 18 months, involved consultants and eaten at the project budget.
So when everyone bitches about Excel VBA being used for horrible things, the cause is probably further up the stack.
The other cause is what I call monkey hammer. If you give a monkey a hammer he's going to hit things. Everything looks like a VBA solution when you're a monkey and the only hammer you have is VBA. I am a slightly more evolved primate these days.
I suspect that dystopian environments of locked-down mandatory corporate Windows laptops with no software installation privileges, firewalled networking and even the USB ports disabled are also part of the reason for every function being crammed into the browser to the point that the browser has become an operating system host... Creativity (and catastrophes) happens where there is freedom: local scripting and browser scripting !
Depends on the level of corporate restrictions. Workstations with the "developer" policy applied may do that (if they managed to smuggle the executable through the HTTP proxy, and as long as the program doesn't open an inbound port - upon which event the OS kills it) but others can only run whitelisted executables. Every day I miss the Debian computer I have at home.
Best practice security recommendation for executables these days (in corp env) is to block all execution of all executables outside of protected folders, i.e. Program Files and Windows. Severely limits the initial attack surface (disable that rule or supply chain attack).
As a developer who hates installing programs that might be one offs, I hate the idea of it, but I can't deny the benefits.
That was my idea from the beginning among forbidding macros in Office and enforcing text email everywhere for corporate comms among an internal Jabber/SIP server for group videoconferences and a hacked up News (NNTP) server for internal discussions and news, which would be one of the best tools to implement an easy discussion board to mark both issues and schedules. But $BOSS won't like that, they want to execute anything everywhere.
Yes. At this point it's well-known that ports 80 and 443 are the two ports no company[0] can afford to block. This means, among other things, that making your product as a webapp is by far the best approach if you want to "worm your way into" corporate environments, as any worker can use it out of the box, while anything else would require IT approval.
--
[0] - Except those creating high-security environments with airgaps and whatnot, but that's a special case.
Yeah in the early 2000s Java was supposed to be the universal platform of write once run everywhere. And then every IT department locked Java out, so we said fuck it and wrote everything in PHP.
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.
> Worth also pointing out that sometimes when you're in a corporate dystopian hell hole do not expect to be able to actually request or install software on your device.
The problem is, cybersecurity insurances nowadays have that limitation as mandatory for coverage... and for good reason.
> sometimes when you're in a corporate dystopian hell hole do not expect to be able to actually request or install software on your device. What is there is what you have and trying to get it changed is an exercise in taking on the bureaucracy. It's not worth it. Many people have tried and failed.
Been at a company that was like this to developers. We couldn't approval to get anything installed, and IT was just plain hostile. They also demanded six months notice for us to get a server that was a copy of an existing computer (we wanted to use it for staging).
I also once built an exe for our internal app in Visual Studio, got a call from IT, they said I had a virus on the computer, requested screen share access, and I watched them navigate to the bin folder and delete the .exe I just built (and just the .exe file).
Had to go through a nice long process to get them to stop doing that. Also they didn't seem to understand that I'm a developer and I develop software for the company.
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.
A lot of systems have python or perl already installed. I feel like perl in particular is probably way more portable and performant than whatever hacks you have to come up with in excel.
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).
Years ago, I heard that JP Morgan had +20k access databases on their network. The data analysts that make up companies far and wide one day discovered that they hate what they’re doing every day. They investigate the “record macro” button. Some might even find it nifty. They use it again and again. Some may even try to get smart and investigate and get curious of the code that it spat out. Some might even go further and attempt to learn enough to change some things around.
A handful might just learn data structures and algorithms to build out a auth / permission system that mimics Django. Might rebuild the UserForm UI from scratch. Implement markdown, sax parsing, custom scroll bar, logging, games.
The answer is because a data analyst probably got bored of what they’re doing every day.
Or possibly they went to the IT department who threw down so much red tape from their ivory tower they were forced into the "shadow IT" sector. I've seen in large enterprises where some analysts have the skills to take the Frankenstein they built to the proper level, but are met with "well we need to start a project and make tickets, timelines, requirements, etc..". They certainly have good reasons - supporting something that anyone has to step into and learn is a valid concern. But as long as the business has access to tools that solve their problems, the "bored" ones will find a way. Too much friction.
I agree, and I'll add this: these people are not exactly bored, they're just the type of people that see a problem, a solution, and got the time to connect them together. Now I think the best attitude toward that particular type of people is to encourage good practices instead of mocking or shutting it down
This is the experience I had as well as a lot of other people that start out in Finance and then move on to something related to data engineering or system implementation.
I don't see it ending anytime soon simply because it is easier to build something somewhat complex inside of Excel and put it on a network share than go through IT to install IDE, build something, and then go through security to deploy it. Not every problem requires a jira project and overly complicated solution.
That said, I am wholeheartedly against large things being built in VBA. A few little scripts to query a cube in one system and combine with data from a table in another based on changing values in a few cells is fine but there is a point where you have to go elsewhere.
Ultimately I am a huge fan of of the Alteryx+Tableau/PowerBI stack for the vast majority of projects, so long as you have the server licenses where things can be automated.
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.
I think proper Python macros is one thing LibreOffice could have implemented years ago, to help attract people and break MS near monopoly on spreadsheets. It exists today, but last time I tried felt very hacky. There was no proper macro editor for it, IIRC you had to extract spreadsheets as if they were zip files and save .py written elsewhere in directories inside. Very cumbersome.
Microsoft recently released OfficeScripts, which are JS with a record macro button. Problem is, it's currently too limiting. Will be good in a few decades though if support isn't dropped.
Why do Linux/Unix/Mac developers write shell scripts, when there are so many better languages out there? A large part is it integrates well with the shell and it is ubiquitous.
VBA is basically the scripting language of Office. It integrates well with Microsoft Office, and in a business environment, pretty much everyone has access to it.
Are there better languages? Sure. However, it is hard to beat the integration and ubiquity.
And, VBA is a much, much better language than (ba)sh script!
The difference here is that Linux devs would get rightly chided for building entire applications in shell scripts. The existence a "glue language" isn't a bad thing, rather, it's a good thing. But when you wake up to find that your whole project is made of 100% glue, you might consider that a bit of a mess.
password-store does have some strong merits stemming from its Bash implementation, but from having followed its newsletter for a few years (and made a few changes in a fork) it also brought its fair share of bugs and weirdness.
Are entire VBA projects really that ubiquitous? As far as I can see, there are really two category of those: first are the huge proprietary plugins from large B2B companies that serve as a way to deeply integrate their products into Excel Spreadsheets, and the second are more like extremely customized tools built by the enthusiastic tinkerer of a non-technical team to make a complex and repetitive task easier.
But if you can install matlab or torch you'd do that instead right? Which gets back to the whole restrictive IT thing where you don't have those tools. In fact the only full featured languages on the machine are javascript in the browser and vba. VBA is about 11 times faster than numpy for dense matrix math as it is compiled, and all the support math libraries like nonlinear solvers and whatnot are in dlls that were native coded, but with a lower ffi penalty than python. VBA is really a very underrated mathematical language that is mostly used for horrifically architected mission critical CRUD applications.
"There's this one guy who doesn't really know how to program, but he made some software that benefits the company. This software is now so complex the original creator is in way over his head, but continues to work on the software anyways, and it'a a huge mess" is category 3. I've seen it happen multiple times.
This happened to a friend of mine. He had no programming experience but was tech savvy.
The organisation was using an excel spreadsheet for a bunch of things. The org identied his tech savvy-ness and asked him to add some functionality.
He taught himself VB for this task. I still remember the message he sent me happy he'd discovered functions. I asked him how many lines he had written, he was 3000 lines deep by the time he discovered functions.
He knew this was bad. He kept telling management this was too complex for an excel spreadsheet that is emailed around, and they should hire a developer to build proper solution.
He later left the org for greener pastures and on more than one occasion they contacted him to asking to add additional functionality to the spreadsheet. Each time he'd tell them they should hire a developer to write a real application, with a real database and they weren't interested. So he'd quote some stupid hourly rate hoping they'd go away and each time they agreed to it.
Last I heard, his spaghetti spreadsheet still lives on 10 years later.
the thing is, for the most part this is straight up good.
sure it has bugs and stuff like that, but its solving a real need. Bringing more value to the buisness than almost anything else for its cost
Systemd means replacing your at least somewhat standardised shell scripts with an underdocumented, underspecified, opaque scripting language. It's not an improvement.
A few years ago I ported a UNIX log processing application into Java, that was basically a bunch of Korn shell scripts born in AIX, doing all sorts of data fetching, log processing and uploading of results into other severs, scattered around a couple of scripts and UNIX tools.
It was a bit of a mess indeed, however as the author of the Java port, I would assess the Korn shell scripts were still much better approach, despite the mess.
Reason for the port? Whoever was taking charge of the application wasn't confortable with UNIX and decided having it done in Java was easier for having random external consulting companies develop it further.
Yep, I did a few VBA+Access apps in the mid-to-late 90s. VBA in Access 7/97 was kind of buggy, too, so there were some truly awful workarounds involved.
And speaking of HTML, there was also VBScript, which for me is inextricably linked to classic ASP.
When I worked for an alphabet agency, I had to develop apps for people deployed to Afghanistan. The only computers they had access to were running locked down Windows XP with no way to install anything new. They were stuck with Office because it was already vetted and installed.
Therefore I was stuck with Office too, even though I'm a Linux guy. I got a fair amount of kudos building some real frankensteins for them purely in VBA.
Having been in a similar, but not the same, situation, I resorted to an HTML file with some JavaScript code in a script tag. Was IE locked from running, even though the machine was air gapped? Or did you find VBA more convenient that JavaScript?
> Was IE locked from running, even though the machine was air gapped? Or did you find VBA more convenient that JavaScript?
At least partly it was due to being in an environment where other people I worked with were already using VBA. They suggested VBA for the task, and they were able to help me get up to speed with it fairly quickly. And at that point in time I was still young enough to be open to trying new things just for the sake of it, my own opinions were not fully encrusted yet :)
I did dabble in javascript for a simple webapp for one small project, but that was kind of a tangent to what we usually worked on.
Cos they already know it and it's good enough to accomplish their goals. Nobody gives a dying duck about your new/better thing unless it makes their miserlable office slave life slightly more bearable not in the long term, not in the medium term, not even in the short term. In the IMMEDIATE term.
The real answer as pointed out in this document is simple - IT Security and Administrative policy in non-technology companies trends towards restriction and justification rather than permissiveness.
I work in a role that develops air gapped custom communications system, my title is engineer - and to that end I have a broad cross domain knowledge - including traditional system administration tasks. I have to go thru special justification to get local admin to install software our company makes. T
here appears to be a future that will prevent me from using a thumb drive to move our software and configurations from my work PC to our systems - when we ask IT for a solution, they tell us "us the approved file sharing mechanisms" - which are basically limited to OneDrive. On top of all of that, per the written policy, we regularly violate written policy - for example distributing software requires LOB executive permission - which in the context of our larger company would be CEO level - and this is just one glaring example.
IT is either clueless or doesn't care and no one outside of my LOB cares - or is aware - and nothing will change until security policy prevents a major project from delivering on time.
Implementing prohibitively tight security and mandating that any files are shared through a product with security footprint of Onedrive is crack-smoking-monkey level of insane.
And I know that feeling. I had a job once where if you wanted to install a text editor, not only did you need permission, but someone from IT dept had to come to your desk and install it themselves. And this was at an ordinary mid-sized private company manufacturing nothing special.
All you can do is starve these companies of support, by leaving as soon as you discover such attitudes, and encourage any other devs there to do the same.
> IT Security and Administrative policy in non-technology companies trends towards restriction and justification rather than permissiveness.
I work in a tech company and the IT department is like that. The worst part is that IT/security is separate from the operational branch, and they don't care if it impacts our projects. Even though we are the same company, it seems they only care about their own profits (we get billed), we probably would have better service going to competitors, but we can't (obviously). We lost contracts because of it.
I'm trying to explain to our IT that we need to match our customers expectations on how to interface with them, not the other way around, that has so far, either fallen on deaf ears or not made it to the correct person.
I have a ticket open about MX Resolution failures on outbound email to a certain subset of customers - IT keeps blaming unspecified configuration errors on the customer side, not a misconfiguration in our infrastructure. If they gave me a RCA and told me what was wrong, I'd be happy to go to the customer and tell them what's wrong. They won't do that though, nor will they open up a ticket with our vendor to resolve or investigate the issue on our end.
> I'm trying to explain to our IT that we need to match our customers expectations on how to interface with them, not the other way around
That's exactly the problem.
Here is a personal anecdote.
Our customer wanted us to setup a development/test machine. Because the software had some real-time constraints, we had to use a CPU with enough physical cores and a customized Linux distribution, accessible through SSH with a remote desktop, it didn't need direct access to neither our corporate network nor the customer network. Essentially, what we needed was a computer with an internet connection and root access for at least one member of the team.
So we setup to talk with the customer to decide on the various requirements. We forwarded them to our an IT security department, and they essentially replied with "this is not a standard configuration, do it yourself". I ended up making the plan myself, had it checked with some guy at the IT security that happened to be cooperative and after a few back-and-forth on some details to make sure it was fine, I started to set up the server. At the same time, my manager made sure we had a spot to put the computer in the server room, all good. We essentially did it all by ourselves, and the customer was ok, I wouldn't say "happy" because all these exchanges with IT security took way too much time. All that was needed was for the IT guy to plug in the machine and configure the network.
Then it went downhill. They first stated that they couldn't let us have our own computers in the server room, only VMs. It was not only completely inadequate due to the real-time requirements, but the price was absurdly high, like hundreds of euros a month. Plus, it is not what they told us earlier.
So, we insisted. They then sent us someone who was probably an architect of some kind and started to suggest some ridiculously complex architectures with a dedicated router, firewalls, etc... when all we really needed was an internet connection with no special privileges (something the customer has already agreed with). Not only it would have cost thousands just for the study, and who knows how much for the actual setup and maintenance, but it came with annoying restrictions.
In the end, we told the customer we couldn't do it, so they did it themselves and we did the dev and tests we had to do on the customer machine. Needless to say, the customer didn't really appreciate the whole affair, and we got dumped.
What we probably should have done, and I have seen it many times is to get a regular consumer-grade DSL/fiber plan just to work around the IT department.
Sometimes it’s the only thing available in a very locked down enterprise or corporate environment and the pace of implementing something new is too slow.
VBA is a lovely language, that supports object-oriented programming (with composition... no inheritance). It has deep access to and control of Excel. It's mature and stable (Microsoft is no longer significantly changing it). "Real programmers" hate on it largely because of all the amateur spaghetti VBA code written by the business people (that the programmers are occasionally asked to debug).
Options are good. Resuming on error can be just as much a feature or flow control paradigm as using exceptions for flow control.
VBA gives users options. If you want a straitjacketed 1990s predeclared OOP language, you can use Option Strict and Option Explicit and forbid Goto statements and On Error statement. If you can deal with ambiguity, you don't need to.
And of course even a a language with misfeatures is better than the VP of the IT Dev Silo giving you the choice of spending $2m and a year or doing your work by hand.
Counterpoint: VBA is an awful language, other than its access to/control of Excel, Word, etc.
It's full of bizarre quirks, like <i>control characters in code</i> 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 readable. When code breaks for this reason, it does so with incredibly unhelpful errors, and it is literally impossible for the developer to reproduce unless they know it's a potential problem with VBA, and then they have to switch their interface language to one they potentially don't even know to reproduce the problem.
In Word, at least, probably half of the most useful functions (insert a paragraph after the current one, etc.) will break if you use them on the last paragraph in a table cell, requiring tons of spaghetti-code workarounds.
Want to pass around a string of text that contains multiple formats, the equivalent of referring to the innerHtml property of a DOM element? Good luck with that, unless you want to do it all using hacky scripted-select and copy/paste.
Someone in a parallel thread compared it to Bash, and I actually agree with that. No one should be writing anything complicated in either language.
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 is simple / clean.
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.
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.
VB(A) is like Python. It's not pretty, but it gets the job done. (* if you think it's pretty, it's because you are inexperienced and don't know the many better alternatives *)
Any tool with a good ecosystem (tools/libraries/integrations) which allows you to get real work done is useful.
Visual Basic as a desktop app development system (or MS Access which added DB benefits) was very useful in a large number of scenarios. And when you outgrew that, you must have had enough money to pay to scale up to a "real" solution.
Without a doubt, a HUGE TON of money has been made using VBA based systems.
From my own experience (as a mostly-outsider finance dev), my biggest Excel/VBA rewrite was for a company that made $$$$ before, during, and after 2008 doing credit default swaps. Sure the Excel workbook took 5 minutes to open (before I rebuilt it), but VBA was doing a lot of heavy lifting. And the people with the knowledge were making big bucks for the company and themselves with bonuses.
This is really a lesson. Whether the tools are ideal or not, what matters more is if they are accessible to people not specifically trained to use such tools. Again, that's why Python has become #1 outside the client web browser. It doesn't mean the tools are the best, but it means they do the job and are accessible.
Python is arguable a pretty language and it's also a fully featured language with support for classes, first class functions, and yet manages to remain relatively simple and approachable.
But even that isn't why it's popular. It has the most robust data science tools available which has created a steamroller effect and decent web frameworks in Django and Flask.
Python has also replaced Java as "the first learning language" at many universities.
So there are many reasons for it's rise in popularity. VBA not so much.
Python is no Scheme, but it's definitely pretty as programming languages go and for many cases there are no better alternatives. Based on about 25 years of programming with tens of languages.
Huh. I distinctly remember working with VBA-based systems like 20 years ago where that was a massive difference - like, I'd been writing code on mid-'90s VB4 and it had "on error goto" but VBA didn't like 10 years later. But maybe it was specific to one or two VBA-based platforms.
Either way, it was super infuriating since it meant the only non-catastrophic error-handling possible was "on error goto next" and then manually checking error codes.
I've been developing VBA macros since 20 years. It's largely the same language as it was when I first started. I've made lots of automations with VBA but nowadays, I've almost fully moved to UiPath RPA. I think RPA is very underrated and it should be used in place of VBA for complex automations like button clicks, data entry, scrapping, etc.
interesting, looks promising. ive made both vba and python automated scripts for button clicks etc in other software in the past, could come in handy in future I imagine to have a more dedicated setup. Is UIpath free?
The article linked within the article, "Your Organization Probably Doesn't Want To Improve Things," is interesting because I know *exactly* what the author's problem is.
The problem is they're an intelligent person falling short of their potential.
As understandable as it is, raging against people around you for their shortcomings isn't going to help you or them. You've got to do the hard and scary work of grinding your way up to get to where you belong.
That's true, the stupidity of the workplace will always exist.
But like I know that pain. I've been there before. After getting into FAANG there's still plenty of meaningless work but at least the people are smart.
Couple of years ago someone I know in manufacturing asked me to add a "cell hiding" encryption function to an Excel spreadsheet (because they still use excel spreadsheets for showing redacted price information to clients), that they could unhide when they wished to view the data themselves.
Quite a clever solution they use, I thought.
I implemented a simple XOR based encryption in VBA and it worked.
So, I imagine that's just one of many real world business use cases.
I quite enjoyed the bizarre deep dive into VBA and Excel tho
It started as a simple way to automate the boring stuff in Excel. In my very very junior days working I had to compile a neat dashboard from different sources which came in Excel format. Sometimes these workbooks had some mistakes, sometimes they were forms that were mangled by production managers (this before password protection and fixed layout forms were a thing — eeeesh),… it was mindless fixing, converting text to numbers, wrong date formats, aligning, copy pasting of hammer values from several files into a master file then printing it and dozens of forms onto an inkjet for the monthly operations meeting. What started as a full week job becomes 2 hours + printing after VBA started automating. The offending mangled forms had to be resent to the feeder managers with a note if they need additional help/advice how to overcome the limitations of the forms…).
My main issue is that unlike VBA, I can't program it from right there in Excel. Sometimes I don't want to start up a full-fledged add-in project that's meant to be reused. I just want to run a quick-and-dirty script once to fix something right now. I discovered Script Lab (https://learn.microsoft.com/en-us/office/dev/add-ins/overvie...) while writing this, so maybe that'd help.
The other issue: it is not trivial to share an addin to end users. You need to publish it to marketplace or sharepoint. Sideloading requires SMB server and GPO. However there is an option that is not mentioned anywhere: it is possible to embed it in a document and it will install when it is open for the first time (after user confirmation).
JS Add-ins are way limited than VSTO Add-ins. With the former you are limited to a side panel and add buttons to a specific section in the ribbon while with the VSTO you can even customize views with region forms.
> My main issue is that unlike VBA, I can't program it from right there in Excel.
Yeah, that's pretty much a deal-breaker. On top of the obvious thing: can "add-ins" be installed by unprivileged users, without involving the IT department? Can they be embedded in the spreadsheets? A "no" to the former is a real deal-breaker, but a "no" to the latter also hurts adoption. Nice thing about Macros and VBA is that, security settings notwithstanding, every instance of Excel is capable of running them out-of-the-box, without making the user install anything extra.
Good if all you do is display a fancy UI for some data entry or visualisation, but OfficeJS can't do half the things VBA can do unfortunately. If only the addin system had FFI. I'd switch forever.
Edit: Another big issue with OfficeJS is you need to be able to host a web server. That's not usually something most end users have access to...
There have been several half-baked efforts to introduce new kinds of automation into Office but none of them have all the functionality of VBA. But I guess working on that again is too boring and unappealing so we get flavors of the month instead.
Because it's the only option? When I was writing my PHD thesis in Word 2003, there was no other possibility to generate list of references than to write the script yourself. So I did. This doc is still lying around somewhere and probably works. While not very pretty, still more readable than Perl or Python :D
420 comments
[ 4.9 ms ] story [ 287 ms ] threadFrom that end-user direction, solutions emerge. And they're in VBA.
ISE does occasionally hang / crash, but it's quite rare compared to how VSCode behaves across every machine I've used it with. It really seems to be just a Powershell problem, haven't had the same issue in any other language.
When I'm really making great progress on something, having to fart around with killing and restarting the shell constantly is really disruptive. Yes, Code has better and more features, but for me the extra productivity does not overcome the crashy shell.
I don't like vscode for powershell development and I find the pycharm experience for powershell (lol) much better.
C:\Windows\Microsoft.NET\Framework64\ has both MSBuild.exe and csc.exe, but only for .NET Framework up to 4.0. I was under the impression that 4.8 was installed on Win 10 machines via Windows Update.
JScript is deprecated, and is likely to be removed at some point too...
CMD is often blocked on many people's machines due to group policy.
PowerShell is really the only other option other than VBA, as discussed in the article. Only reason I haven't used PowerShell til now is the version was hidiously outdated and didn't even support classes... Of course with PowerShell you can evaluate C# code.
Can you access raw memory from it?
If the answer to either of those is no, then that’s a big difference.
Here's a ransomware incident report from someone opening an Excel document with macros enabled:
https://thedfirreport.com/2023/05/22/icedid-macro-ends-in-no...
A better diffentiating factor would be who developed the macro. If it's built in house by someone merely using it to make their lives easier it's doubtful they inserted malicious code. I guess ideally IT should review the code.
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.
There are two reasons: 1. They have a specific job with a specific set of duties (think sysadmins, or administrative duties) in a large company or in a state beurocracy. 2. They would rather go home or do something more but they are not permitted: they have metered time in the office and other people would and do shut them down on any initiatives.
To me, a workplace like that is like a kafkaesque nightmare but they seem to be fine with it, or rather, have accepted it. It lets them focus on other things in life outside of work.
i mean, i would imagine some people want to see purpose in their jobs, while others are just treating it as a job and whatever happens with the output of the job is of no consequence. And this is esp. true of gov't jobs, but by no means do the gov't have a monopoly on such inefficiencies.
But my opinion is that there's something systemic that is preventing these jobs from being competed on and efficiencies eked out.
The problem is actually in the work culture, where other coworkers would prevent another worker from becoming too efficient and proactive. So, nothing changes.
Although I wasn't in the condition of automating the time required down to N minutes, I can see how this dynamic plays - essentially, BigCo with dysfunctional management, where efficiency doesn't really matter.
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 declare failure[1] and then I had to explain to my boss why I was a month behind on my work that was due in a couple of weeks[2].
I imagine that for every "automated my job and now I can do it in 15 minutes" story there are 15 stories of "I automated my job and now I work just as hard maintaining the automation" and another 50 stories of the "I tried automating my job but failed" kind. Only the first one gets re-told.
[1]: Mainly due to hardware quirks I didn't have the experience and skill to work around.
[2]: This is not a story about how automating something is bad; it's a story about the bad decisions one makes when one is inexperienced!
A few weeks into the job I completely automated these in python and all I had to do was turn my laptop on in the morning, then off in the evening, and I was done.
They were slower, but they also chit chatted with half the office, went to lunch, etc.
Their jobs consisted of pulling some data from here or there, entering it into excel, sending a few emails, entering some data into another system, printing some checks. All stuff that's easy to automate (you'd probably need more than Excel in this case)
In one of his memoirs, the science fiction author Arthur C. Clarke recalled his days as a young man working for the British bureaucracy (something to do with teacher pensions, as I recall). His particular job involved consolidating huge lists of figures into reports. He observed that the numbers in the reports were rounded to two significant figures, well within the accuracy of his slide rule, and started using the slide rule to do all his work.
He could finish his daily quota before lunch and take every afternoon off.
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.
This is quite reminiscent of the good old "emacs operating system" paradigm just applied to a different context!
But then, the next phase starts: that scripts gets copied over (because Jim wanted to run it too) and modified (Jane has a different VBA version) and expanded (now it does "THIS!" too).
Now it's a 1500 line kludge and they want to unload it, ie pass it over to development for maintenance.
... and THAT should be considered a GOOD THING!
It means you've got a tried and true business case for the application, the requirements capture has already been done, you've got an instant user-base and a very clear bar to jump over. Of course, the application must be able to outperform the old application in every way, or else questions will be raised.
I think it's important to point out that the inception of these excel VBA monstrosities is innocent and pragmatic. An SME has a job to do, they're doing their job, but have a need for a custom tool to help do their job.
It is ALMOST NEVER the case that they should drop what they're doing and engage a SW development team to go through a lengthy VERY expensive process with uncertain outcomes-- all the while still having to do their job. It's much more pragmatic, in many cases, to tackle the problem piece by piece, as need arises, with little spreadsheets, scripts and little databases.
I think complaining about VBA monstrosities is wrong-headed. They should be, in a way, embraced as a starting point for devs-- hopefully BEFORE they become mission-critical to the company, however.
There is no way for the IT customer to negotiate this "correctly". It always leads to the same result.
The problem is IT exists to administer computer systems, not to help business people create or maintain software. This brings the wrong mentality and skillset.
They embedded a technical developer into a business team, and had that individual write the "kludgy" business apps that needed quick automation for throwaway tasks or for data processing standup. The dev has access to more than VBA, specifically, Python, GitHub, the ability to spin up what amounts to VPS's in the cloud with access to all of the database infra. All tools are shared with the rest of the company through a tech sharing program that is being heavily promoted across teams, and of course hosted in a repo, often with docs or a website if possible.
This "fills the gap" of dev latency for small dev tasks that don't necessitate pulling in an entire IT team. I don't really understand why this isn't more popular. The business team this individual was hired onto was over-the-moon when this occurred because they were doing absurd things like copy-pasting and hand-modifying JSON payloads many times a day and simply lacked the skillset to fix the problem, due to the issues you described. These issues were immediately resolved in under a month for hundreds of man-hours saved.
Just give business teams a tech resource that's well-trained and understands proper dev for on-demand work that doesn't justify the agile scrum whatever nonsense, and you won't end up with a forest of Excel macros.
The Software People get called in when it becomes difficult to maintain the ad hoc solution.
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#.
Back in the dark ages, we had a horrible reporting engine in Word VBA that pulled report definitions off a fileshare and cut and pasted bits of templates together and then printed them. Literally there was a computer in the office the IT team hadn't taken back because the guy had quit and we logged it in as one of us and ran that .doc all day to do numerous engineering reports. This was quicker and cheaper than filing a PO for the reporting option on the CAD/CAM software which would have taken at least 18 months, involved consultants and eaten at the project budget.
So when everyone bitches about Excel VBA being used for horrible things, the cause is probably further up the stack.
The other cause is what I call monkey hammer. If you give a monkey a hammer he's going to hit things. Everything looks like a VBA solution when you're a monkey and the only hammer you have is VBA. I am a slightly more evolved primate these days.
https://portableapps.com
I think there's even a Lazarus IDE available for every company user who wants to create reliable RAD based software bound to corporateware.
As a developer who hates installing programs that might be one offs, I hate the idea of it, but I can't deny the benefits.
--
[0] - Except those creating high-security environments with airgaps and whatnot, but that's a special case.
https://en.m.wikipedia.org/wiki/Law_of_the_instrument
The problem is, cybersecurity insurances nowadays have that limitation as mandatory for coverage... and for good reason.
Been at a company that was like this to developers. We couldn't approval to get anything installed, and IT was just plain hostile. They also demanded six months notice for us to get a server that was a copy of an existing computer (we wanted to use it for staging).
I also once built an exe for our internal app in Visual Studio, got a call from IT, they said I had a virus on the computer, requested screen share access, and I watched them navigate to the bin folder and delete the .exe I just built (and just the .exe file).
Had to go through a nice long process to get them to stop doing that. Also they didn't seem to understand that I'm a developer and I develop software for the company.
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.
Not entirely correct
https://www.encomputers.com/2018/05/disable-macros-in-micros...
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).
Edit: regarding the video embedded in article
Years ago, I heard that JP Morgan had +20k access databases on their network. The data analysts that make up companies far and wide one day discovered that they hate what they’re doing every day. They investigate the “record macro” button. Some might even find it nifty. They use it again and again. Some may even try to get smart and investigate and get curious of the code that it spat out. Some might even go further and attempt to learn enough to change some things around.
A handful might just learn data structures and algorithms to build out a auth / permission system that mimics Django. Might rebuild the UserForm UI from scratch. Implement markdown, sax parsing, custom scroll bar, logging, games.
The answer is because a data analyst probably got bored of what they’re doing every day.
I don't see it ending anytime soon simply because it is easier to build something somewhat complex inside of Excel and put it on a network share than go through IT to install IDE, build something, and then go through security to deploy it. Not every problem requires a jira project and overly complicated solution.
That said, I am wholeheartedly against large things being built in VBA. A few little scripts to query a cube in one system and combine with data from a table in another based on changing values in a few cells is fine but there is a point where you have to go elsewhere.
Ultimately I am a huge fan of of the Alteryx+Tableau/PowerBI stack for the vast majority of projects, so long as you have the server licenses where things can be automated.
VBA is basically the scripting language of Office. It integrates well with Microsoft Office, and in a business environment, pretty much everyone has access to it.
Are there better languages? Sure. However, it is hard to beat the integration and ubiquity.
And, VBA is a much, much better language than (ba)sh script!
Exception, not the rule.
If there's a third category, please enlighten me
The organisation was using an excel spreadsheet for a bunch of things. The org identied his tech savvy-ness and asked him to add some functionality.
He taught himself VB for this task. I still remember the message he sent me happy he'd discovered functions. I asked him how many lines he had written, he was 3000 lines deep by the time he discovered functions.
He knew this was bad. He kept telling management this was too complex for an excel spreadsheet that is emailed around, and they should hire a developer to build proper solution.
He later left the org for greener pastures and on more than one occasion they contacted him to asking to add additional functionality to the spreadsheet. Each time he'd tell them they should hire a developer to write a real application, with a real database and they weren't interested. So he'd quote some stupid hourly rate hoping they'd go away and each time they agreed to it.
Last I heard, his spaghetti spreadsheet still lives on 10 years later.
We have such sights to show you.
And many Linux users whined for many years after that mess was replaced by systemd and many still do.
It was a bit of a mess indeed, however as the author of the Java port, I would assess the Korn shell scripts were still much better approach, despite the mess.
Reason for the port? Whoever was taking charge of the application wasn't confortable with UNIX and decided having it done in Java was easier for having random external consulting companies develop it further.
I looked it up -- VBA=Visual Basic for Applications.
https://en.wikipedia.org/wiki/Visual_Basic_for_Applications
And speaking of HTML, there was also VBScript, which for me is inextricably linked to classic ASP.
Therefore I was stuck with Office too, even though I'm a Linux guy. I got a fair amount of kudos building some real frankensteins for them purely in VBA.
At least partly it was due to being in an environment where other people I worked with were already using VBA. They suggested VBA for the task, and they were able to help me get up to speed with it fairly quickly. And at that point in time I was still young enough to be open to trying new things just for the sake of it, my own opinions were not fully encrusted yet :)
I did dabble in javascript for a simple webapp for one small project, but that was kind of a tangent to what we usually worked on.
I work in a role that develops air gapped custom communications system, my title is engineer - and to that end I have a broad cross domain knowledge - including traditional system administration tasks. I have to go thru special justification to get local admin to install software our company makes. T
here appears to be a future that will prevent me from using a thumb drive to move our software and configurations from my work PC to our systems - when we ask IT for a solution, they tell us "us the approved file sharing mechanisms" - which are basically limited to OneDrive. On top of all of that, per the written policy, we regularly violate written policy - for example distributing software requires LOB executive permission - which in the context of our larger company would be CEO level - and this is just one glaring example.
IT is either clueless or doesn't care and no one outside of my LOB cares - or is aware - and nothing will change until security policy prevents a major project from delivering on time.
From a job security perspective it makes a lot of sense.
That's the “Nobody ever gets fired for buying IBM” idea.
We're doing 'the cloud' wrong, rather than it being a way to leverage BYOD and easier access to information, we're going the opposite way.
And I know that feeling. I had a job once where if you wanted to install a text editor, not only did you need permission, but someone from IT dept had to come to your desk and install it themselves. And this was at an ordinary mid-sized private company manufacturing nothing special.
All you can do is starve these companies of support, by leaving as soon as you discover such attitudes, and encourage any other devs there to do the same.
But, no, because I otherwise love my job, also where would I go?
I work in a tech company and the IT department is like that. The worst part is that IT/security is separate from the operational branch, and they don't care if it impacts our projects. Even though we are the same company, it seems they only care about their own profits (we get billed), we probably would have better service going to competitors, but we can't (obviously). We lost contracts because of it.
I have a ticket open about MX Resolution failures on outbound email to a certain subset of customers - IT keeps blaming unspecified configuration errors on the customer side, not a misconfiguration in our infrastructure. If they gave me a RCA and told me what was wrong, I'd be happy to go to the customer and tell them what's wrong. They won't do that though, nor will they open up a ticket with our vendor to resolve or investigate the issue on our end.
That's exactly the problem.
Here is a personal anecdote.
Our customer wanted us to setup a development/test machine. Because the software had some real-time constraints, we had to use a CPU with enough physical cores and a customized Linux distribution, accessible through SSH with a remote desktop, it didn't need direct access to neither our corporate network nor the customer network. Essentially, what we needed was a computer with an internet connection and root access for at least one member of the team.
So we setup to talk with the customer to decide on the various requirements. We forwarded them to our an IT security department, and they essentially replied with "this is not a standard configuration, do it yourself". I ended up making the plan myself, had it checked with some guy at the IT security that happened to be cooperative and after a few back-and-forth on some details to make sure it was fine, I started to set up the server. At the same time, my manager made sure we had a spot to put the computer in the server room, all good. We essentially did it all by ourselves, and the customer was ok, I wouldn't say "happy" because all these exchanges with IT security took way too much time. All that was needed was for the IT guy to plug in the machine and configure the network.
Then it went downhill. They first stated that they couldn't let us have our own computers in the server room, only VMs. It was not only completely inadequate due to the real-time requirements, but the price was absurdly high, like hundreds of euros a month. Plus, it is not what they told us earlier.
So, we insisted. They then sent us someone who was probably an architect of some kind and started to suggest some ridiculously complex architectures with a dedicated router, firewalls, etc... when all we really needed was an internet connection with no special privileges (something the customer has already agreed with). Not only it would have cost thousands just for the study, and who knows how much for the actual setup and maintenance, but it came with annoying restrictions.
In the end, we told the customer we couldn't do it, so they did it themselves and we did the dev and tests we had to do on the customer machine. Needless to say, the customer didn't really appreciate the whole affair, and we got dumped.
What we probably should have done, and I have seen it many times is to get a regular consumer-grade DSL/fiber plan just to work around the IT department.
VBA gives users options. If you want a straitjacketed 1990s predeclared OOP language, you can use Option Strict and Option Explicit and forbid Goto statements and On Error statement. If you can deal with ambiguity, you don't need to.
And of course even a a language with misfeatures is better than the VP of the IT Dev Silo giving you the choice of spending $2m and a year or doing your work by hand.
It's full of bizarre quirks, like <i>control characters in code</i> 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 readable. When code breaks for this reason, it does so with incredibly unhelpful errors, and it is literally impossible for the developer to reproduce unless they know it's a potential problem with VBA, and then they have to switch their interface language to one they potentially don't even know to reproduce the problem.
In Word, at least, probably half of the most useful functions (insert a paragraph after the current one, etc.) will break if you use them on the last paragraph in a table cell, requiring tons of spaghetti-code workarounds.
Want to pass around a string of text that contains multiple formats, the equivalent of referring to the innerHtml property of a DOM element? Good luck with that, unless you want to do it all using hacky scripted-select and copy/paste.
Someone in a parallel thread compared it to Bash, and I actually agree with that. No one should be writing anything complicated in either language.
[1] https://stackoverflow.com/questions/20652409/using-vba-to-de...
[2] https://stackoverflow.com/questions/29832281/vba-range-funct...
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.
Any tool with a good ecosystem (tools/libraries/integrations) which allows you to get real work done is useful.
Visual Basic as a desktop app development system (or MS Access which added DB benefits) was very useful in a large number of scenarios. And when you outgrew that, you must have had enough money to pay to scale up to a "real" solution.
Without a doubt, a HUGE TON of money has been made using VBA based systems.
From my own experience (as a mostly-outsider finance dev), my biggest Excel/VBA rewrite was for a company that made $$$$ before, during, and after 2008 doing credit default swaps. Sure the Excel workbook took 5 minutes to open (before I rebuilt it), but VBA was doing a lot of heavy lifting. And the people with the knowledge were making big bucks for the company and themselves with bonuses.
This is really a lesson. Whether the tools are ideal or not, what matters more is if they are accessible to people not specifically trained to use such tools. Again, that's why Python has become #1 outside the client web browser. It doesn't mean the tools are the best, but it means they do the job and are accessible.
Python is pretty and I say using spaces beats using curly brackets, begin/end or if/else or other block marking strategies
Experience is also knowing that pretty or not has some subjective component to it
VBA evolved in "harsh" conditions, which kind of explains some of its weirdness though.
But even that isn't why it's popular. It has the most robust data science tools available which has created a steamroller effect and decent web frameworks in Django and Flask.
Python has also replaced Java as "the first learning language" at many universities.
So there are many reasons for it's rise in popularity. VBA not so much.
Huh. I distinctly remember working with VBA-based systems like 20 years ago where that was a massive difference - like, I'd been writing code on mid-'90s VB4 and it had "on error goto" but VBA didn't like 10 years later. But maybe it was specific to one or two VBA-based platforms.
Either way, it was super infuriating since it meant the only non-catastrophic error-handling possible was "on error goto next" and then manually checking error codes.
The problem is they're an intelligent person falling short of their potential.
As understandable as it is, raging against people around you for their shortcomings isn't going to help you or them. You've got to do the hard and scary work of grinding your way up to get to where you belong.
But like I know that pain. I've been there before. After getting into FAANG there's still plenty of meaningless work but at least the people are smart.
Quite a clever solution they use, I thought.
I implemented a simple XOR based encryption in VBA and it worked.
So, I imagine that's just one of many real world business use cases.
I quite enjoyed the bizarre deep dive into VBA and Excel tho
My main issue is that unlike VBA, I can't program it from right there in Excel. Sometimes I don't want to start up a full-fledged add-in project that's meant to be reused. I just want to run a quick-and-dirty script once to fix something right now. I discovered Script Lab (https://learn.microsoft.com/en-us/office/dev/add-ins/overvie...) while writing this, so maybe that'd help.
Microsoft Script-lab will allow you to do just that. https://www.microsoft.com/en-us/garage/profiles/script-lab/
The other issue: it is not trivial to share an addin to end users. You need to publish it to marketplace or sharepoint. Sideloading requires SMB server and GPO. However there is an option that is not mentioned anywhere: it is possible to embed it in a document and it will install when it is open for the first time (after user confirmation).
Yeah, that's pretty much a deal-breaker. On top of the obvious thing: can "add-ins" be installed by unprivileged users, without involving the IT department? Can they be embedded in the spreadsheets? A "no" to the former is a real deal-breaker, but a "no" to the latter also hurts adoption. Nice thing about Macros and VBA is that, security settings notwithstanding, every instance of Excel is capable of running them out-of-the-box, without making the user install anything extra.
Edit: Another big issue with OfficeJS is you need to be able to host a web server. That's not usually something most end users have access to...