[HN Gopher] Why do people still use VBA?
___________________________________________________________________
Why do people still use VBA?
Author : sancarn
Score : 284 points
Date : 2023-11-14 23:12 UTC (23 hours ago)
(HTM) web link (sancarn.github.io)
(TXT) w3m dump (sancarn.github.io)
| throwaway154 wrote:
| 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.
| Dan_W wrote:
| That's exactly why I use it. Only dev environment available to
| me.
| 13of40 wrote:
| 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.
| jodrellblank wrote:
| PowerShell including ISE, with tabs, multi-line cursor,
| syntax highlighting, autocomplete, step-through debugger,
| snippets, scriptable/extensible.
| RajT88 wrote:
| People think I am nuts for preferring the ISE over
| VSCode, but the ISE never crashes while running scripts!
| hobs wrote:
| Then you haven't used it enough :) The bare shell on the
| other hand is usually solid.
| RajT88 wrote:
| 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.
| hobs wrote:
| 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.
| pjmlp wrote:
| You're not, I would also rather have the Powershell team
| improve ISE than their decision to migrate into VSCode,
| but alas.
| TheJoeMan wrote:
| 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.
| 13of40 wrote:
| 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.
| resoluteteeth wrote:
| 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)
| jodrellblank wrote:
| Today, yes, but ISE has shipped with Windows since what,
| XP? And VBA never has - it's a part of Office.
| Pxtl wrote:
| 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.
| orthoxerox wrote:
| 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.
| anthk wrote:
| I think it does since the Windows XP days, at least a CLI
| based compiler/interpreter.
| orthoxerox wrote:
| 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.
| TeMPOraL wrote:
| 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.
| alisonatwork wrote:
| 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.
| vbezhenar wrote:
| 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?
| dartos wrote:
| 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.
| meibo wrote:
| You can call any native or COM function from VBA, the only
| real limitation is that it's strictly single-
| threaded(-ish).
| nradov wrote:
| You can call Windows API functions from VBA.
| hnlmorg wrote:
| Yes you can. VBA can make the same Win32 API calls as VB6.
| Something I exploited back in the tail end of the 90s.
| anthk wrote:
| COM/OLE. Old as hell. Macro viruses in Office/Outlook has
| been a shitfest since late 90's.
| technion wrote:
| 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...
| mr_mitm wrote:
| 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.).
| theonlybutlet wrote:
| 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.
| donatj wrote:
| 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.
| meibo wrote:
| 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!
| asdfman123 wrote:
| This sounds like a very specific, personalized version of
| hell
| wiseowise wrote:
| 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?
| donatj wrote:
| 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.
| prerok wrote:
| 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.
| chii wrote:
| > 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.
| prerok wrote:
| 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.
| progmetaldev wrote:
| 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).
| coumbaya wrote:
| 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.
| 0xpgm wrote:
| Nice story! Curious, was the manager a technical person
| e.g. a former engineer?
| coumbaya wrote:
| Yep absolutely he was a technical manager.
| darkwater wrote:
| 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!
| coumbaya wrote:
| 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 !
| pizza234 wrote:
| 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.
| onion2k wrote:
| 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.
| kqr wrote:
| 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!
| technion wrote:
| 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.
| xnx wrote:
| A familiar experience! https://xkcd.com/1319/
| amonavis wrote:
| 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.
| bluedino wrote:
| 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)
| sheepshear wrote:
| 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.
| Turing_Machine wrote:
| 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.
| sonicanatidae wrote:
| 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.
| technion wrote:
| 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.
| throwaway2037 wrote:
| 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!
| pyeri wrote:
| 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!
| toolslive wrote:
| 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.
| mathgladiator wrote:
| And thats great because demand has be satisfied!
| lodovic wrote:
| 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.
| liotier wrote:
| 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 !
| TeMPOraL wrote:
| 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.
| crispyambulance wrote:
| > 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.
| civilized wrote:
| 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.
| jimbokun wrote:
| 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.
| julian_t wrote:
| 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#.
| baz00 wrote:
| 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.
| liotier wrote:
| 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 !
| anthk wrote:
| >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.
| liotier wrote:
| 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.
| baz00 wrote:
| Try getting those through a corporate DLP proxy.
| nolongerthere wrote:
| 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.
| TeMPOraL wrote:
| 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.
| actionfromafar wrote:
| Proxies can be pretty harsh too. Not sure if we have a
| whitelist or a blacklist but it's pretty restrictive.
| letsdothisagain wrote:
| 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.
| j33zusjuice wrote:
| 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
| baz00 wrote:
| It's an extrapolation of that. The golden hammer gives too
| much credibility to the people weilding the tool.
| Modified3019 wrote:
| "Monkey hammer" is definitely very illustrative of a
| particular kind of chaos, I like it.
| BobaFloutist wrote:
| I'm pretty sure they were referring to the established
| aphorism "When all you have is a hammer, everything looks
| like a nail."
| jeffwask wrote:
| Crossed with a thousand monkeys and a thousand
| typewriters.
| aksss wrote:
| 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.
| mschuster91 wrote:
| > 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.
| cableshaft wrote:
| > 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.
| Twirrim wrote:
| 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.
| CodeWriter23 wrote:
| > 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...
| kjkjadksj wrote:
| 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.
| username135 wrote:
| 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.
| dieselgate wrote:
| 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
| _boffin_ wrote:
| 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.
| owlninja wrote:
| 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.
| Valgrim wrote:
| 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
| shapefrog wrote:
| IT came back asking for a budget of $750,000 to put the macro
| that one team uses into production.
| internet101010 wrote:
| 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.
| flomo wrote:
| 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.
| theonlybutlet wrote:
| 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.
| _boffin_ wrote:
| 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?
| theonlybutlet wrote:
| 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.
| Qem wrote:
| 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.
| theonlybutlet wrote:
| 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.
| emodendroket wrote:
| Yeah to be honest I did something like this and then I switched
| to C#. Now I"m just a software developer.
| jbandela1 wrote:
| 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!
| kibwen wrote:
| 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.
| djbusby wrote:
| Entire apps in shell can be very good, eg: gnu pass
|
| Exception, not the rule.
| vluft wrote:
| pass is not a gnu project.
| jgtrosh wrote:
| 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.
| emodendroket wrote:
| Sure, they're great, so long as I don't have to modify
| them.
| Valgrim wrote:
| 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
| mech7654 wrote:
| Gigantic engineering mathematics calculation tools.
| galangalalgol wrote:
| 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.
| Arch485 wrote:
| "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.
| AndrewDavis wrote:
| 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.
| RugnirViking wrote:
| 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
| _dain_ wrote:
| _> Are entire VBA projects really that ubiquitous?_
|
| We have such sights to show you.
| addicted wrote:
| 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.
| lmm wrote:
| Systemd means replacing your at least somewhat standardised
| shell scripts with an underdocumented, underspecified,
| opaque scripting language. It's not an improvement.
| pjmlp wrote:
| 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.
| Johnny555 wrote:
| 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
| Traubenfuchs wrote:
| I'd argue that developers beyond a certain age are as
| guaranteed to have come into contact with VBA as with HTML/JS.
| meepmorp wrote:
| 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.
| mhh__ wrote:
| 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.
| sokoloff wrote:
| Excel is by far the most used no-code/low-code platform, IMO.
| baq wrote:
| Excel is purely functional programming for the masses and I
| mean that as a good thing.
| enasterosophes wrote:
| 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.
| appplication wrote:
| 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.
| rprospero wrote:
| 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?
| gonzo41 wrote:
| I've done this too. with modern browsers you can do a lot
| with JS and HTML5 without much of a backend.
| enasterosophes wrote:
| > 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.
| wly_cdgr wrote:
| 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.
| Aloha wrote:
| 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.
| jojobas wrote:
| 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.
| GuB-42 wrote:
| 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.
| Aloha wrote:
| 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.
| _rm wrote:
| 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.
| Aloha wrote:
| We as a group are considering work to rule.
|
| But, no, because I otherwise love my job, also where would I
| go?
| GuB-42 wrote:
| > 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.
| Aloha wrote:
| 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.
| GuB-42 wrote:
| > 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.
| nunez wrote:
| Because it's cheaper/easier than replatforming for many
| businesses and there are still people that know it and are
| hireable
| j45 wrote:
| 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.
| theodpHN wrote:
| Path of least resistance.
| richsu-ca wrote:
| The language, the object model, and the IDE combine for a fun,
| highly productive programming environment.
| mcbishop wrote:
| 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).
| emodendroket wrote:
| I mean I think it's fair to look askance at any environment
| that includes misfeatures like `On Error Resume Next`.
| layer8 wrote:
| You mean like most shell scripts? VBA is no different from
| Bash here.
| emodendroket wrote:
| I wouldn't consider that much of a defense!
| MagnumOpus wrote:
| 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.
| blincoln wrote:
| 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...
| mcbishop wrote:
| 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.
| blincoln wrote:
| 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.
| mcbishop wrote:
| 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.
| michaelteter wrote:
| 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.
| asdfman123 wrote:
| Future civilizations will marvel at the intricate grandeur of
| our Excel spreadsheets
| guappa wrote:
| They won't be able to read our media, and if so they won't be
| able to decode the excel format.
| personalityson wrote:
| Excel format is just a zipped collection of XML files
| orthoxerox wrote:
| That's the new one. The old one is 90% direct dumps of
| C++ structs.
| raverbashing wrote:
| > 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.
| bigtunacan wrote:
| 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.
| jampekka wrote:
| 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.
| Dwedit wrote:
| Because it's basically VB6
| Pxtl wrote:
| Vb6 at least had "on error goto".
| qsdf38100 wrote:
| Vba has "on error goto".
| Pxtl wrote:
| _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.
| masteruvpuppetz wrote:
| 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.
| RugnirViking wrote:
| 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?
| asdfman123 wrote:
| 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.
| emodendroket wrote:
| No matter what lofty heights you achieve you're never really
| free of external constraints.
| asdfman123 wrote:
| 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.
| keepamovin wrote:
| 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
| lencastre wrote:
| 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...).
| uxp8u61q wrote:
| 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.
| janci wrote:
| 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).
| aspaviento wrote:
| 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.
| TeMPOraL wrote:
| > _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.
| emodendroket wrote:
| 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.
| ekianjo wrote:
| Inertia
| IYasha wrote:
| 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
| rkagerer wrote:
| Because it's fairly simple and it works.
| kagevf wrote:
| 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.
| teaearlgraycold wrote:
| I'm glad I've worked primarily in early SV startups. None of this
| BS to deal with.
| 0xpgm wrote:
| With the interconnected nature of modern life, that BS probably
| touches an important part of your life
| totallywrong wrote:
| 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.
| lobochrome wrote:
| Because it is just awesome!
| system2 wrote:
| 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.
| account-5 wrote:
| 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.
| Havoc wrote:
| 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
| menotyou wrote:
| 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.
| tinus_hn wrote:
| 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.
| MichaelZuo wrote:
| 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?
| TeMPOraL wrote:
| Yes. But 90% of "effort and hassle" is dealing with IT
| department bullshit. Excel/VBA lets you sidestep that
| entirely.
| dmurray wrote:
| 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.
| TeMPOraL wrote:
| 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.
| jstanley wrote:
| 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.
| mschuster91 wrote:
| > 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.
| execveat wrote:
| 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.
| kstenerud wrote:
| 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.
| mission_failed wrote:
| 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
| kstenerud wrote:
| 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.
| layer8 wrote:
| It's weird to me though that VBA apparently doesn't count as
| "high level".
| jampekka wrote:
| 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.
| j-a-a-p wrote:
| 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.
| TeMPOraL wrote:
| > _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!
| jojobas wrote:
| 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.
| ethbr1 wrote:
| 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.
| bee_rider wrote:
| If you started using water in, like, industrial or
| agricultural quantities I guess the town would eventually
| get curious what is going on.
| ethbr1 wrote:
| 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.
| bee_rider wrote:
| 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.
| jojobas wrote:
| 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.
| 7952 wrote:
| 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.
| xen2xen1 wrote:
| Oh dear God how many times I've had to support that.
| sanitycheck wrote:
| 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".
| arethuza wrote:
| "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.
| bjornasm wrote:
| I have this great new product called "DELETE FROM
| products WHERE provider != 'mycompany'"
| xen2xen1 wrote:
| Little Bobby tables!
| gremlinunderway wrote:
| 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.
| pjacotg wrote:
| Completely agree. I've always thought that Excel/VBA
| shows you where all the short comings are with your in
| house systems.
| lelanthran wrote:
| It might be sobering for "real" devs to find out that
| more "real apps written in real languages" go unused than
| apps in VBA.
| civilized wrote:
| 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".
| analog31 wrote:
| This is vastly underestimated. The difference between
| coding and managing coding.
| civilized wrote:
| 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.
| jimbokun wrote:
| 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.
| culebron21 wrote:
| 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.
| j-a-a-p wrote:
| 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?
| specialist wrote:
| 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.)
| jampekka wrote:
| Jupyter style notebooks are already becoming the next VBA
| in some fields. And this is not a good thing.
| specialist wrote:
| Why? The usual suspects? Lack of version control? Hard to
| deploy (reproducibly)?
| jampekka wrote:
| 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.
| specialist wrote:
| 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.
| jampekka wrote:
| 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
| jimbokun wrote:
| 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.
| pjacotg wrote:
| 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.
| usrbinbash wrote:
| 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.
| ethbr1 wrote:
| > _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.
| usrbinbash wrote:
| 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.
| ethbr1 wrote:
| > _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.
| gwervc wrote:
| > 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.
| f1shy wrote:
| If IT people would only understand they are giving a service
| for the rest of the company... and not the way around.
| usrbinbash wrote:
| > 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?
| jampekka wrote:
| 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.
| FredPret wrote:
| 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.
| pillefitz wrote:
| 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.
| usrbinbash wrote:
| > 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.
| kbelder wrote:
| 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.
| 7thaccount wrote:
| 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.
| usrbinbash wrote:
| > 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.
| dalyons wrote:
| 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.
| usrbinbash wrote:
| > 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.
| dalyons wrote:
| 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.
| usrbinbash wrote:
| > 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.
| rainonmoon wrote:
| 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.
| dalyons wrote:
| 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.
| rockooooo wrote:
| 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.
| FuriouslyAdrift wrote:
| 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.
| jimbokun wrote:
| 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.
| jampekka wrote:
| 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?
| zztop44 wrote:
| I'm loving this thread. I feel like Hunter S Thompson
| reading the gonzo review: "okay, that's what I do. Shadow
| IT."
| 2devnull wrote:
| 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.
| jampekka wrote:
| 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.
| Qem wrote:
| > 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.
| jampekka wrote:
| 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.
| dzolob wrote:
| 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?
| jimbokun wrote:
| 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.
| jampekka wrote:
| 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.
| jimbokun wrote:
| Well, I work for a company that processes Private Health
| Information, so a breach is a potential existential
| threat.
| jampekka wrote:
| 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?
| rainonmoon wrote:
| 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.
| TimTheTinker wrote:
| 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.
| 4star3star wrote:
| VBA can call executables, but I can see some places locking
| things down such that that would not fly.
| jimbokun wrote:
| 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.
| steve1977 wrote:
| 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.
| unixhero wrote:
| 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.
| jmkni wrote:
| 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.
| gonzo41 wrote:
| 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.
| benj111 wrote:
| 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.
| orthoxerox wrote:
| 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.
| turkishlurker wrote:
| 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.
| pillefitz wrote:
| More like: Obtaining a japanese chef's knife, getting a permit
| to carry it and forbid your friends to use it.
| pharmakom wrote:
| Please, please Microsoft start pushing F# as an alternative to
| VBA!
| eurekin wrote:
| 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)
| agumonkey wrote:
| 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.
| al_be_back wrote:
| 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.
| runnr_az wrote:
| 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
| TrackerFF wrote:
| 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.
| lucidguppy wrote:
| 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.
| Aeolun wrote:
| You can use JS in basically all the places you can use VBA right?
| It's available in every browser.
| francisofascii wrote:
| 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.
| layer8 wrote:
| - 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.
| lnxg33k1 wrote:
| I think I just read the best reply ever
| ncjcuccy6 wrote:
| But typescript has "using" now? How can VBA still be so
| popular?
| layer8 wrote:
| VBA supports automatic cleanup via Class_Terminate. :)
| nulbyte wrote:
| 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
| 7thaccount wrote:
| 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.
| dabeeeenster wrote:
| It might be niche for HN, but in muggle world its enormously
| popular.
| jimbokun wrote:
| Heh, muggle world is the niche, but that niche is arguably
| bigger than all of the dedicated software engineer niches.
| kitd wrote:
| 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.
| steve1977 wrote:
| 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.
| gold7777 wrote:
| 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.
| steve1977 wrote:
| 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.
| PH95VuimJjqBqy wrote:
| At that point you're abusing the word niche
| jimbokun wrote:
| Thus the "Heh".
| wjnc wrote:
| 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.
| specialist wrote:
| > _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.
| tssva wrote:
| 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.
| 7thaccount wrote:
| 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.
| omneity wrote:
| What would have happened if VBScript had won the DHTML wars?
| layer8 wrote:
| We'd have VBTypeScript now. ;)
| markphip wrote:
| 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.
| isilofi wrote:
| - 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
| layer8 wrote:
| 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.
| meragrin_ wrote:
| > - No build steps, it just runs, and fast.
|
| Huh, fast? Sure, it's fast enough for its use cases, but it is
| not fast.
| bertil wrote:
| 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.
| layer8 wrote:
| You can still record VBA macros in MS Office.
| sumtechguy wrote:
| 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.
| jimbokun wrote:
| 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.
| jncfhnb wrote:
| 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.
| mrweasel wrote:
| 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.
| rchaud wrote:
| 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.
| hardlianotion wrote:
| And it's absolutely baked into Microsoft Excel.
| jimbokun wrote:
| 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.
| c0nsumer wrote:
| 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.
| jimbokun wrote:
| 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.
| c0nsumer wrote:
| 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.
| blincoln wrote:
| 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.
| ethbr1 wrote:
| 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."
| jimbokun wrote:
| 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.
| c0nsumer wrote:
| 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.
| specialist wrote:
| 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.)
| cm2187 wrote:
| 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.
| steve1977 wrote:
| Maybe that turned out to be more of a strength than a
| weakness?
| cm2187 wrote:
| A strength for whom? Not the end user.
| mcbishop wrote:
| 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.
| cm2187 wrote:
| The lack of basic functionalities like generics, sort
| functions, lambdas, is a hinderance if you got a taste of
| .net.
| mst wrote:
| 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.
| mcbishop wrote:
| Sorting is fine in VBA. But, I admit, it'd be sweet to
| have lambdas.
| tiahura wrote:
| Here's another: - ChatGPT is well-trained on VBA.
| NoMoreNicksLeft wrote:
| 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.
| jbjbjbjb wrote:
| 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.
| zubairq wrote:
| I agree mostly with the article, VBA doesn't have to get past the
| pointy haired bosses or the purchasing and compliance
| departments!
| culebron21 wrote:
| 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(...).
| orthoxerox wrote:
| > 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.
| jason0597 wrote:
| 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.
| ethhics wrote:
| 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
| 1PlayerOne wrote:
| Yes. Excel macros mostly these days. Used to do Access database
| projects but they are slowly being phased out by Power BI.
| J_Shelby_J wrote:
| 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.
| hnthrowaway0315 wrote:
| So far it is still the most convenient tool to automate MS
| Office, and you can do a lot with COM.
| Loxicon wrote:
| 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.
| owlstuffing wrote:
| You may as well ask, why do people still use JavaScript?
| ubermonkey wrote:
| 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.
| melagonster wrote:
| 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?
| larodi wrote:
| 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.
| 4star3star wrote:
| 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.
| jimbokun wrote:
| Excel is arguably the best end user IDE ever invented.
| nobodyandproud wrote:
| Microsoft is pushing Python for Excel and JS for add-ins.
| personalityson wrote:
| 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)
| ecshafer wrote:
| 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.
| asow92 wrote:
| 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?
| thrownaway561 wrote:
| 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.
| onetimeuse92304 wrote:
| 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.
| tmnstr85 wrote:
| 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.
| gold7777 wrote:
| 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.
| wg0 wrote:
| 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?
| indymike wrote:
| 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.
| personalityson wrote:
| Say no to clouds and dependency hell, return to VBA
| WheatMillington wrote:
| 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++)
| clausok wrote:
| 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."
| zitterbewegung wrote:
| 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.
| emj wrote:
| 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...
| sancarn wrote:
| 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.
| robomartin wrote:
| > 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.
___________________________________________________________________
(page generated 2023-11-15 23:01 UTC)