For the Last Time, No!

Here’s a happy little dialog that I’m freakin’ sick and tired of seeing:

Excel 07 macro dialog

You see, Excel 07 has new file formats. “XML is the wave of the future”, they said. “Everything’s going XML”, we’re told. Ten years from now we’ll be saying “Remember that XML fad? Man we were jerks. And look how thin I was.”

Excel’s main formats are now:
Excel Workbook *.xlsx
Excel Macro-Enabled Workbook *.xlsm
Excel Binary Workbook *.xlsb

Then there’s some legacy formats and other sundry formats you’d expect from a spreadsheet program. The binary format is necessary because as great as XML is, it just can’t hold 18 quadrillion cells of data. Particularly large workbooks will be better off in binary. But the main point of my gripe is that there’s two new xml formats, not just one. And the one that I almost never want to use is the default. So I get to see this dialog box every time I save a new workbook with macros. Yippee.

To be fair, this is a fairly uninformed diatribe. There may be a way to change the default, but I doubt it. The program should recognize when there’s macros and suggest the macro-enabled version as the default. Also, there may be a good reason for having two xml formats and an equally good reason for making the non-macro-enabled one the default. I admit I haven’t been keeping up on the Excel 12 blogs, but if you can point to a post on this topic, leave a comment.

More complaining to come, I promise.

Posted in Uncategorized

23 thoughts on “For the Last Time, No!

  1. Don’t get me started on this one. It’s just part of the “macros are dangerous” mentality at Microsoft. Notice that the default button is Yes, which means “wipe out the macros.” Novice users, of course, will have no idea what a macro or a VB Project is, and they’ll just choose the default. Even though there’s a 99.99% chance that the macros are present for a reason.

    One good thing about Excel 2007 is that you can designate a directory as a “trusted location.” Opening a file from a trusted location directory doesn’t display the normal warning prompt. It would be nice if saving a file to a trusted location defaulted to a macro-enabled workbook if the workbook contains macros.

  2. Dick & John,

    It all can be summarized with one nice and ‘hot’ abbreviation:

    VSTO (Visual Studio Tool for Office)

    There is no reason to get **** about it because:

    – VBA belongs to the past and
    – VSTO is part of the great feature

    The ‘macros are dangerous’ mentality is part of the marketing strategy to convince/force the market to use the .NET platform and in particular VSTO.

    MSFT need a global large group of (unpaid) beta testers so they can ‘boost up’ version 3.0 of VSTO when Excel 2007 hit the market next year.

    Except for that as a shareholder of MSFT I want a high return on my investment on a quartely basis – OK?

    Kind regards,

  3. Dennis,

    I recall a post by Dick stating that VBA will likely never go away, and I agree. I really don’t understand any of this. Just how many VSTO developers are out there, anyway, that it makes a difference? For that matter, how many people can say they use VBA on a professional level (meaning you sell VBA projects for a living, not just work with it daily)? I assume the vast majority of macro code out there is amateur code from people who just need to get something done. Now you take a case like mine where I finally have code tight enough and a machine fast enough to run my application at a reasonable speed–do you really think I’m going to rewrite things in managed code and have it slow back down to a crawl?

    I see them as two completely different tools. As much as I’d like to gain proficiency in .net, the payoff just isn’t there because the end result will be a different way of coding the same result.

    As to the original issue, if it gets to be an aggravation, I’m thinking I can put something into my personal macro workbook to change the default or, if not, to intercept and override the save function.

    It’s all dumb, dumb, dumb. But at least the million rows will let me spend less time in Access.

  4. As for whether there’s some what to change the default file type, there is. Find the Excel 2007 options dialog (a challenge for long time users in itself), then select the Save tab, and it’s the topmost setting in right-hand portion of the dialog.

    As for VSTO, with a list price several hundred US dollars higher than (and in addition to) Office 2003 Professional, it’ll only be for corporate developers and the few hobbyists with money to burn.

  5. Good one, fzz. I’ve been using this beta for months, and I didn’t realize that you could change the default Save As format. Well, I think I knew it, but I never thought of changing it because I assumed that I could only change it to the old XLS format.

    So that will solve the problem for me and Dick.

  6. My company, a mid-size merchant electricity generator, has also bought into the “macros are dangerous” philosophy as part of our Sarbanes-Oxley compliance issues. In fact, we’ve actually extended it to “spreadsheets are dangerous.” Apparently us accountants can cost our companies billions by entering erroroneous formulas in our spreadsheets.

    My real concern with .xlsm format is that it will enable the folks that control our corporate networks to lock down the xlsm format. As one of the ameteur vba coders that zfraile mentions who uses code to get thinks done, I can see this getting to be a huge hassle for me. On the bright side, it’ll just force me to create code & addins that house the macros but operate on other files. Please don’t tell me that Exel 12 restricts that functionality…


  7. I didn’t realize the default could be changed like this; perhaps it couldn’t in an earlier Beta, and I never revisited it.

    I suggested that if a new file was created and code inserted, that Excel be smart enough to know I put the stupid code there and I wanted to save the workbook with the code. The suggestion was not implemented.

    However, I’m not sure I’d want the default to be .xlsm, since half or more of my workbooks have no code.

  8. Hi everyone,

    I own the design of that dialog, and I do appreciate the feedback. I’ve heard a lot of speculation in the comments before mine, but let me clear a few things up before I discuss why you see the dialog.

    VBA is not going away anytime soon.

    Steven Sinofsky stated that we will support VBA in Office 2007 and the next version. (and probably for some time after that) Then, our support policy requires 10 years of support after release.

    Zfraile makes some very good observations in his comment. VBA and VSTO are completely different tools, targeting different customer bases. VBA is generally used by internal developers or non-developers to complete business solutions. VSTO, like COM Add-ins is for the professional developer who makes a living selling solutions for Office. IMO this message should be clearer from us.

    Dennis, there is no conspiracy to scare customers from VBA to VSTO. We are seeing real customer requests to provide a format that is free of macros because of past viruses. Some of our customers are setting file extension blocks on thier mail stores for our .doc, .xls, and .ppt file formats, for the very reason that you cannot determine the existence/non-existence of code by extension in these formats. These blocks make it difficult for their users to collaborate using Office files through email.

    Why the dialog, why not just switch, based on the existence/non-existence of code in the project?
    This was considered, but it leads to a very difficult user experience, especially considering the change to extension. Open a workbook, save as book1.xlsx. Add some code, click “save” and now you’ve got book1.xlsx and book1.xlsm. I still think it is better to allow the user to make the decision, rather than the software make the decision in this case. The other problem with deciding for the user is deciding on which format to use. Just for starters, we have .xls, .xlsx, .xlsm, .xlsb, nevermind all the other formats.

    Why ‘Yes’ as default? We had to pick one, but I argue if you didn’t want a macro-free format, you would have set the file type in the Save As dialog. Also, you won’t lose the code in this case until you close that file (saved as a macro-free workbook).

    As fzz states, you can change the default save format through the Excel options.

    I hope this gives you some insight as to why the decisions were made. We may very well be wrong, this is why we have betas. Keep the feedback coming. I’ll try to get some posts on my blog to address some of the questions brought up here.

    Kevin Boske
    Program Manager – Office Programmability

  9. Claims that it’s better to let the user make the decision would ring a lot truer if Microsoft adhered to that principle more generally. For instance, providing a mechanism to TURN OFF automatic date conversions. The classic example is entering gene sequences like SEP10 and DEC7 and having Excel convert them into date serial numbers.

    With regard to protection mechanisms, why doesn’t Microsoft provide tools to strip out VBA code from Office files? It could be done crudely (and in an untrusted way) using VBScript to launch an Excel application instance, disable events, load workbooks, and use Application.VBE to delete general modules and blank class modules. It can’t be all that much harder to do this in an EXE.

    And saving both an .xlsx file and an .xlsm file isn’t a big deal. As it is, open an .xls file, then save it as an XML file, and you’d have both an .xls file and an .xls[xm] file. If an Excel workbook has VBA code in it, Excel should be smart enough to save it in a format in which that VBA code would be saved along with the worksheets.

    As for the default button, NO program should EVER default to DATA LOSS. That includes VBA code in .xlsx files. If one opens an .xlsx file (no VBA), adds VBA, then closes the file and chooses to save changes, there’s no opporunity to change the file type. If the user goes with the default in the option above, they save the .xlsx file and lose their VBA code.

    IF you wanted to make this user-friendly, you’d change the buttons to ‘Save .xlsx file’, ‘Save .xlsm file’, Cancel and Help. THAT would be useful. THAT would give users choice. THAT seems unlikely to happen while MSFT program managers aren’t willing to admit there’s a problem.

  10. Thanks for commenting, Kevin. I’m glad to know you have a blog; I wish I would have know that sooner. I’ve subscribed and look forward to reading it. A couple of other points:

    Just because you’re not part of the consipiracy doesn’t mean there isn’t one. :) VBA is definitely on the way out. Not tomorrow, but someday. I suppose that’s true of any technology; I just wish they would improve the VBE and the language until it’s dead.

    Re macro viruses: The last macro virus I saw was about 13 years ago. I was working at KPMG and using a Macintosh SE (Apple was a client, apparently). It was that virus that turned all your Word documents into template files instead of doc files. It didn’t exactly grind the firm to a halt. I think we’re all quite aware of the potential damage a macro virus can do. I also think that many people are too concerned about this. We don’t need to shore up our defenses against macro viruses. Whatever we’re doing is obviously good enough. I’m not saying Kevin should ignore his customers, just that his customers should allocate their resources according to true risk, so MS won’t have to do stuff like this.

  11. Thanks Dick.

    I’ll try to post some more generic programmability-related content.

    To fzz’s question regarding protection mechanisms, we found in the past that ISV’s attempting to reverse-engineer the binary file formats would often corrupt the files. This is one of the myriad of reasons we moved to the new Open XML file formats. We do today, provide a mechanism to remove VBA (amongst other items) using the Open XML formats. I have a couple of posts on my blog describing how to remove VBA, with code:

    I disagree with fzz’s assertion that saving multiple files is not a problem. Our financial customers are concered with a “single version of the truth”, particularly around the new SARBOX rules.

    We also considered the two-button approach (save as xlsm/save as xlsx), but there were as many people wanting (save as xls/save as xlsx) or (save as xlsx/save as xlsb) or the all-binary (save as xls/save as xlsb). Soon you get a dialog that looks like the cockpit of a 747. The user has the choice to set the file type in the Save As dialog, before this dialog is displayed. For that reason, I don’t think that defaulting to “Yes” in this scenario equates to data loss. The user can also set the default file type to one of the macro-enabled formats.

    I do want to hear some more specific feedback around adding code to existing files. Today, do you open a new workbook, add data AND code, then save the file? Or open the workbook, add data, save, close, reopen (at a later time) and then decide to add code? Or do you simply work with a workbook until you reach a point that you can’t go any further without code?

    Finally, to fzz, I’m here posting and replying with my name, email address and link to my blog, so I don’t think it’s quite fair to characterize us as you do in the last sentence of your comment.

    Kevin Boske
    Office Programmability Program Manager
    kboske ‘at’

  12. Re specific feedback: Yes. That is, yes I do them all. I have a workbook that has nothing but code in it. My secretary gives me a workbook she’s been using for years and asks if I can write a macro in it. Those are the two extremes and everything in between happens too, including this scenario: a workbook with data and code. Then it’s determined that it’s getting too big and the code should be moved to an add-in, leaving a workbook with data and no code.

    I’ve just made a great case for simply picking one file format as the default and being done with it (so good job Kevin). (I’m still in the camp that says one file format and screw the big corporate types and their SARBOX problems, but I’ll never win that battle.) So here’s my compromise: For previously unsaved workbooks only, default save as xlsm if code exists, otherwise xlsx. For previously saved workbooks, default to the previously saved format with warning as appropriate.

    One benefit to the current system will be the utter lack of “I’m getting a macro warning but I don’t have any macros” questions in the newsgroups. The empty modules will be noticed and presumably deleted when the workbook is saved.

  13. Reread my .xls file example. If there’s an .xls file and an .xlsx file, there are two versions of the truth. Whenever an existing file is saved using Save As, the result will be two files. Same problem figuring out which file is the TRUE FILE.

    Also my other example. Open an .xlsx file, then make changes to it including adding VBA. Now Save (not Save As). Only the dialog in the original blog article appears. No dialog appears that allows changing the file type. Go ahead and save it as an .xlsx file. The VBA code still exists in the workbook while it remains open. Now close the workbook. Excel doesn’t display any dialog. All VBA code gone.

    Now open a .csv file, add VBA code to it. Save (not Save As). Excel displays a similar dialog. Save the file as a .csv file. Again, the VBA is still in the workbook while it remains in memory. Now close the workbook. Excel displays another set of dialogs. If Excel is smart enough always to prompt when closing .csv files, why can’t it always prompt when saving .xlsx files that contain VBA code? I understand that the .csv dialogs appear for different reasons (they could lose worksheet content), but I’m intentionally playing devil’s advocate.

    Then there’s the example of how Excel 2007 handles Excel 2/3/4 .xls files. Upon an attempt to save, Excel displays a 1-button dialog stating that it can’t save the file in its current format. Click the button, and Excel displays the Save As dialog. Wouldn’t that be a better procedure for saving .xlsx files that have VBA code? That’d cut down your current, marginally crowded 3-button dialog to a 1-button dialog – what could be simpler? And following up immediately with a Save As dialog would be too confusing for users who’ve added VBA code?

    If Excel must force the user to create multiple files explicitly so Microsoft can cover its collective backsides over & beyond the various disclaimers in the EULA, why not a different tack: don’t allow VBA to be added to any files other than .xls(5/95 and/or 97-2003)/.xlsm/.xlsb? Make users save files as .xls, .xlsm or .xlsb formats before allowing them to add VBA. That’d be an improvement. Further, when they do try to add VBA code to an .xlsx file, that’s the time when Excel should display a Save As dialog.

    There are many different ways to handle this. IMO, Microsoft has chosen one of the worst.

  14. “VBA is generally used by internal developers or non-developers to complete business solutions. VSTO, like COM Add-ins is for the professional developer who makes a living selling solutions for Office”

    Thats beacuase VBA(XLA) is not secure enough….if it was … developeres probably wouldnt be shifting to COM Add-ins….. Ask the Bastien(ASAP) ….he just shifted from XLA to Com…partially…

    ….May be PUP7 will be COM instead of XLA as well….John ?

    …but then if it did become secure….MS would have a couple of products(or shall we say technologies) less to sell…


  15. Hi Kevin

    Thanks for the insight.

    When a user records a macro, it goes into Module1. When he records another, it goes into Module1. Now she opens the VBE, edits the code and writes a few more subs, then close the VBE. Then she records another macro, this goes to a new Module2. Excel knows that Module1 is edited by hand, it is Dirty.

    My experience is that recordings are quick fixes and disposable most of the time, while “real, written code” is valuable. So now that I know Excel knows real code from recordings, and of course spot the presence of userforms and class modules, I’d like the defaults to behave different depending on that. Make a qualified guess, it is probably pretty easy. Or even better, make “qualified guess” one of the saveas options.

    The dialog should be at least as clear as it is if you actually are going to lose the code, maybe it deserves a Warning icon instead. Would it be possible to export the VB project to an external file, or ask “Do you want to export the VB project to a separate file?” after saving as xlsx ?

    Best wishes Harald

  16. At least if you click “No” to that prompt, can you default the Save As type dropdown to “.xlsm” when you return to the Save As dialog?



  17. I think that what I want is that when I create a sheet I get whatever is the default format (xlsx?). Save and close. Reopen, add macros, press save….at that point I want the file to be saved with everything that I have done – including the macros. A dialog with the following option would do it:

    “This workbook has macros. Do you wish to CHANGE the file format to allow macros to be saved?”
    then Yes – to change the file to xlsm, no to save as is without macros or cancel to then go back and pick the save as dialog and make my own choice of all the other file formats.

    Then you still only have one version of the file – now it’s an xlsm not an xlsx – with your macros in. Given the huge number of people that I still see who wouldn’t know a file extension if it bit them on the nose that will be seamless to inexperienced users.

    And I’d definately go with the default should be to save your macros not lose them – you can always get rid of them again if you didn’t mean it – if the default is to lose them then you’ve lost your work with no way to get it back!

  18. P.S. Even if you stick with the current dialog – lets have buttons that say “Save as macro free”, “Return to Save As” rather than the Yes/No option!

  19. I wish you were right, mkp, I’d like that behavior too. But all links to the workbook from other files will break if you change the file extension, won’t they? Shortcuts, workspaces, remote formulas, recently used file list, ….

    There is a big important customer base that Kevin refers to as “Our financial customers”. I bet they have a lot to say on how this is going to be. They (at least all the ones I know peraonally (except Stephen of course)) wouldn’t dream of visiting Excel blogs or newsgroups, and they don’t know how to get rid of macros or bogus macro warnings. It’s more like a “have anyone seen my python snake lately?” feel to it. So I guess an automatical code removal system is desireable for a lot of important people, no matter how silly the idea of code free Excel files is.

    Best wishes Harald

  20. in 10 years xml or it’s descendant markup will be “here” and in use across the semantic web, MS may well not be there..

  21. hii guys;

    i really can’t figure out your are talking about this issue in 2006, but curruntly on 2010 i’m just experiencing excel 2010 and I really faced bad luck as i’ve made considerably above 100 handrad lines code and saved it in xlsx and it’s wiped.

    any idea if i can retreive my code?
    it would really great

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.