Microsoft Excel

Am I the only one that sees humor in the fact that the number "214" is in the same sentence for a list of "shortcuts"? (^_^)

Thank you for the Paste Special shortcut!  I use that all the time and now have to do Alt, then E, then S.  Alt+Ctrl+V I think will be a nice improvement.

Why not port the Word command ListCommands to Excel? Then you needn't worry about losing files (and users could generate their own, custom shortcut lists.)

On a related note, why doesn't Excel list the option "Excel commands" in the drop-down box "Macros in" in the Macros dialog? Word lets the user execute arbitary "Word commands" there.

You can never have too many shortcuts, even if you can't remember them all. I like ALT-CTRL-V, I'll have to memorize that one.

Hello, and sorry for posting this here, but the relevant file format blog entry was locked.  <sheepish grin/grimace>

Excel 2007 looks great--and finally, we're done with the pesky 64K/255 limits!  (For me, that was a killer right there--'bout time, and good job.)  And of course, that's only the beginning....

One question (and possible disappointment):  is there any way to replace Excel's use of ZIP compression when saving files with something a bit more potent, such as WinRAR compression?

I routinely RAR my big (in 2003, 80 MB or so) Excel spreadsheets to cut them down to size, but that no longer works on the new XML or binary Excel sheets because they're semi-compressed already with Zip.  Now if I change the file name from, e.g., *.xlsb to *.zip, extract to a folder, then recompress with RAR, I can reduce the xlsb file size by about 40-50% (e.g., 17.4 MB to 9.16 MB), which is very helpful for uploading on a somewhat slow connection (< 100KB/s).  But the resulting file is not usable by Excel, and it's a tricky process to have end-users go through to patch things up again and make it usable.

Is there any way to specify that Excel should use a different compression engine?  Alternately, given that UNRAR is free, is there any way to make it recognize an XLSB file that's already been re-compressed with WinRAR instead of ZIP?   Alternately again, is there any way to _turn off_ compression when saving, making external compression feasible again?

I presume not, currently, but I hope this is taken as a suggestion.  For many users, the new "compressed" files end up _invincibly_ being _much_ larger (almost 2X) than before, taking that much more storage space and bandwidth.  :(

Thanks.

Paste Special Shortcut - Brilliant!

One erratum in my note: I should say "almost twice as large as they could be" rather than "as they used to be"--even Excel 11 and prior apparently did some of their own simple compression.

RAR-compressing a sheet that has been manually decompressed from XLSB (as in my prior note) results in a _very_ significantly smaller file than does  RAR-compressing the same sheet saved as a regular Excel 2003 XLS file, and that RARred XLS result is in turn significantly smaller than the normal XLSB sheet.  (So, Uncompressed-XLSB->RAR <<< XLS->RAR << XLSB.)

Please do not forget those of us that paste using SHIFT+INS. Could you create a duplicate Paste Special keyboard shortcut ALT+SHIFT+INS?

I use these two in Excel 2002:

Sub PasteUnformatted()
'
' Keyboard Shortcut: Ctrl+q
   ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
       False
End Sub

Sub PasteHtml()
'
' Keyboard Shortcut: Ctrl+Q
' PasteSpecial(Format, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting)
   ActiveSheet.PasteSpecial NoHTMLFormatting:=True
End Sub

For all those requesting more shortcuts, you could roll your own using Application.OnKey in an Auto_Open macro in Personal.xls.

For John Rylander, zip format may not be optimal, but it's the most widely used compressed format. RAR does do a better job, but 7zip (7z format) seems to do better still.

Agreed re 7z being impressive, often (and about as often not) more compact than RAR, but it's a lot slower (1.5 to 2.5X?), too--last time I tried it, anyway.

Maybe the _simplest_ MS solution would be to have a raw binary form, though that might be (temproarily) huge.

Maybe the _best_ MS solution (given that they cannot afford to buy a license for either RAR or 7zip ;) ) would be to handle normally xlsb binaries that contain objects compressed with either of those methods (I'm quite sure it'd take neither time nor trouble to get a free license to decompress either technology).

And maybe the most real-world solution is for Eugene Roshal (author of WinRAR) or Igor Pavlov (7zip) to incorporate smart Office-based compression and decompression (i.e., that decompresses the crude Zip stuff before _really_ compressing the file, and puts it back into crude zip form when decompressing).

Regarding Paste Special, you'd still have to mouse to and click on the particular Paste Special type (values, format, etc), so I don't think the keystroke combination is any more useful than using a mouse to click on a Paste-Special-Values icon that you've put on your Excel toolbar using Customize.  I'd want a Paste Special Values keystroke combination, for example.

Also, it would be nice to have the ability to assign my own combinations to each of the commands I've added to my toolbars.

Maybe this is already possible and I've just never found it ...

Yes, I have to agree that Ctrl+Alt+v is very nice, I do the same for myself currently via VBA.

Sysmod, your code for PasteSpecial for Excel 2002 looks more like VBA for MS Word?  In any case,  I would think that Ctrl+Shift+v opening up the PasteSpecial dialog in Word would probably also be a good idea too. :-)

I really like Francis' thought about having Excel list the 'Excel Commands' from within the 'Macros' dialog. (For that matter, right-clicking on a Control and being able to choose 'Assign Shortcut' would seem a good idea as well, except that it runs the risk that the average user might make a mess?) But Francis' idea would put it out of reach of the average user and so only fairly strong Excel user's -- those used to making Macros and/or assigning shortcut keys -- would find it, and they could make very good use of it I would think. Another approach might be to allow shortcut keys to be assigned within the 'QAT Customize' dialog.

(Just thinking out loud here.)

-- Mike

Hey cgrantski,

The 'Paste Special...' dialog works really well with accelerator keys, tabs, arrow keys, etc.  (I use it currently myself in my own VBA addin.)

Try either of the following lines (but not both!) in a Macro:

    Application.Dialogs(xlDialogPasteSpecial).Show

or:

    Application.CommandBars.FindControl(ID:=755).Execute

Then assign it the 'Ctrl+Alt+v' shortcut.

If you then use it, you'll find that Pasting Values only requires:

(1) Copy
(2) 'Ctrl+Alt+v' (for "Paste Special...")
(3) 'v'          (for "Values only")
(4) 'Enter'      

The Arrow keys and the Tab keys all work as expected as well. It's very nice.

Mike wrote:
"Sysmod, your code for PasteSpecial for Excel 2002 looks more like VBA for MS Word? "

Here's the Word macro I have tied to Ctrl-\

Sub PasteUnformatted()
   Selection.PasteSpecial DataType:=wdPasteText
End Sub

Patrick O'Beirne

I feel like the village idiot but Alt+Ctrl+V does nothing for me after copying a range of formulas

BTW, one issue on the WinRAR or 7zip utility to work especially with Office files (unzipping then fully compressing instead of just compressing the already zipped file, and decompressing and then re-zipping) is that it may be difficult to ensure that the resulting un-RARred or un-7zipped file will have the same CRC as the original.  If true, that's a serious issue (or inconvenience, if one uses a CRC that hashed the uncomrpessed version of the Office file) for the vast majority of naive users.

MS intervention here would be very easy, I suspect, but even more helpful.

Sander Viegers is a user experience (UX) designer in the Office Design Group who contributed to Excel...