Excel code language

I’ve been bleating on for ages that we should have had VSTA in Office 2007 (we did I guess – I mean in Excel and Access) and I really really think we need some serious coding improvements in Office 14.

Initially I was thinking just an updated VBIDE would be fine. Then looking at VSTO/VSTA I thought that would be better, a nice fresh .net IDE with choice of C# or VB.net (from now on to be known as VB).

Then I was messing with Resolver and its python language, and I spotted this comment here:

“Will there be a role for IronPython (or IronRuby) here, someday, such that you could use these languages inside Excel? That’d be very cool.”

I’m a bit torn. Python looks like an easier language for people to get going with, but loads of people have already invested in VB/VBA. (A bit of a ribbon/commandbars situation?)

So my question to you is this:

Which would you prefer in future version of Excel:

  • An updated VBA editor – leave the language as-is
  • A .net style editor and an updated VBA – VB.net sorry VB
  • A .net style editor and the full choice of .net languages
  • Something else entirely?
  • All of the above?

I am not really sure, personally I’d like C#, but I don’t know how valuable that would be to many other devs. (and C# Excel is a bit grim I accept). One thing I would like to see though is a simple pain free migration path away from spreadsheets + code, like VB6 offers for VBA today.

Just VBA with a better editor would probably do, but try/catch/finally is nice, as are parameterised constructors and overloading. Is VBA plus these 3 basically VB.net? And objects in VBA kill performance, maybe better objects would be good?

And what about deployment – .net has never really shone in the deployment stakes in my view. VBA embedded in the doc is so simple (but yeah I know security/maintenance/blah blah…)

What would you like to see? For yourself? and for Excel devs in general? And what about non-advanced users? Tom suggests something vaguely similar to scratch for kids.

Do we need 2 languages (current/active ones – not many of us still use XLM) in Excel (like Access has)? One with VBA or more power and one point and click drop down simple one (perhaps macro/server safe)?.

I think for me, all things considered I think I’d just like a bit of an update to the VBAIDE. A tabbed editor, a richer project explorer, MZ tools still working – that would probably do for me I think (for now). Would that do for you or do you want more? Or something different?

Cheers

Simon

Advertisements

25 Responses to “Excel code language”

  1. MikeC Says:

    Morning Simon, you’re really on fire with topics at the moment!

    IMO, I would say that, subject to continuing MSFT support, VBA with an enhanced IDE would be the best choice for Excel.

    * easy to start to learn, good learning curve
    * covers pretty much every Excel-specific requirement

    To take the first point first (seemed like a sensible thing to do…) VBA is easy to start to learn for a novice. Want to know how to do something in VBA? Record a macro then look at the code. Easy. Possibly the most straightforward method of learning how to do things in any language I’ve ever seen. Yes, I know, you can’t do that with some things, but they tend to be the more advanced options which a novice isn’t likely to want to do (or even know it’s a possibility).

    For the second point – how many things do you want to be able to do within a spreadsheet (remember – it IS a spreadsheet at the end of the day, not a fully-fledged development platform!) that VBA can’t handle? It might not always be an ideal solution at the end of the day, but it’s generally possible. Or at least everything I’ve ever tried is – feel free to tell me I’m talking tosh! =;-)

    (OK, EXCEPT for automatically mailing the workbook without requiring the user to accept the security dialogs (and before anyone suggests it, an audit trail was required, so using CDO isn’t an option))

    It’d be nice to see the option to use Excel as a platform for operations based on other languages such as .NET or whatever, but when VBA does such a good job, would it really be worth the effort to do this for such a minimal gain?

    Cheers,
    Mike

    *sits and waits for the torrent of “Mike – you’re talking tosh” replies*

  2. dermot Says:

    I’m with MikeC on this. Devs tend to forget that 99.999999% of all VBA is not written by them, but by ordinary users, and often for important business tasks. So keep it really, really simple.

  3. Stephane Rodriguez Says:

    “And objects in VBA kill performance, maybe better objects would be good?”

    Strongly disagree. It’s not VBA which kills it, it’s whatever Excel does internally to sync up everything after each method call.

    (that’s why direct access to the file format, OOXML, is a farce since you are losing all what Excel does for you.)

    “I think for me, all things considered I think I’d just like a bit of an update to the VBAIDE.”

    I think it’s doable today, I don’t have the motivation to do it though. Remember that the VBA/VBE integration is just an add-on itself. It’s not an add-on in the sense of the stuff exposed through xlAutoOpen. But it’s a low-level COM add-on (you can get the integration SDK from Summit software). A consequence is that you can replace it with something else that would have more features, such as a tabbed editor, .NET editor, …

  4. Simon Says:

    Stephane – IDE/motivation – snap!
    VBA objects with no Excel OM run slow, I’ve never bothered to try in Access/Word, but I’m sure they would be as slow there so I’m not convinced its Excel.

  5. gobansaor Says:

    As Dermot says, whatever replaces VBA should either be VBA or something even “easier” than VBA (that’s why I like the approach taken by the MIT Scratch project). As for professional developers, better integration with .NET is the way forward (even now using something like ExcelDNA takes a lot of the pain away). .NET then allows the developer to pick his/her language(s) of choice, c++,c#,vb, or JScript and with advances in DLR scripting support, Python or Ruby.

    Or even Java via the IKVM toolset!

    Tom

  6. Stephane Rodriguez Says:

    Simon, my product has a COM API which is exposed to COM-compatible environments, including VBA. Anyone running VBA code can experience several orders of magnitude speed improvement over the equivalent VBA code targeting an Excel instance. This proves the bottleneck is not VBA.

    If you say VBA without Excel OM run slow, I still disagree. It is slower than say running a C dll interfaced with Excel (for obvious reasons since there is no method call marshalling), but not something of the scale of what you are talking about.

  7. Rob Bruce Says:

    Aren’t VBA objects slow simply because the language is not compiled until run time? Just in the same way that MS Intermediate Language is not ‘properly’ compiled in .NET; it still has to go through the JIT compiler every time it is run?
    I don’t think a move to .NET would help: Then add on the interop overhead: I don’t think Excel is going to stop being a COM application any time in the near future.

    Rob

  8. Dennis Wallentin Says:

    I believe that MSFT, at least, should update the VBIDE and improve the VBA model (including support for ADO.NET and maybe LINQ). I don’t think it’s realistic to assume that MSFT will completely rewrite Excel in the nearest future.

    The “marriage” between .NET and COM includes a performance penalty due to the existence of a interop layer (for VSTO solutions it involves a VSTO layer as well). However, this may or not may be be a critical factor in specific cases. Overall I find .NET solutions to be both stable/reliable and easy do deploy (except for VSTO solutions).

    I have always had the impression that VBA is an interpretating language and as such it’s slower then compiled code.

    Kind regards,
    Dennis

  9. Charles Says:

    Compiled VB6 does not run any faster than interpreted VBA as far as I have been able to detect in tests (might be faster on very intensive mathematics), apart from when you hit the UDF VBAIDE refresh bug.

    Most of the time used by VBA is usually passing data to/from Excel, and is highly dependent on the method you use for the transfer. Then a lot of time is spent in the VB runtime itself (which is compiled anyway).

  10. Biggus Dickus Says:

    I find everyone (especially Microsoft) forgets that Excel VBA is meant to be an “Application Macro Language”. That means its primary (and only) purpose is to automate a spreadsheet. I have always thought that VBA (as much as I love it) was over-kill, and that VBA has hurt rather than helped growth of “quality” spreadsheet automation.

    I find it interesting how Access has upgraded its “Macro” capability and how they seem to be moving toward that and away from VBA (for various reasons). Maybe Excel could use a similar “Macro language” (??).

    But in the end I would like to see VBA continue, with an upgrade IDE – simple. Whether that happens is another question. I believe that MS wants to kill VBA and there isn’t much we can do about it.

    They also have worked hard on VSTO (and I question the success – although they claim it’s a BIG success (?)), in an attempt to get “professional developers” (as opposed to us “professional non-professional developers”) to include Office in their solutions. I still think that is a waste of time. They’re trying to sell Office to a group of people who simply aren’t interested (and in fact are openly antagonistic) toward Office. Why not sell Office development to the true believers – power-users and us “Professional Non-Profesisonal Developers” (??).

    In the end all I really want is the ability to send a file around the world, have the code go along within the file, and the user be able to open and run the code natively within Excel without any MSI’s or “One-Click” BS……… I also want to be able to connect up to that user’s desktop using remote-control software and edit my code in context on their desktop !!! If the model is any different we are dead.

    At this moment VBA offers that for me – it’s all I need or want. Any NEW solution has to have the same capability – that’s all.

    Dick

  11. Jon Peltier Says:

    I’d like to see them continue with VBA. It’s easy to learn, it’s easy to deploy, it preserves (or at least doesn’t completely trash) verson compatibility.

    The IDE could use some improvements, and the OM could be hooked more completely into more objects. (In 2007 the OM was actually stripped away from many shape and chart objects, probably because there wasn’t time to hook it up.)

    In VBA for example it’s next to impossible to determine what cells are linked to by chart text elements, though you can use Excel 4 Macro code for some elements. The linkage is there, because if you save a workbook as html in Excel 2003, you get blocks of XLM that include all of this information. I’ve started writing a routine to extract what I need from this saved-as html workbook. Downside: different versions of Excel save different versions of the html file.

  12. dermot Says:

    ..charts have no privacy with Jon around!

  13. Simon Says:

    Good points Dick.
    I’m wondering if VBA is too hard for people who just want to say unhide all the worksheets in their workbook. And at the same time misses some features power developers could do with – like the stuff Dennis mentions.
    I’d love to see something like the Access Macros stuff in Excel in addition to a refreshed VBA.

  14. Marcus Says:

    Hi Dennis,

    I just finished migrating 40K LoC from Excel/Access to a VB6 DLL.
    The speed difference was negligible. From my understanding when you run a syntax check in the VBA IDE, Excel generates p-code which is executed.

    The main speed gains were obtained removing dependancies on the Excel object model such as removing data transfers from range objects to variables (multiplied by 1 million simulations).

    P.S. Hey Simon – I’m currently in London and on the job hunt.

    Kind Regards – Marcus

  15. Simon Says:

    H Marcus – good luck with the hunt – should be easy, I hear the streets are paved with gold down there.
    Good point on the ease of migration to VB6, there is still nothing comparable in .net.

  16. Harlan Grove Says:

    From Lotus 1-2-3 Release 1A, spreadsheets’ macro languages have included at least conditional branching and the ability to call other macros. And 123R1A also provided custom menus. Spreadsheet USERS who’ve delved into macros are used to having some conditional branching/looping programming constructs available.

    Aside from printing, e-mailing and, perhaps, saving itself via overwriting its image on disk, I don’t see many compelling opportunities for Access-like macros. The example of ‘unhide all worksheets’ is fine, but it won’t take long for some user to want to unhide all worksheets with red background color for the worksheet tab, or all worksheets containing 2007 in their name, etc. Unless the components provided by such a macro facility provided for most likely wanted selection logic, they wouldn’t be much use.

    The main reason I first began using Excel was to have UDFs. The existing macro recorder and Access-like macros are/would be irrelevant to UDFs. Nearly all useful UDFs require some looping and branching logic.

    As for macro languages, anything that accepts structured, procedural coding style for the looping/branching logic is fine by me.

  17. Dennis Wallentin Says:

    Marcus and Charles

    >>The speed difference was negligible

    That’s true when it comes to VB6 and VBA but not when VBA is compared with C++.

    In my opinion good coding practice actually means heavy us of variables.

    Kind regards,
    Dennis

  18. Stephane Rodriguez Says:

    “That’s true when it comes to VB6 and VBA but not when VBA is compared with C++.”

    I think the topic is not VBA versus C++, it’s VBA targeting Excel versus C++ targeting Excel.

  19. Biggus Dickus Says:

    Harlan:

    “From Lotus 1-2-3 Release 1A, spreadsheets’ macro languages have included at least conditional branching and the ability to call other macros. And 123R1A also provided custom menus. Spreadsheet USERS who’ve delved into macros are used to having some conditional branching/looping programming constructs available.”

    I don’t think you’ve seen Access macros in 2007. They have all the functions you describe (“conditional branching and the ability to call other macros”, Error trapping etc. but I’m not sure about Custom Menus anymore though thanx to Simon’s best friend Mr. Ribbon).

    In effect Access Macros are now a fully functional “Access” development language (and I understand they are only going to get MORE functionality going forward ;-)) – just like our old friend 1-2-3 1A macros (where I cut my teeth).

    Here is some text from the Access 2007 help:

    “Error handling and debugging Office Access 2007 provides new macro actions, including OnError (similar to the “On Error” statement in VBA) and ClearMacroError, that allow you to perform specific actions when errors occur while your macro is running. In addition, the new SingleStep macro action allows you to enter single-step mode at any point in your macro, so that you can observe how your macro works one action at a time.
    Temporary variables ”

    and

    “Three new macro actions (SetTempVar, RemoveTempVar, and RemoveAllTempVars) allow you to create and use temporary variables in your macros. You can use these in conditional expressions to control running macros, or to pass data to and from reports or forms, or for any other purpose that requires a temporary storage place for a value. These temporary variables are also accessible in VBA, so you can also use them to communicate data to and from VBA modules.”

    Hmmmmmm………
    Dick

  20. Simon Says:

    Dick
    yep thats what I’d like for Excel please

  21. Harlan Grove Says:

    OK, good to know Access 2007 has conditional branching/looping in its macros. I only have Office 2003, and Access 2003 doesn’t provide this.

    But there’s still the matter of UDFs. Can such macros be used to define UDFs? If not, coding is still necessary. As for automation, getting back to the example of unhiding some worksheets, from my limited perspective a macro facility would need a fairly general means of selecting worksheets. For example, select all sheets with red tab background color and containing 2007 OR cell X99 = “DISPLAY”. What would that look like? From my limited perspective, that’d require either many selection properties or natural language processing for a single property.

    Spreadsheets are much more granular than databases, so I’m skeptical that lightweight automation facilities that work for databases would work as well for spreadsheets.

  22. Simon Says:

    Harlan
    Are you saying people might want to do a wider variety of actions, to a wider variety of objects in a s/s compared to a db?
    Thats a good point.
    I truly believe the UDF thing is solvable, and should have been solved by now.

  23. Biggus Dickus Says:

    “Are you saying people might want to do a wider variety of actions, to a wider variety of objects in a s/s compared to a db?”

    I am NEVER surprised by the absurd complexity of the business process at clients that ompact my databases and as such my jury is still out on whether I would be able to rely entirely on Access macros to accomplish everything I need to do. I don’t think we can say that spreadsheets are more complex than databases.

    I’m sure it would be possible to create a macro language for Excel like the one in Access. I don’t think it is in the cards though for any versions on the planning tables at MS at this time so it could be years before such a thing was available anyway.

    In the meantime VBA would be fine for me ;-).

    Dick

  24. Harlan Grove Says:

    I’d imagine that if Microsoft upgraded the old (originally Excel 4 or 5) View, Scenario and Report functionality/add-ins, they’d handle a rather large portion of what an Access-like macro facility would likely cover.

    Sticking with worksheet selection, what’s so absurdly complex about a system in which there’s an override visibility setting (my example: cell X99 equals “DISPLAY”) as well as current visibility criteria (red tab color and name contains 2007, but better to make that contains YEAR(TODAY()) as text)?

    My perspective is based on my newsgroup participation. USERS will use colors as criteria. GET USED TO IT! At the moment, only XLM and VBA provide Excel itself access to object colors. I just can’t see how this could be accommodated by a simple macro facility without giving that facility natural language processing or dispatching expressions in properties to Excel itself (a la conditional formatting formula criteria) or some scripting language. Actually, the former would be simpler, but it’d require greater use of XLM functions.

    Re XLM: I wouldn’t have any problems myself deprecating macro sheets. While that means I can live without XLM control flow functions, XLM property functions are still needed. Why (Oh WHY!) aren’t XLM’s GET.* functions at least promoted to standard worksheet functions? Yes, they’d have to be volatile, so using lots of them will hurt recalc speed, but I can’t see any other downside to making them available in cell formulas.

  25. Ross Says:

    but off topic and I’ve missed the boat a bit too, but i can so i will!

    >>I truly believe the UDF thing is solvable, and should have been solved by now.

    Why can’t I write click a menu that opens a box, that i can build a UDF in – (looping, branching, or just a “mega formula”) thenhave Excel writing it out to C++ and store it on my PC, so it all works supuer quick?

    It already kinda gets done in some 3rd party apps – wouldn’t this be useful? – It too hard for me to do though! – at the momment!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: