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.
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 !
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.
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.
> 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.
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.
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.
> 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.
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 !
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#.
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.
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.
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 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 !
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 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.
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)
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.
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.
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.
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 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.
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).
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.
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!
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!
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
The positive part of snaps, and I don't like snaps for myself, is that snaps keep updating on an unmaintained server. So as a default for VPS providers seeking to hand hold users Ubuntu perhaps makes sense.
At least that's what I understand.
However on a server I use Debian (stable) as I purposefully don't want to be at the 'cutting edge'. [I also use Debian on my laptop because it just works.]
It is an unfortunate precedent that they paid the ransom, however, I don't believe there are any institutions better suited for solving financial crimes than banks. I bet they've hedged the risk and have means to go after the hackers.
I'm not impressed by the USB. That it's possible to put transaction details on a USB stick means it's possible for customer data to go walking out the door on a USB stick, too. I'm pretty sure infact this goes against data security requirements of US regulators.
Seconding Zettlr. It also handles citations smoothly with a linked .bib file. The sidebar (a sidebar, there's more than one) also does directory tree structure like your favourite code editor too.
You mean kicking back over some eggs benedict, a meat and cheese charcuterie board, with some champagne on the side, sitting back in your pine-panelled centrally heated chalet overlooking mountain landscapes relaxing having risen at 4.30pm for a hill run and vigorous workout isn't fitting what most Europeans do on a Sunday morning?
I'm pretty sure they're always getting into moving fights through corridors and narrow spaces in big rooms. Especially fancy commercial kitchens. At least that's what I see in movies.
I once worked with new awful in-house software that (thankfully) depended on 3rd party software. We had the legacy in-house system parallel running, which was even more awful. The legacy's awful came from it being unable to do anything it didn't already do without extensive re-wiring, and a team who believed if they don't share their knowledge of it they had a job for life. The replacement's awful came from it being inefficiently programmed with large DB latency due to the team's belief at throwing more CPU behind queries instead of re-writing queries.
The teams sat next to each other but didn't speak.
The legacy team called in a bomb threat to a package left under the IT director's desk. It was taken very seriously. While their cause was already lost, that didn't help them personally.
The new system team eventually called Oracle, who came and re-wrote their queries.
The call-in was an anonymous, apparently, call to the company's switchboard stating a bomb package had been left in the vicinity of the desk. We were evacuated to a separate part of the building for a few hours while it was dealt with.
I think it means they call the police (from a payphone if they are smart), and tell them there’s a bomb under the IT directors desk that’ll explode in x minutes/hours.
It works for what I need: What I need: Browser (Firefox), Email (Thunderbird), Notes (Zettlr) + Reference (JabRef), Python, Postgres, Shell, occasional LibreOffice, the odd podcast (gPodder) and feeds (Liferea), very occasional audio and video editing.
From that end-user direction, solutions emerge. And they're in VBA.