From that end-user direction, solutions emerge. And they're in VBA.
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.
--
[0] - Except those creating high-security environments with airgaps and whatnot, but that's a special case.
I think there's even a Lazarus IDE available for every company user who wants to create reliable RAD based software bound to corporateware.
As a developer who hates installing programs that might be one offs, I hate the idea of it, but I can't deny the benefits.
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.
The problem is, cybersecurity insurances nowadays have that limitation as mandatory for coverage... and for good reason.
But then, the next phase starts: that scripts gets copied over (because Jim wanted to run it too) and modified (Jane has a different VBA version) and expanded (now it does "THIS!" too).
Now it's a 1500 line kludge and they want to unload it, ie pass it over to development for maintenance.
... and THAT should be considered a GOOD THING!
It means you've got a tried and true business case for the application, the requirements capture has already been done, you've got an instant user-base and a very clear bar to jump over. Of course, the application must be able to outperform the old application in every way, or else questions will be raised.
I think it's important to point out that the inception of these excel VBA monstrosities is innocent and pragmatic. An SME has a job to do, they're doing their job, but have a need for a custom tool to help do their job.
It is ALMOST NEVER the case that they should drop what they're doing and engage a SW development team to go through a lengthy VERY expensive process with uncertain outcomes-- all the while still having to do their job. It's much more pragmatic, in many cases, to tackle the problem piece by piece, as need arises, with little spreadsheets, scripts and little databases.
I think complaining about VBA monstrosities is wrong-headed. They should be, in a way, embraced as a starting point for devs-- hopefully BEFORE they become mission-critical to the company, however.
There is no way for the IT customer to negotiate this "correctly". It always leads to the same result.
The problem is IT exists to administer computer systems, not to help business people create or maintain software. This brings the wrong mentality and skillset.
They embedded a technical developer into a business team, and had that individual write the "kludgy" business apps that needed quick automation for throwaway tasks or for data processing standup. The dev has access to more than VBA, specifically, Python, GitHub, the ability to spin up what amounts to VPS's in the cloud with access to all of the database infra. All tools are shared with the rest of the company through a tech sharing program that is being heavily promoted across teams, and of course hosted in a repo, often with docs or a website if possible.
This "fills the gap" of dev latency for small dev tasks that don't necessitate pulling in an entire IT team. I don't really understand why this isn't more popular. The business team this individual was hired onto was over-the-moon when this occurred because they were doing absurd things like copy-pasting and hand-modifying JSON payloads many times a day and simply lacked the skillset to fix the problem, due to the issues you described. These issues were immediately resolved in under a month for hundreds of man-hours saved.
Just give business teams a tech resource that's well-trained and understands proper dev for on-demand work that doesn't justify the agile scrum whatever nonsense, and you won't end up with a forest of Excel macros.
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#.
The Software People get called in when it becomes difficult to maintain the ad hoc solution.
He supposedly can do a days work in fifteen minutes and then just hang out. Their computers are super locked down, can’t install anything, can’t go to any non-whitelisted sites, but they have Excel.
There are 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!
There are two reasons: 1. They have a specific job with a specific set of duties (think sysadmins, or administrative duties) in a large company or in a state beurocracy. 2. They would rather go home or do something more but they are not permitted: they have metered time in the office and other people would and do shut them down on any initiatives.
To me, a workplace like that is like a kafkaesque nightmare but they seem to be fine with it, or rather, have accepted it. It lets them focus on other things in life outside of work.
i mean, i would imagine some people want to see purpose in their jobs, while others are just treating it as a job and whatever happens with the output of the job is of no consequence. And this is esp. true of gov't jobs, but by no means do the gov't have a monopoly on such inefficiencies.
But my opinion is that there's something systemic that is preventing these jobs from being competed on and efficiencies eked out.
The problem is actually in the work culture, where other coworkers would prevent another worker from becoming too efficient and proactive. So, nothing changes.
A few weeks into the job I completely automated these in python and all I had to do was turn my laptop on in the morning, then off in the evening, and I was done.
They were slower, but they also chit chatted with half the office, went to lunch, etc.
Their jobs consisted of pulling some data from here or there, entering it into excel, sending a few emails, entering some data into another system, printing some checks. All stuff that's easy to automate (you'd probably need more than Excel in this case)
In one of his memoirs, the science fiction author Arthur C. Clarke recalled his days as a young man working for the British bureaucracy (something to do with teacher pensions, as I recall). His particular job involved consolidating huge lists of figures into reports. He observed that the numbers in the reports were rounded to two significant figures, well within the accuracy of his slide rule, and started using the slide rule to do all his work.
He could finish his daily quota before lunch and take every afternoon off.
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.
We both worked at a tox lab and there are masses of numbers to be reviewed. He strung together 8-10 steps to transform, massage, etc. the data for presentation to mgmt, accounting, etc.
What he found was that most of the time, it all ran fine, but when it didn't he had to spend some of that saved time troubleshooting an issue.
They also added more to his plate, since he no longer needed XX hours to accomplish the data push.
In the end, he was more clever than the last person, but didn't have the 7.75 hours of free time that's often touted.
It may exist, but it's rarer.
This is quite reminiscent of the good old "emacs operating system" paradigm just applied to a different context!
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.
$Csc = gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | select -last 1
if ($Csc) {
Set-Alias -Name csc -Value $Csc
$Csc = $null
}
if ($Csc = gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | select -last 1) {
Set-Alias -Name csc -Value $Csc
Remove-Variable Csc
}
gci "$env:windir\Microsoft.NET\Framework64\*\csc.exe" -ea silent | % {
Set-Alias -Name csc -Value $_
} $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)
}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.
ISE does occasionally hang / crash, but it's quite rare compared to how VSCode behaves across every machine I've used it with. It really seems to be just a Powershell problem, haven't had the same issue in any other language.
When I'm really making great progress on something, having to fart around with killing and restarting the shell constantly is really disruptive. Yes, Code has better and more features, but for me the extra productivity does not overcome the crashy shell.
I don't like vscode for powershell development and I find the pycharm experience for powershell (lol) much better.
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.
Not entirely correct
https://www.encomputers.com/2018/05/disable-macros-in-micros...
Generally I update them with client-ran JavaScript. I love sharepoint lists for their ease of use to users, but the limitations are pretty rubbish if ever you want to do anything programatically, unless you can figure out how to authenticate (and/or use a library which handles that for you).
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.
Can you access raw memory from it?
If the answer to either of those is no, then that’s a big difference.
Here's a ransomware incident report from someone opening an Excel document with macros enabled:
https://thedfirreport.com/2023/05/22/icedid-macro-ends-in-no...