Why do people still use VBA? | Hacker News Reader

Why do people still use VBA?

sancarn.github.io

324 pointssancarn409 comments

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.

That's exactly why I use it. Only dev environment available to me.

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.

PowerShell including ISE, with tabs, multi-line cursor, syntax highlighting, autocomplete, step-through debugger, snippets, scriptable/extensible.

People think I am nuts for preferring the ISE over VSCode, but the ISE never crashes while running scripts!

Then you haven't used it enough :) The bare shell on the other hand is usually solid.

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.

I think you might be misunderstanding what I didn't say - I use the console for debugging (Set-PsDebug!) and even then it crashes (sometimes.)

I don't like vscode for powershell development and I find the pycharm experience for powershell (lol) much better.

You're not, I would also rather have the Powershell team improve ISE than their decision to migrate into VSCode, but alas.

And yet if you send your PS script to say an HR person… it won’t run on their computer without messing with security settings.

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)

Today, yes, but ISE has shipped with Windows since what, XP? And VBA never has - it's a part of Office.

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.

Windows doesn't ship with C# out of the box. It ships with the runtime for .NET Framework 4.8, but not with the SDK.

I think it does since the Windows XP days, at least a CLI based compiler/interpreter.

Would you look at that, it does!

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.

*wink*

    $code = @'
    using System;
    using System.Drawing;
    using System.Runtime.InteropServices;
    using Microsoft.Win32;


    namespace Background 
    {
        public class Setter {
            [DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Auto)]
            private static extern int SystemParametersInfo(int uAction, int uParm, string lpvParam, int fuWinIni);
            [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError =true)]
            private static extern int SetSysColors(int cElements, int[] lpaElements, int[] lpRgbValues);
            public const int UpdateIniFile = 0x01;
            public const int SendWinIniChange = 0x02;
            public const int SetDesktopBackground = 0x0014;
            public const int COLOR_DESKTOP = 1;
            public int[] first = {COLOR_DESKTOP};

            public static void RemoveWallPaper() {
            SystemParametersInfo( SetDesktopBackground, 0, "", SendWinIniChange | UpdateIniFile );
            RegistryKey key = Registry.CurrentUser.OpenSubKey("Control Panel\\Desktop", true);
            key.SetValue(@"WallPaper", 0);
            key.Close();
            }

            public static void SetBackground(byte r, byte g, byte b) {
                RemoveWallPaper();
                System.Drawing.Color color= System.Drawing.Color.FromArgb(r,g,b);
                int[] elements = {COLOR_DESKTOP};
                int[] colors = { System.Drawing.ColorTranslator.ToWin32(color) }; 
                SetSysColors(elements.Length, elements, colors);
                RegistryKey key = Registry.CurrentUser.OpenSubKey("Control Panel\\Colors", true);
                key.SetValue(@"Background", string.Format("{0} {1} {2}", color.R, color.G, color.B));
                key.Close();
            }
        }
    }
    '@

    $null = Add-Type -TypeDefinition $code -ReferencedAssemblies System.Drawing.dll -PassThru

    Function Set-OSDesktopColor {
    param (
        $r,$g,$b
        )

        $null = [Background.Setter]::SetBackground($r,$g,$b)

        }

I'm not much of a PowerShell wiz so apologies if this is hideous, but I stuck this in my profile.ps1 a few years ago:

  $Csc = gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | select -last 1
  if ($Csc) {
    Set-Alias -Name csc -Value $Csc
    $Csc = $null
  }

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.

  if ($Csc = gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | select -last 1) {
    Set-Alias -Name csc -Value $Csc
    Remove-Variable Csc
  }

Or even:

  gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | % {
    Set-Alias -Name csc -Value $_
  }

VBScript is deprecated: https://nolongerset.com/vbscript-deprecation/

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?

Can you call os level functions from an excel macro?

Can you access raw memory from it?

If the answer to either of those is no, then that’s a big difference.

You can call any native or COM function from VBA, the only real limitation is that it's strictly single-threaded(-ish).

You can call Windows API functions from VBA.

Yes you can. VBA can make the same Win32 API calls as VB6. Something I exploited back in the tail end of the 90s.

COM/OLE. Old as hell. Macro viruses in Office/Outlook has been a shitfest since late 90's.

The "difference" is only an advantage to attackers, in that executables are typically blocked as email attachments and Office macros are not.

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...

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.

This. My friend automated his whole job in Excel.

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

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!

This sounds like a very specific, personalized version of hell

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.

Nice story! Curious, was the manager a technical person e.g. a former engineer?

Yep absolutely he was a technical manager.

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.

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

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.

It's been going on for a long time.

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 may exist, but it's rarer.

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.

And thats great because demand has be satisfied!

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.

That whole process sounds like a reasonable division of responsibility.

The Software People get called in when it becomes difficult to maintain the ad hoc solution.

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 !

>no software install...

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.

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.

Try getting those through a corporate DLP proxy.

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.

Proxies can be pretty harsh too. Not sure if we have a whitelist or a blacklist but it’s pretty restrictive.

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.

You say creativity happens where there is freedom, but I often hear artists claim they work best when given constraints.

What you call monkey hammer is actually the “golden hammer,” or “law of the instrument.” Idk if that matters to you, but it’s an already defined thing.

https://en.m.wikipedia.org/wiki/Law_of_the_instrument

It's an extrapolation of that. The golden hammer gives too much credibility to the people weilding the tool.

“Monkey hammer” is definitely very illustrative of a particular kind of chaos, I like it.

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

Crossed with a thousand monkeys and a thousand typewriters.

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

> 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.

> that doesn't need special (management, management's management, adding to a registrar of projects, budgeting or project manager assigned, etc)

Not entirely correct

https://www.encomputers.com/2018/05/disable-macros-in-micros...

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've been surprised to see many pro devs using Excel/VBA as a secondary tool.

One example: a couple years ago I was working with a big hedge fund and one of their data analysts sent me an Excel model he had built and I was tickled to see the .xlsm extension (i.e., VBA code on board).

"Ahh ha", I thought, "Let's see what these macro-recording cowboys have been up to."

There was a lot of VBA inside, all written by this Caltech comp sci data analyst who was a Python superstar. The VBA was for pulling data from a database, putting it on a sheet, building some formulas, and some pretty formatting. There were even a few userforms!

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

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

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

As I have gained experience as a developer using the right tool for the right job becomes paramount. And the lazy answer can be much better than some incomprehensible mess of ideas.

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

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

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

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

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

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

Certainly! If you're ever interested in seeing some awesome VBA stuff I have a big list here: https://github.com/sancarn/awesome-vba

Lots of awesome stuff still in the VBA/VB6 community!

A business I run depends heavily on Google Sheets that I inject values into and read computed values out of. That lets us define rather complex business logic in spreadsheet form, and business and finance people can easily tweak it. Everybody is very happy with this solution.

I ran a pre-admission (surgery) clinic at a major university hospital through Excel.

VBA is powerful and quick at prototyping/iteration.

I would even venture to say that VB6 was the zenith of CRUD apps

Because it’s amazing! /s

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

IT came back asking for a budget of $750,000 to put the macro that one team uses into production.

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.

You get it, "record macro" is the key to this. MS could switch them to C# or JS or python or whatever, if they just added a button which did that.

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.

Wasn't aware of this, thanks. I'll give it a try. It's at least a good place to try different things with Python.

Agree, Python implementation in Excel is trash. Not at all a competitor for VBA. Not yet anyway.

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.

Yeah to be honest I did something like this and then I switched to C#. Now I"m just a software developer.

I had to develop a simple CRUD interface for some of our analysts.

The immediate problems I faced was:

1) The analysts wanted every (CRUD) step to happen within excel - excel was indeed going to be their interface, so I needed something which I could launch from within excel.

2) The IT department refused me to grant command line access

3) The IT department refused me to install non-approved dev tools. To get them approved, would potentially take months.

4) The DB admins weren't too keen on letting me add a new DB to the existing Oracle DB. The IT department weren't too keen on me doing my own DB (see step 3)

Hell, just getting new add-ins to excel requires me to BEG the IT folks. And if I'm lucky, the add-ins will just suddenly appear. Will it take a day? a week? a month? Who knows.

So keeping all those things in mind, my only real alternative was VBA.

In the end I managed to get some permatemp solution up and running, which the analysts use once every two weeks.

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.

This matches my experience as someone once deployed to the Middle East. I automated much of what I could using VBA on entirely airgapped XP computers.

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?

I've done this too. with modern browsers you can do a lot with JS and HTML5 without much of a backend.

especially since given IE on XP, I bet `new ActiveXObject("Here.We.Go")` would allow some truly <s>spectacular</s> horrifying things!

> 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.

Let's face it: IT is the bureaucracy department of modern times which can keep itself 95% busy with self inflicted problems and has 5% service orientation. Processes are opaque for outsiders and typically not helpful.

I really had to lough when I read the following description of the IBM BPM but this sums up a good part of the issue:

  "...while IBM BPM does come with a REST API, this REST API is borderline useless to Technology teams and SMEs

  Some REST calls use javascript encoded as strings Others require html embedded in json embedded in xml
  
  Database tables aren’t queried by name but by GUID.

  There’s no documentation of which GUID relates to which table/process.*"

Quite a lot of things became so outragedly complex no one outside of the IT bothers to handle these, and sometimes not even inside IT. It started with AJAX where suddenly half of the development effort went into designing frontend code and backend services, which honestly does not even touch the end users automation problem. And it went further downhill afterwards. UIs nowadays look modern but are generally as user hostile as the technology stack used to produce these.

In Excel my UI is just "there", I have a nice code generator aka as macro recorder, no IT department questioning my authorization to do something nor does not have time or budget to help me with my business problem.

So VBA is the workaround for users around the IT department. Not perfect, but better than what you would get else.

So the answer is:

Because it is the only programming language Corporate can’t choose not to install.

The wonders of ‘Enterprise’, it amazes me when people bring it up as if it’s any kind of advantage or excuse.

Huh? The parent clearly points out that it's much less effort and hassle to get an 80% solution.

Who wouldn't want to spend a tiny fraction of the effort to get 80% of the outcome?

Yes. But 90% of "effort and hassle" is dealing with IT department bullshit. Excel/VBA lets you sidestep that entirely.

There's a sentence in the article which says that this is an explicit policy decision of the company.

> It is supposedly “Against the technology strategic vision of the company” to allow “end-users” access to high level programming languages.

At this point it's not even explicit - it's an implicit decision of most companies, and even OSS projects, because it became part of the "common wisdom" of computing, part of the zeitgeist.

This is where the idea of "a computer as a bicycle for the mind" died.

On the topic of "computers as public transport for the mind"...

A project I work on has some processes that I need to run that can only be initiated through the Azure DevOps Pipeline interface, and these need a "worker agent" on a VM or something, and there is only one worker agent, and some of the jobs take half an hour or more.

So the effective outcome is that despite every member of the team having a full multi-tasking computer on our desk (A multi-tasking computer each! Sometimes more than one each! Plus loads of cloud VMs), we can only run a single task at a time between us and we have to coordinate scheduling manually.

Is this the future?

It is like this because the process involves "secrets" that are meant to be hidden from the team but are accessible to the program when running inside the Pipeline. If it weren't for this secret-hiding, I could just run the process manually on whatever computer I want.

And the secret-hiding doesn't even really work, because I can freely commit code to personal branches on the repository that the Pipeline runs from, and I can run the Pipeline on whatever branch I want, so I could commit a program that prints out the secrets. Ah, but Microsoft has thought of this: if any of the secrets appears in the output, they get replaced with "***".

(Let's skip the part where this accidentally leaks a "secret" username, where I know a particular piece of text that should be output but instead all I see is stars...)

The secret-hiding doesn't work because I can just make the program output base64 of the secret. I don't do this because I don't want to start pasting secrets around in places they shouldn't be available, but it is sometimes tempting.

Anyway, welcome to the future of computing. Thanks for listening to my TED talk.

> And the secret-hiding doesn't even really work, because I can freely commit code to personal branches on the repository that the Pipeline runs from, and I can run the Pipeline on whatever branch I want, so I could commit a program that prints out the secrets. Ah, but Microsoft has thought of this: if any of the secrets appears in the output, they get replaced with "**".

Github Actions at least allows restricting secrets to be exposed only to specific branches, and in Gitlab you can enforce that pipeline steps using critical secrets can only run in protected branches, so you'd need to fool a maintainer with a malware-laden pipeline change in a merge request first.

At this point you should just leave this dumpster fire of an organization and find a more reasonable place to work. I can't relate to the people who keep inventing atrocious workarounds ignoring the problem that they work in a hostile work environment.

I work in security and can't relate to banning Python & replacing it with Microsoft crap either.

And rightly so.

Imagine for a moment that someone in accounting built a system in lisp to automate part of his job. As time goes on, he takes on more responsibility, which he writes more lisp for.

One day, he gets hit by a bus.

The lisp program he wrote is now an integral part of the running of the accounting department simply by accumulation and momentum, with tons of business logic baked in. Where do you look to find a replacement?

With VBA, there's a much higher chance of an accountant being familiar with the language, and a much smaller surface area for what they can do.

this already happens with Excel and access. Entire companies rely on a spreadsheet some wizard invented years ago and now no one knows how to change it, and it goes weird if multiple users try to access it at once so make sure you copy it locally first and change the file name so you can track the versions

Yes, it does. The difference is that with a known language (VBA in accounting, for example), you stand a hope in hell of untangling the mess or at least managing it so that the company doesn't fall over.

This is already happening with IT maintained systems though... As specified in the article. So it really isn't an argument imo.

IMO, companies should have a language of choice which is actively encouraged to be used by everyone for all automation needs. Different departments build libraries to automate aspects of their jobs and other departments can use them if needed. I.E. it becomes yet another tool, just like Excel.

It’s weird to me though that VBA apparently doesn’t count as “high level”.

A physical world analogue wouldn't be far from a renovation company declaring "flathead screwdrivers are against technology strategic vision of the company" and their use is therefore strictly banned. Construction workers would of course use letter openers and butter knives to turn the flathead screws they inevitably encounter in their work, and that would be just fine.

VBA is the ultimate agile programming language. The company's IT aka Bureaucracy Department is stuck with Scrum, Squads and what not. And meanwhile in the other departments people are just getting things done with Excel/VBA.

Nothing has changed. In the last century this also happened and it was called islands of automation. In my bubble back then it was considered a good strategy, let departments first play around, and if they are on to something integrate it.

> In my bubble back then it was considered a good strategy, let departments first play around, and if they are on to something integrate it.

Funny how with computerized process, IT departments are effectively central planners. The lowly workers get to only do what the IT secretariat allows. It is this way because national^Wcorporate security!

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

We decided that the water/electricity utility doesn't get to control how you use the amount you consume.

Modern IT is more like if your water utility had final say over which faucet you installed and how you used it.

If you started using water in, like, industrial or agricultural quantities I guess the town would eventually get curious what is going on.

Well, you'd still be paying for it.

Authorizing use would be akin to the pre-Carterphone ATT model where only pre-approved uses would be allowed ('you can't attach your equipment to our network').

Thankfully, we eventually realized that was a dumb decision and moved to something closer to user freedom + network protects itself + zero trust.

Better to just guide behavior at the pricing level, and let people make their own decisions about use.

I think the analogy to water use just doesn’t work well.

If we had to make some sort of water use analogy, I’d go with something like; the corporate network is a somewhat protected environment that needs to be maintained to be useful. So it it is more like a reservoir than a faucet.

It would actually be OK for a couple people to go swimming and even pee in the reservoir. Some people could even boat in the reservoir, if they went out of their way to make sure that their boats are clean, safe, no pollution, etc. But lots of places just have a general “don’t go in the reservoir” rule. Not because a person would damage it, but because everybody doing it would.

It is hard as a residential user to use enough water to damage the reservoir, but hypothetically if you managed to, somebody would check in. Even if you are paying, the town doesn’t want to run dry. If there is a drought, residential users might be asked to use less water.

Price doesn’t work as a signal in corporate IT for individual workers, because it is expected that the company will “subsidize” the worker to the extent needed to do their job. If we want to make the analogy work—at least in some areas, landlords are required to provide water to their customers. In that case, you can use as much water as you want for free, but your landlord will get curious and might find some way to get you on the hook if you pass some reasonable threshold.

You can also do some things as a user like dump toxic waste down your toilet. This would be sort of like running a publicly visible unpatched XP system on the network. It would damage the system, and why do you have that in the first place?

Anyway, that was fun to write, but I don’t know that it is particularly useful. In order to make the analogy fit, we need to bring in as much complexity from the water management system as the IT system has.

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

> but you don't get to have petrol pipes, beer pipes or milkshake pipes laid to your property, neither you get to choose 160V DC or 430V 400Hz electricity

Sure you can have all of these. They're just not offered as part of normal utilities. Nobody will care if you build yourself some, except maybe for petrol pipes due to fire/explosion risk.

Good luck getting you council to approve buried pipes from a brewery even 100m down the road. Within the confines of your parcel - maybe, so enjoy your Visual Builder for (beer) Aficionados.

Although those kind of questions around continuity happen across business. Sometimes the service you are offering depends on individual contact and flexibility rather than offering a commodity utility. And dependance on a small number of individuals is an acceptable and understood risk. The trouble with software is when managers don't understand that risk and offload it on another department when things go wrong.

Oh dear God how many times I've had to support that.

I was talking about this with a friend the other week... I think what IT depts really need to do is let people go crazy with Excel/VBA, but write a script to monitor activity on xls files on the network over the long term.

If there's an xls which has been in regular use for more than 18 months, and it contains macros, then it can be assumed it performs some important role and should be properly documented and checked and could also be rewritten in a "real" language and officially supported. Set up a meeting with whoever made it, and whoever's touched it most. Approach it more like "we're improving your cool thing" than "we're taking away your toys".

"let people go crazy with Excel/VBA"

Many years ago a company I worked for used to send out a spreadsheet to its suppliers which they would complete with the products they offered and then when it was received back there was a button in the spreadsheet that would automatically upload the data to a central database.

When I first saw this I was curious how it worked and did a bit of investigation - turns out there was VBA behind the button that established the database connection and uploaded the data. What was amusing was that the user had hardcoded the database connection string including username and password. Of course this wouldn't work outside of the firewall - but I'd be careful about letting people get too crazy.

I have this great new product called "DELETE FROM products WHERE provider != 'mycompany'"

Little Bobby tables!

To be honest, not hard coding a connection string is quite tough too. It really isn't an easy problem to solve. And especially when every piece of software out there connects to data in different ways.

The reality of the situation is with proper IT support, there could be compiled Excel Addins which provide API connections to core systems such that proper authentication also takes place. But that requires a first step by IT. Either that or authentication via a web server to get a temporary connection string. Either way, it requires prior infrastructure.

You're actually on to a really important thing that IT depts misunderstand about Excel/VBA monstrosities.

They exist because they work. You want UX or business analysis? You literally just got that done for you for free if you run into a Excel/VBA application. The hardest part of dev is figuring out requirements, so stop looking at these as toys and start realizing that shadow IT exists because of a gap in development. Full stop. You can argue all day long that you're working your assess off, and you do great products, but the existence of these apps is empirical proof that IT has missed the boat on developing something of importance.

Use that.

Completely agree. I've always thought that Excel/VBA shows you where all the short comings are with your in house systems.

It might be sobering for "real" devs to find out that more "real apps written in real languages" go unused than apps in VBA.

No one at my company would ever let IT take over their Excel/VBA processes.

The moment IT touches your stuff, your job transforms from solving problems to writing emails and having meetings.

Any change, no matter how trivial, takes dozens of emails, dozens of meetings, and half a year to orchestrate.

If IT wants to help solve more business problems, it needs to fundamentally change its self-concept and purpose away from "prevent hypothetical bad things from happening at all costs" and move it towards "solve more business problems".

This is vastly underestimated. The difference between coding and managing coding.

Working with IT isn't even managing coding. Management implies power to hold someone accountable, while working with IT an extended exercise in nagging and supplicating an organization which is completely unresponsive and unaccountable in its outcomes and methods.

You might as well become an immigration lawyer and spend all day begging the government to explain why your latest M-10582-9DJVA-V isn't being processed in the normal time frame, even though it was stamped in triplicate and sent by Certified Mail with a full-color copy of every identification document you own.

The best way to deal with IT is to avoid depending on it ever in the slightest way. If you give it an inch, it will take a mile.

Or, you know, just ask people what they're using Excel for, and seeing if there's a way you can help them improve on it.

You nailed it, haha! I had exactly the same experience: I was working in 2007 in analytics department, and we had no IT bureacracy, meanwhile the IT department, we had to deal with, would say it would need months of approval for anything substantial (like a simple dashboard or a report), and a year to implement.

I was wondering what would be replacing Excel/VBA after 3 decades as a citizen developer alternative. I could not think of something that even comes close. Any ideas?

I'm very optimistic about Project Jupyter style notebooks. I believe, without any evidence, that they have much greater potential thoughout IT, devops, whatever.

Example: Imagine a CI/CD pipelines using notebooks.

I hate Jenkins/Hudson style build systems so much I could just spit. I just want to run a shell script.

(Alas, I haven't had the gumption to try this idea out yet. Soon.)

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

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

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

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

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

Thanks. Agreed.

By "stateless", I'm assuming you mean functional programming paradigms of immutable, idpotent, and no side effects.

FWIW, for build pipelines, my quarter-baked notion is to use ZFS snapshots (or equiv).

I'll check out Pluto for Julia.

As you know, state is a challenge for "serverless" too.

I've been reacquainting w/ RDBMS tools. There are a few new strategies (implementions) for change tracking. Back in the day, we just banged the rocks together (ook, ook), so I'm very eager to learn the new hotness.

In the notebook context the main gripe is that notebooks have the "invisible" memory state that means that one can't deduce from the notebook code what it actually does. Or more concretely the order of execution of the cells affects what the notebook does. This leads to sort of higher level side-effects. With usual side effects you get spaghetti, with notebooks you get moving spaghetti in five dimensional space.

Immutability and idempotencency are good, and related, ideals too, although I think these can get too "unergonomic" if taken too dogmatically (like in Haskell or Redux), they should be used with almost goto-level discretion.

Of course there's the clear (short term) usability benefit of maintaining the memory state in that stuff doesn't have to be recomputed. But we can have that benefit and be stateless with pure functions and memoization. I quite often whip up a buggy and brittle ad-hoc solution to do so. There was also the IncPy project [1] that did this more rigorously, but it hasn't been updated in 13 years.

In general I'm a bit baffled why pure function memoization is so rarely used or proposed. Despite the old adage, cache invalidation is not actually half of the three hard problems in CS. With pure functions it's trivial.

Another baffle is why snapshotting/change tracking (and compressing) file systems haven't caught on. Instead these tend to get implemented badly in any sufficiently complicated application.

[1] https://github.com/pajju/IncPy

Sorry: of course side effects and mutability (not pure functions and immutability) should be used with goto-level caution.

Also the "higher-level side effects" apply more or less identically to REPL development.

Nix somehow manages changes. (Relies on ZFS?) My future perfect notebook style build script would start there.

I wasn't even thinking about REPL style work. Mea culpa: I don't actually know how jupyter et al work, so I'm talking out my hat.

Your explanation reminded me of "prevalent" persistence (vs full orthogonal persistence). I guess I assumed something like that was happening between cells.

I suppose it's analogous to the transition of UI frameworks.

Bad: Mutant components directly.

Good: Mutate thru event queue. Get undo/redo for free. Debugging still sucks.

Better: Pretend it's a simulation and use an entity component system. I think this is what the kids are calling "reactive".

> pure function memoization

Answering just for me: because I'm just a simple bear.

I've been imperative for so long, continuations, currying, and lazy eval break my brain. Yup, a fully functional world would be a lot more simple. Maybe it's time for me to revisit clojure.

Thanks again. This is fun to think about.

I guess that some developers requirements are orthogonal to those of citizen developers. For example version control could be a must have for us, but for others a layer of complexity that is just waiting to stand in the way of getting things done.

Yes. Jupyter et al are quick to get started with and you can get quite far until things start to get unmanageable.

But this may not be good for anybody in the long run. For example it tends to lead many students to not understand basic concepts, like variables. Which is understandable because variables don't behave like variables in notebooks (e.g. the same variable in the same notebook may refer to different values in different cells depending on how they are run). For many students this can cause almost insurmountably wrong mental models (which they will of course carry to "production" later on).

But as I argued in another thread here, it doesn't have to be this way. E.g. Pluto does notebooks in a more rigorous manner.

Almost all "software engineering" languages and tools makes getting started and actually getting something done quickly needlessly difficult. Probably uncontroversial that git UI is a total mess, and things are getting even worse with more build tools, dogmatic static typing and general pointless ceremony.

There have been countless attempts, but the web seems strangely resistant to a low barrier to entry, high level GUI building tool.

Even though it's a visual environment, everything is strongly text based, from HTML to CSS to JSON payloads.

I work in banking and I've noticed an uptick in Python where a decade ago it would have been VBA. Still plenty of VBA around but Python is in the mix as well.

I can find bad examples of how things work in basically every department I chose if I look long enough. Are there IT-Managed things that border on insanity? Oh yes. Are these a good excuse to build a shadow IT? No, they are not.

Don't get me wrong: I'm not bothered at all when a couple analysts get together and hack away at their own little tools in VBA. Kudos to them for getting into the spirit of things, and maybe they will understand my day to day better as a result.

What does bother me, is when these analysts suddenly expect my systems architecture to somehow accomodate their private projects in whatever capacity. When I ask for documentation (there isn't any), an architectural overview (nope), or even access to the repo for that abomination (access to a what now?).

Because, why shouldn't their spreadsheet inject data into my processing pipeline? Why shouldn't I write a controller that accomodates whatever tidbits of REST they bothered to watch half a youtube video about? When suddenly I get asked this in a meeting: "What do you mean we need authentication? Why does IT always have to make things so complicated?!?".

So yeah, please, people should absolutely build their VBA, lowcode or whatever tools. I do the same thing, the only difference is, I call them shellscripts, and they live in git repo.

But same as I don't let my CLI tools lose on the production server, I won't let it happen with things that have never even been through one code review.

> or even access to the repo for that abomination (access to a what now?).

Did someone give the analysts access to a repo?

Because I'd hazard ~80% of the companies I've seen don't allow "non-development" users access to the corporate version control system.

I'd be happy to put up a repo for them, if they ask. Problem is, they often don't.

And not to make too big a deal out of it, but using github, gitlab or anything along these lines, is mostly free, not exactly rocket science, and private repos exist.

> I'd be happy to put up a repo for them, if they ask. Problem is, they often don't.

No doubt. But that requires them knowing you exist, and what to ask you for.

The companies I've seen do this well (1) make it self-serve (anyone can click a link, without knowing who to reach out to) & (2) remove as many dumb organizational roadblocks as possible (e.g. company-wide repo visibility and search, no job role filtering to who can use tools, etc).

> but using github, gitlab or anything along these lines, is mostly free, not exactly rocket science, and private repos exist.

Putting internal files on an external third-party service under a personal account?

It solves the technical issue, but it creates some security/data issues.

> The VCS docs were on display in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door saying ‘Beware of the Leopard.

> When I ask for documentation (there isn't any), an architectural overview (nope)

As if any of those were present in the average web project lol. I complained about those points in sprint review this morning, and this is a big project made by IT companies.

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

> they are giving a service for the rest of the company

That is very true. And part of that service is to ensure that things run smoothly, securely and according to industry standards.

How well would an IT guy provide that service if he were to let some unvetted, undocumented script hacked together by someone who isn't a professional software engineer, run its merry way across the production database?

From security people's perspective things would be smooth if all computers would be plugged off and their batteries removed. Oftentimes it's not that far from that solution.

God forbid code gets written to solve a business problem rather than conform to a spec sheet right?

Businesses - and jobs - only exist to solve economic problems in the real world. Everything else, including traditional accounting, IT, legal, and HR functions are just there to make the real work easier, not harder.

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

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

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

> Instead you ask what is needed and provide a restricted REST API.

Happy to. Problem is, that API has to be built, and tested, and vetted, and maintained, and who's going to do all that work? Because I know a lot of software devs, and none of them lack for tasks.

If it needs to happen, and your team can't do it, somebody else needs to. Your best bet then is to give them the access to do it properly instead of forcing them to hack it together.

I, on the other hand, am tired of being called in to investigate why the janky Excel macro written four years ago by an ex-employee doesn't work for all the external stakeholders this manager just sent the spreadsheet to, only to find that the hardcoded database and local admin user creds in the VBA script are now leaked and in the clear.

A lot of people pushing shadow IT "solutions" wildly overestimate their own ability, while maintaining garbage-tier information security standards. That doesn't sound like you, but it's the far more common situation those of us in "IT" are forced to protect the wider organisation against.

Shadow IT exists for a reason and that's the dysfunctional bureaucracy of IT.

The "Circle of IT" is real. Small companies start out nimble, but then stuff gets crazy and someone decides to standardize it all under one department. This works for awhile, but eventually this organization becomes so useless that it can't serve any functions of the business anymore, so a shadow IT group is built that the business SMEs love as they just "get stuff done". This works for several years, but the executives in IT hate this "rogue" group as it is a constant reminder of their incompetence. Eventually they re-absorb this group and crush them with beauracracy until it all starts again.

> the dysfunctional bureaucracy of IT

I am not here to talk about management-bureaucracy, of which IT depts; same as all other branches one can find in established corporate culture, have more than enough.

I am talking about the perceived "bureaucracy" of us tech guys here, aka. following established procedures to ensure smooth running of mission critical systems.

Yes, I want things to run through code reviews. Why? Because these things go to a production system that our customers (and thus the companies income) depend on. Yes, I want authentication standards. Why? Because there are a gazillion cryptolockers, and worse, out there, who would love nothing more than to run rampant on a nice and juicy production database.

Do you have a customer focus? Are you trying to unblock people as fast as possible to solve their legitimate business need? Or are you using these as excuses to effectively say no to anything anyone proposes?

If yes to the first, you’re a unicorn in an ocean of IT departments that do nothing but block.

> Do you have a customer focus?

Indeed I have a customer focus.

My customers are the people and businesses who rely on the fact that the production servers run smoothly. And I serve their legitimate business needs, among other things, by not allowing some gung-ho hacked-together unvetted magic spreadsheet to kill runtime performance by performing a blocking query with deep joins that forces the DB server into running a full scan over 10E9's of records.

Again, as I said elsewhere, I have nothing against non-IT departments building their own private software. I do the same. But as soon as this software wants to touch the prod-server, or any other part of the infrastructure I am responsible for, it is my job to ensure they meet the same standards as everything else in the stack.

And yes, saying "No." when it is appropriate, is part of that job.

The real answer is "ok, that is a bad idea for XYZ reasons, what problem are you trying to solve? is there another way we can help you solve for it? Maybe a cheap replica would work for you?"

And look, i have nothing to go off but the justifications and choice of words in your replies. But in my experience this attitude of "high priest protecting the gates of production from barbarians(company staff)" is strongly correlated with obstructionist IT departments that everyone resents and tries to work around, and chokes the company. Resulting in the creation of the shadow IT mentioned in many other replies - because IT doesnt serve the customer needs of the employees. You might not care , or see that as your job, but thats exactly the problem that so many threads on this post are discussing.

> The real answer is "ok, that is a bad idea for XYZ reasons, what problem are you trying to solve?

That's the answer that I give immediately after the "No."

Look, I get what you are saying. I am not trying to keep people away from the capabilities they need to improve how the whole show works. The problem is, what people in my business "guard" are often complex, critical systems, which themselves don't always meet the standards that their "guardians" would like to implement (just ask about legacy software :D). We have to say "No." and we have to enforce standards and procedures.

Because there are a lot of really clever people around in tech, and clever people love to tinker. And that's wonderful! That's the entire spirit that got me into this biz! Take a problem, and build a solution.

But things have to work. And they have to work tomorrow, and 2 years from now. And they have to be safe, they have to be compliant with a gazillion regulations, they have to pass audits. They have to be patched, they have to be maintained. And all that still needs to happen even after the guy who built them leaves the company. And they have to work for many many many people who are not tinkerers, who just want to click a button on their phones, and rightfully expect the whole shebang behind that button to "just work".

That's why there have to be people who say "No." from time to time.

If that happens indiscriminately, and without a care about why these clever people tinker up their solutions, then that's not good, I fully agree.

Speaking as someone in offensive security, you and people like you are the reason companies don't get completely ruined when the inevitable happens. Principled IT is often overlooked but the biggest factor in my experience. Thank you for taking your responsibility to your customers seriously. I'm honestly astounded at how many people in this thread resent IT so much, but it certainly makes my engagements easier.

I don't think anyone on here honestly hates or even truly resents good IT doing hard work and trying to keep the systems safe in good faith. Everyone knows it's a thankless job and that some frustrations will exist. I don't expect every single IT person to approve everything I need immediately. Things like cybersecurity are obviously important.

They're talking about those that play all the management games and add little if any value. Those that have a title like Senior Developer who can't write basic code. Those that can't understand the basics of their jobs and can't support the systems they're supposed to. Sure they might make the overall company more secure as a result of their behavior, but it's a byproduct and not the intent. It makes being a business SME a living hell as there is always so much friction to just doing anything on your computer. We're probably all venting a bit collectively.

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

When I started work at a higher-level IT job where I can start saying yes or no like this, I wanted to say yes to everyone and never be that guy blocking people. I still end up saying no very frequently because people will not want to think about anything not relevant to their specific use case at all (how are you authenticating/who controls the app/what happens when you/the owner leave?/is there a project plan?).

I can't count the number of integrations/projects I've already dropped because I asked a few follow-up questions and never got a response. Any business that actually wants to follow the law and reduce the risk of massive data loss or other embarrassing cyber event needs to screen things, ask questions, and sometimes prevent one very smart person from setting up an undocumented rube goldberg machine that will drag down an entire team if they leave and it breaks.

The "bureaucracy of IT" is driven by legal, compliance, and security reasons. The reason why small companies are "nimble" is they are flying by the seat of their pants and one investigation/ransomware/insider threat away from ruin.

Not saying that isn't normal (been there, done that... thanks FINRA), but that's the reason.

Yep.

The core data should sit behind some kind of service that enforces legal, compliance, and security policies.

Then tools that access the data in a compliant way should be given a lot of flexibility for what tech stacks they use to process the data.

What bothers me as practically 100% shadow IT worker (to the point of buying my own devices and internet connections with my own money) is that IT-departments don't care about the users, usability or productivity almost at all (and security people are especially bad at this). And that a lot of IT people frankly don't understand IT very much.

Without shadowing it, I couldn't get anything done. I have to install new (open source) software or packages more or less every day, but IT would expect me to wait for a week for some bureaucracy for each package. IT fights me getting a computer with a specific GPU although it is required to use a library that I need. IT forces a reboot of my laptop in a middle of a conference presentation. IT blocks me from sending Python source code files over email. IT makes my computer boot to take ten minutes. IT forces me to use OneDrive that often simply doesn't work.

Maybe the abomination is not the private projects? Maybe it's the systems architecture?

I’m loving this thread. I feel like Hunter S Thompson reading the gonzo review: “okay, that’s what I do. Shadow IT.”

And the software that is installed is always 4+ years out of date. But oddly there are no “security concerns” about running 4 year old conda install that has had zero updates ever.

I don't think it's often even about actual security concerns as such but rather about following "best practices" (i.e. what some company sells) so nobody in the org can be blamed when the security fails.

This happens in physical security as well. It's rather common to have door accesses set up so that a person may not have access to go through a door, but can access both sides of the door from other routes. But there was a door-based access policy so nobody is to blame.

Sadly the main concern in many/most organizations is to avoid getting blamed for bad things, so rather than actually trying to prevent bad things, a lot of effort is used to just dissipate the responsibility away.

> IT forces me to use OneDrive that often simply doesn't work.

Also happens to me. OneDrive sucks. It can't even generate proper zip files. Any zip files over 2GB or so I download from it shows as corrupted when I try to extract under linux. IIRC is because OneDrive puts some invalid flags in the files.

Oftentimes the download just is left short. And I recall it not even giving error, just sending a part of the file, which of course is a corrupted archive.

OneDrive sucks and organizational structures that buy OneDrive sucks and the company that produces it really sucks.

You make very good points, but I think you miss the mark on shutting down intent.

You wouldn’t ignore an excel produced by a competent ceo or cfo (those that know all the shortcuts), so why, instead of helping ppl refactor and release their work properly, you gaslight them as incompetent just because they are not IT?

Security is the non-negotiable.

If they want to play with whatever tech tools to get their job done, have at it. They can ask for help when they really need it.

But if they are taking short cuts with the security of the data, that needs to be cracked down on immediately, as they are putting the entire company in jeopardy.

If security is non-negotiable, the only solution is to destroy the data so that it can't be ever recovered by anybody. Or even better not having any data in the first place.

Securing some data is very important. Some data indeed shouldn't exist in the first place. But for a lot of data it matters very little. Most security breaches have rather mild consequences.

Treating all data as megatopsecret and all security breaches as end of the company produces not only unproductive systems, but bad security.

Well, I work for a company that processes Private Health Information, so a breach is a potential existential threat.

Breach to private health information that can be linked to an individual more exactly? Is this kind of information all around the organization's computers?

Something that seems often forgotten in these discussions is that it's not just putting the entire company in jeopardy, but the customers, clients, and vendors as well. Security seems to be some magical obstructive force to these people because any concerns besides their own convenience are purely abstract. Well, ask anyone who's had their identity stolen from the hundreds of breaches in the last couple of years if that concern is abstract. Staff who can't see past their own desk to understand the role of security are a serious danger to society.

To be fair, as an SME, I do have documentation and an architectural overview. In my experience when I have provided these, they have been ignored anyway. I do not have access to git, because why would IT give me something useful? I think many users would use git purely as a VCS if they had access, but nope...

It shouldn't be a spreadsheet. The IT departments should democratise the tools which devs use, so even end-users can use modern tools for the job at hand. Then popping a user-made tool into your processing pipeline would be fair enough, and code can be collaboratively maintained. In the end, just as IT wouldn't want SMEs making changes without their knowledge, SMEs wouldn't want IT changing their core system without their knowledge either.

In my opinion, the more people who know and understand the core systems, the better.

Edit: for what it's worth, I do use github (https://github.com/sancarn/stdVBA), but you won't see nearly any versions of any corporate codebases, why? Git doesn't work great with VBA spreadsheets at all. I'm not going through a 10 step process to upload the updated file to the github repo every time I update a macro in a spreadsheet. This is why on-board git is important.

Someone needs to build a wasm interpreter in VBA.

Then we can write programs in Go/Rust/etc and run them in office or wherever.

VBA is to corporate environments what JavaScript is to the web.

VBA can call executables, but I can see some places locking things down such that that would not fly.

Honestly, this is something I've been wanting to do for a while... Last I looked though, I couldn't find many good resources on how WASI (or the byte code) worked... And VBA being single threaded might make things difficult too...

I have already built my own code interpreter in VBA to make Lambda syntax possible: https://github.com/sancarn/stdVBA/blob/master/src/stdLambda.... so I know it's definitely possible, just haven't figured out WASM yet...

One of my first full time software engineering jobs was working on the trading floor of a bank, sitting next to the currency traders.

I was hired by the head of Market Risk Management, whose job was to make sure the bank didn't lose too much money on any given day. He hired me because he did not trust the officially approved IT department to write the code to implement his algorithms. One example: they got something wrong because they did not understand mathematical precedence operations, like multiplication over addition.

So one need was to get all the trades as input to the market risk calculations. This was early 2000s, and I installed Apache with Perl CGI on a PC under the desk, and created a little app for the traders to enter trades and track their positions. The traders started favoring this to the official IT solution because it was easier to use and see their positions.

All of this to say, yes, figuring out how to bypass IT is an important function in a lot of corporate environments.

And back to Excel: the traders used it for all of their calculations and simulations. We tried to work with them by giving them tools that plugged into Excel so they could leverage it along with what they were already doing.

Was also working in trading floor support (as IT) in the early 2000s. We actually even had proper Excel add-ins (programmed in C++) that provided special functions and also connected to backend systems as far as I remember.

> We tried to work with them by giving them tools that plugged into Excel

This is probably one of the best ideas out there. If companies built a bunch of Excel Addins to leverage business systems, that would be revolutionary for many businesses.

Because there was no good alternative until recently. The future is with the new "add-ins" model: https://learn.microsoft.com/en-us/office/dev/add-ins/overvie... Say what you will about typescript, but at least it's better than VBA.

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.

EDIT: I did not see the script-lab mention at the end of your comment.

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).

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...

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.

Future civilizations will marvel at the intricate grandeur of our Excel spreadsheets

They won't be able to read our media, and if so they won't be able to decode the excel format.

Excel format is just a zipped collection of XML files

That's the new one. The old one is 90% direct dumps of C++ structs.

> if you think it's pretty, it's because you are inexperienced

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.

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.

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).

I mean I think it's fair to look askance at any environment that includes misfeatures like `On Error Resume Next`.

You mean like most shell scripts? VBA is no different from Bash here.

I wouldn’t consider that much of a defense!

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.

hey, I deliberately use that all the time lol

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.

[1] https://stackoverflow.com/questions/20652409/using-vba-to-de...

[2] https://stackoverflow.com/questions/29832281/vba-range-funct...

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.

I have invested copious amounts of time into building VBA libraries (https://github.com/sancarn/stdVBA) and as much as I love vba there are some highly limiting actual problems with VBA (https://sancarn.github.io/vba-articles/issues-with-vba.html) which really hurt the language. But yes a large portion of the hate VBA gets is from the state of VBA projects (https://sancarn.github.io/vba-articles/why-is-vba-most-dread...)

Thanks for your detailed write ups!

I find myself thinking about Excel, and spreadsheets (electronic tables) as a whole, and the fact than only few people outside of it actually understand how oldies got it really well with reactive-functional programming in the spreadsheet language. It is what React/Angular is struggling to get right with more than dozen releases so far.

Also so many people fail to understand why the spreadsheet is so convenient to end users, and as a result of this failure - provide sub-par UIs which actually make thing more difficult, not easier.

Sometimes one has to make a step back and understand that grannies did things right, even though they didn't have graphical UI - business was still running back in these early days, and actually what businesses need for most of the time is tabular view with options to do reactive functional calculations on top of it. Ask your SME friend and he'll confirm it.

So much of the effort put into web development is in presentation. You're right - if you just need the data, it's hard to improve on the spreadsheet.

Excel is arguably the best end user IDE ever invented.

No choice. I've learn VBA and Powershell only because they are the only things I have access to at work that allows the computer to work for me rather than the other way round. In the past I've run autohotkey portably to automate corporate systems that seem designed to maximize the number of clicks to get anything done, and for "glueing" systems that don't speak together. I'll learn anything that makes my life easier.

I love PowerShell, it’s a fantastic language.

I agree, it's massively undervalued. Sure the syntax isn't for everyone but out of the box it has much of the stuff in it that on Linux I'd be reaching for awk, jq, etc. Not to mention being able to pull in stuff from .net and other windows things. I can't fault it.

Apparently it's so ubiquitous that you don't even need to say what it is, every just knows.

I looked it up -- VBA=Visual Basic for Applications.

https://en.wikipedia.org/wiki/Visual_Basic_for_Applications

I'd argue that developers beyond a certain age are as guaranteed to have come into contact with VBA as with HTML/JS.

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.

Just recently we had a meeting with a client that demoed their current business workflow process. One of our client's very clever business users created a hacky but also amazing VBA solution for sending emails, assigning work, creating reports, etc. It works just the way they want it, and our team was there to replace it. Made me sad, because our solution will cost a fortune, won't do half of what this guy's solution does, involves a third party SASS solution with a very limited API, and so it will cut him out of his ability to customize it.

An on-prem clone of Airtable would be a much better replacement for 99% of software that is written in Excel VBA, but:

- you have to buy it and justify the expense, but your company already pays for Excel - if it's FOSS, then your cybersecurity will want to scan it and demand you fix every single "critical" CVE, but they don't dare block the use of Excel - you have to run it on a server, so you need to buy a server as well, but Excel runs on desktop machines and you probably already have a network share, too - the server will probably be locked down tight and have no access to other servers, while Excel running on desktop machines has the level of access of the user running it - the IT will try to lock down the server-side installation and grant you as little rights as possible (please submit an enhancement ticket if you need to change the data type of the column), but they can't tell you what you can't do in VBA

I'm not an SME, I work in IT myself, but the amount of self-inflicted hurdles in modern enterprises is staggering. I run a large team that develops ETL jobs, and I needed a database to cross-reference tickets vs jobs vs source systems vs releases vs subteams, because of course no existing system knows all this. Ended up running this in Excel with some Powershell scripts: one to scrape JIRA, another to scrape Airflow, the other to access the target database under my personal account and download the list of tables. Still easier that doing it by the book.

I've been in manufacturing for ~13 years across 5 different organizations. There'll inevitably come up a spreadsheet use case where you'll have to execute a well defined series of steps on a recurring basis (and some of these steps may not be possible using the built-in functionality alone). Which is where VBA comes in. I've sometimes wondered what I would do in the absence of VBA and come to the conclusion that

a) I'd be forced to complete a painstaking task "manually", and likely committing the occasional error in the process, not to mention all the time I'd have "wasted"

b) In the case of "optional" tasks (whatever that may mean) I'd have had to give up on whatever functionality/feature VBA enables and some level of detail/sophistication/speed would thereby be lost.

To get a bit more concrete in terms of use cases, any spreadsheet task involving a bill of materials or having to do with stock management is probably ripe for some VBA enhancement. I am aware that it is looked down upon by some, but advising against VBA in favor of Python or some other "proper" tool that calls for an IDE is a bit like telling someone who wants to take up home cooking to get a fancy Japanese chef's knife set plus a sharpener instead of the good old all-purpose knife he is certain to have lying around.

More like: Obtaining a japanese chef's knife, getting a permit to carry it and forbid your friends to use it.

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.

From an IT security perspective, maybe that's insane.

From a job security perspective it makes a lot of sense.

That's the “Nobody ever gets fired for buying IBM” idea.

The most important part about OneDrive is it offers a CYA level of monitoring and control.

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.

Have you considered not working in that role?

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.

We as a group are considering work to rule.

But, no, because I otherwise love my job, also where would I go?

> 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.

Because MS took a decade to get python into excel…and then promptly implemented it in a cloud fashion that sends confidential data out to their servers so I can’t fkin use it for work

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.

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?

yes, you can download community edition

I do RPA from VBA personally using IAccessiblity. See stdAcc (https://github.com/sancarn/stdVBA/blob/master/src/stdAcc.cls) and an example (https://github.com/sancarn/stdVBA-examples/tree/main/Example...). You are basically doing the same as what you'd do in UiPath, by the looks of things. Just a slightly different flow.

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…).

16 years ago I developed some apps with MS Access that interacted with MS Outlook. It was rather easy given the integrated IDE, debugger and being able to create forms and call a CLI app (zip). I contemplated suggesting my managers to build something serious with some web tech -- also ubiquitous and easy to deploy PHP -- and it looked a lot more complicated right away!

Later, I used to be Django developer, and I think it would be even harder to deploy and maintain.

The only inconvenience I recall was some functions had tedious API, arrays/lists were hard, had to be created like kinda Collection.new(...).

> The only inconvenience I recall was some functions had tedious API, arrays/lists were hard, had to be created like kinda Collection.new(...).

Imagine what people would do if VBA was a better language and had a better IDE that wouldn't scream at you every time it found a syntax error in your code-in-progress.

I worked for large companies as an excel modeller some years ago. Excel was my world.

VBA is built in. That is the reason.

Its the same reason emacs users use elisp.

Because of microsoft office ( particularly excel ). It's just as simple as that. I remember years ago people thought that google's free office/spreadsheet offering was going to be the end of microsoft office/excel. I remember having a good laugh back then. The business world, especially finance, runs on microsoft office. I just don't see it changing anytime soon. It's amazing how entrenched it is.

I used it recently because it's the built-in scripting option for Outlook. I found myself writing the same emails over and over again, so I automated the writing with VBA, using input prompts for the variable parts. I'm not familiar with the available objects, so leaned heavily on chat gpt for that. Associated the script with a macro, then linked to a menu button and now I can quickly compose an email with a shortcut key.

> Why do people use VBA?

Good article.

What it doesn't mention is the versatility and value the combination of VBA and the various MS Office applications brings to the table for small and medium businesses.

Translation: VBA, as a tools for SMB's, can make them money.

VBA is often discusses in terms of Excel. However, it is available --and very useful-- across the entire MS Office suite.

Over the years we have used VBA for applications ranging from engineering to business. From automated code generation (generate Verilog FPGA code based on easy-to-maintain data entered into Excel) to financial analysis and projections (example, Bass Diffusion Model product evaluation).

One of the most fun applications I remember was using VBA to create a training application for dealers and customers using PowerPoint. We created a full simulation of this device (control panel with buttons and an LCD display), using VBA to run the show. This was super easy to distribute to our dealers, required no installation and everyone could run it. Of course, today it would make more sense to build such a thing as a web app.

Still, VBA makes such things accessible to lots of people. You can use it with Excel, Word, Access, PowerPoint, etc. As a tool, it is useful and convenient. Most people could not care less about the, often pedantic, opinion us engineering types can have about such things.

As a software engineer I wish something like Python was a first-class citizen across the MS Office suite. I know they are slowly making this happen. I haven't looked into it for a while. It seems MS wants you to have a subscription to Office 360, which is a nonstarter as far as I am concerned. I could be wrong.

I read essays from 40 years ago, about what the office of the future could and should look like.

There always seemed to be an assumption that you would empower the user with tools.

I suppose VBA does that to an extent, but it seems like we haven't moved the idea forward in 30 years.

I'd like to say it's because it's 'good enough', I suspect it's more a hold over from an earlier time that hasn't been eradicated yet.

There is at least one semiconductor test platform which uses Excel workbooks as the programming interface. Automotive chips going in to new vehicles today are being tested for functionality using Office 2003 and VBA

I work as an Equipment Reliability Engineer at a nuclear power station. The only programming we are allowed to use is Microsoft Excel Macros, nothing else. There’s a reason why VBA is still alive.

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

This is all beyond the scope of VBA, but:

Don't get so emotionally invested in tools because they're just tools at the end of the day.

The business doesn't care what tools are used so long as they do their job.

Also, knowing how to navigate a convoluted tooling systems ensures your job security, so why are you complaining again?

> so why are you complaining again?

Because a job that would take 15 minutes turns into a 3 hour task. It might surprise you but some people actually enjoy their job and ticking off tasks :)

Many VBA people are just SMEs who needed to spice their work with a bit of script so they learned one thing they had immediately available to them that could be used to solve their problem.

Many of these people do not think about themselves as developers. They have primary responsibilities outside of IT structures which usually means that "more professional" tools are not available to them.

They invested substantial amount of effort to learn the language and are locked into the platform because everything they know about programming, every tip, every trick, every solution to every problem is all about Windows, Excel, VBA, etc. and they would have to essentially start from scratch if they wanted to do anything else like Python.

> they would have to essentially start from scratch if they wanted to do anything else like Python

I do tend to disagree here. It really depends how invested they are with VBA. Many VBA skills are highly transferrable to Python and other high level programming languages.

I was fortunate to have experience with multiple languages from the start, but many of my colleagues have programmed in other languages other than VBA after learning VBA only to begin with. From Ruby to Python and beyond.

VBA has a few interesting things in common with Javascript:

1. The development environment is already installed.

2. The platform does a lot. Being able to program using Office components or program using browser components gives the programmer a lot to work with.

3. The platform extends into a "real" programming environment - VBA is a gateway drug to C# and all the other MS developer tools. Just like learning JS in the browser eventually turns into, can use my JS skills for writing other code on my machine?

Programmable platforms have historically been really important to adoption and longevity in the enterprise. The emergence of REST APIs as features on many web apps fills a lot of this gap for SaaS.

I work with accountants. An average accountant is 50+. If they learned something like VBA or used some old friend who created automation, they stick to it. You can't explain what JS is to them. VBA just works.

It's all I know how to use lol I'm not kidding. As a financial analyst I'm highly productive with VBA, and don't know anything else (I can do a little C++)

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

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.

Microsoft added python and they have office scripts that run JS.

But the functionality is locked to higher tiers of 365 accounts. So I guess VBA is still the king.

Why? the obvious answer is that businesses still make heavy use of Office apps like Excel, and VBA allows for extending/customizing files-as-micro-applications.

Important to note that office apps and Macro-tools were essential before the Web and Mobile apps became popular. Businesses have to carefully balance between Adopting the Newest tech/fad and Growing their business, and Staffing/skills.

PowerShell was (and still is) the go to for me in any M$FT shop. People like to group VBA and PowerShell together but I am amazed by the vibrancy of the PowerShell community and the deep history it holds. PowerShell releases are frequent and I'm excited about what I continue to see and hear being developed.

WTF!?

Everyone needs to seriously look at DDD again. You want a product?

- Small team composed of a few developers, one or two SMEs, one or two DEVOPS.

- SMEs teach the devs the domain language. Explain requirements in gherkin language or equivalent.

- Devops hand hold the developers to get it into production. (Devops guy can probably be split between 2-3 teams).

Many SMEs want to work their problem, not code. You're helping them.

VBA is anti-technology. There is no version control, there are no tests, automated integration tests? HAH!

*PS: "You build it you own it" Is wrong. You need a small "meta-programming" team that makes sure the teams have the tools they need to own production without their brains exploding. Perhaps these meta-programming teams can be split among a few corporations - as you don't really need them there all the time.

Is VBA insecure, not usable or not Turing complete? Putting up whole Postgres (or SQLite) with a programming language on top (Python, Ruby or something else) is maybe more expensive, has more and different operational concerns and might not be necessary at certain scales?

Someone works on an Excel file every day and reads an article about how they can make their job easier with automation. The language and IDE are built in so it's easy to get started. It's also easy to distribute since anyone with Office can run it.

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.

It's all because of change control. The moment you have to deal with it as a non central IT dev or upskilled BA, you hate the experience and then start getting creative with the tools you've got. And then 20 years happens.

It's wild to think of how many general-purpose programming environments Excel now has access too – VBA, Office.js, and soon Python. Like Windows, Office seems to keep on accreting.

Upon reading answers, I wonder how much, if any, market is there for VBA only (self bootstrapped?) tools that bring modern development practices into it (version control, testing)

If you are in a locked down corporate dragnet. You want to write some code, automate something or compute something. VBA may be what you have available to you.

Also at this point, probably everything that can be done in VBA has been done already, so there is definitely a code snippet out there on a forum somewhere for whatever it is you are trying to do.

Say no to clouds and dependency hell, return to VBA

The clean solution is quite simple - if you don’t want non IT people writing software then buy a product to solve the issue or hire some experienced professional developers. The problem is that a lot of time the clean solution isn’t feasible because of constraints or culture and you end up with something in the “dirty” solution end of spectrum.

Because it's cheaper/easier than replatforming for many businesses and there are still people that know it and are hireable

I always find these discussions fascinating because, despite being a developer for like 25 years, I have absolutely no idea what you guys are talking about. I know that somehow business is all run on xls, but practically it’s hard to understand what that means. Something something Salesforce

You can use JS in basically all the places you can use VBA right? It's available in every browser.

The language, the object model, and the IDE combine for a fun, highly productive programming environment.

Cause you can open, write and deploy the code right from the document itself. There is no external tools needed. Everything is baked in and works. I hate VBA with all of my hearts, but I'm going to use the tools available and with less resistance.

Path of least resistance.

You may as well ask, why do people still use JavaScript?

I agree mostly with the article, VBA doesn't have to get past the pointy haired bosses or the purchasing and compliance departments!

Yes. Excel macros mostly these days. Used to do Access database projects but they are slowly being phased out by Power BI.

Because Excel is the highest velocity application development tool. It's total shit after the first week but it's very very quick prior to that.

Excel is by far the most used no-code/low-code platform, IMO.

Excel is purely functional programming for the masses and I mean that as a good thing.

So far it is still the most convenient tool to automate MS Office, and you can do a lot with COM.

I think its really surprising how so many corporate IT departments are awful at enabling employees. You can see it a lot in /r/sysadmin where they complain how they got some crappy solution working "great" but their users refuse to use it. The average employee at a F500 company hates their IT department. All they do from their perspective is make it harder to do their work. This is why you see all of these VBA scripts.

Because it's basically VB6

Vb6 at least had "on error goto".

Vba has "on error goto".

googles

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 real "holy shit" moment in that link is the fact that this org was still all-in on Lotus Notes well past the turn of the century.

The writing was absolutely on the wall about Notes well before the Y2K panic. Staying on that platform when the world was passing you by, even if you couldn't get exactly the same functions in Outlook/Exchange or whatever else you slotted in, was foolish and honestly constitutes professional malpractice for whomever made that call.

do we have another language can easily control Microsoft office? I mean, it is possible to perform analysis by another tool/programming language, but what if we need to control PowerPoint?

Actually, every language in theory. At least any language which can use COM APIs can interact with PowerPoint. Ruby, Python, NodeJS, C, C++, C#, Java, Rust, ... Pretty much you name it and it can control powerpoint unless it is sandboxed.

thank you,I did know this!

Because it is just awesome!

small trivia: long ago I had to massage a db dump made into excel files, so I hacked up a DSL to write business rules validation / transformation, all in VBA (where I also learned that the object model included some cute transparent delegation subtype thing). C-suite decided we needed more speed, so they brought up two seasoned engineers, one of them all about .NET interop. But doing Office logic outside of VBA/Office brings a lot of pain (excel embeds type in formatting IIRC) so he ended up recreating a mini excel object, still hit performance issues and ejected himself from the project. tl;dr I was surprised how "pragmatic" it ended up.

Because it's fairly simple and it works.

I'm glad I've worked primarily in early SV startups. None of this BS to deal with.

With the interconnected nature of modern life, that BS probably touches an important part of your life

Why BS? It appears to be incredibly useful for a lot of people. You don't seem to have had a long career, let me tell you that it pays to keep an open mind.

Please, please Microsoft start pushing F# as an alternative to VBA!

Microsoft is pushing Python for Excel and JS for add-ins.

Problem is you edit Python code inside the cells, there is no IDE for it (from what I've seen, haven't tried it yet)

Inertia

– It’s built in.

– The IDE is built in.

– The syntax is beginner-friendly.

– It’s stable and doesn’t change every six month.

– It’s well-documented.

– No build steps, it just runs, and fast.

– It’s resource-efficient (CPU, RAM).

– You can easily create dialogs and forms using the built-in visual GUI builder.

– You can break into the built-in debugger from your Office document.

– If you want to get fancy, it has interfaces and classes.

– You can call any win32 function and use any COM object.

I think I just read the best reply ever

But typescript has "using" now? How can VBA still be so popular?

VBA supports automatic cleanup via Class_Terminate. :)

All valid points, but I think the biggest reason is:

- It's what's available.

There is a bit baked into this statement which the article breaks down further:

- Companies won't approve anything else in the hands of ordinary users

- Companies' developers are too busy with too high priority items

And some things not mentioned in the article are also baked in:

- Even when developers get around to a project that could replace VBA, they don't understand the project, underestimate the time and resources required, and deliver a subpar product as a result

- Companies lay off people doing work IT and developers can't be bothered to support with no real plan other than overburdening the remaining ordinary users with extraordinary problems

VBA serves an awesome niche. I once built an awesome simulator that did some pretty complex optimization stuff. The main sheet had input cells for the user, a couple of radio buttons for toggling certain features, and a button to fire off the built-in Excel solver plugin and pull certain values from that process and display it all on a GUI on the first sheet. It took me just a couple of days despite zero VBA experience and most importantly I could send it to all of our customers who then had a full simulator that they could play with alongside their engineers. They didn't have to install anything (just click a button within excel to add a plug-in). Simple simple.

It might be niche for HN, but in muggle world its enormously popular.

Heh, muggle world is the niche, but that niche is arguably bigger than all of the dedicated software engineer niches.

I read a stat somewhere that there are at least an order of magnitude more Excel "programmers" in the world than all pro developers in other languages combined.

And looking at how much work is involved in even just setting up a JavaScript frontend, I’m pretty sure the Excel programmers are yet another order of magnitude more productive than the latter.

Which is kinda sad actually.

I agree. I worked as an analyst where due to security policies we only had VBA. There was application which consisted of an Access DB for a "front end" (containing an interface to input data, generate reports in ppt/excel, etc) which connected to another access database as the "back end" and an admin console to pull in data from a SQL database. I had a lot of fun maintaining it.

Yeah I know MS Access & Co. have kind of a bad rap, but if I look at what we were able to actually achieve for the business/users with those tools, it’s really painful to see how little progress we have made in the last 20 years or so.

At that point you're abusing the word niche

Thus the "Heh".

One of the bigger things I've ever built was a massive set of tooling based upon Excel + VBA + proprietary API. The old days ('00s). The best thing of developing in VBA was that the API was properly documented. Any function had documentation via VBA, plus via the primary tooling, plus via big old books. On top of that I had a premium support line to the developers of the API as I seemingly was one of the few worldwide actually using it. Heck, even a professor that showed up in the documentation was kind enough to help me for a bit. A shitty, but high-paid job for 10 FTE was reduced to a one-person show where the main job was adding intellect, not pay-for-clicks. Probably still is a one-person show. It's the one thing Python and R never achieved for me (note - I am lowly skiled at his): object oriented programming that helped, not hindered.

> button to fire off the built-in Excel solver plugin and pull certain values

That's pretty cool.

VBA is terrific for glue code. Back in the day, before the Internet opened up the security hellmouth, ActiveX was pretty great for use cases like yours.

Early '90s, I made an in-house cost estimation app using Access 2000. It'd extract data from our MicroStation (belch!) CAD drawings to generate budgets and bill of materials. Huge time saver.

Here's a modern example:

https://softwareconnect.com/construction-management/planswif...

Cost estimation apps rely on a database of SKUs, assemblies, etc. Every entry can have dependencies, equations, etc. Like "for every 10ft of X, add 1 widget Y".

Super easy to implement with dynamic languages like LISP, where data can be code (macros). Not something Visual Basic is known for. My "one cool trick" was using VBA's built-in "eval" function equivalent.

In the late 90s I worked for a small network solutions company. Cable infrastructure, lan, wan, email/file/printer servers. Most of our work was campus sized networks. Military bases, hospitals, corporate campuses, etc. We did all our drawings in Visio which hadn't yet been purchased by Microsoft. Visio added VBA support around 97 or 98. As soon as they did myself and the other network engineer on staff, we didn't have any developers, wrote a similar system in our "spare time" to extract a bill of materials and generate a cost estimate. Included everything down to the number of rack screws.

I ended up leaving about 3 months after it was done but they continued to use it for a few years until it was replaced by a COTS system.

What a small world. My wife is a MicroStation guru (civil engineer), but not a programmer. I've often wondered about how to make CAD work more productive.

What would have happened if VBScript had won the DHTML wars?

We’d have VBTypeScript now. ;)

It seems like the bigger concern would be all the important data they have in Lotus Notes, as well as some of the other systems mentioned.

VBA seems like a solid choice compared to where they have their data being stored.

- It allows a user to easily extend their current work environment (i.e. MS Office applications). Other languages and IDEs could do that, too, but not so easily. This allows users to somewhat more gracefully extend their documents/data and knowledge instead of starting all over again with a "proper" programming language/environment.

- I would not call it fast or resource-efficient, but fast enough and efficient enough for most unsophisticated purposes.

- No red tape for installation, IT cannot (easily) disable it, isn't an extra line item on any bill

It’s fast and resource-efficient in that it doesn’t need to start up a comprehensive extra runtime environment like the CLR, and doesn’t need double the memory like GC languages tend to do, and is faster than many interpreted languages due to its P-code and its tight native integration, not needing an additional interop layer to communicate with its environment.

> – No build steps, it just runs, and fast.

Huh, fast? Sure, it's fast enough for its use cases, but it is not fast.

Is it still something you can code with your mouse?

I remember having to deal with it on my first job because the team knew nothing about coding. They had “recorded” macros by clicking around and never seeing a line of code. It was incredibly brittle: any change to the table, even adding a comment, would break it, but it allowed them to automate a task.

You can still record VBA macros in MS Office.

This sort of programming is very powerful. It lets people who have no idea what big O is or how to make a fully automated CI/CD system with testing and all of the bells and whistles just get something done. Just to make a dialog and automate something. That type of programing is very powerful. Many times a huge mess to clean up but very powerful and gets things done. We sometimes lose sight of that. Computers are to get things done. Getting rid of the moat we have build could be very powerful.

Yes.

In an ideal world this is how first draft of software would be done. And professional software engineers only come in when it's time to make it secure, fast, less brittle, scalable, available to more users, etc.

Like finding the screen that takes forever to load because there's a hidden O(n^2) in there and replacing it with an O(n log n), etc.

It’s also the ultimate discoverability tool.

People struggle with not knowing how to describe a task they can do but not with code. The record gets you very close very quickly. If you’re fluent in adjusting selection logic you’re usually going to have it pretty easy.

We used some weird testing tool from HP I think. It could record you clicking around in an application and generate a VBScript. You could then go in a modify the generated code, add parameters and fix the brittleness. It meant that you could get a ton of coding done in a very short about of time.

Macros recording a series of mouse clicks: That's what I did when I was a finance intern many moons ago. Knew nothing about programming at the time.

And it's absolutely baked into Microsoft Excel.

All true, but the fascinating thing about the article is that it's the Subject Matter Experts demanding to use something other than VBA to be more productive, and the IT people saying no VBA is the only thing allowed because...reasons.

Often those SMEs are data folks, and if they are allowed to develop in $TOOL_DU_JOUR, and that app becomes business critical, the IT folks will be stuck supporting it. And if that thing doesn't become well supported, or it isn't easy to find someone who can support it, that's a problem.

I've run into this quite a bit at my workplace. Some business group writes an app in Excel using VBA + an add-in and it becomes the core part of some workflow. But IT didn't know about it, nor did they know about the (for example) 32-bit ancient Excel add-in that it requires, which then breaks when an Office upgrade happens...

Now IT is stuck where a routine upgrade broke some weird edge case thing and needs to maintain a downlevel version of Office for a small group until they can re-develop their business-critical tool in something else.

Use-known-stuff rules up front -- in this case which may well be VBA -- alleviate a lot of these long-term problems.

Or just make it known that if you want support, here are the languages our IT staff know and can help you with.

Otherwise, you're on your own.

That's a great way to go, but unfortunately the reality is often the folks go off and write stuff in the non-supported way. The business gets dependent on it, and then years later -- because IT is there to support the company overall -- IT gets stuck supporting it even if long ago they said they wouldn't.

Sure, you can have an internal political fight, but it only goes so far when everyone there is supposed to be working for the company. So while there'll be strong incentive to move to something else, there's still a need to keep it working in the mean time.

If you can prevent this up front it's better all around.

I've been on both sides of that kind of situation, and IMO when an IT department gets that calcified, it's a sign that their days (or the company's days) are numbered. Either the non-IT staff will get fed up and replace IT, or the non-IT staff won't be able to get their work done efficiently and the company will fail.

The non-IT staff build those things because they've identified a way for the company to improve itself, but the process for getting what they want from IT is too expensive/onerous, IT has delivered disappointing results too many times, etc. Find a way to meet in the middle, or the non-IT staff are just going to build them in their own shadow cloud account and eventually make the IT department redundant.

To me, it's a pressure release/ROI question.

There are tons of incredibly beneficial computing improvements that it doesn't make sense to spend IT resources on, because there are better ROI opportunities for them to focus on.

But! That doesn't mean the things they can't handle aren't valuable.

My preferred method is (1) require documentation (using a standard template) on all processes implemented by non-IT (what it does, how it does it, what value it delivers to the company, what the fallback manual process is, etc.), (2) store these process docs in a centralized location, which then becomes IT's backlog, and (3) any change control / regulatory requirements.

The grand bargain is then:

   - Anyone is authorized to improve processes, if they generate the documentation

   - IT has the authority to force decommissioned of an existing solution *after* they've delivered a working replacement

That seems to align everyone's incentives more clearly on "the good of the company."

True, but that's kind of like Twitter having to go back and rewrite their Ruby services in Scala down the line.

Getting a viable Minimum Viable Product is all important. If a non-developer can hack that together in Excel + VBA, more power to them.

Going back and rewriting it in a Proper Programming Language after the fact is an acceptable cost, once you have something solving an actual business need.

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

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

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

In our case (and in the article), we wanted to collaborate with IT, code along-side them in a technology of their choosing. However IT explicitely told us that they would NOT allow us to do any coding whatsoever.

I, as an SME, am fully happy coding in C#, Java, Rust, whatever! As long as the language is turin complete, is pro-code and versatile enough, I'm all ready to go.

Do note that IT actively chose to develop the solution in Microsoft PowerApps, despite my advising that the solution would be better suited as a web app.

Yes and:

- embedded database via Access Data Objects (ADO).

There's still no modern equivalent. The ADO notion was lost in the transition from workgroup (file sharing) to client/server (ODBC).

ORMs, ActiveRecords, builders (eg JOOQ), templates, etc. are all partial solutions. Abstractions with sharp edges and traps.

(Yes, I'm working on it.)

But unfortunately microsoft didn’t invest into VBA in 20 years, other than keeping the light on. And it lacks so many modern features.

There was an attempt at a .net version of VBA (would have worked the same way, with a mini visual studio embedded in Office), called VSTA. But it was killed. So the cattle (business users) is stuck with 1990s technology.

Maybe that turned out to be more of a strength than a weakness?

A strength for whom? Not the end user.

It depends on the user. I use the basic VBA components to build my own features (exactly how I want them). I'm happy that Microsoft isn't meddling.

The lack of basic functionalities like generics, sort functions, lambdas, is a hinderance if you got a taste of .net.

It's a hindrance to -us- but I suspect for code that's designed to be passed from SME to SME that -not- having access to features so you have to write things the ugly stupid way ... may actually be an advantage, since the next SME along only has to have a sufficient tolerance for 'ugly' rather than an understanding of the features you and I would both want.

Sorting is fine in VBA. But, I admit, it'd be sweet to have lambdas.

Here’s another: - ChatGPT is well-trained on VBA.

I feel a strong urge to suggest that it may in fact stem primarily from a deliberate and maniacal worship of Nurgle, the Chaos God of despair, disease, and destruction.

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.

Entire apps in shell can be very good, eg: gnu pass

Exception, not the rule.

pass is not a gnu project.

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.

Sure, they're great, so long as I don't have to modify them.

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.

If there's a third category, please enlighten me

Gigantic engineering mathematics calculation tools.

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

>Are entire VBA projects really that ubiquitous?

We have such sights to show you.

For the longest time many, if not most Linux distros, used a bunch of shell scripts for init.

And many Linux users whined for many years after that mess was replaced by systemd and many still do.

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.

Think this is the same guy that makes videos about vim? Didn't know he had other types of content, he's good at conveying information.

Edit: regarding the video embedded in article

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.

No matter what lofty heights you achieve you're never really free of external constraints.

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.

Reply on news.ycombinator.com