A Better VBA Than VBA
I am a self-taught programmer; I got my start in college creating an Excel-based system for balancing the books of an administrative office at UCSB, where I was an undergrad at the time. It was very unusual for undergrads to work in administrative jobs on campus, but having major spreadsheet mojo at a time when spreadsheets were becoming the greatest thing since sliced bread is what opened the door for me.
The programming language that made all this possible came to be known as Visual Basic for Applications, an embedded version of VB that lived in most Office apps (as well as some third-party apps).
A few years back Microsoft began nudging developers to use .NET for Microsoft Office programmability. When I was at eBay, my team participated in the launch of Office 2003 and Visual Studio Tools for Office, which was basically a big interoperability library to let you program Office from .NET. At the launch event, Bill Gates demonstrated an Office app that used our API, which was impressive and exciting even if nobody would use Word and Excel to list stuff for sale on eBay in real life.
I’m a .NET fan, but I have to say that it doesn’t seem to be such a terrific fit with Office — it added quite a bit of complexity and in many ways reduced, rather than enhanced, the things you can do with Office application programming. I’m not even sure where the whole Office/.NET programability initiative has gone today. I wonder if Microsoft should revisit it, to start from scratch. I definitely find myself missing writing nice little VBA macros to automate my work in Excel and Word. (My last big VBA project was the Excel/Yahoo Maps integration I did back in 2005 and which people still occasionally email me about.)
I also wonder whether the very interesting work that’s being done on the dynamic language runtime might have something to offer here.
I could envision how IronPython (or even C# utilizing the dynamic runtime) could become a better VBA than VBA. One of the big historical criticisms of Office/VBA applications is that they’re brittle — difficult to maintain and upgrade and prone to breaking if the user doesn’t have a configuration that is just right. But I was never clear on how .NET solved those problems. Maybe some dynamic language extensions that are specific to the mission of automating applications like Word or Excel would add some value here?
I’d love to build an integration between Microsoft Office and Approver.com, but frankly at this point I have no idea where to get started. Do I need to pay Microsoft for a copy of Visual Studio Tools for Office? Will Office 2010 break my application somehow? What happens if I need a solution that will work on the Mac?
I feel your pain. The project I am on at work started as a VSTO application until I helped kill that direction. They had done 3 months of research, and still hadn’t figured out how the code was going to be deployed and updated. And of course, its more lock-in to MS Excel, so there is pretty much no way right now for it to support the Mac.
I changed it to a Web app, and it works pretty close to what we would have had in Excel because of all the AJAX work I did. And we are about 95% compatible with the Mac using Firefox, the only thing that doesn’t work is keyboard shortcuts because on Windows we support copy-paste functionality with keyboard shortcuts, but the third-party control we use doesn’t support command-C and command-V. Still, I think it was easily the right call for the project.
And of course, MS is dropping VBA in Office:Mac 2008 anyway, so VBA is a dead-end. With a portion of the .NET runtime in Silverlight on the Mac, why not make that part of Office on both platforms?