Bring VBA into the modern world
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 langauge.
Notes:
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.
Richard Clark
shared this idea
I want to thank everyone for weighing in – none of us on the Excel team are surprised that this item is getting a lot of votes, and we have read all of the feedback in this thread. Extensibility in Excel is something that we are passionate about, and while we’ve been focused on other investments over the last few years (primarily around getting Excel onto more devices with versions for Mac, iOS, Android, and Windows 10), this is something we’ve been actively discussing as we figure out how to enable extensibility of those new platforms.
As it stands, here’s our current position.
First, VBA is very important to Excel, and we plan to keep it around for the foreseeable future. As we add new features to Windows Desktop and Mac versions of Excel (where VBA is supported), we’ll continue to add object model for those features (see http://dev.office.com/blogs/VBA-improvements-in-Office-2016), so you have programmable access to all of the capabilities of the application. Put even more bluntly: we know VBA is awesome, and we’re committed to ensuring that you can continue to use it to build powerful solutions now and in the future.
That said, the VB runtime was built long before today’s cross platform world (on the VB6 platform which the Visual Studio team has talked about on UserVoice – see http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/3440221-bring-back-classic-visual-basic-an-improved-versi), and with the significant investments we’ve made to run Office across multiple platforms, we want to bring all the great scenarios VBA enabled into this new world and we also want to take advantage of the new opportunities this new world creates for our customers. This has caused us to explore new approaches in order to address the new opportunities of a cloud anchored, cross device world (service connectivity, cross-platform authoring and execution, standards-based languages, cloud based deployment/management, and more).
An early stage of this work is the cross-platform JavaScript APIs available to developers in Office 2016, and we are actively working on dramatically extending that API set to be in line with the existing VBA/COM OM. The good news here is that the new APIs will work regardless of the Excel endpoint/device, which will mean that solutions will be much more universal than they are today. Once we’ve broadened the API set, we’ll start working through some of the tooling (a more modern editor than the VBE) and macro recording.
We’ll be actively communicating these changes as they happen via the developer blog at http://dev.office.com/blogs, and appreciate your support and feedback as we release these updates, since your feedback is what allows us to ultimately end up in the right place.
Cheers,
Dan [MS]
86 comments
Comments are closed-
Anonymous
commented
If MS can implement JavaScript, why not VBA? Why force a change where none is needed. Every analyst in the financial services team here frequently writes VBA macros. Only three in 52 people can write in JavaScript. Six can write in Python. Why not modernise VBA and keep the many thousands of familiar, loyal users. I suspect that a more puritanical and condescending motive is in play here... we want a different language and any language, even if it isn't our IP and regardless of user needs, is better than VBA. Shame.
-
Eh Jh
commented
VBA is gold for end user developers. The fact that it's housed within the application and can be walked through while simultaneously watching the spreadsheet model update is extremely important. JavaScript is not an end user programming language. If don't invest in Excel VBA for the end user, or worse, do away with Excel VBA, you'll be making a horrific mistake. One nice investment would be making the Scripting Runtime dll native to Excel. In other words, make the scripting dictionary and file system object native to Excel VBA without need for the library reference. There are many other investments that could be made...all very winning investments for End-user programming. Again, JavaScript is NOT end-user..good luck!!
-
Sam Isaacson
commented
Dear Microsoft, your argument for javascript add-ins is not without merit BUT when you don't need a cross-platform solution, then no matter how many times I re-think through the pros and cons of how to develop my spreadsheet solutions - VBA wins over javascript (in its current form). Perhaps if you made javascript a drop-in replacement for VBA with a modern code-behind IDE in Excel and running in the Excel process this would change. The current javascript add-in structure is great for IT but exactly the opposite of what an Excel Developer (20 years of doing this makes me one) wants when they want to knock out a solution. IMHO: you are making a grave mistake by leaving VBA to die - the "niche" of developers doing useful things with VBA & Excel is more than significant enough for you to keep developing for them! It doesn't have to be either VBA or Javascript.
-
Anonymous
commented
please bring back the offline help pages!
-
Herika
commented
please bring back the offline help pages!
-
Ricardo
commented
Microsoft lost more than they knew when they abandoned VB6 programming. Don't make the same mistake with VBA programming.
-
Zev Spitz
commented
(Continuing my previous comment) In fact, the Excel / Office team would only be required to implement a langauge server for VBA (https://langserver.org/) which could communicate via the Language Server Protocol. I imagine COM addins could be used to enable JSON-RPC which is used by the LSP.
VS Code should be considered as the default client for such an integration, but any editor supporting the LSP could be used.
This would free up the team from having to create, debug and test the UI almost entirely.
-
Anonymous
commented
Please be serious and think about the corporate user who work with VBA. The VBA Editor is like a dinosaur in terms of looks and some missing functionality (themes with more than 16 colors, syntax highlighting, minimap, word completion with intellisense, multiple line editing, etc.)
VSCode is a perfect solution for programming with all of its functions, plus color scheme and syntax highlighting (like Monokai ... that would a great improvement.
Please consider creating a "modern" javascript API including these functionality
-
Anonymous
commented
please bring back the offline help pages!
-
Zev Spitz
commented
Consider integrating VS Code into Office (https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/20010145-integrate-vs-code-into-excel-at-the-same-level-as). This would allow the use of an advanced code editor with debugging capabilities, without making any changes (or perhaps some minor changes to allow debugging) to the VBA interpreter. It would also further the cross-platform goal, and provide a shared code editing/debugging experience for all languages/platforms (office.js already has type definitions on DefinitelyTyped, and thus Intellisense in VS Code).
-
Jan Wx, NZ
commented
JavaScript to me is a huge step back. Please stick to VB or VBA. I don't buy your comment above that the VB runtime was built a long time ago, it is still compatible with C# so what's the issue? I would hate to lose the on-platform performance and think of all the people in the financial world who would hate to see their functions having to run in java script presumably in the cloud where it becomes slow and insecure.
Rather focus on keeping an OO language going and please for goodness sake do something about the performance. 2016 is measurably 10x slower than 2010, some silly programmer stuffed up somewhere (and 3x slower than 2013). If it works don't touch it :) and don't make it worse -
madscijr
commented
FOR EXCEL:
Here's how Excel's (and Word's) Macro Recorder could be improved in a HUGE way:
Give the user the ability to change the macro's programming language, on the fly, while editing. That's right, the macro editor should have a built-in language converter.
I would recommend supporting VBA, Python, and JavaScript to start.
Maybe for now just keep it simple, and don't get into the difficult-to-translate features of the language, ie closures in JavaScript that are not possible in VBA. Just support the basic procedural and object-oriented functionality that VBA currently supports, which can be automatically translated to & from JavaScript and Python syntax.
This would not be limited to record-time, where if a macro is recorded in Python syntax, it would stay in Python. Rather, the editor will be able to translate from one language to another while editing just by the user selecting a different language from a dropdown.
This will attract more users to use macros, and make it easy to invest their time in learning a language.
THE EVEN BIGGER PICTURE:
Ultimately, Microsoft ought to include this macro recorder and language translator in *ALL* their Office and Windows products, and Windows itself (both desktop and mobile).
This will not only make all their products more usable and easy for novices to program (and learn to program), but it will give Microsoft a HUGE edge over their competition at Apple, Google, etc.
BRING BACK EASY VISUAL PROGRAMMING FOR THE MASSES:
1. The macro editor should support a GUI UserForm editor, where you can drag-and-drop controls onto a form, and easily double-click on them to edit their code, etc., just like in the main MS Office products, Visual Studio, and VB6. Make it easy for the regular user to harness the power of their computer in general.
2. Make it easy to use Web-based APIs: there should be a simple GUI way to reference a Web API, like VBA currently lets you reference a COM object. You reference the library, and then you can just dim a variable as whatever types the library makes available, and you get Intellisense for all the methods. You can examine the class in the object explorer, etc.
This is 2017, let's get back to making programming EASY again, and empower regular people to automate their tasks! Visual Basic 6 and VBA were a revolutionary step forward with this, but somewhere along the way, mainstream programming languages and tools threw away a lot of the ease of the visual programming aspect. The expectation changed to be that the user should educate themselves, but the truth is that a lot of the best practices and concepts are more complicated than ever. If anything, there is just more out there. Way too much information to have to know for the casual person to be able to effectively program. With VB 6 and VBA you didn't have to learn 1000 different frameworks and design patterns and protocols, it was possible to just kind of wing it. As long as you knew some basics of procedural and OO programming, you could reference a library and figure it out with IntelliSense and the object explorer. The world needs this kind of ease again, badly. -
madscijr
commented
First, I have a concern about making the macro engine cloud-based. One of the best things about desktop Excel is the immediate responsiveness, and that you don't have to be online to use it. And one of the best things about VBA in general is that with COM you can automate many many other applications on your computer, and integrate them into Excel, right from your Excel (or Word, Access, etc.) macro. This makes VBA extremely useful as a general tool for Windows. To make it cloud-based would introduce some things I am not too excited about:
1. Dependence on an Internet connection
2. Dependence on 3rd party Web service APIs. How many times have you found an old URL has changed or the site taken down? When you have an application installed on your computer locally, you know it will always be there (unless your computer dies, and even then as long as you have the install CD or backed up your PC, you have a fallback plan.)
3. Laggy performance and user experience compared to desktop applications. The Web-based experience just doesn't equal the desktop experience.
4. The whole cloud movement basically gives you less control over your own computer, and forces you to rely on fragile dependencies. -
Anonymous
commented
a listbox with multiselect set has no control source, the developer has to write code to populate a cell or range of cells with the selected items from the listbox. The Control source should allow either or both of: a) single cell control source where all input values appear as a string in the cell (with a delimiter like ";"), or the control source should be a range of cells equal to the length of the row source
-
Number kruncher
commented
There are many routines posted in forums that will fill the spaces with blanks or use the Send Keys approach, however, these have proved unsuccessful for many.
-
Monica
commented
Please provide a better programming environment for Excel. Some of my clients are die-hard excel fans for some reason, and doing VBA in excel is a nighmare compared to Access and vb.net in VS.
-
Wiens
commented
Hello in a VBA macro the following Exit statements are missing:
Exit While
Exit SelectAs a workaround one can use the 'Goto' statement for exiting a loop. These statements are currently only available in VB.NET, see for more background information the following website:
https://msdn.microsoft.com/en-us/library/t2at9t47.aspxIt would be nice if these statement are also compiled in a VBA macro.
-
Hideki ASHIDA
commented
I've made some macros (wrote VBA code) for over five years.
My requirement, first of all, is a feature of "Folding" code block.The more code written, the poorer visibility, i.e. hard to overview the whole structure of codes.
In addition, it becomes hard to jump to a line where I need, i.e. requires much scrolling."Notepad++", for example, is a freeware. Nevertheless, it has such feature, so it is easy to see and write codes with using "Notepad++".
Please make VBE have a feature of "Folding" code block.
Thank you, in advance.
-
Andrew Jackson
commented
I'd like to be able to read and write built-in and custom VbProject and VbComponent properties. For example, the VbProject's Conditional Compilation Arguments, GUID and character encoding.
The VB6 VBIDE model already has ReadProperty and WriteProperty methods on VbProject and VbComponent objects, but these properties are only for user-defined properties, and they're not available in VBA's VBIDE object model..
Modern Office object models expose BuiltinDcoumentProperties and CustomDocumentProperties properties, so perhaps VbProject could add BuiltinProjectProperties and CustomProjectProperties, and VbComponent could add BuiltinComponentProperties and CustomComponentProperties?
That would allow enumerating, reading and writing properties that are built-in and/or user-defined, and it would follow the modern Office conventions.
-
Wiens
commented
The following two Exit Statements are missing in Office VBA when one wants to Exit the function block While-Wend or Select Case-End Select:
Exit While
Exit SelectIt would be nice if these statemens would also be available/compiled in VBA like in VB.Net, see this link for more information about Exit Statements:
https://msdn.microsoft.com/en-us/library/t2at9t47.aspx