Last week Microsoft released the 2010 technical preview version of Office 2010 to an excess of noise in the media and blogosphere. You’re all probably bored spitless of all the hype. Everyone was spewing the same press release fodder, and nobody had anything of substance to report.
Two weeks ago, I wrote that my main laptop died, and I had to scramble about to get a new one all configured. At about the time the Office 2010 preview was released, my dead laptop came back to life. To punish it for scaring me like that, I installed the preview on the revived machine. The installation went very smoothly, better than you’d expect for a beta version, and better than many installation experiences.
I’ve run Excel 2010 a few times now, and Word 2010 once by accident when I double clicked on a document in Windows Explorer. I didn’t notice at first because I was mesmerized by the dynamic splash screen, but Excel 2010 starts up very quickly. In general Excel 2010 seems to lag a bit less than 2007 while doing routine tasks.
The Excel 2010 Ribbon
My first impression of the 2010 ribbon was that it was cleaner and maybe a little smaller than in 2007. When I took a few screenshots and put them together, it was clear that the new ribbon was no smaller than before,still a good row taller than an overloaded toolbar area in 2003. But the 2010 ribbon definitely was cleaner.
The 2003 interface is pretty cluttered, but that clutter is a price I’m willing to pay for an efficient interface that displays all of my most-used commands at once, without hiding 90% of them. (I don’t think I’ll ever forgive Microsoft for this design debacle, but apparently we’re stuck with it.) The 2007 ribbon looks nearly as cluttered as the classic toolbar interface, not because it has lots of controls, but because everything is in boxes, and there is no shortage of graded coloring.
The 2010 ribbon has the same density of controls as in 2007, but it looks cleaner because of its lighter background and the elimination of much of the chart-junk-like features. Instead of boxes around all of the groups of controls, as in 2007, the groups are separated by a single light line, and the shadowing is much less pronounced.
A Designer Cleans up the 2007 Ribbon
A year ago Andreas Lipphardt of XLCubed wrote Microsoft, Pimp Down My Ribbon, a request to clean up the 2007 ribbon. He even showed how. He started with the ribbon out of the box:
Andreas cleaned up the ribbon by removing all the gradients and glows, and without all of the gratuitous effects, it certainly looked much cleaner.
Microsoft stopped here with the 2010 ribbon, but Andreas took it one step further, removing the large office button and the oversized ribbon buttons (e.g., Paste), and closed up the excess space. This was becoing too much like the classic interface.
Why Can’t Users Clean Up Their Own Ribbons?
Now they can, to some extent. Microsoft has added the ability to make changes to the interface through the interface. Even though RibbonX, the ribbon-specific subset of XML, is relatively easy to use to customize the interface, RibbonX is still way beyond a typical user’s capabilities. This ribbon customization capability gives back to the user some small measure of control, maybe 10% of what we once had.
Charting on the Ribbon
I’ll show a couple shots to further illustrate the cleaner ribbon appearance in 2010. Here are the Charts groups from the Insert tabs of Excel 2007 and 2010.
Although the 2010 group looks much cleaner, the buttons are all the same as before, and in fact, all the options under the buttons are the same. The gallery of chart types in 2010 is just like that in 2007. The Chart Tools contextual tabs are also cleaner but with essentially no real changes. The one addition is a Draft Mode button on the Design tab. Its purpose is to allow charts to be drawn with only rudimentary formatting, to speed up screen refreshing in Excel 2007.
A Couple New Features – Look But Don’t Touch
However, right next to the Charts group on the Excel 2010 Insert tab are two new groups: Sparklines and Filter, which houses the Slicer. These much heralded features are probably the most talked about new tricks in Excel 2010. The advance press for Sparklines on the MSDN Excel blog is very promising.
At first I thought that neither feature is enabled for testing in this early preview version. I didn’t notice that I was using Compatibility Mode, which disables advanced features introduced in 2007 and later. I apologize for my newbie error, and I anticipate testing out these features in the very near future.
Chart Dialogs
I made a few charts, just to see what if anything has improved in the awkward Excel 2007 dialogs. One thing has changed: after being temporarily retired for one version, the ability has returned to double click on a chart element to open its format dialog! Unfortunately the dialogs that open up are unchanged from those of 2007. Too many tabs, too many options hidden in dropdowns when there is ample space to use listboxes or option buttons, too many clicks required to do otherwise quick tasks. And the F4 (Repeat Last Action) command is still AWOL. It remains to be seen whether any changes will be forthcoming in upcoming releases of the preview.
I’ve been working on a charting interface add-in for Excel 2007, and I suspect that it will be useful for users of Excel 2010 as well.
Macro Recorder
I recorded a few macros, nothing too complex, to see whether the huge gaps in Excel 2007’s macro recorder coverage have been addressed. In 2007 if you recorded a macro while doing anything with shapes, the macro came out blank. When working with charts, some actions were recorded, but many were missed, particularly those related to formatting of the shapes that make up the chart’s elements. Because the recorder didn’t record, we were without a tool that was so helpful to decode the intricacies of the object model.
Without examining the details, I was able to see that all actions were represented by commands in the recorded macros. Shapes and chart element formatting were again represented in the code. This is a great (re)addition to a developer’s toolbox.
Summary
Excel 2010 looks a bit cleaner and seems to run a bit more smoothly than Excel 2007. In the small part of Excel that I have examined, very little has changed. I will be looking at Sparklines and other neat new stuff in the near future.
Rick Williams says
Thanks for the review. It’s good to know there is at least /something/ being improved in the new version. I think the ribbon customisation is a big win, as is the double clicking on chart elements. But yes, it is still so frustrating to reformat charts without the F4 redo! I notice that format painter also is disabled for chart elements, which would be at least a start of a work around. There isn’t even an easy way to (for example) remove all markers from a line plot without visiting ‘change chart type’ and lose other formatting you may have applied. And on top of that – the modeless “Format Data Series’ dialog box, which /might/ have been useful for something like this has the decidedly UNhelpful habit of reverting to the first ‘tab’ (Series Options) everytime the selection is changed!! Oh my god, I cannot express how infuriating this is! to do this for *FOUR* data series: click,Ctrl+1, click, click, click, click, click, click, click, click, click, click, click, click. Want to change the marker fill, shape and size (from 7 to 2) instead? 41 clicks plus typing the size of the marker. or use the scroll buttons and that takes it to a nice total of 61 clicks.
Absolutely ridiculous!
sam says
Jon,
Here is list of improvements / Bug Fixes is Excel 2010
1) The Limit of 8192 areas is removed (increased to ???? – Havent tested is yet but its atleast 524288). Now it makes more sense have more rows
2) Auto Filter and Pivot Filter comes with Search box (not powered by Bing :-))
3) If there are more than 10 K unique items in a filter drop down a message is displayed saying “Not all items displayed”
4) Pivot Tables has the following extra options
a) Show Value as % of Parent column – Extremly helpful when you have more than 2 row fields
b) Fill Lables -The Pivot then starts looking like a Data table
c) What If Analysis (For Server based pivots)
d) Manage Sets (dont know what this is yet – Excel crashes when I click on this)
5) Slicers – work for me – Its just a floating page field for a pivot…. nothing to get very excited about
6) Spark lines work for me – They are a good start but they have a long way to go as there are very limited options(compared to the options available in Sparklines.xla form http://sparklines-excel.blogspot.com/
There are No bullet charts, box plots area charts etc.
There is no possibility of showing Bench mark lines (Avg/Percentile etc) for Bar charts
7) Conditional Formating – Solid Fills for Data Bars – Accuracy finally takes precedence over fluff – It can now be used as a spark line
It also has some neat options like setting the Max value, Show only the Databar and hide the data etc
8) Very Limited Ribbon Customization
Can Create your own tabs
But cannot
a) specify Size of button directly (need to say don’t display Lables to get small buttons !!)
b) specify Screen tips, Super Tips etc
c) Very limited image Gallery for custom buttons
d) Can remove and rearrange groups in existing Tabs – But cannot add/modify existing groups……but some how its not consistent… Next time you open XL its back to normal
e) Customization files can be exported/imported but edited only via Note Pad
UI Editor does not recognise them
f) Green Blob appear in place of menus/buttons of a custom tab which has many buttons if you resize the Excel window or open the file on a “small screen”
9) Pastspecial options available on right click
10) Macro Recorder Caution : If you install Excel 2010 on a machine which has older versions of Excel then the macro recorder in the older version gets completely messed up…. and starts recording junk even for simple actions
I have sent you a file showing images of a customized ribbon highlighting
underutilized real estate
Sam
jeff weir says
There’s the odd thing about excel 2007 (and presumably 2010) that I like more than in excel 2003. But I think you could sum it up by stating that for every step forward in one area, there’s 2 steps back in another area. So on average, it sucks.
Jan Karel Pieterse says
Hi Jon,
Sparklines and slicers are available in this build, but you need to have a cell selected to use them.
Jon Peltier says
I tried using Sparklines with cells selected, first with the range I wanted to plot selected, then with the cell selected where I wanted the range selected, and the buttons never became enabled.
I made no pivot tables, so I didn’t try the Slicer. Given that Sparklines seemed disabled, I assumed (yes, I know the ass-u-me thing) that the slicer also was always disabled.
I’ll have to try to jump start the sparkline feature, because I wanted to play with something new.
Dennis Wallentin says
Jon,
Perhaps we can agree that the new version of the Ribbon UI is in the right direction.
For me Excel becomes a better companion when doing BI-analyses with large sets of data. The improvcements in the P/T are good but I wished there was a way to export data from the P/T tool to XML. The Slice tool is very nice and although the Sparkline tool is version 1.0 it now exists in Excel.
The major good news for me is that it now exist a x64 version of Excel. OK, not everyone will be using it but for those who make computation on large amount of data should be pleased. Of course, the major limit is the x32/x64 situation.
Sam,
The major disappointment (for me) with the Ribbon is the lack of a similar tool in Excel as we find in VSTO and is provided by third-parties in the .NET environment. OK, a new version of the Custom UI editor will be available but it’s still an external tool.
I can understand that MSFT is looking ahead when the new .NET IDE and then provide a similar tool but it still a must to improve the present tool.
Kind regards,
Dennis
Jon Peltier says
A few people have commented that they can use the Sparkline tool. I restarted Excel 2010, checked the Insert menu, and the Sparklines controls were disabled. I filled a range with data, selected the range, and the controls were still disabled. No joy.
I decided to uninstall 2010, and also uninstall Office 2003 which is also on that machine. Then I’ll reboot, apply all the latest Windows updates (it’s XP Pro SP3 if that matters), then reinstall 2010 only. Then try it again.
If anyone has any suggestions, let me know.
sam says
Jon,
Sparklines and Slicers are disabled if the file is opend in “Compatibility mode”
Sam
Jon Peltier says
Compatibility Mode
Duh!
Colin Banfield says
Jon, can’t imagine why your sparklines aren’t enabled. I have three versions of Excel installed – Installation order was Excel 2007 first, Excel 2010 technical preview second and Excel 2003 last.
Sam, there are a few more PT custom calculations you didn’t mention. The full list would be:
% of Parent Row Total
% of Parent Column Total
% of Parent Total
% Running Total In…
Rank Smallest to Largest
Rank Largest to Smallest
The new What-If and Set features work only with OLAP sources. What-If allows manually changing values in the OLAP based pivot tables and having totals recalculated with the new values. However, the feature works only with simple server aggregations (i.e. sum). The Set feature allows you to predefine the dimension members and measures you want to work with. (e.g. long lead time products, last twelve months etc). Instead of dragging and filtering individual dimensions and measures every time you wish to work witha subset of the data, you simply drag the set (which you give a name to) to the row or column area. Named Sets and What-If were introduced in Analysis services 2005. Server based named sets show up in the pivot-table field list. The sets and What-If scenarios created in Excel are saved in the workbook – they aren’t written back to the server.
Mike Alexander says
Jon: There are still a few things that are annoyingly difficult to find.
Example, try to find Print Preview in less than 10 seconds.
Jon Peltier says
Colin –
Sam indicated that compatibility mode disables advanced (2007 and 2010 only) features, and a couple screen shots I took last night indicate I was in incompatibility mode. Right out of the box I wouldn’t have expected that.
Mike –
Is this a test?
JP says
Would it be safe to say that Excel 2007 is like Excel 2000 (lots of new features) and Excel 2010 is like Excel 2002 (just a few minor upgrades)?
Mike Alexander says
Jon: Not a test. It’ took me 20 minutes to find it. I must admit, now that I know where it is, it’s easy to find, but it’s definitely not where you would think it should be.
Colin Banfield says
Jon, this post might be a good place for users to list the new features they’ve discovered in Excel 2010. Sam listed a few things I hadn’t yet discovered (1 and 3 on his list). Those are the kind of features you have to be looking for (or discover by accident). So to add to Sam’s List (excluding the new PivotTable options already mentioned):
1) Completely re-organized Office button menus
2) Charts
2(a) Draft Mode – removes all special effects (glow, shadow, reflection etc.), anti-aliasing (most noticeable with lines, where the edges are jagged) & gradient fills.
2(b) Removal of 32K data point plot limit (haven’t determined new limit but I plotted over 200K points)
2(c) Chart updates faster than Excel 2007 SP2 AND Excel 2003. To test this, I used your test on updating 10K random numbers in an XY chart. Excel 2010 won, followed by Excel 2003 and then Excel 2007 SP2. Interesting observations in this test were that draft mode didn’t make much difference in the results (Excel 2010 won whether the chart was in draft mode or not) and using multiple processors didn’t make much difference to the results (because I was running a system with a quad processor, I had to enable a single processor only to test against Excel 2003).
2(d) Double-click on series displays format dialog box (as Jon discovered).
2(e) PivotChart field list filters make a comeback on the chart, but are much less intrusive than the Excel 2003 implementation.
3) New Conditional Formatting Icon Sets (one directional and two rating). The sets are now categorized. Furthermore, you can mix and match icons from different sets to create a custom set (with up to five icons).
4) Tables
4 (a) The filter drop-downs now appear in the worksheet column header when the table is scrolled (yay!)
4 (b) New structured referencing shorthand – For calculated columns, the table name is no longer included in the reference, and “[#This Row],” is replaced with “@”
5) Insert screenshot
6) New SmartArt graphics – amazingly though, still no fishbone graphic!
7) New tools for inserted picture
7 (a) Background removal tool
7(b) Artistic Effects
7(c) Enhanced Brightness, Contrast and Recolor tools
8) Several Trust Center Enhancements and the security warning bar that is now in red to attract the user’s attention.
9) New & revised worksheet functions
9(a) A bunch of revised statistical functions (with new names)
9(b) Two new Math functions – AGGREGATE and ISO.CEILING. AGGREGATE is like SUBTOTAL on steroids.
9(c) Two new Date functions – WORKDAY.INTL and NETWORKDAYS.INTL. These work like the existing functions, except that you can specify the weekend days.
9(d) MOD function fixed!!!
10) Macro recording works perfectly for shapes, but there are still problems with charts. The very first chart macro I recorded (with a few formatting options) failed on playback.
11) Equation Editor
12) Copy as Picture option added to Ribbon copy button
13) Undecipherable Paste options icons replace text options
14) Gradient Fill controls modified
15) Greatly expanded Format Shape dialog box
Anything else?
Jon Peltier says
Jimmy –
I’d been thinking that 2007 was like 2002, a forgotten version that was hardly implemented. 2002 because it offered so few changes, 2007 because many users were unhappy with the changes.
Colin –
Thanks for the list. I have yet to see such a list from Microsoft.
Colin Banfield says
Jon, I think that Dave Gainer did a much better job in listing new features during the
Excel 2007 beta. It seems that the Excel team believes that Sparklines are the best thing to talk about right now. I’m already jaded by the three posts on this one topic, and more posts are promised!
Jon Peltier says
It’s official: I’m an idiot. After reinstalling Office 2010, I restarted Excel. This time, compatibility mode was not activated, and all of the buttons which were disabled before have been enabled. I made a few sparklines, and I will admit that they’re pretty cool. Of course, I was looking for drawing baselines or target lines or other features, which aren’t available. But at least I know how to get to these features.
Mike Alexander says
Colin: They seemed to have corrected the Camera Tool bug that made your linked picture disappear if the source was on the same sheet, but off-screen.
Colin Banfield says
Mike, for some reason I thought that that bug was fixed in Excel 2007 SP2. But it’s still there. Thanks for pointing this out.
Jon Peltier says
I’ve amended the text above so people don’t come away thinking that the new features haven’t yet been enabled. That was my mistake: the features are ready for use, and I will be looking at them soon. Especially those sparklines. A lot of people have made comments like “It’s about time, Microsoft, what took so long, and why don’t they have all these features?” The new sparklines are a first version, and they don’t in fact do everything, but this is a fine start.
DaleW says
I’m not sure whether to be happy or sad that Sparkllines seem to be the hottest new feature in Excel 2010.
Did Microsoft implement their Sparklines v1 in the drawing layer (like Fabrice’s open source add-in), or by a custom font as the commercial Sparklines add-ins (BonaVista and Bissantz) primarily use?
BTW, anybody know if anyone has implemented Sparklines for Open Office Calc? (I’m not talking about faking it with something like TinyCharts, but a modestly functional implementation.)
Thanks!
DaleW says
Oops, I just noticed EuroOffice Sparklines for OpenOffice. This 2009 experiment may be nice for pure and very simple Tufte Sparklines (with dialog options to enable/disable each of the four colored indicator dots on a line), but it appears to have very limited functionality so far, even compared with Excel 2010 screenshots.
So I guess that if we want to create Sparklines using a spreadsheet, Excel 20xx is the only practical spreadsheet so far?
Jon Peltier says
Dale –
I’ll be writing about Excel’s Sparklines in an upcoming post. They are actually well done, for a first version; as well done as the second version of in-cell Data Bars, which I am also writing about. I’m sure if you need more advanced sparklines, the third-party add-ins will be able to handle your needs.
Excel’s sparklines are neither drawing layer additions nor tricks with special fonts. They are actually drawn on the back surface of the cells, behind any text which may exist in the cells. This is the same as the data bars. I believe that both are actually bitmaps constructed on the fly and inserted behind the cells.
Sandi says
As always, your posts don’t disappoint. Thanks Jon !
sam says
Also removed in 2010
Cells with more than 255 Characters are not truncated when you copy by “Move or Copy”
Metrick says
Feature: Direct manipulation of data points on charts
97-2003: You can drag data points on a chart and change their source values on the worksheet.
2007: Dragging data points to change the source values on the worksheet is no longer supported.
Has dragging data points to change the source values make it make back to 2010 version.
Jon Peltier says
Metrick –
As far as I know, the Goal Seek capability of charts (i.e., the dragging of points) has not been reinstated, and will not be reinstated.
Microsoft has admitted their mistake, though, and have released an Excel Add-In for Manipulating Points on Charts (MPOC). Wonder of wonders, it’s even written in VBA, not the latest flavor of dot-net.
This add-in lets you select a point, then use sliders on a userform to adjust values. I’ve been noodling around with a similar add-in that actually lets you click and drag the point, like in the good old days. It’s been hard, though, because I am trying to invoke Goal Seek for those cases where the plotted value is a formula depending on other values.
Dennis Wallentin says
>>not the latest flavor of dot-net
Simple because MSFT knows that VBA-developers don’t know how to handle this kind of high-tech add-ins .
All the . regards,
Dennis
Jon Peltier says
Either that, or dot-net would be absolute overkill for such a simple project.
James Robinson says
I have several excel documents that i save as HTML. These documents contain graphs, and in Excel 2007 the graphs save as either PNG or GIF perfectly.
I have found that in Excel 2010 that these graphs, although still being saved as PNG or GIF, actually contain no data. They are blank.
Has anybody else experienced this?
Does any body know a fix or work-around?
Jon Peltier says
James –
Is this the 2010 technical preview, or the beta itself? I haven’t tried all of my tricks yet in 2010, but Save As HTML is related to the exporting feature I use frequently.
James Robinson says
Excel 2010 BETA
SHE says
Working with Excel 2010 containing lots of VBA code AND a brand new i-7-720 laptop.
Have noticed a significant delay in sort routine completions, and significant delay in completing cell sizing by VBA code.
Anyone else having similiar problems ?
Sheldon says
Added comment regarding sorting.
I did confirm that Excel2010 has problems with sorting.
I uninstalled 2010 replacing it with 2003: sorting was returned to normal speeds.
(I was concerned that it might by my new laptop.)
A recorded macro sorting routine in Excel 2010 is very different from all previous versions???
Mark McHenry says
Good stuff. I will be interested in reading more info. about Sparklines and Power Pivot because I have already had prospects and customers start to ask me about it.
Nigel H says
I run a business app in Excel 2003, using an Add-In and a custom command (menu) bar. I fought shy of going to 2007 because of the way such custom menu bars were treated and effectively hidden away. There are tools out there to recreate the classic 2003 interface in 2007, which would overcome this problem. What is the situation in 2010? Can you create a custom bar, and display it so that users have direct access to all the functions and menu drop downs that it contains?
Thanks
Jon Peltier says
Nigel –
The tools that mimic the Classic Excel interface provide only a band-aid approach to the problem, especially since most are not customizable. Excel 2010 provides the ability to customize the built-in tabs from the Excel interface, without having to write XML code. I think you still need to code your customizations if you plan to deploy them.
Clifton says
Am I missing something? My macro buttons which have worked perfectly well for years in Excel 2003/2007work perfectly well first time into Excel 2010 but when the spreadsheet is saved and reentered the buttons are all mashed together over to the left and are virtually invisible. I know they are there because they can be unravelled. Do I need to build a user form to contain all the buttons? Surely not! Can anyone help?
Jon Peltier says
Clifton –
That happened to me a lot in 2007, mostly with files created in 2003 and subsequently saved in 2007. This seems to be fixed when later service packs were installed, which doesn’t help when many users will not or cannot upgrade. I had to write code to reset the position of the buttons and other shapes that went awry.
shopkins says
RE: – Office 2010 sorting problems (SLOOOOW) and Control Buttons changing size and placement……
The BETA version definitely showed these two problems (among a few others). These two were potential killers tho’.
The release version ( Home and Student X16-32007.exe – which I downloaded from Microsoft) DID NOT exhibit these problems. Office 2010 is performing very smoothly and faster than Office 2007, but slightly slower than Office 2003.
And, contrary to several ‘opinions’, I have loaded office 2000, 2003, 2007 and 2010 on the same processor without running into any problems. (I have software that must run under each of these release versions.)
Jon Peltier says
SH – Thanks for clarifying this. I’ve only had time to test that my utilities work in 2010. The 32 bit version worked fine, since all the major changes happened in 2007. The 64 bit version caused minor problems until I wrote some conditional compilation statements to select the proper version of Windows API.
Fatboy says
In Excel 2002, if f I had a chart on top of another chart, I used to be able to just click on the chart in the back, and it would become visible. Now, it still stays hidden behind the chart in the front. The only way to make it visible is to right-click and bring it to front, or move the other chart out of the way. Is this typical of Excel 2010? Is it a bug if you try running on XP 64?
Jon Peltier says
Fatboy –
Believe it or not, this behavior where the active chart does not reveal itself is the intended behavior.
I don’t know what they were thinking.
Clifton says
Jon – I am still having problems with my macro buttons disappearing after a ‘save’, even with the official release of Office 2010. I have had to build a new button bar (User Form)and put all the controls on there for this to survive the save and reentry. I’m a bit surprised there are not more queries about this? Is there a Microsoft guru who could look at this problem report? Thanks, Clifton.
Jon Peltier says
Clifton –
Where are these buttons, and what kind of buttons are they? What format is the file being saved in?
Clifton says
Jon, thanks for reply. The buttons were originally created under Excel 2003 from the ‘Toolbar Options’ bar, the small rectangular object called ‘button’ alongside Check Box, List Box, Combo Box etc. There are not too many of these buttons, each of which simply calls a macro to enable the user to do something. They are generally at the left hand side of the worksheet. I have tried saving the file in various formats (compatible mode, macro-enabled worksheet etc) but the problem persists. The buttons work happily when entering the workbook, but on save and reentry the buttons have been mangled, one on top of the other, over to the left of the worksheet, unusable. Thanks.
shopkins says
Clifton:
What you describe is exactly my experience with the Office2010 beta – right down to the button size and position change on saving the spreadsheet. Question: Did you uninstall the beta version before installing the release ?? If not, I would suspect some ‘interaction’ between the two.
I was really worried about the beta version. I have a spreadsheet with 18 large userforms, and at least 400 ‘buttons’ on the 10 Excel worksheets. I checked compatibility of this spreadsheet under O-2000,2002,O2003 and O2007 without problems occurring (except 2007 which was missing some “shapes” code). With beta, this turned into a real mismash during a save (97-2003 compatible) and on the next call.
Incidentally, I downloaded X16-32007.exe from Microsoft as the tail end of a O2007 Home and Student purchase and subsequent ‘free’ O2010 copy. Could there be differences between versions ??
Jon Peltier says
Clifton –
Toolbar Options puts buttons onto menus or toolbars, not onto the worksheet. To get worksheet buttons, they are either from the Forms menu or from the (ActiveX) Controls Toolbox.
In general, the Forms menu controls are compatible between Excel 2007 and earlier versions, though I have seen some misbehavior. This bad behavior includes moving, resizing, or renaming of the controls, and on only one occasion I’ve seen the controls forget which macros had been assigned.
The ActiveX controls can behave disastrously when workbooks are modified and opened in different versions of Excel, with renaming, resizing, moving, disappearing of controls. On some occasions, I’ve seen the controls replaced by images of the controls. Really. But even in Excel 2007 and before, it was generally safer to stick to Forms menu controls.
SH –
Beta versions usually have worse problems with secondary features, like charts and controls. This is why it is always advisable not to install the beta on any machine which is or will be used for important work. “The beta ate my homework” is heard all too often, from people who really shouldn’t be spending time and risking work with the beta.
Clifton says
Sorry my mistake – the button is from the Forms menu. When the button is invoked it invites entry of the associated macro. Maybe I should deinstall the Beta version before reinstalling the release version? I have found no problems migrating these buttons from 2003 to 2007, it’s just the move to 2010 which is giving me the problems. Or maybe I should just abandon the buttons and put them onto a new User Form instead? Thanks for continued assistance.
Jon Peltier says
My personal preference is to avoid placing buttons in the worksheet, in case I want to use the underlying code elsewhere. However, sometimes there is no need to make things any more complicated than a couple simple buttons.
As I pointed out, beta versions of any software are at risk of being incompatible with production versions. Sometimes to the extent of not being completely uninstallable. It’s probably best to uninstall whatever version of 2010 you have loaded, and install something that might be more reliable.
shopkins says
Jon and Clifton:
Just spent the afternoon setting up for Win7 and Office 2010 Pro x64 on a really fast i7-860 with 8GB memory….
Wow, what a disappointment.
Aside from the need for VBA code modification for x64
…and I haven’t seen the way for 32bit/64bit compatibility as yet
The active X controls moved all over the place, got larger, got smaller and in general were the perfect ending to a terrible day.
More to come later, but I am going to have to put the X32 version back on the system.
I’m planning to put the 32 bit H&S on the same system as the X64. The short time I ran the X64 did show some speed advantage (perhaps enough to offset the slower speed of the larger worksheet size). Did not see any advantage to loading and saving workbook times.
Sheldon
Jon Peltier says
Sheldon –
The ActiveX control repositioning and resizing was a problem with 2007 as well. I spent many hours on one project just wrestling with these things.
The word from people who have used Excel 2010 a bit is that unless you really need to handle enormous worksheets, you’re better sticking to the 32-bit version. Compatibility with existing libraries is one important reason.
Ellen Eifert says
I am experiencing problems using Excel 2010 and using my existing Macros (which worked on 2003). I have the same problem when I re-record the Macro. It has to do with when I use Filters. I am filtering in a column for certain Sales People and then changing another column of the product sold to a different name. While I am doing it the data stays in the column I changed, but when I run the macro again it does nothing with the Data.
Jon Peltier says
Hi Ellen –
I haven’t done much at all with 2010 since I wrote that article, and I really don’t do much programming with filters. I can’t really envision your problem.
shopkins says
RE: floating and resizing of control tools follow up
Office X64 and VBA7
API calls
I have NOT been able to confirm the dad-gummed resizing and repositioning (R&R) of control tools with Excel 2010 (32 bit or 64 bit). I had multiple (R&R) when saving the rebuilt spreadsheet: I thought I had it in hand because the early ‘events’ occurred over a network connection, but I’m positive that it will occur again – right when we don’t expect it and can’t recover. (Murphy’s Law)
One reason I went to Office X64 was the expectation of an elimination of “not enough memory” and other OS oriented error messages. My “major” Excel spreadsheet is fully loaded with perhaps 500+ control tools, in Userforms as well as on Excel spreadsheets themselves. On occasion, out-of-memory occurs with every keystroke, sometimes freezing on the screen (cleared by scrolling only).
X64 definitely has new commands in VBA7, etc., making compatibility difficult at best. I’ve tried the PTRSafe method which didn’t work at all. (This potentially means that X64 versions may have to stand alone with respect to all X32 versions. A lot of study will have to be put into X64/X32.) However, there is ample evidence that X64 ‘processes’ quite a bit faster than X32 with respect to I/O and Excel/VBA code. Win7 X64 also seems to process Excel X32 code faster also.
API calls I’ve used work well whether X32 or X64. The WAV play-a-sound-file works most of the time, but not always. X64 may be clipping the sound file, delay timing may ‘fix’ the problem.
Sheldon
Jon Peltier says
Sheldon –
I still occasionally encounter problems with resizing and repositioning of ActiveX controls and charts, in 2007. I think Forms menu controls are not affected. I also just learned of some code that worked in Excel 2007 Build 6524, but broke when the user upgraded to Build 6535. I have not figured it out yet, but it’s been a bugger since the upgrade back in June.
It seems to be the opinion of early adopters that, unless there is a need for ginormous workbooks, one should stick to 32-bit Office 2010, even on a 64-bit OS. I’ve only used 64-bit 2010 in an undersized VM, so I don’t have anything relevant to contribute.
Clifton says
Sheldon – I can easily reproduce the problem of the control buttons all being amalgamated together on a reopen after a save. The buttons work fine first time into the spreadsheet but do not survive the save and reenter. It’s a real problem for me and my customers as I’ve no choice but to rebuild all the controls onto a user form, update my documentation, and reissue the spreadsheet to customers. No word from my Microsoft contact on the problem either even though I have sent the offending spreadsheet. Surely I MUST be doing something illegal but Excel happily let me put a control onto the worksheet (starting from scratch) and attach a macro, it’s been fine for years but not now.
Clifton.
Lawrence Heltzer says
Jon,
What is the best solution for converting an Excel dashboard file into an app? I’ve been kicking the tires on the trial versions of several compilers (latest releases of XCell and LockXLS) in XL2003 and XL 2010 and have yet to find one that does not distort the charts and the VBA.
Is there such a beast out there? Or is the compiler approach a deadend for protecting Excel-created dashboards?
Lawrence
Jon Peltier says
Lawrence –
You have to think how badly you need to lock up your Excel workbook. Is this a commercial product, how much does it cost, how widely used will it be, etc? Is each additional increment of security worth the exponential increase in effort to accomplish it?
sheldon says
#IF VBA7 constructs:
Finally have gotten compatibility between Excel/vba 2010-64bit and previous versions of Excel/vba-32bit: Except that 2010-32bit introduces a problem seeing the PtrSafe ‘handle” and seems to assume 64bit excel 2010 is being used. This results in a compilation error. I’m looking for an Excel/VBA pointer that says ’64bit’, but so far it’s elusive.
Interestingly the floating, size changing controls don’t seem to be a problem any longer:
without reason (that I see), and a propensity for me to keep my fingers crossed.
Jon Peltier says
Sheldon –
The way to use conditional compilation is like this:
mel says
i have a new i7 laptop and have installed office 2010 of which excell does not seem to work, i create a new spreadsheet all working fine, then i save and re open and nothing works, none of the buttons work. i can right click to do some actions but even if i do this it doesnt let me resave so no point adding to it. if anyone can help im a newby, not too computer savvy so please basic language thanks Mel
Jon Peltier says
Mel –
Not sure I can help, but you can try a few things. First, make sure you have updated Windows (go to Microsoft Update on the Microsoft web site, and add all the Windows updates). Then do the same for Office 2010.
If this doesn’t do it, try uninstalling Office 2010 (Start menu > Control Panel > Programs and Features > Uninstall or Change a Program), then reinstall.
If this doesn’t work, see your IT person, or go to the store where you bought the computer.
sheldon says
Mel:
I installed Office2010 (X32) from a download from MS on an I-720QM Laptop running Windows 7 (X64) with 4GB memory.
I assume your are in a similar situation.
I did not have any problems with installation, or running my existing Excel spreadsheets. I did not have the problem with shrinking/growing/position changing controls on Excel pages until several months after installation. These movements seemed to stop, for unknown reasons and no known intervention on my part. I
am up-to-date on Office2010 patches, none of which addressed control movement in the subject matter.
I have Win7 (x64)/Office2010 H&S (x32) on 3 units presently. Win7 (x64) and Office 2010 Ultimate (x64) on another unit is another question with control movement happening fairly regularly. I have achieved compatibility between X32 and X64 Excel: see Jon’s suggestion a couple of items above.
The control problem definitely occurs during the save operation. If you watch the Excel screen closely you’ll see the change as it happens. I suspect it happens when VBA is still open when the save operation happens, but haven’t been able to confirm this.
Jon’s suggestion above is well stated. Uninstall your Office 2010 (and any other version of Office on the same machine): Reinstall the Office 2010. If you installed a downloaded version (rather than from a CD or DVD), you may want to confirm a good download occurred.
Sheldon
gs says
hi
please help me excel 2010 macros are not working
library missing errors ..
and how to set security level to low in excel2010
regards
gs
Jon Peltier says
The missing library may be related to any third party add-ins that call libraries which are not present in your new setup.
To adjust your security settings, click on the File tab, choose Excel Options, Trust Center, Trust Center Settings, Macro Settings. Choose the “Disable Macros with Notification”, which means you’ll be asked whether to enable macros when opening a workbook that contains VBA code.
RON says
I have tried the new 210 EXCEL. I am quite disappointed by the loss of one feature.
In 2007 I could make the graphs with the cells AND the data in them change their alignment. IN 2010 I can only get the data to change alignment. If there is a way to get the cells to bend I would sure like to know. I have a new graph to make and it requires the top to alignment at about 60 degrees to the right. But How????
Jon Peltier says
Ron –
I don’t understand what you are asking for. Do you want to change the text orientation? Do you want to rotate the chart?
RON says
I want to make a chart that has the data at the top at an angle
and the cells to go with it.
Rick Williams says
I’m not entirely sure what you’re asking for either, but if you want a copy of some cells, that you can move around (and rotate, etc) try making a linked picture of the cell region:
http://www.addictivetips.com/microsoft-office/copy-excel-2010-spreadsheet-data-as-linked-picture-paste-special/
sheldon hopkins says
Update on changing controls size and position.
I hadn’t many problems with this problem recently – nor have I seen it addressed by Microsoft. Yesterday morning the bottom fell out. All systems running Win 7 (64bit) and Office2010 (32 and 64 bit) miniaturize and move 11 controls out of 23 on one Excel worksheet at a greater than 90 percent occurrence. There have been a spate of Office2010 updates from Microsoft, but I have no idea what caused this problem to recur so severely.
I had to find a solution as I can no longer keep the miniaturization from happening.
FIX…… For the affected spreadsheet I saved all of the DATA in a separate “data file” and use that data file to populate all data locations in the “processing file”, i.e. start.xls and process.xls. The data file is considerably smaller than the processing file. When closing the process file I (re)save the data using VBA, and close, but do not save the process file. Often I see the controls miniaturize during shutdown, but since that file isn’t saved, they do NOT get reloaded on subsequent calls. ( This also confirms that the saving of the file is not causing miniaturization. )
This is a quick and dirty fix – and NOT a solution to a significant problem. But I’m weary of working on the problem. I have never seen the problem with Excel 2003, nor with Windows XP. Is Microsoft aware of, or recognize the problem ?? What is the potential that the considerably faster processing of newer cpu’s is causing Excel to be unable to keep up with worksheet modifications using VBA ??
Jon Peltier says
I think the problem is an incompatibility between different shape types in Excel 2007. I don’t know whether 2010 has fixed this, but I don’t really expect it to have been corrected.
Your solution is actually what is done with large formal software projects: create separate tiers for different parts of the program. A typical project will have a data tier, a business rules (calculation) tier, and a user interface (or reporting) tier. When the tiers are separate, changes in one (your data) do not risk problems with others (your interface). It goes against the all-in-one concept of an Excel workbook, but it’s really much more robust, and more easily maintainable.
Jon says
Jon,
I am curious if you’ve made any progress on a modified MPOC that allows one to drag points in a scatter plot (as in Excel 2003). I’m personally not interested in the Goal Seek ability – just the ability to drag points that are plotted from values (not formulas).
We use this feature to modify arithmetically predicted values based on intuition and understanding.
Thanks!!
Jon
Jon Peltier says
Hi Jon –
I have not gotten too far with this tool. Every time I sit down with it, something else comes up. It is on my short list of activities.
Anne Clarke says
I’m having some trouble with macros recorded in 2010, and I’m trying to send the worksheet to someone running on 2003, but none of the macros work for them.
does anyone know how to fix this?
sheldon hopkins says
Re: Anne Clarke’s problem
I’ve been working among versions of Excel from 2000 thru 2010, including 64bit Excel. Macros have worked with no modification among all versions with the exception of the 64 bit Excel (where VBA code need modification, but not necessarily with all macros).
Two suggestions: Be SURE that Macros are enabled, and be SURE that you are sending worksheets with the xls extention (and not xlsx).
Jon Peltier says
Sheldon –
You’ve been lucky. In my experience, macros recorded in one class of Excel (i.e., Excel 2007 or later vs. Excel 2003 or earlier) need at least minor tweaking, and sometimes major overhaul, before they work in the other class.
What you have to do is take the macro recorded in one version, then run it in the other version, see which commands fail, then modify the code and test in both versions until the macro works everywhere.
CliftonVilla says
Am I missing something? After using formulas, macros and VBA for several years, I find today that a simple formula e.g. =COUNT(A1:A100) does not update automatically as new data is entered to the 100 cells in A1 through A100. What’s going on? Only if the formula is revisited, in effect retyped, does the formula update with the correct data. This is def a problem in Excel 2007 but not sure about 2010.
sheldon hopkins says
RE: CliftonVilla ~ Count not updating
I had a recent learning mode problem with calculation and calculationmode. Be sure calculation is enabled; check that calculation mode is set to xlCalculationAutomatic. The mode is saved with the workbook, and must be reset and saved .
Take a look and see if Excel has evaluated the Count formula and replaced the formula with the result. Obviously, and according to MS experts, this cannot (should not) happen, after all this is a spread sheet. However, I have several cells (42 at the last count) specific in one workbook where this phenomenon happens repeatedly: Excel 2003 thru 2010 . The solution was to write the formula via thisworkbook workbookopen() into the troublesome cell EVERY time the spreadsheet is called as well as every time the worksheet is activated.
Jon Peltier says
Sheldon –
Thanks for answering CV’s formula. The problem is in fact usually caused by a workbook stuck in manual calculation mode, which when opened changes Excel’s calculation mode to manual, and any workbook subsequently saved will save this mode for the next time it is opened, etc.
If you only have a handful of cells that exhibit this behavior, I would suspect either automatic event-driven VBA code somewhere that changes calculation mode, or some kind of worksheet corruption. Such corruption is hard to detect, and usually solved only by recreating the worksheet or workbook.
sheldon hopkins says
Calculationmode seems to be a real challenge in VBA.
I had the occasion to “try” a VBA induced manual mode – do some work – then reset automaticmode.
Saving the file would not set things back to automatic: Calling the file again showed Calculate in the status bar. I’m still looking for a way to programmatically determine the mode setting.
The only solution I could find was to use immediate mode to set the xlcalculationAutomatic followed immediately by a saving the file.
Incidentally, this work was to PREVENT Excel/VBA from changing active x control tools size, text size and/or position. I’m 99 % there, including 64 bit Excel, but have the problem with saving the mode (back to automatic). Once I solve the VBA save automatic calculate, the control tool problem will be in the past. Maybe Microsoft will have a solution too – but we need it NOW. (This problem does seem to be directly related to today’s superfast processing.)
Jon Peltier says
Sheldon –
Application.Calculation is a read-write property that is xlCalculationAutomatic or xlCalculationManual. This is what you’re calling calculationmode.
Application.CalculationState is read only, and is xlCalculationPending, xlCalculationCalculating, and xlCalculationDone. This mimics what is seen in the status bar.
You might try using DoEvents before changing Application.Calculation. to make sure Excel finishes any ongoing calculations. Sometimes I set this kind of property twice.
Charlie says
How do you place page numbers on sheets?
Jon Peltier says
Charlie –
Page Layout tab > Print Titles.
Lee Schelin says
Excel Ribbons!!! Why? I can’t stand them. I would rather build my own tool bars and have the buttons I use all the time. Ribbons clutters up the screen and displays many features I never use. Boy would I love to be able to have Excel 2010 look like 2003.
Jan Treise says
Jon, the macro support for SmartArt is virtually nonexistant. I’ve decoded most of the object model necessary to create/modify it in VBA with the exception of converting SmartArt to shapes, Any ideas or workarounds on how to do this programmatically.
Thx
Jon Peltier says
Jan –
I’ve never tried to automate SmartArt. If you’re using Excel 2010 or 2013, I assume you’ve tried the Macro Recorder (it would be worthless in 2007). Was Google any help at all?
Jan Treise says
Using Excel 2010. The macro recorder is still no help in 2010, since it does not record any user actions related to SmartArt. However the object browser does show object members, so I’ve gained insight by inspecting the object model and some experimentation. I can create SmartArt, set text, add/delete nodes, etc. However, Google has not been helpful in either a) locating the procedure call to convert the SmartArt object to shapes or b) selecting and copying all individual shapes within the SmartArt object. I’ll keep looking; just wondered if you had any experience you could share.
Thanks
cliftonvilla says
Does anyone know of the physical limit for VBA code and associated forms in Excel 2010? I am trying to copy and paste some existing code in an Excel file of size 180 mb and it crashes with the message ‘An unhandled win32 exception occurred in EXECL.EXE [4080]’. As usual the error messages are completely useless but I am wondering if it’s to do with the size of the file. Thanks.