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#?

gnat
  • 21,442
  • 29
  • 112
  • 288
Kiril
  • 529
  • 2
  • 4
  • 11
  • 11
    "The only reason I can think about is legacy." And that is not enough for you? – Euphoric Aug 04 '14 at 14:44
  • The good ol' "we always did it that way". If this is the only reason I'm kind of disappointed. – Kiril Aug 04 '14 at 14:52
  • 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. –  Aug 04 '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 04 '14 at 14:57
  • @Doval I'm not saying that they should completely abandon it, but why not enable an easier integration. I would be so happy, if I could embed a dll in a xlsx/docx out of the box. – Kiril Aug 04 '14 at 15:02
  • 1
    @Kiril - you question asks to ship Office with .NET "instead" of VBA. That would require abandoning it. – JeffO Aug 04 '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 04 '14 at 15:58
  • VSTO has been shipping with every version of office since 2007 that I remember. Yeah the default implementation is VBScript / COM but that don't mean you are stuck there, especially for meaty stuff. – Wyatt Barnett Aug 05 '14 at 16:48
  • No discussion about a .NET integration into office will be complete without mentioning [Excel.DNA](http://excel-dna.net/). – Doc Brown Jan 13 '16 at 06:24
  • "The IDE looks like Visual Studio 2003" - no. The IDE **is** Visual Studio 6.0, which predates .NET. – Mathieu Guindon Nov 30 '18 at 17:41

4 Answers4

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.

Petter Nordlander
  • 362
  • 2
  • 9
  • The explanation in the article is very good. Thanks. – Kiril Aug 04 '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 04 '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: https://web.archive.org/web/20160201170821/http://blogs.msdn.com/b/vsto/archive/2013/06/18/roadmap-for-apps-for-office-vsto-and-vba.aspx – rkagerer Jul 06 '20 at 04:28
  • 1
    @rkagerer - thanks, I updated the link in the answer. – Petter Nordlander Jul 07 '20 at 06: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.

jmoreno
  • 10,640
  • 1
  • 31
  • 48
  • 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 04 '14 at 15:06
  • 3
    Support for VBA and VB.Net/C# don't have to be mutually exclusive. – Joel Coehoorn Feb 09 '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.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
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.

Unknown Coder
  • 249
  • 1
  • 8