Thursday, September 17, 2015

Microsoft failed totally with the VB6: very dissapointing really as the tech for the solution exists already.


If the new boss is listening, Microsoft failed totally with the VB6 - dotNet transition when it tried to tell people what they wanted and push its new product with no regard to how they used existing. i.e. forget that you can do 90% of what you want in VBA and that VB6 gave you an easy way to build components for reuse and users just want a few improvements, no - force dotnet, buy a full version of visual studio then force users to use VSTO to build a complex set of libs and dlls that need installed, project solutions etc and to debug you have to launch excel as the start app. 
In comparison to VS, Alt-F11 to edit code inside the app is so convenient for a cpl of quick functions. MS has never understood scripting but really VBA is a script within excel (deployed with the spreadsheet) which is why it was so popular. VSTO is a mess of complication in comparision, the project overhead totally unsuited to simple tasks which is why it falls back to VBA for most users. 
The developer vs power user argument is bogus too. I'm a developer but I often want to do a quick macro or script for a user rather than a new project solution with all the overhead that entails. 
The result of all this is that 15 years after dotnet, people are wanting VB6 to be released again and MS still doesn't have a coherent office dev strategy! Its really poor! I think VB6 is only wanted as it is the closest thing to VBA components and for backward compat and because there is no native way to start a dotnet project from within excel. 
Very dissapointing really as the tech for the solution exists already. Improve the internal sripting in VBE with DLR and you can leave the rest of class libs dev etc in dotnet/VS for more complex stuff. excelDNA provides a way (currently much simpler than VSTO) in which you can compile code to run in excel with less overhead. This allows you to type code in notepad and build code dynamically and internally on the fly. Unfortunately you still don't have an editor and you still have to release the xll.
Is MS wants a strategy (or a prototype for one), it should buy xldna and bundle it with excel so turning it on is not installing an addin but a built in feature in excel that can be enabled. Then it could improve the editor that comes up with Alt F11 to be a basic dynamic language runtime/dotnet editor. I mean if you could host notepad++ even (which has multi language intellisense) within the Alt F11 window and compile exceldna style internally you're nearly there! This would allow you to build functions and modules and basic scripts that fit in exceldna (that could ideally be written in iron python, ruby, F# as well as c# (maybe even VB.NET as the easiest port given its the obvious successor to VBA...). write inline code, save it internally in sheet and use an internal xldna runner to run and debug within a sheet. A simple reference mechanism to allow other dotnet libs (on the path) to be used directly and a default reference to the excel object model (or any other office) and you've more or less got the VBA replacement. 
If you want to debug your class libraries you could still run a full version of VS with excel as the debug exe as per normal and step in from the new VBE env in dotnet into any classlibs running under VS (otherwise you would just get step over functionality for all external libs except the code in the VBE). 
Then you could still have have a VBA/VB6 runtime object exposed which would allow backward compatibility but you could compile each module in a different language potentially allowing new and old bridging.
Its a testament to MS continued efforts to plug products rather than provide platforms that users want that they still want vb6 back when all they really need is a way to do code within excel diretly in a newer language. exceldna with the DLR could easily provide a way to do this with a little effort from MS to integrate into excel. I mean there is probably a dotnet runtime installed with excel anyway so surely an editor and debugger for local scripts wouldn't be hard to integrate? 
This is what your users really want. Write typical VBA excel object model code in python or c#, internaly in excel with the ability to call out to other libs... VSTO and Apps for Office totally miss the point....
Ma La commented  ·