Restore MDI file handling (open all files in one window); Kill SDI (each spreadsheet opens in a seperate window)
In the good versions of Excel (prior to 2007) one could open multiple workbooks in the same window. Now, one has multiple instances of Excel (and every other Office app) whenever one opens more than one document / spreadsheet. Quite stupid.
(2016-04-27 Dan [MS] - updated title to fix MDI/SDI usage - they were previously being used "backwards")
Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!
Martha, the good old Ctl-F4 still works for closing the workbook without closing Excel
Simon Ashton commented
There can be problems if you don't close Excel and re-open a new version... for me it leaves the old files' VBA scripts running.
Also, if the "old" file was large and not running very fast, the "new" file also seems slow and can be susceptible to unreliable glitches, eg custom ribbons icons not working (filter & freeze pane - yet their work on their original ribbon), and also when the cursor and arrow keys stop responding forcing you to click to another program and back again to get it to work.
So if you have glitchy or slow running issues, closing Excel and reopening from new can sometimes improve Excel's performance
Martha, one way around this is to customize a ribbon or the quick access toolbar and add either the "Close File" or the "Close All" command to it. The "Close File" command will close the current file, and if it was the only file open, will leave Excel open. The "Close All" command will close ALL open files (and prompt if you want to save all like it used to back in MDI days), and leaves Excel running. Either way, it's better than shutting down Excel and having to reopen it every time you want to close one file. Yet another reason SDI sucks.
To add the command, right click a ribbon and customize it, select the "All Commands" option, then scroll down to Close File or Close All (they're listed alphabetically).
Martha H Rendeiro commented
One result of the SDI is that when closing the last workbook, the entire Excel app closes and has to be reopened. In order to avoid that, the new workbook has to open before closing the last one. Of course the new workbook is in a completely new window and is probably on a different monitor in a dual monitor setup. Then go back to the old workbook which is somewhere else to close it. Much easier to be able to close the active workbook with the "little x" but leave the Excel app open until closing by clicking on the "big X." The "little x" seems to be gone entirely.
I didn't mean to hijack this thread to be a VBA discussion, so this will be my last post in this thread about VBA. But I did want to clarify a little.
VBA is simply the programming environment and it's associated language. VBA is the look and feel of the VBA IDE as well as the core language components of VB (IF-then-else, Do While, Case, etc.).
But VBA is NOT "Activecell.select" or "Selection.Cut". Those belong to Excel Libraries which are automatically added into your VBA project. This is what allows VBA to talk to Excel.
MS never said it will discontinue development of these Excel Libraries. In fact these libraries are probably exactly the same libraries that Excel itself uses to do virtually everything. If MS stops developing these libraries, then all changes (good, bad, or indifferent) to Excel would also stop.
So what I'm trying to say is that the VBA IDE and core VB language may no longer change, but the functionality within a VBA macro will continue to change and grow as Excel (and its libraries) change. This should include new MDI functionality (if we ever get that back) because Excel itself will be using the same libraries as VBA does.
Roy, I know what you're saying about using VBA to connect to other office apps. It's a major pain in the back end. But I've done it multiple time. I've used Excel VBA to pull data from Access databases based on user entry. I've even done that to MS SQL Server databases. I have also created macros that save the current worksheet (tab) to it's own separate workbook and then attached that workbook to an Outlook email and automatically sent it. All invisible to the user. And I've seen many other examples of people doing similar things.
So while it might be a PITA to accomplish inter-application functionality, I wouldn't rule that out as something thats never being done.
There, no move VBA discussion for me, at least not in this thread.
Wikipedia says (and it fits what I've read over the years):
"... implementation of Microsoft's event-driven programming language Visual Basic 6, which was discontinued in 2008 ..."
(VB6 being the thing discontinued, not VBA)
"... the VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 ..."
(apparently just added 64 bit support, though not complete support)
"Office 2013 and Office 2016 include VBA 7.1"
(nothing specified; just housekeeping for the new versions?)
"... Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the .NET Framework ..."
"... allowing even .NET-unaware applications to be scripted via .NET languages. "
(always wondered why not when they ended VB)
(oh, that would have been nice...)
"However, VSA was deprecated in version 2.0 of the .NET Framework, leaving no clear upgrade path ..."
(oh... I see... )
So, for now, I'd say our best hopes go with MS leaving the path open to other languages to write versions that, like Python to whatever extent, can replace, or work with/within VBA. That way, the onus is on others and their own development paths are not cut nastily short by decisions in other departments that they cannot really influence.
As for this applying to our issue though, I'm thinking a laundry list of deficiencies will get made up over time since MS is NOT going to address them. With luck, they will become a list of reasons to use one language or another that might step into the void rather than staying an ever increasing list of reasons to revile MS and Excel.
In particular, VBA was clearly never really pictured outside an MDI environment so there are definitely issues one might want to take note of in some of this Suggestion's comments. Flickering seems a small point (I know, important to the experience and some people's products) when compared to coordination issues that have been mentioned, for example.
The bottom line is SDI creates what are in essence entirely different programs running in Windows and even though they are fellow Office programs (how much more "fellow" could they be?), it just isn't seamless and never will be, or would have been. Excel had a fantasy if they thought it could be. Drinking the "Office works seamlessly with itself" Kool-Aid does NOT make the advertising a reality.
Evidence of that? Well, 30 years of living the dream... Of ALL the complaints in ALL the Suggestions you've all read, the sometimes complicated set-ups and uses of Excel, the writing of workflows maybe more complicated than many standalone software programs... of ALL those, when have you even one time read someone mentioning, even in passing, writing VBA code in Excel that reached out into other running Office programs to accomplish any task? Ever? On some other website? Some guru website telling you that your goal is best accompished by workign the material in Excel, perhaps, firing up Word to present it, and firing up Outlook to mail if off to 12,000 addresses. And using VBA from Excel, since it is the first program in the flow, to control it all, not just the Excel portion?
No one even tries to suggest doing something like that. Not even MS. So just how well can we ever expect VBA to coordinate, safely and accurately, two or twelve instances of Excel, even if it is "just" instances of Excel, in order to work with all the files required by the work flow?
Bring back MDI, allow one to set a choice, especially one available per spreadsheet rather than application-wide, and life is good for everyone.
People who want separate instances could even have a "/switch" in the program shortcut (like the "/e" switch I just love). The complaint about moving spreadsheets between monitors was always spurious, though it is often said it was either THE reason for this, or at least the straw that broke the camel's back. Anyone desiring that could have always opened separate instances, or they could have normal-windowed Excel, drug its borders to cover the screen, then drug the appropriate one over onto the other monitor/s and moved MDI windows in the huge workspace. So even the rumored thing never needed a nuclear fix.
It's all just sad and wretched. I'll eat a five pound pot roast with all the fixin's if it ever gets satisfactorily addressed. (I mean, I might do that anyway, but it's the thought that counts, right?)
Futski: You say MS has no intention of "dropping" VBA because of all the code that's already been written using VBA. Taking a step back it becomes clear that that must be the case. Even so, there's a difference between not dropping VBA and continuing to maintain/tweak VBA. I've heard that MS has stopped maintaining Excel VBA. Can you (anybody) confirm? If so, that will surely be important because the switch to SDI must affect the interface with VBA in various ways. One obvious effect is the inability (under SDI) to freeze the screen to prevent focus from shifting to a newly created/opened workbook. I would expect that little glitch to be handled by updating VBA methods but if MS isn't maintaining VBA any longer, how does that ever get fixed?
IMHO the real reason MS killed MDI, is for security / stability reasons (actual stability improvement is debatable). Anyway, one bad acting spreadsheet or VBA module gone awry was able to lock up or bring down all spreadsheets running under a single Excel MDI instance. I believe their intent was to prevent one spreadsheet from crashing them all and losing their associated changes. But in todays day, improved containerization of code and data should easily allow MS to fix the original problem, thus allowing them to bring back MDI. This is basically the same way bad acting web pages no longer bring down the whole browser, but rather just the bad page.
Mats Samuelsson, regarding Visual Studio, it is not a replacement for VBA, nor is VBA a replacement for Visual Studio. They are completely distinct entities with very little overlap except for the "Visual Basic" part of the name. One cannot create a simple VBA macro using Visual Studio, nor can one write code for an Arduino using VBA.
For those interested in the history, VBA is based on ancient Visual Basic 6. VB6 was discontinued 20 years ago. Visual Studio continued to advance VB.net into a very modern language today, but it's almost completely incompatible with old VB6 code. And with VBA being a derivation of VB6, there is almost no crossover available. Fortunately MS has no intention of dropping VBA because of the countless lines of code written in it.
Mats Samuelsson commented
Exactly David, the possibilities to connect different requests including advanced formulas when using Excel makes the usage of Access impossible. I've built my company's whole administrative system in Excel, including business calculations, registration of purchases, orders and invoices, statistics, warehouse ledgers etc., the whole shebang. As there are many different databases I've tried Access but I lost too many interactions between calculations and the databases. Why use two applicationss when you can do the same in one! I barely survive with Excel 365 with all the difficulties but it's most annoying, certainly travelling with a laptop is a **** job.
Mats Samuelsson There are reasons why Excel might be preferred for a database app over Access. One IMO very good reason is that Excel workbooks are easily created and other systems will generally be able to read them. So if you wanted a communications channel between system A and system B, you could use Excel spreadsheets as a common "language". But how to pass Access information between systems? Not so easily done. This is not esoteric nonsense. Vendors, for instance, submit price lists on Excel spreadsheets and a company sends information to back to vendors on spreadsheets. Vendors are foreign systems. Could this be done using Access? I'm not trying to downplay Access but I believe MS is finding that Excel is awkwardly more powerful than it realized.
Mats Samuelsson commented
David Portwood, in my opinion Microsoft made the strategic move to SDI for several reasons.
1. Memory allocation problems when opening several workbooks within the same container like in Excel 2010. How fantastic the program still is working in many aspects, it has bugs and crashes are common, especially if you have built some VBA procedures controlling the interaction between wb’s, opening, copy/pasting, closing, flushing variables, etc, etc.
2. Creating an Excel for working with “simple” spreadsheets in tablet environments for the web was a priority where there accordingly was no room for VBA. Then they could stop the further development of VBA, which had become quite a big competitor to VB/Visual Studio. The VB Editor needs a bigger overhaul if it shall continue to exist as a programming environment for Excel, especially the Forms Editor and ActiveX components are relics from ancient times! Well, if that happened, who will buy Visual Studio?
3. Even with OneDrive you could only exchange the web-version of an Excel file with your teammates. I don’t know if exchanging true original Excel Files works today in OneDrive, after a poor OneDrive test experience my company moved immediately to DropBox.
4. If anyone in the future wants to run database application, move to Access for heck’s sake!
Access, a dying application with a limited number of hard-core users needed more of the market share, otherwise it was threatened to be phased out!
5. How many will buy an Office package containing a full spectrum of applications if you can manage all everyday tasks with Excel, Outlook and maybe Word? They must simply attract other segments and new users in the app-market, there’s a limited space for how much new features you can push into the 3 main app’s without they would become incalculable and heavy-handed.
Hopefully Microsoft will kick some butts, employ new managers and come to their senses!
Most of the issues I'm hearing about (all, I suspect) could be resolved with better data organization and use of VBA rather than formulas/functions. However, that would require a slightly more advanced knowledge of database theory plus some VBA programming expertise which most people don't have and aren't going to acquire. Bottom line, dealing with a disorganized clutter of data - which seems to be the typical circumstance for most users - is simply easier under MDI. Perhaps surprisingly, larger scale projects requiring a properly normalized, relational data organization is also easier under MDI if only because focus unavoidably shifts whenever a new wb is created/opened causing an annoying screen flicker. Although it's possible that a solution to the second problem (perhaps freezing the screen to avoid focus shift) may someday present itself, nevertheless it appears that MDI makes everybody happy so what was the advantage to moving to SDI? The move has clearly hurt a large group of Excel users. Where is the supposedly larger group that it helped? I'm not understanding the long term MS strategy here. I assume there is one and it is controversial else it wouldn't be hidden. Didn't I read somewhere that MS has stopped maintaining VBA? If so, that must be intertwined in all this and a clue as to what MS intends for Excel down the road.
Michelle Bryce-Fritts commented
I dear with up to 11 workbooks simultaneously with multiple spreadsheets each. Using the MDI can be more efficient and uses less ram to handle the process.
While I can use the SDI for smaller workbooks with a few of single spreadsheets, the banners and ram suck can be a pain. I would vote to have access to both MDI and SDI for different purposes and projects.
Let's correct the assumption that when you used two instances of Excel in versions prior to SDI it functioned the same as it does using MDI. Copy/paste did not function the sam and creating formulas by selecting cells in the 2nd instance did not work as the 1st instance didn't recognize the 2nd.
Before everyone attacks....I prefer the old method and have voted for and still hope that one day someone at MS sees the light and makes MDI/SDI and option.
Richard Laycock commented
Let's correct something even Microsoft seems to have wrong...
Excel prior to this SDI only error ALWAYS allowed BOTH SDI and MDI, regardless what others claim.
Double-clicking any shortcut for Excel.exe will open a new instance in 2007 or 2010. I never used 2013 but it probably worked the same.
I can dual boot into my 7 pro drive and still work on 2007. I frequently have one instance on one Monitor 1 and another on #2. Both with multiple workbooks. MS doesn't have to create anything new. They simply have to admit they screwed up and go back to the way it was.
Sorry, I wrote that badly:
Physically set the monitors only once when setting up the computer. NOT every, every day! Just the shortcut keys once a day when logging on to match what Windows presents to the screwy physicality. Lordy, not moving the monitors themselves each day!
Too bad we can't rotate monitors 90°. Windows can be easily rotated too, in 90° increments, so one could just do that each day. Ctrl-Alt-(arrow of choice) and your computer matches your monitors in their "goofy" orientation. If I have to logon with a password to MY computer, I can do this without feeling put upon! (Well, one has to go to the Action Center once after buying the computer to turn on the use of those shortcut keys. Oh my, that does it!)
When you'd need width too, you could spill the windows you tiled off the edge of whichever monitor so they cover both (the once a year I do this, I get columns A through AU). LOTS of columns too then.
So many of the difficulties we are listing in here could actually be addressed individually by Excel, in ways that do not depend on MDI/SDI. Even the more involved thoughts, like if you open a file from INSIDE Excel, use Excel to do it (File|Open), not Windows (by Exploring to it and clicking, say), Excel could almost certainly easily keep track of those (it does now, build 1904, in that only one instance shows in Task Manager while using Windows to open the 2nd file shows two in TM) and if you tell it to, allow presentation of the files like it did for MDI, even though they are truly SDI, and provide a single ribbon at the top. So whatever window, maximized or normal, that it starts with, ribbon at the top that keeps track of which file you are in at the moment and acts upon it, like tiled windows and MDI did.
Literally, how hard would that be? Probably? Not very since they can keep track of the files a single instance opened, and all the pieces are simply windows anyway so they can just work with the pieces differently, and use both those things to present it AS IF it were MDI. Who cares? Real estate-wise.
Solve half a dozen of the little things without the major switchback to real MDI, like they did with the copy/paste issue the switch created but is now solved, and I bet ¾ of us could make nice-nice with it all.
That could be seen as "divide and conquer" but it would be "responsive to customer needs" in my book.
TAZ1911--unfortunately, you can no longer do this. Every file opens in a separate instance of Excel now. It is the source of all of the frustration here. I regularly use a model that is ~25 linked spreadsheets. It no longer works efficiently with the new versions. If only Microsoft would listen to the frustrated users here!
TAZ1911 - similar situation for me the other thing is that with MDI docs will retain their position when opened and closed with SDI you have to waste time repositioning them on screen too.
Pain in the rear when you have 100's of files to deal with and need to open / close often.