14

I know that it's possible to call .NET code from your VBA code, but why does VBA continue to exist? The only reason I can think about is legacy.

I just had to sort a Scripting.Dictionary and the amount of code needed was frightening.

The IDE looks like Visual Studio 2003. And there are many little details, that drive you insane (Like changing the line, and getting a warning because of some compilation error). Or, if you open more than one spreadsheet, it mixes into you "VBAProject" and this is really confusing.

The whole module/classmodule/form division is actually not that bad, but I end up everytime with directly writing logic in forms, or having one huge module that handles everything.

Why can't I press Alt+F11, and hack in C#?

10
  • 11
    "The only reason I can think about is legacy." And that is not enough for you? – Euphoric Aug 4 '14 at 14:44
  • 3
    Microsoft is the king of legacy applications and backwards compatibility... any other answer would be surprising to me given this is Microsoft we are talking about. – user40980 Aug 4 '14 at 14:53
  • 2
    @Kiril It's most likely not "we always did it that way"; it's probably "we've compared how much we stand to gain from breaking backwards compatibility vs how much we'll lose, and found it's not worth it." – Doval Aug 4 '14 at 14:57
  • 1
    @Kiril - you question asks to ship Office with .NET "instead" of VBA. That would require abandoning it. – JeffO Aug 4 '14 at 15:15
  • 3
    @Kiril: a lot of very dodgy characters would also be very happy to embed dlls in documents, but not for your benefit. – whatsisname Aug 4 '14 at 15:58
16

Microsoft Office have multiple ways to let you change/enhance the default behaviour programmatically. VBA is a battle tested, proven and widely spread language for in-doc scripting. A lot of office people know VBA and use it, while they don't know more complex programming languages like C#. Office would not be sold as much if customers had to rewrite loads of old macro enabled documents that does business critical things - after they learnt a new language or whatever. Backwards compability is a key feature!

A full .NET stack for Office likely requires some set of dependency management (dll:s, etc) and will easily get heavy to manage for simple tasks - it's hardly an alternative for lightweight scripting. VSTO gives you the ability to go with C#, but at the price of a heavier plugin development cycle.

A program manager at Microsoft has written about this here. It's clear that VBA is and will still be around for small scripting purposes.

5
  • The explanation in the article is very good. Thanks. – Kiril Aug 4 '14 at 15:57
  • 6
    As an ex-staffer I can add that some clients pay big money to have Gates/Ballmer/Nadella etc available on Speed-dial as well as regular chats and that VBA is considered critical enough that any changes which break VBA behaviour (especially in Excel and even between versions) get attention EXCEPTIONALLY quickly. Also it's by no means just for the unskilled; there is an army of professional developers using it. C# is quite commonly sought with VBA as working knowledge. – James Snell Aug 4 '14 at 22:52
  • This argument didn't stop MS from obsoleting VB6 years ago in favor of VB.Net which broke lots of code. – Mike Lowery Aug 30 '18 at 23:32
  • 1
    The link is dead (killed off by Microsoft in Feb 2016) but you can still read it here: web.archive.org/web/20160201170821/http://blogs.msdn.com/b/vsto/… – rkagerer Jul 6 '20 at 4:28
  • 1
    @rkagerer - thanks, I updated the link in the answer. – Petter Nordlander Jul 7 '20 at 6:43
3

Well, the answer is not strictly "legacy". The answer is that VBA is neither VB6 or VB.Net: it is VBA. A seperate, but related language. If replaced VBA with VB.Net it would inevitably break a lot of DOCUMENTS.

Replacing VBA with VB.Net would almost certainly result in data loss for a significant number of users of their prime products -- not a good thing.

And their target market for VBA is not programmers.

2
  • 7
    VBA is a very close cousin of VB6. The only material differences are those having to do with the API; i.e VBForms instead of the Excel or Word object models. Absent those differences, you can copy/paste VBA code to VB6 (or vice versa), and it will still work 99 percent of the time. – Robert Harvey Aug 4 '14 at 15:06
  • 3
    Support for VBA and VB.Net/C# don't have to be mutually exclusive. – Joel Coehoorn Feb 9 '17 at 22:31
2

If you consider the main reason people buy Office is to keep compatibility with all the existing documents, many of which have macros and VBA in them, it would be a very brave Microsoft to treat those users like they did the VB6 crowd and tell them to suck it up and start coding in .NET, just take a look at the #1 uservoice request ever!

I imagine the LibreOffice guys would cheer themselves into unconsciousness though!

VBA is for productivity in Office, not "programming". The day you need more power from your documents is the day you hire a programmer to rewrite everything. I guess another reason is why Visual Studios macros are not .NET either - think of the devenv4 COM object as not much different to VBA.

1
  • He's not asking them to ditch VBA. He's asking them to have .Net as an additional option. – Joel Coehoorn Feb 9 '17 at 22:31
1

I think there is a slight, but important, difference between legacy and popularity. And when you've done as many contracts as I have, you learn that VBA is insanely popular :) I can't tell you how many contracts I've done for "Excel jockeys" that don't know a thing about programming but can crush VBA like it's a matter of life or death.

Not the answer you're looking for? Browse other questions tagged or ask your own question.