Integrate VS Code into Excel, at the same level as the VBA IDE
VS Code has an equivalent feature set to the VBA IDE (editor with Intellisense, syntax highlighting and validation; debugger with locals and watch panes, code navigation). Leveraging VS Code for editing and debugging macros embedded in Excel documents would bring the following benefits:
VS Code is cross-platform, which would allow a shared UI across both Windows and Mac systems.
Excel would benefit from the features of the VS Code UI, and from its continual improvement.
This would require the following:
Language server for VBA (https://code.visualstudio.com/docs/extensions/example-language-server)
NB. Language servers can be written in any language, so it might be feasible to reuse the current VBA parser and Intellisense generator.
Debugging extension 9https://code.visualstudio.com/docs/extensions/example-debuggers)
For other languages, modification to existing language servers, to provide Intellisense based on the current application context (Excel, Word, Access etc.)
A UserForm designer extension for VS Code.
A mechanism for installing VS Code, and for opening embedded scripts/macros in VS Code
VBA editor is a bit like the white hairs
@JamesLee @vinnyjames I think an overhaul of the IDE is not something Microsoft is willing to invest in ATM. However, if MS would be willing to provide a language server, any editor (VS Code, Atom, Visual Studio, Eclipse, etc.) could theoretically be used to write VBA code.
Fortunately, the Rubberduck community have started work on a VBA language server (https://github.com/rubberduck-vba/Rubberduck/issues/5176) using the Rubberduck parsing engine, so we won't have to wait on MS for that either.
Make sure to vote for the issue James Lee mentioned. It has more votes and should be getting more attention.
Microsoft.....where are you....?
They ignore every request about getting a proper VBA IDE request. It's so frustrating...they are ignoring the very people who promote their products and build on them.
Microsoft....the IDE...tell us your plans....come on...
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
Anyone from MS feel like responding to any of this?
@Zev the VBA limitations would be manageable for me if it was in a modern IDE. Things like better auto-complete, navigating to definition, renaming variables/functions, and not complaining up a storm every time a line of code isn't perfect before you move the cursor to another line to copy a variable etc. All of these features are standard and/or relatively easy to implement in IDEs like VS Code.
All that being said, yes I'd prefer to code in C# but I think a more practical/logical first step would be to support separate IDEs to write the code in. I can't imagine it's that hard to have the office application grab the code from an editor/file system and update the spreadsheet on the fly.
So this makes some good counter arguments.
Another benefit to .NET would be the huge number of programmers who learn it for other reasons.
Class support is garbage. No proper inheritance.
> it's quite frustrating to have to use the much more limited language of VBA
What specific language features do you feel are missing from VBA? Not editor features, because that is the subject of this suggestion. The only language feature / API I miss very strongly is functional data processing, which would require 1) first-class functions, and possibly 2) generics.
> much harder to manage than it should be with Excel's VBA editor
Again, does this reflect the limitations of VBA? Or is this because of the ancientness of the VBA editor?
The litmus test would be to use http://rubberduckvba.com, which is an active addin for VBA dedicated to adding features to the editor.
@vinnyjames agree. it's relate hard to just write codes in VBA editor. But other editors all lacks of supporting for Excel VBA.
My workflow now is export to edit and import to check if all work fine.
Yeah C# would be the best. We maintain a file with multiple modules and 10s of functions in each module. Not really a ton of code in my opinion but much harder to manage than it should be with Excel's VBA editor. Especially if you have more than one version of the file open to compare/merge code.
I disagree that it's a simple use case. Having had to maintain VBA projects myself in the past with medium complexity, it's quite frustrating to have to use the much more limited language of VBA. I say this as someone who started on GWBASIC and eventually moved on to "real" languages like C++ and Java. Having to go back and use VBA you quickly notice the dearth of useful syntaxes even compared to VB.NET. Personally I would use C# if given the option, not VB.NET, but no reason not to support both.
> translate 90% of VBA scripts to VB.NET
What would VB.NET bring to the table that you don't already have in VBA? It would only increase the complexity of what is a very simple use case -- scripts that manipulate Office application objects embedded inside Office documents.
Best solution is probably to support both. AutoCAD still supports Lisp but also has .NET as a modern option. I think it wouldn't be that hart to translate 90% of VBA scripts to VB.NET, and if it's not supported, you could simply say "not simple enough for the translator."
> I hear you saying we need a VBA.net.
I think the only changes to VBA which would add significant value, would be 1) access to the .NET framework, and 2) functions as first-class-citizens, which would enable LINQ-style data processing.
No doubt about translation to VB.net. But with the right API I feel the language part could be done. I hear you saying we need a VBA.net. ;)
I agree with you 100% Zev. I made a post using Microsoft's feedback hub which might get more attention from the folks at Microsoft. Here's the link to my feedback if anyone wants to upvote:
> when you have processors with 8 cores 16 threads and there you have a language which is single threaded.
In order to use that power, you need a language which introduces significantly more complex paradigms. Just like it doesn't make sense for virtually all LOB applications to be written in assembly, in spite of the greater power and flexibility this would offer; the use case for VBA usually doesn't require the performance efficiency that multithreading would bring.
> why anyone would complain about VBA and suggest that JS is a viable replacement
> It makes far more sense for the official language to be VB.NET as this is the rightful heir to VBA7.
And yet, after nearly 20 years, VBA has not been replaced with VB.NET, because VB.NET is still not a drop-in replacement for VBA. Programming in VBA is a simple matter of opening the editor writing code, and saving the file; the code is now part of the document. Providing embedded VB.NET code in an Office document, or via an Office add-in, is an order of magnitude more complicated/
But again, the point here is not "what should the official language be?", but rather making it possible for **any** language to be embedded in an Office document and to make use of the application's API, and providing a unified editor experience via VS Code.
> But VB.NET is a must since it will be much more similar to VBA and allow porting of macros more easily.
VB.NET is far closer (though not entirely so -- https://anthonydgreen.net/2019/02/12/exhausting-list-of-differences-between-vb-net-c/) to C# than VBA. There may be some superficial resemblance in syntax between VBA and VB.NET, but a language is far more that its' syntax.
> allow porting of macros more easily
... which is why there are a number of code translators between C# and VB.NET, because that is a far simpler problem than translating between VBA and VB.NET.
Once you support VB.NET it is trivial to support C# since they can produce identical byte-code. But VB.NET is a must since it will be much more similar to VBA and allow porting of macros more easily. An auto-translation would be nice.