What Level Are You?

When I make a post here, I always wonder how many people actually benefit. Is it too advanced? Too simplistic? In other words, I have no idea what kind of Excel users actually read this blog.

So how about a poll? Please reply, even if you don’t actively participate here by posting comments.

Writing To A Range Using VBA

If you need to use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. Like this:

>On my system, writing 100,000 values using a loop takes 9.73 seconds.

A faster way to accomplish the task is to put the value in an array, and then transfer the array to the worksheet. The procedure below writes 100,000 values in 0.16 second — about 60 times faster than the looping method.

Displaying CommandBar FaceID Images

If you create custom menus or toolbars, you’re probably familiar with the FaceId property — a numeric value that specifies an image. People have come up with lots of ways to display the FaceId images. Here’s one I haven’t seen before.

Start with an empty workbook, and paste the ShowFaceIDs procedure into a VBA module. Adjust the ID_START and ID_END constants to specify which images you’d like to see (it’s currently set up to show the first 500). Run the macro, and the 18 x 18 pixel images are displayed on the active worksheet. Click an image and you’ll see it’s FaceID value in the Name box.

Sub ShowFaceIDs()
    Dim NewToolbar As CommandBar
    Dim TopPos As Long, LeftPos As Long
    Dim i As Long, NumPics As Long

‘- – – – – Change These – – – – –
   Const ID_START As Long = 1
    Const ID_END As Long = 500
‘- – – – – – – – – – – – – – – – – – – –

‘   Delete existing TempFaceIds toolbar if it exists
   On Error Resume Next
    Application.CommandBars(“TempFaceIds”).Delete
    On Error GoTo 0

‘   Clear the sheet
   ActiveSheet.Pictures.Delete
    Application.ScreenUpdating = False
   
‘   Add an empty toolbar
   Set NewToolbar = Application.CommandBars.Add _
        (Name:=“TempFaceIds”)

‘   Starting positions
   TopPos = 5
    LeftPos = 5
    NumPics = 0
   
    For i = ID_START To ID_END
        On Error Resume Next
        NewToolbar.Controls(1).Delete
        With NewToolbar.Controls.Add(Type:=msoControlButton)
            .FaceId = i
            .CopyFace
        End With
        On Error GoTo 0
       
        NumPics = NumPics + 1
        ActiveSheet.Paste
        With ActiveSheet.Shapes(NumPics)
            .Top = TopPos
            .Left = LeftPos
            .Name = “FaceID “ & i
            .PictureFormat.TransparentBackground = True
            .PictureFormat.TransparencyColor = RGB(224, 223, 227)
        End With
       
‘       Update top and left positions for the next one
       LeftPos = LeftPos + 16
        If NumPics Mod 40 = 0 Then
            TopPos = TopPos + 16
            LeftPos = 5
        End If
    Next i
    ActiveWindow.RangeSelection.Select
    Application.CommandBars(“TempFaceIds”).Delete
End Sub

Even if you don’t create CommandBars, you might have fun looking at all the little pictures.

Posting From Word 2007

This is just a test post, using Word 2007 Beta. I tried to get it to work with my own blog, but no luck. WordPress is one of the supported options, and it’s supposed to work with the MetaBlogger API. But either I can’t figure out what their cryptic dialogs want, or it just doesn’t work.

Let’s try a list:

  • Bullet Point #1
  • Another one
  • Final Bullet Point

    Does indented text get tagged as blockquote?

How about a picture?

http://j-walkblog.com/images/absolutopenmic.jpg

Let’s try a table:

Column 1

Column 2

Column 3

Row 1, Column 1

Row 1, Column 2

Row 1, Column 3

Row 2, Column 1

Row 2, Column 2

Row 2, Column 3

 

Here’s a code listing using the [ vb ] tags.

 <pre>Sub AddToShortCut()
‘ Adds a menu item to the Cell shortcut menu
   Dim Bar As CommandBar
    Dim NewControl As CommandBarButton
    DeleteFromShortcut
    Set Bar = CommandBars(“Cell”)
    Set NewControl = Bar.Controls.Add _
      (Type:=msoControlButton, ID:=1, _
      temporary:=True)
    With NewControl
      .Caption = “Toggle &amp;Word Wrap”
      .OnAction = “ToggleWordWrap”
      .Picture = Application.CommandBars.GetImageMso(“WrapText”, 16, 16)
      .Style = msoButtonIconAndCaption
   End With
End Sub
</pre>
<p>

(I updated this using Live Writer — which at least lets you edit the HTML code.)

 

 

And it handles images, too.

Excel 2007 FAQ (Draft)

This is my first stab at a FAQ for Excel 2007. It’s based on Beta 2 Technical Refresh, so there is a chance that some of the problems noted will be fixed in the final version. Please feel free to add your own Q & A’s. Or, just the Q if you don’t have an A.

[Note: Updated Nov-6-2006 with some corrections.]

User Interface

Q: How do I open a file?

A: That round logo in the upper left corner is not just for cosmetics. It’s called the Office Menu button, and it’s used for a variety of purposes, including file operations. Click it.

Q. How do I get to the Excel Options dialog box?

A. Click the round Office Menu button, then click Excel Options.

Q. I clicked the round Office Menu button, but I don’t see Excel Options.

A. Look at the very bottom. It’s a button, not a menu item. And make sure you don’t click Exit Excel by mistake.

Q: how do I hide/show the Ribbon?

A: Use Ctrl-F1 to toggle the display of the ribbon.

Q: Where did the xxxxxx command go?

A: There’s a pretty good chance that it’s on the ribbon somewhere. But there’s also a chance that it’s not on the ribbon. In the latter case, you can add the command to your QAT. Also, try using the old Excel 2003 hot keys (for example, Alt+T, I to display the Add-Ins dialog box).

Q. What’s a QAT?

A. QAT is Quick Access Toolbar. This is the only user interface element that can be customized by the end user.

Q. Where are my old custom toolbars?

A. Click the Add-Ins tab and you’ll see them.

Q. I can’t make my old custom toolbars float.

A. No, you can’t.

Q. How do I “tear off” the Fill Color icon so I can float it?

A. You can’t.

Q. How do I get Help? The ‘ask a question’ box is gone.

A. Press F1, or click the little question mark icon in the title bar.

Q: How can I hide the status bar in Excel 2007?

A: You must use VBA to hide the status bar: Application.DisplayStatusBar = False

General

Q: I opened a workbook and my worksheets have only 65,536 rows.

A: Save it in an Excel 2007 format, close it, then re-open it.

Q: Where is the list with open workbooks?

A: Use View / Window / Switch Windows. Better yet, add this command it to your QAT. Right click on Switch Windows to add it so it is always one click to access it.

Q: Ctrl+A doesn’t select all of the cells in my worksheet.

That’s probably because the cell pointer is inside of a table. Press Ctrl+A three times to select all worksheet cells.

Q. The Custom Views command is grayed out.

A. That’s probably because your workbook contains a table. Convert the table to a range, and then you can use Custom Views.

Q: What happened to the ability to create a pivot table using the Multiple Consolidation Ranges option?

A: That option still exists, but you need to add the ‘PivotTable and PivotChart Wizard’ command to the Quick Access toolbar (Found in ‘Commands not in the Ribbon’), and use that command to start a new pivot table.

Q: I can’t find the command to apply names to cell references in a formula. In Excel 2003, the command was Insert / Name / Apply.

A: The Define Name control in the Formulas / Defined Names groups is a drop-down. Click the down-arrow, and you’ll see the Apply Names command.

Q: Why doesn’t the F4 function key repeat all of my operations? 

A: I don’t know. The very useful F4 is much less useful in Excel 2007.

Q. What happened to the ability to “speak” the cell contents?

A. To use those commands, you must customize your QAT. They are listed under ‘Commands Not in the Ribbon’.

Q: Where is the Mail Recipient (body) option in Excel 2007?

A: You must customize your QAT. They are listed under ‘Commands Not in the Ribbon’

Formatting and Printing

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That’s probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the ‘Display options for this worksheet’ section, and remove the checkmark from ‘Show Page Breaks’.

Q: Can I add that ‘Show Page Breaks’ option to my QAT?

A: No. For some reason, this very useful command isn’t available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can’t find a way to get the orientation back to normal. There’s no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I’m trying to apply a table style to a table, but it has no effect.

That’s probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can’t find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Charts and Graphics

Q: Double-clicking on a chart element doesn’t display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it’s very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it’s always visible.

Q. I added the Chart Elements control to my QAT, but the original control no longer shows added elements such as trendlines and error bars.

A. Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What’s a dialog box launcher?

A: It’s the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I’m working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title ‘Format Shape,’ and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn’t it? When you’re finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don’t work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. I added a few shapes to a Chart sheet. Those shapes don’t get changed when I apply a new document theme.

A: Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose ‘All Commands’ and then select ‘Change Shape’. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can’t I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment’s border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don’t. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to “Set as Default Shape.” This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you’ve applied to the shape as the default (fill, outline, effects). The command should probably read “Set as Default Shape Formatting.”

Macros

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don’t have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable ‘Show Developer tab in the Ribbon.’

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don’t accept the default “Yes” button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I’m trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can’t. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you’re a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys “%w{F6}”

Q: I’m trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the “Copy xxxxxxxx” option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp “HP10062493”. The text in quotes is the topic ID pasted from the clipboard.

Exporting All Graphics

One way to export all graphic images from a workbook is to save the file in HTML format. This creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and range images (created with the “camera” tool).

Here’s a VBA procedure that automates the process. It works with the active workbook.

Sub SaveAllGraphics()
    Dim FileName As String
    Dim TempName As String
    Dim DirName As String
    Dim gFile As String
   
    FileName = ActiveWorkbook.FullName
    TempName = ActiveWorkbook.Path & “” & ActiveWorkbook.Name & “graphics.htm”
    DirName = Left(TempName, Len(TempName) – 4) & “_files”
   
‘   Save active workbookbook as HTML, then reopen original
   ActiveWorkbook.Save
    ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Workbooks.Open FileName
   
‘   Delete the HTML file
   Kill TempName
   
‘   If Excel 2007, delete all but *.PNG files in the HTML folder
   If Val(Application.Version) >= 12 Then
        gFile = Dir(DirName & “*.*”)
        Do While gFile <> “”
            If Right(gFile, 3) <> “png” Then Kill DirName & “” & gFile
            gFile = Dir
        Loop
    End If
‘   Show the exported graphics folder
   Shell “explorer.exe “ & DirName, vbNormalFocus
End Sub

It starts by saving the active workbook. Then it saves the workbook as an HTML file, closes the file, and re-opens the original workbook. Next, it deletes the HTML file because we’re just interested in the folder that it creates (that’s where the images are). If you’re using Excel 2007, the code loops through the folder and deletes everything except the PNG files. Previous versions use a combination of GIF and PNG files, so this step is skipped if you’re using a pre-Excel 2007 version. Finally, it uses the Shell function to display the folder.

You’ll find that the quality of the images is much better in Excel 2007. But, unfortunately, pictures of ranges still look terrible.

Instant Slide Show

Here’s a little VBA procedure that gives you a full-screen slide show, displaying all of the charts on the active worksheet.

Sub ChartSlideShow()
    Dim ChtObj As ChartObject
    Application.DisplayFullScreen = True
    Application.ScreenUpdating = False
    For Each ChtObj In ActiveSheet.ChartObjects
        ChtObj.Chart.PrintPreview
    Next ChtObj
    Application.DisplayFullScreen = False
End Sub

Press Space, Enter, or Esc to go to the next slide. This looks a lot better with Excel 2007.

Who will be the first to beef it up by using the OnTime event?