Why do folks use VBA?

2023-10-18

Introduction

Just lately, I watched a video by YouTuber ThePrimeTime which particulars a dev’s frustrations with enterprise tradition. Prime is an ex-entrepreneur who presently works in software program improvement at Netflix. His views on this video have been criticised for being jaded by FAANG enterprise cultures he has labored in. I personally don’t really feel this fashion. Though there’s some fact to the developer’s (mataroa’s) article, I feel it misses the basis causes of many points raised.

I’ve blended emotions in regards to the article in query. Some areas I agreed with and others I disagreed with. Nevertheless I did wish to deal with one specific concern / assertion:

I work on a platform that price my group an eye-watering sum of cash to supply, over the span of two years, and the engineers chargeable for it elected to make use of spreadsheets to manage the infrastructure, so we now have a spreadsheet with 400 separate worksheets that powers however one a part of this complete shambling mess.

I’m speculating right here, however I’d think about that the enterprise/SMEs (Topic Matter Specialists) are utilizing VBA to some capability to manage their 400 worksheet assortment. So this begs the query…

Why do folks use VBA?

With a view to reply this query, we should first have a look at one other query – who really makes use of VBA within the first place? In 2021 I ran a ballot on /r/vba the place I requested redditors why they code in VBA.

From these information, we will clearly see that almost all of people that use VBA achieve this primarily as a result of they haven’t any different alternative. Many organisations run their total enterprise processes with Excel, and when a bit little bit of automation is required VBA is normally #1 on the listing.

The flexibility of VBA

Within the enterprise I presently work for, within the engineering division, we now have entry to quite a lot of applied sciences (automation platforms):

• OnPrem – PowerShell (No entry to Set up-Module)
• OnPrem – Excel (VBA / OfficeJS (restricted entry) / OfficeScripts / PowerQuery)
• OnPrem – PowerBI Desktop
• OnPrem – SAP Evaluation for Workplace
• OnCloud – Energy Platform (PowerApps, Energy BI, PowerAutomate (non-premium solely))
• SandboxedServer – ArcGIS (ArcPy)
• SandboxedServer – MapInfo (MapBasic)
• SandboxedServer – InfoWorks ICM (Ruby)
• SandboxedCloud – ArcGIS On-line

We even have numerous databases managed by IT:

• D1. OnPrem – Geospatial database
• D2. OnCloud – Geospatial mirror
• D3. OnPrem – SAP database
• D4. OnCloud – SAP BW4HANA partial mirror
• D5. OnPrem – Telemetry platform
• D6. OnPrem – Sharepoint
• D7. OnCloud – Sharepoint On-line
• D8. OnCloud – EDM Telemetry platform
• D9. OnCloud – Giant mirror database
• D10. OnPrem – LotusNotes database
• D11. OnPrem – IBM BPM database
• D12. OnPrem – File System
• D13. OnPrem – Hydraulic Mannequin Data

D1–D13 databases are summarised within the desk beneath itemizing what kinds of information are saved during which methods, the significance of the information saved in every database, and whether or not the database is actually a duplicate of OnPrem data:

Notice: D6 can be C tier if it weren’t for the actual fact we proceed to retailer a enterprise crucial spreadsheet on Sharepoint OnPrem for compatibility causes. See the control of VBA for particulars.

And the information’s significance / on cloud replication:

Notice: On-line replicas are de facto replicas by way of information’s significance, though the explanation we have to hook up with them is diminished

Lastly, let’s look at how our Automation Platforms hyperlink to our Knowledge Platforms. Hyperlinks proven within the diagram are the place the automation platform can entry the information from the varied information platforms/sources:

Notice: Among the hyperlinks from VBA to OnCloud providers are based mostly on my makes an attempt alone. There isn’t any doubt in my thoughts that VBA can interface with SAP BW4HANA and our different cloud providers, I simply haven’t found out the authentication necessities and protocols but

Right here’s the place you would possibly begin to see a problem. Seems like the one automation platforms which may hook up with all the information sources we’d like is VBA and Powershell. Energy BI Desktop has been launched in our enterprise however doesn’t hit all of the platforms which VBA does, and even when it did Energy BI can’t be used for course of automation where-as VBA can, so what’s the purpose making the change? Customers who do use Energy BI to focus on these different datasets normally generate CSVs of this different information and retailer these in cloud sharepoint system, however what generates these CSVs? VBA.

Now, we’d love to make use of a better degree language in our organisation to deal with this enterprise automation. Nevertheless, each request for a excessive degree language to be put in throughout the crew/enterprise e.g. Python / Ruby / Node / Rust and so on. has been rejected by CyberSecurity in favour of applied sciences like PowerAutomate, PowerApps which as you may see above barely contact any of the information we’d like. It’s supposedly “Towards the expertise strategic imaginative and prescient of the corporate” to permit “end-users” entry to excessive degree programming languages. Now even when the information entry was there in our enterprise, PowerPlatform would nonetheless be inadequate to carry out the vast majority of our processes as a result of the algorithms required are so advanced {that a} PowerAutomate options would grow to be infuriating to take care of and incomprehensible to even IT people (e.g. See projection algorithms).

Finally the stand-out applied sciences for us are Powershell v3 (doesn’t even help class syntax and can’t set up modules), and VBA, purely from a versatility standpoint. On account of this ‘monopoly’ on expertise I and others have spent tons of of hours constructing open source VBA libraries which increase VBA selling it to an affordable language by fashionable requirements.

The upkeep assure of VBA

D10 and D11 above are intimately linked. In 2000s a lot of our methods have been constructed on high of IBM Lotus Notes databases. In 2019 Lotus Notes was acquired by HCL, and since then longevity of help has been wavering. Assist will formally die in June 2024. In consequence, since 2019, expertise groups have been making an attempt emigrate a lot of our methods to new applied sciences. The enterprise spent an eye fixed watering sum of money growing a system utilizing IBM Enterprise Course of supervisor to supercede certainly one of these Lotus Notes databases. The anticipation was that D11 can be backfilled with all the information from D10, as soon as totally constructed, and D10 can be archived.

It’s now 2023:

• We’re 8 months away from official help dying.
• Expertise groups have thrown away their help contact for IBM BPM.
• There isn’t any substitute in sight for each IBM BPM and Lotus Notes databases.
• IBM BPM answer is poorly maintained
• IBM BPM answer has quite a few points and doesn’t operate as wanted
• Resolution has been shoehorned into IBM BPM, regardless of the platform not being match for objective
  • i.e. whereas IBM BPM does include a REST API, this REST API is borderline ineffective to Expertise groups 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 identify however by GUID.
    • There’s no documentation of which GUID pertains to which desk/course of.
• The info from D10 was by no means really transferred to D11, which means the enterprise is now utilizing 2 methods as a substitute of 1.
  • D11 information mannequin doesn’t actually help the information in D10 both.
• Expertise groups look like ignoring waning help contracts.
  • This could possibly be attributable to lack of sources, funds and/or precedence.

SME’s use these instruments each day, and in the end it’s SME’s who decide adjustments that have to be made to the system. If SMEs use VBA, they will management and keep the system as wanted. They’ve a upkeep assure, one thing that needs to be mentioned for IT methods too, however can’t be.

See Also

1Password detects “suspicious exercise” in its inside Okta account

The management of VBA

In a current challenge we’re constructing a brand new all encompassing IT system to supercede a enterprise crucial spreadsheet. This could in the end demote D6 to C tier significance. The spec for this method was initially easy – Give us a NodeJS server with a MySQL database. Use React for the UI. Give admins/SMEs (subject material consultants) entry to the codebase with entry to git for code management. IT and SMEs will collaborate to construct the system.

• Expertise groups demanded that admins/SMEs won’t have entry to the code.
• Expertise groups demanded that FrontEnd be in-built Microsoft PowerApps, to adjust to “Strategic Imaginative and prescient”.
• Expertise groups demanded that BackEnd be in-built Microsoft Azure Pipelines, to adjust to “Strategic Imaginative and prescient”.

Sadly, as an admin/SME with extra improvement information than many individuals in expertise, these calls for don’t sit properly with me:

• Expertise groups don’t perceive work within the groups thus don’t perceive enterprise logic and calculations
  • Thus devs writing enterprise logic is error susceptible.
• Expertise groups has often deserted bespoke expertise initiatives, leaving no useful resource to take care of and enhance the system.
  • Collaboration with SMEs will make sure that at the very least 1 crew maintains useful resource to take care of system.
• SMEs want to make sure that they’ve confidence in what’s produced.
  • How, with out observing that the code doesn’t work for all edge circumstances?
    • Unit checks?
      • Maybe, however with out seeing the code how can we confirm these unit checks exist? And are ran often*.
• SMEs enhance and keep the prevailing legacy system, and have unparalleled information of how methods work together.
  • Much less information will be shared in upskilling Expertise groups the place it’s required.
• SMEs want to make sure all information is transferred and represented accurately in new system.
  • SMEs unable to do that with out backend entry.

Finally, so long as code stays in VBA it’s managed by the SMEs and the enterprise. Expertise groups hardly ever relinquish management to enterprise groups. SMEs can make sure that software program is developed correctly in a modular vogue and doesn’t find yourself as a cluster of barely working applied sciences loosely linked collectively.

The UX of VBA

It is a smaller one, as a result of you may all the time make acquainted feeling instruments in any language, however there’s something to be mentioned about the truth that most engineers use spreadsheets of their day job, and the way VBA is embedded inside these spreadsheets and might enhance on UX. Giving somebody a international device in a well-known surroundings, is usually rather more highly effective to that person, than a international device in a international surroundings.

Conclusion

In conclusion, sure, we (and plenty of others in companies) do select to make use of spreadsheets (and VBA) for a lot of duties inside our organisations, there are lots of causes for this, together with:

• Poor alternate options offered by IT attributable to safety issues.
• Poor connectivity of alternate options to supply methods, normally as a result of they’re nonetheless WIP.
• Faults in IT technique which don’t account for sure use-cases.
• Unwillingness to collaborate with SMEs attributable to safety and upkeep issues.
• Lack of coaching for customers/managers/SMEs in various methods.
• Customers/SMEs wanting some degree of management over the enterprise logic in these methods.
• It’s the one viable expertise which is on the market to everybody, because it’s a part of Workplace.

This doesn’t imply that we’re in any respect blind to VBA’s weaknesses although:

There’s little doubt in my thoughts that there are some parts of fact to mataroa’s article. Typically administration is poor, however as a rule I consider most individuals in organisations try to do the precise factor, and are doing no matter they will with the instruments which can be accessible to them.

Authors