The Camera Tool

Category: General Weirdness | [Item URL]

Excel's Camera tool (i.e., a toolbar button with an image of a camera) is a bit elusive these days. It no longer appears on any of the built-in toolbars. So if you want to use it, you'll need to add it to a toolbar (it's in the Tools category of the Customize dialog box). It's description is as follows:

Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.

Even if the Camera tool is not on a toolbar, you can get exactly the same effect by following these steps:

1. Select the range
2. Choose Edit - Copy
3. Press the Shift key and choose Edit - Paste Picture Link (this command is available only when the Shift key is pressed).
4. The result is a linked picture of the original range. You can move this picture anywhere you like.

The Camera tool is most often used to overcome Excel's problem with printing non-contiguous ranges. As you may know, when you try to print non-contiguous ranges, Excel insists on printing each area on a separate sheet of paper. You can use the Camera tool to create linked pictures of the ranges on a separate worksheet. You can then print the pictures on a single sheet of paper.

Another use for the Camera tool is to overlay a picture of a range on top of another range. In the figure below, some of the cells in columns C:F contain an "x" to indicate a missing value. Notice that column B shows a red [Enter] if any columns C:F contain an "x.". But where does the red [Enter] come from? Notice the formula bar is empty (the "[Empty]" text isn't in the cell).

The red text actually comes from a linked picture of another range placed on top of the worksheet range. If the linked picture is formatted with no fill and no lines, it is essentially transparent, and the user can click right through it and select a cell. The only way to actually select the linked picture is to click on its (invisible) border.

Here's where it gets weird. This "click through a picture" technique no longer works in Excel 2000 or later. Clicking anywhere within the picture (even when formatted as no line and no fill) selects the picture, not the underlying cell.

But there's a solution. In order to be able to select "through" the picture, you need to save the workbook as an Excel 5.0/95 Workbook. Then reopen it, and you can select through the picture. You can even then re-save it as a normal workbook and you will still be able to select through the picture. Go figure...

(contributed by Bob Umlas)

Obscure Shortcut Keys And Mouse Clicks

Category: General Weirdness | [Item URL]

Excel is certainly no slouch when it comes to user interface elements. You can right-click on just about anything, and you'll get a shortcut menu that often contains the command you're looking for. And, of course, it supports dozens of shortcut key combinations.

Most of the shortcut keys are documented, but the typical user probably knows 5-6 of them. Following are some of the more obscure user-interface elements in Excel.

• Shift+F10 displays the shortcut menu for a cell or range selection (equivalent to right-clicking the cell or range).
• Select a range of data, and press F11 (or Alt+F1) for an instant chart.
• To toggle between normal view and formula view, press Ctrl+` (that's the key that displays the ~ symbol).
• Right-click the Excel icon in the menu bar (to the left of the File menu), and you'll get the same shortcut menu that appears when you right-click a workbook's title bar (which is not visible if the workbook is maximized). Included on this menu is the very-handy View Code command -- which takes you to the ThisWorkbook code module. (Contributed by Bob Umlas)
• You're probably familiar with the "VCR" navigational controls to the left of the row of sheet tabs. But have you ever right-clicked on those controls? Try it. You'll get a handy menu of all sheets in the workbook. Click and sheet name, and you're there!

• If you press Shift and click either of the inner VCR navigational controls, the sheet tabs will scroll a screen's worth of tabs at a time. (Contributed by Bob Umlas)
• Most people know about the F5 key (equivalent to Edit - Go To), which brings up the Go To dialog box. This is used to go to a named range. You can also type a cell reference (such as AZ902) into the Reference box and click OK to go directly to that cell.
• If you're a VBA programmer, you might be interested in the fact that you can also type a VBA procedure name in the Go To dialog's Reference box. Click OK, and the VB Editor will be activated, and the cursor will be at the first statement in the procedure. (Contributed by Bob Umlas)
• In the Define Name dialog box (Insert - Name Define), the Refers to box can be very frustrating. Say you want to edit the reference... When you press an arrow key, the cell reference changes! To get into normal "edit" mode in the Refers to box, press F2 first. Then you can edit the range reference using standard techniques.
• If you use the "move selection after enter" setting (Tools - Options, Edit tab), you can override this by using Ctrl+Enter. The cell cursor won't change when you have a single cell selected. Also, you can use Shift+Enter to make the cursor move in the opposite direction. (Contributed by Bob Umlas)
• You've probably noticed that the status bar displays the sum of the selected cells. But many people haven't discovered that right-clicking the status bar lets you change the function that's applied to the selected range. (Contributed by Andy Brown)
  
  
• While we're on the topic of right-clicking... In Excel 2000 and later, right-clicking a worksheet's scrollbar displays another shortcut menu that contains substitute commands for scrolling a sheet. As far as I can tell, these shortcuts are worthless. By the time you right-click and make your choice from the shortcut menu, you could have done your scrolling directly. Or maybe I'm missing something.

Moving Data Validation Messages

Category: General Weirdness | [Item URL]

If you use Excel's data validation feature, you probably know that you can specify an input message that will appear when the cell is selected. Normally, this message appears near the cell.

When the prompt message is displayed, you can click it drag it to a new location. In the figure below, the prompt message for cell A1 has been dragged away. After you've dragged a prompt message, all new Data validation prompts are displayed in the same screen position!

There doesn't seem to be a way to "reset" this behavior back to normal -- except by closing and re-opening the workbook.

(contributed by Bob Umlas)

A Range Name Is Really A Named Formula

Category: General Weirdness | [Item URL]

Excel users often refer to named ranges and named cells. This terminology is not quite accurate. When you create a name, you are actually creating a named formula -- a formula that doesn't exist in a cell. Rather these named formulas exist in Excel's memory.

When you work with the Define Name dialog box, the Refers to field contains the formula, and the Name In workbook field contains the formula's name. You'll find that the contents of the Refers to field always begin with an equal sign -- a sure indication that it's a formula.

As you can see in the figure below, the workbook contains a name (InterestRate) for the cell B3 on Sheet3. Whenever you use the name InterestRate in a formula, Excel actually evaluate the formula with that name and returns the result.

If you understand this concept, you'll realize that you can create more complex (and useful) names. For example, you can create a name called ThisMonth (see below) which doesn't refer to any cells.

After this name is defined, entering the following formula will display the name of the current month:

=ThisMonth

Furthermore, named formulas are treated as if they were array-entered. Consider, for example, the following array formula, which returns TRUE if A1:A11 is in sequence, and FALSE if not.

=AND(A2:A11>A1:A10)

This is an array formula, so it must be array-entered with Ctrl+Shift+Enter.

Now, created a named formula, say InSeq, which is defined as follows:

  =AND(Sheet1!$A$2:$A$11>Sheet1!$A$1:$A$10)

After defining this name, you can use a formula like this, without array-entering it:

=InSeq

A Sheet Named History

Category: General Weirdness | [Item URL]

Spreadsheets that keep track of historical information are quite common. But did you ever try to give such a worksheet the (obvious) name History?

Try it, and Excel complains that it's a reserved name. This one had me puzzled, until someone pointed out that the History sheet name is reserved for keeping track of changes when the Track Changes feature is used (a feature I avoid using).

A Dangerous SpecialCells Bug

Category: VBA Weirdness | [Item URL]

The SpecialCells method is a handy way to work with a subset of cells. But using this method doesn't always work as it should.

Macro1, listed below, works with range C1:C16385. When executed, the macro selects all cells within this range that contain a formula which returns an error (for example, #DIV/0!). It works just fine.

Sub Macro1()
  Range("C1:C16385").SpecialCells _
    (xlCellTypeFormulas, xlErrors).Select
End Sub

Now look at Macro2. The only difference is that the macro works with range C1:C16386 (i.e., one additional cell). This macro does not work as expected. Run it, and you'll see that it selects all cells in the range.

Sub Macro2()
  Range("C1:C16386").SpecialCells _
    (xlCellTypeFormulas, xlErrors).Select
End Sub

I haven't bothered to experiment with this to find the exact conditions that trigger the bug, but the problem seems to occur with any range that exceeds 16,385 rows.

No big deal? Well, keep in mind that your code could be using the Delete method instead of the Select method.

By the way, I've been using VBA for a long time, and I had never encountered this problem. Just luck, I guess.

Submitted by Bob Umlas.

Gaps In The Excel Object Model

Category: VBA Weirdness | [Item URL]

If you use VBA long enough, you'll eventually understand the concept of an "object model," and gain an appreciation of how VBA manipulates Excel objects. But you'll also discover that the object model is not perfect. In some cases, it's as if the designers took the afternoon off and never resumed where they left off.

A few examples:

• If you want to write VBA code to insert a page header in the center position, you'll discover that the CenterHeader property (as well as the other "header" and "footer" properties) leaves a bit to be desired. These should be objects, which contain a Font object. Rather, you're forced to create a text string that uses obscure codes. The statement below, for example, inserts "XYZ Corp." as a center header, using Times New Roman font, 11 points, bold, and italic:

ActiveSheet.PageSetup.CenterHeader = _
&""Times New Roman,Bold Italic""&11XYZ Corp."

• Using VBA, it is not possible to determine the source range for data used in an Excel chart. The Values property, for example, always returns a variant array (not a Range object). The only solution is to parse the SERIES formula and extract the range address. I've encapsulated this tedious task into a class module, available here.
• Have you ever wanted to write code to determine if a chart's data labels contain formula links? If so, you've either given up, or discovered that the only way to do this is to select the data label and then use an obscure XLM macro:

ExecuteExcel4Macro("GET.FORMULA(SELECTION())")

• And, what if you are one of the 5-6 people in the world who needs to determine if a secondary chart axis contains a linked formula? You're out of luck. XLM won't even come to the rescue. Because of this flaw, it's impossible to write code that can identify all links in a workbook. A linked formula in a secondary axes can not be identified.
• Excel 2002 introduced several enhancements to worksheet protection. including the ability to prevent selection of locked and/or unlocked cells. Unfortunately, the new Protection object does not include these properties! Apparently, Microsoft assumed that developers would be satisfied with the old EnableSelection property. But the problem is, this property is not saved with the workbook.

A User-Define Function Can’t Change The Worksheet. Oh Yeah?

Category: VBA Weirdness | [Item URL]

VBA programmers know that they can create custom functions that can be used in worksheet formulas. A basic rule is:

A function used in a formula can return a value. It cannot make any changes to the workbook.

There is at least one exception to this rule. A VBA function used in a formula can change the text in a cell comment. The function below demonstrates.

Function ChangeComment(Rng1, Rng2)
   Rng1.Comment.Text Rng2.Text
End Function

If cell A1 contains a comment, the following formula will insert the contents of cell B1 into the comment for cell A1:

  =ChangeComment(A1,B1)

There may actually be a practical use for this. The function below returns the value of the cell it references, and also inserts the current date and time into the referenced cell's comment. This reflects the time at which the cell was last calculated.

Function TimeStamp(Rng1)
  TimeStamp = Rng1.Value
  Rng1.Comment.Text CStr(Now())
End Function

David Hager reports that the following Methods, when used in a custom function, can also make change to the workbook:

• Merge
• UnMerge
• AddComment
• ClearComments
• InsertIndent
  

The Elusive Formula1 Property For Conditional Formatting

Category: VBA Weirdness | [Item URL]

Excel's Conditional Formatting feature is useful, but one gets the impression that the designers were forced to implement it in a hurry. It suffers from two serious problems:

1. Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules. Doh!
2. There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."

In an effort to solve #2, I created a custom VBA function. But then I discovered something very strange:

When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 property via VBA will give you a different formula, depending on the active cell position!

To demonstrate... Activate cell A1 and add this conditional formatting rule: formula is =B1>0

With cell A1 activated, execute this sub:

Sub Test1() 
    Dim F1 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    MsgBox F1 
End Sub 

You'll see the correct formula.

Now, activate any other cell and re-run the procedure. The Formula1 property will return a different formula, which is relative to the active cell.

In order to retrieve the actual Formula1 property value, you need to convert the formula to R1C1 notation using the active cells as the reference. Then convert that R1C1 formula back to A1 style. The procedure below returns the actual Formula1 value, regardless of the active cell position.

Sub Test2() 
    Dim F1 As String, F2 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell) 
    F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1")) 
    MsgBox F1 
End Sub 

XLM Rears Its Ugly Head - -And Crashes Excel

Category: VBA Weirdness | [Item URL]

Caution: This will crash Excel. So if you try it, make sure you don't have any unsaved work.

Try this useless and strange tidbit:

Enter this simple procedure in the code module for ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Stop 
End Sub 

1. Put something in the workbook (anything, so it will print)
2. Click the Print Preview button.
3. At the breakpoint, press F5. All is OK.
4. Now try it again, but this time when you get to the Stop statement, keep pressing the F8 key, even after you return from the Print Preview. Somehow, you will be thrown into some ancient XLM code which doesn't work (see the figure below) Anything you click on just turns bold! Time for the Three-Finger Salute.

Page 3 of 4 pages
[Previous page]   [Next page]