It would have a couple of stages.
Stage 1: Get Visual Studio features into the VBA development environment.
Stage 2: Fully integrate VBA into Visual Studio.
Stage 3: Continually improve VBA until it is a modern language.
VBA is probably the most used programming language in the world.
It has so many established users that MS could really get big brownie points by continually improving the experience for users and developers. Why it has stagnated for so long I have never understood.
I have seen your response for the above posted previously and you are missing a major point. We don't all work in open environments where we can distribute COMs, addin etc...
VBA gives use a level of control directly within a file, that can be sent to other users, clients where they don't need to install anything beyond the office suite. If the direction your blog stated of addin's, don't need the user to select anything, install anything or have the addin hosted anywhere or do anything but open the file I have sent then, please let me and everyone else know, I will happily change.
But like many, my computer set up is not as an admin, cant install anything, cant host outside of the environment of the company I work for, who wont spend money so I can produce an addin, there does need to be something and currently VBA is it.
A modern gui like visual studio, doesn't need to even be in the office suite, but maybe a link like the VS and unity, where VS can deal with the object model and write directly back etc....
So please Microsoft, consider the question from the perspective of the power-user and the profitability of Excel - and not from the perspective of a professional developer - and dedicate some resources to updating VBA and its IDE.
James Lee commented
Comment on an idea about VBA IDE that's under review at https://officespdev.uservoice.com/forums/224641-feature-requests-and-feedback/suggestions/15446658-give-the-vba-ide-a-major-overhaul-finally
I see moderators declining VBA-related requests, but I think there are some changes that are VBE-only that would be fairly simple, and would have a *huge* impact. My #1 VBE request would be features from Notepad++ where sections of code (loops, it/endif, etc) are connected with a line and can be collapsed ... the amount of troubleshooting time that would save...
the more advanced version (which is maybe asking too much) is to integrate that with the debugger. so if you have to walk through some code to see what is happening, it would skip through collapsed sections of code automatically, so for example a macro triggers an action to loop 1000 rows, it processes that code without having to put in another breakpoint after that code block and hit F5 each time you hit that code block or function to get past it
Oh lordy, please do this.
Mathieu Guindon commented
I own rubberduckvba.com and manage the Rubberduck open-source VBIDE add-in project.
There are a couple of problems with this feature request: it's conflating VBA (the language) with the VBE (the editor).
Getting VBA "fully integrated" in Visual Studio isn't going to fly, for many reasons. First, a parser would need to be written from scratch for Roslyn to work with it (VB.NET syntax trees can't work with VBA). Then the language would need to be built from the ground up with Roslyn. This is a tremendously complex task, with little to no benefit at all. The COM libraries (including the VBA standard library) would have to be ported over to the .NET world, which is another enourmous task, with real possibilities to introduce subtle bugs literally everywhere. The quirks of the language would have to be replicated in Roslyn too, so as to not break billions of lines of code that are working fine in COM-based VBA land.
Moreover, VBA developers don't want to use Visual Studio. Most VBA developers aren't programmers. They're financial or marketing analysts, merchandise planners, whatever: forcing them onto Visual Studio would be a massive problem: the full-featured-ness of VS would be playing against it, people familiar with the VBE would be completely lost. So not only integrating VBA in Visual Studio would be a technical near-impossibility, it very likely wouldn't get the buy-in from its target audience.
VBA isn't inherently tied to Office, either: there are well over 200 3rd-party applications that used the SDK to integrate VBA in their applications. Sage ERP, SolidWorks, Corel DRAW, AutoCAD, to name a few. Integrating VBA into Visual Studio would wreck these.
There is definitely room for improvement though: as I said, I manage the Rubberduck OSS project, and our goal is literally to bring the VBE into this century and, actually, provide a solid answer to "stage 1" above, making the VBE on-par with modern IDE's. "stage 3" is unrealistic because of "stage 2" being pretty doomed. So that leaves "stage 1", which isn't about updating VBA at all, but about updating the VBE: people hate the editor, not necessarily the language.
If the VBIDE API was easier to work with, especially through managed COM interop (i.e. extended from C#/.NET). Improving the VBIDE extensibility library would go a long way towards helping 3rd-parties such as the Rubberduck project, achieve this goal of modernizing the VBE.
- Expose code pane events. One of the most frustrating thing with the VBIDE API is that it's impossible to tell exactly when code is being modified, at least without subclassing the editor or capturing keypresses, which destabilizes the host process. Or...
- Expose annotated parse/syntax trees. Rubberduck needed to implement its own parser and resolver off the VBA language specifications in order to pull its prowesses. This has been a daunting task, and while we're 99% there, that 1% is really, really tricky.
- Expose project properties on `VBProject`. It's currently impossible to know anything about project properties without physically bringing up the properties dialog and implementing some nasty p/invoke to grab textbox values, e.g. to pick up project-wide precompiler constants.
- Expose module and member attributes through the CodeModule API. It's currently impossible to pick up these very important values (let alone modify them) without physically exporting the code file and re-importing it back in.
- Fix the annoying bug that inserts an extraneous empty line when re-importing a UserForm. Rubberduck is working around it, but we shouldn't be needing to do this.
IMO that would be more realistically feasible than anything else mentioned on this page, and it would have very impactful effects on the ability of 3rd-parties to seamlessly integrate with the VBE.
Taylor Scott commented
Honestly, something as simple as a dark mode for the VBE would be more than enough to keep me happy for several year
Zev Spitz commented
Visual Studio is probably a bit heavy for this purpose, but maybe VS Code? (https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/20010145-integrate-vs-code-into-excel-at-the-same-level-as)
Anthony Taylor commented
@bettersolutions - totally agree...
The original suggestion for visual studio link etc. was not to change what is done, but if MS dont want to invest in VBA as they have hinted at, then by doing a link into visual studio, we can have the improved interface with no major changes. I would prefer the interface improvements within the VBA interface.
My comments are also not about adding lots of ways of doing anything, but as you have said improve the interface and this can be simple things like a todo list, proper indenting for readability etc.
"One of the reasons VBA is so ubiquitous is that it hasn't changed significantly in 20 years"
This point cannot be stressed enough. Business Users that want to be productive do not want (or have the time) to be learning new features and functionality every 6 months.
Take a look at C#, the features, functionality and user interface have been constantly evolving and changing at a relentless pace since Visual Studio 2003. This in my opinion is why so few have migrated from VBA to C#.
For example, let's consider delegates in C#.
C# 1.0 - Introduced Named Delegates
C# 2.0 - Introduced Anonymous Methods
C# 3.0 - Introduced Lambda Functions
I appreciate there is a strong argument for new features but I think trying to find the right balance is what is needed here.
There is no doubt that VBA could do with a few enhancements (especially to the User Interface). But what VBA does not need is 5 different ways to achieve the same thing (which is what we have in C# 6.0.
Most C# Developers now, do not care about increasing productivity, they just want to write the most complicated code possible (for kudos amongst their peers), which is extremely easy to do using C# and Visual Studio.
Anthony Taylor commented
I dont think the core should change, but the interface should be updated to provide the basic feature of modern languages. Rubberduck project is a huge step in the right direction.
By allowing people to have alternatives, would not be a bad thing, while maintaining the simplicity for the occasional user.
Marcus Mangelsdorf commented
Or most simply, just split the source code from the xlsm file or make it easily ex-/importable. This way we could at least get version control :D
One of the reasons VBA is so ubiquitous is that it hasn't changed significantly in 20 years. Starting to change it now would scare away the occasional user and result in another "full featured platform" that only programmers have any chance of make effective use of.
Mihai Popa commented
Idea - VBS pentru orice fisier EXCEL (fara riscuri, o extindere la formulele Excel)
Intr-un document EXCEL sa se poata adauga cod VBS mult mai usor fara sa se intre efectiv in editorul VBS.
1. Adaugarea de cod VBS pentru o celula dintr-o foaie de calcul:
1.a. se da clic dreapta (cu mouse-ul), din meniul contextual se alege "Adauga cod VBS"
1.b. se deschide un mini editor VBS in care se poate scrie cod VBS astfel:
- sa existe posibilitatea de adaugare cod VBS pentru diverse evenimente pe care le poate trai celula din foaia de calcul (schimbarea continutului, selectare, golire, pozitionarea mouse-ul deasupra celulei etc.)
- codul VBS sa nu ridice probleme de securitate, in acest sens mini editorul VBS sa accepte doar anumite instructiuni VBS (fara instructiuni care acceseaza resurse inafara fisierului Excel in care se scrie codul VBS).
2. Adaugarea cod VBS pentru o foaie de calcul ceva similar ca la punctul 1. Sa permita variabile vizibile in Shet-ul in care au fost definite (variabile vizibile si in celulele din cadrul foi de calcul).
3. Structura codului VBS sa fie foarte simpla, fara proceduri si functii.
4. Codul VBS sa nu ridice nici o problema de securitate astfel incat sa nu fie nevoie de restrictionare executari acestui cod VBS.
Stiu ca tot ce am scris mai sus se poate face si in prezent prin editorul VBS, idea ar fi sa simplificati utilizarea VBS pentru utilizatorii incepatori si medii, pentru mixorarea timpului de scriere a codului VBS, pentru simplificarea codului VBS.