sancarn

COM is still actively being used. Not sure why you think it's a problem with windows too. COM is and always has been quite revolutionary imo.

Not sure about other people but most of my apps I've made actually can't be made in Office Scripts... They are useless for my needs.

good software that exists on other platforms

You mean like AppleScript?

They had announced Python support for Excel a while back and I wish it was better promoted as a viable VBA replacement

But the existing implementation of Python means that it's not even close to a viable replacement lmao

Untested but something like this in VBA should suffice:

    Dim rng as range: set rng = Sheet1.UsedRange
    Dim rows as Long: rows = rng.rows.count
    Dim cols as long: cols = rng.columns.count
    Const SIZE as long = 500
    For i = 2 to rows step SIZE
      Dim wb as workbook: set wb = workbooks.add()
      wb.sheets(1).range("A1").resize(1, cols).value = rng.resize(1).value
      wb.sheets(1).range("A2").resize(SIZE,cols).value = rng.offset(i-1).resize(SIZE).value
      Call wb.SaveAs("C:\Temp\" & i & ".xlsx")
    next

These are the reasons I struggle to leave Windows too. Also the open API ecosystem is great. You can do pretty much everything with win32 libraries.

At least I have yet to see one that is actually useful in the sense of a generic (or even a single-purpose-built) language

Yeah as said, https://github.com/enso-org/enso seems to be a general purpose functional programming language with visual editor, but otherwise I haven't really seen any no-code solutions worth their salt. I'm not particularly a fan of enso either, but it's the best I've seen.

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

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

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

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

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

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

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

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

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

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

so why are you complaining again?

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

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

Good if all you do is display a fancy UI for some data entry or visualisation, but OfficeJS can't do half the things VBA can do unfortunately. If only the addin system had FFI. I'd switch forever.

Edit: Another big issue with OfficeJS is you need to be able to host a web server. That's not usually something most end users have access to...

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

Microsoft recently released OfficeScripts, which are JS with a record macro button. Problem is, it's currently too limiting. Will be good in a few decades though if support isn't dropped.

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

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

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

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

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

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

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

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

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

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

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

JScript is deprecated, and is likely to be removed at some point too...

CMD is often blocked on many people's machines due to group policy.

PowerShell is really the only other option other than VBA, as discussed in the article. Only reason I haven't used PowerShell til now is the version was hidiously outdated and didn't even support classes... Of course with PowerShell you can evaluate C# code.

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

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

I do agree, but they do have their limitations. Can't update sharepoint lists from Excel if you have more than 5000 records, can't update sharepoint lists generally unless you use VBA hacks (or use REST API and somehow get VBA to authenticate). I'm not certain whether you can update in bulk using access honestly, I'd be interested in knowing though... Even using REST API has it's own limitations.

Generally I update them with client-ran JavaScript. I love sharepoint lists for their ease of use to users, but the limitations are pretty rubbish if ever you want to do anything programatically, unless you can figure out how to authenticate (and/or use a library which handles that for you).

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.

Is that really needed? You can use VBA to do all the heavy lifting. That's what we do here: https://github.com/sancarn/stdVBA/blob/master/src/stdLambda....

I have an open source compiler-interpreter for VBA here: https://github.com/sancarn/stdVBA/blob/master/src/stdLambda....

In case you were interested :P (I say compiler, it compiled to byte code, not asm.)

When will Microsoft fix the root cause, and stop with this 'Attack Vector' BS.

A running application should not, by default, have access to everything on disk (and every program running on the PC too). You will never annihalate every attack vector, especially when the limiting factor is dumb users.

I feel like communication of requirements is difficult too. I'm currently a business end-user, with developer experience, who is maintaining legacy VBA technology, trying to get an official system produced by a 3rd party contractor.

The requirements started out truly quite simple - we want a database and a nodejs web server.

But Technology are gate-keepers. We're not being trusted with what we want, or with that level of control over the system. Which then starts this cat chasing mouse thing of what the Technology will allow and what the business wants.

I've always vouched for "Internal IT" - specialised developers who work closely with the team, even doing the jobs users do, to self-identify what technology is required and kickstart these projects.

Edit: in this particular project, I handed over numerous diagrams of how the system should be architectured, and Technology BA's came back with a proposed set of technologies (SASS) that simply wouldn't be technically feasible... It's really made me lose hope.