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:
3. VS Code is cross-platform, which would allow a shared UI across both Windows and Mac systems.
4. Excel would benefit from the features of the VS Code UI, and from its continual improvement.
This would require the following:
1. 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.
2. Debugging extension 9https://code.visualstudio.com/docs/extensions/example-debuggers)
3. For other languages, modification to existing language servers, to provide Intellisense based on the current application context (Excel, Word, Access etc.)
4. A UserForm designer extension for VS Code.
5. A mechanism for installing VS Code, and for opening embedded scripts/macros in VS Code
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.
It makes far more sense for the official language to be VB.NET as this is the rightful heir to VBA7.
I would love to see a VBE based on VSCode that accepts both VBA and VB.NET modules. It should be noted that you can already compile extensions for Office if you would like to code in C# or VB.NET. The VBE is much faster and easier. The interpreted nature of it makes it a great learning tool.
VBA is like a trash in 2019, when you have processors with 8 cores 16 threads and there you have a language which is single threaded.
Have VBA support, but provide alternative like C#. So that user can take advantage of system configurations.
@Conan Keep in mind the use case for VBA -- it is designed to augment either an Office document, or an instance of Office. It's not meant to be a full-blown language / environment for standalone programs. Also, because VBA has a relatively long history, and has been adopted in enterprise environments, it is very difficult to divert resources to new features, while ensuring backward compatibilty with all this legacy code.
Asynchronicity does introduce a certain level of complexity, and in virtually all cases is not needed for the VBA use case.
Framework support would be nice, but not as necessary.
Callbacks would be a very welcome addition, as they would enable a functional style of data processing. I think it's a resource issue.
Note that @Mathieu Guindon is quite familiar with C#, WPF, and .NET, as he is the maintainer of Rubberduck, an VBA addin written in C# which adds a number of useful features to the VBA IDE.
Also note that the main point of this proposal is NOT multiple language support against the Office object model (which if you don't want to embed in a document, is quite possible and straightforward). The primary point here is that the VBA editor+debugger is rather bare-bones, and since Microsoft does not seem willing at this point to invest resources in improving the editor, and extending the editor currently requires a deep knowledge of COM and creating COM addins, VS Code seems to me like a viable alternative.
these are the words of a person who only knows how to code in vba.
just thinking about vba not supporting :
callbacks, asynchronicity, framework support, Module Systems (like Node.Js) and many more
makes me sick of vba.
time has come to replace vba with a way more powerful and modern language like js/python!