[airstrike]

VBA is great for many things but it has its flaws.

The biggest problem with it is really that the tooling around it is decades old so it hurts your productivity. Maintaining VBA code bases is a painful experience, the IDE sucks and the language has enough quirks and shortcomings that it forces you to take long detours to accomplish what would be very simple tasks in more modern languages

The loving part of VBA is its interoperability across the Office suite, but there's no reason why that couldn't be done in, say, Python

Oh, I almost forgot. VBA classes are absolute misery.

[ddingus]

This is the part which saves crazy time:

The loving part of VBA is its interoperability across the Office suite, but there's no reason why that couldn't be done in, say, Python

I agree with you, but Python and pretty much every code environment is missing a few things that create a pretty high barrier to entry:

For a whole lot of cases, VBA is not even needed. People put data into cells, operate on it with formulas in other cells that drop output into still other cells which are then used to get output.

Input can be almost anything these days.

Formulas have a well defined, easy to understand syntax that work across a wide variety of operators. Simple copy / paste operations make sense, often with the intended data mapped right in. (given people are a little organized)

Output can be almost anything these days too.

And it's live. Make a change, see it happen.

That's real power! People don't have to know much to make it all work either.

I have been using Excel to transform business data for years, model business and a lot of other things, and as a rapid prototype system. I can write code too. Often I do, but the more specific and or variable the task is, like a one off need to solve yesterday, the more attractive just banging it out in Excel becomes.

Should one get super crazy, have one of those outputs from Excel be a working program. No joke. A script file is one of my favorite outputs. Mash the data up in Excel, and once the plan of attack is clear, execute the script and watch it run on the real system.

Check this thing out:

https://github.com/tilleul/apple2/tree/master/tools/6502_ass...

It's a perfectly usable, and I would suggest one of the easiest, assemblers I've ever seen! I ran it on my mobile. Crazy.

I just used it to knock out a little routine for a retro-game project I'm working on and was kind of stunned at how lean, accessible, functional this really is.

For Clarity: Replacing VBA with something else costs more than the value add at present, and it's because Excel is the gateway drug into VBA. By the time people reach for VBA, they already are familiar with a lot of it.

[airstrike]

You misunderstand me. I'm not arguing one should replace Excel with Python, I'm merely talking about VBA and the VBE

[pessimizer]

VBA is very stupid, and I facepalmed a lot while learning it, but after you get used to it you can write in a very functional, clear, low/no-side effect style, with reasonable polymorphism using interfaces. It's still bad, but it's available. And I can make the UI out of spreadsheets and output still more spreadsheets, so business people aren't scared of it at all.

edit: I'd certainly rather be writing VBA than js, which is what the seem to be replacing that type of automation with, not python.

[ddingus]

Perhaps we misunderstand one another.

VBA and VBE being replaced with Python would require so much work... and there is a crazy amount of code out there doing an equally crazy amount of work too.

All the points I put here are why doing that replacement work doesn't really add much value.

Which is why VBA is still a thing.

[airstrike]

> Perhaps we misunderstand one another.

I understood you. My reply above was in relation to your:

> For a whole lot of cases, VBA is not even needed. People put data into cells, operate on it with formulas in other cells that drop output into still other cells which are then used to get output.

* > Input can be almost anything these days. *

Nobody is arguing VBA is always needed and inputs being almost anything has no bearing on VBA's shortcomings.

* > Formulas have a well defined, easy to understand syntax that work across a wide variety of operators. Simple copy / paste operations make sense, often with the intended data mapped right in. (given people are a little organized)

Formulas are great, no argument there. Still irrelevant to the "VBA has plenty of shortcomings" discussion.

> Output can be almost anything these days too.

> And it's live. Make a change, see it happen.

> That's real power! People don't have to know much to make it all work either.

You're describing spreadsheets. I love spreadsheets. I should, as I often spend 100 hours in a single week working with them.

> (...)

> For Clarity: Replacing VBA with something else costs more than the value add at present, and it's because Excel is the gateway drug into VBA. By the time people reach for VBA, they already are familiar with a lot of it.

You haven't really proved that point at all. You talked about spreadsheets and then concluded something about VBA, which doesn't follow.

————————————————————

As for your reply above

> VBA and VBE being replaced with Python would require so much work... and there is a crazy amount of code out there doing an equally crazy amount of work too.

The best time to plant a tree was 20 years ago. The second best time is now.

The same was true about Excel 4.0 macros and yet we did it. Python or [insert your favorite language] doesn't need to replace VBA overnight. It can be available alongside it, just like VBA was available alongside Excel 4.0 macros for decades when introduced. Unsurprisingly, XLM felt out of fashion and VBA took over as the superior choice.

> All the points I put here are why doing that replacement work doesn't really add much value.

Sorry, but you really haven't made those points.

> Which is why VBA is still a thing.

VBA is still a thing because Excel has no real competition as an Enterprise spreadsheet app. But its popularity and prevalence don't speak to its quality.

[ddingus]

Ok, I see it. For the record, I would love Python as replacement for VBA.

I also agree on quality.

What I do not see is the value added to improve quality.

[passivate]

>the language has enough quirks and shortcomings that it forces you to take long detours to accomplish what would be very simple tasks in more modern languages

Any examples that you've come across?

[airstrike]

I've already mentioned VBA classes. If you want to read , just read through Chip Pearsons' great Arrays library. 30 functions just to do what you should be able to do natively in a high-level scripting language such as VBA

http://www.cpearson.com/Excel/VBAArrays.htm

Imagine how painful your experience would be writing any decently complex reusable code in VBA without knowing about all of the edge cases covered by Chip in that module

[passivate]

Oh, I was mainly interested in your view to kick off a conversation. I wasn't sure what you meant by long detours. I don't program in VBA, so I was interested. From the outside, it seems like a powerful language for non-programmers to implement business logic.

[airstrike]

Sorry, didn't mean to sound rude at all. I guess I was a bit on the defensive given all the pushback I was getting elsethread

It's hard to really pin it down to a couple of things, but after spending some time in it, you quickly feel the ergonomics aren't really great. It's just a lot of typing to get basic things done like inserting an element into a 1-d array.

For such a high-level language and one directed at non-programmers as you mentioned, you'd think that sort of tooling would be available natively

[passivate]

Its all good. I used to be a dev many years ago, so I just try to stay informed as much as possible now.