Excel Tips & Tricks: The Camera Tool

Everything is captured by the camera: values, formatting, colours, shapes, even the gridlines. What's more, the image can be altered like any other image: scaled, rotated, dimmed, stretched, etc.

Whether you are reading this article looking for a solution to a specific problem or just to enhance your knowledge of Excel, please take an hour or so to play with it. You will find no demo file here, only some guidelines to get you started in your own exploration and one advanced exercise.


A Well Hidden Tool

The best way to access the camera tool is to find it in the toolbar's or ribbon's customize menu. Up to Excel 2003, use:

 * menu (Tools | Customize), or right-click a toolbar and choose "Customize"
 * tab [Commands]
 * category: Tools
 * scroll down until you locate the Camera
 * drag and drop to any existing toolbar
 * close the customize dialog

As a side note, almost an Easter egg, you will find close to the camera tool two other icons labelled "Custom", one showing a deck of cards, the other a calculator. These are meant to be customized and assigned to a macro of your own. However, if you don't, they will attempt to launch respectively the Solitaire game and the Windows Calculator...

In Excel 2007, it's still hidden but you can find it following one of these paths:

 * office button (Excel Options | Customize (left pane)),
    or right-click a toolbar and choose "Customize Quick Access Toolbar..."
 * choose commands from: All Commands or Commands Not in Ribbon
 * scroll down until you locate the Camera
 * click "Add >>" or double-click to add to toolbar
 * close the customize quick access toolbar dialog

If the tool isn't installed, you can also use a little-known menu modifying trick. Up to Excel 2003, the menu shows slightly different items when the Shift key is pressed. To obtain the camera feature, you can thus follow these steps:

 * Select the range you want to monitor;
 * choose (Edit | Copy) or press Ctrl+C;
 * select a destination cell;
 * press and hold Shift while selecting (Edit | Paste Picture Link).

In Excel 2007, the Shift modifier is no longer used, and the options "Paste as Picture" and "Paste Picture Link" are directly available in the copy-paste process. So the Camera Tool, under a different name, is now more easily discoverable.


Static Pictures

When you copy and paste a range or an object, you can also choose to "Copy as Picture" and "Paste Picture". Up to Excel 2003, this is available using the Shift key while navigating the menu. However, the result is quite different.

A static picture is disconnected from the copied area, and becomes an independent object. You can rotate it, stretch it, and otherwise format it as picture, and produce spectacular layouts not easily achieved otherwise. Furthermore, you can directly copy graphic elements such as charts, and paste them as picture. But if you need the picture to update automatically with the source range, the link needs to be preserved.

It's easy to distinguish between static pictures and "picture links", produced by the camera tool. Both are rectangles containing a vector graphics picture, but picture links also have a formula. Simply select the picture and look in the formula bar. If it contains a range reference, it's a picture link.

Incidentally, if you add such a formula to an existing static picture, say "=A1:B3", it becomes a "picture link". Conversely, deleting the formula makes the picture static.


How it Works

In order to truly understand the tool, it's important to know about the internal mechanisms. Let's start with something more familiar: open a scratch workbook with some data, select a region of cells, and copy. Then switch to another program like Word, and use "paste special". One of the options will be "Picture (Windows Metafile)". Once that picture has been pasted, you can try to edit it, using the context-menu. By playing in edit mode, you will discover that the region you copied has been converted to a series of elements, namely shapes and text boxes, which you can drag around, resize, overlap, etc. It's no longer a worksheet range, it's a vector image of the pasted range.

The camera tool uses the same intermediate format. The target area is converted to a metafile, which is in turn displayed in the viewing box. But let's try it.

 * Select a region, possibly containing shapes or a graph
 * Click on the Camera Tool
 * Click anywhere else, or use the pointer to draw a rectangle

You have basically created a rectangular shape, which we will call the "screen", displaying not only the selected range, but also any shapes or charts overlaying it. Let's call this range the "frame".

Notice that you can manipulate the screen just like any other rectangle. Try to resize it or to rotate it. When stretching the rectangle, the underlying metafile structure becomes apparent. When stretching text, the characters themselves are stretched to match. Likewise, all elements are rotated with the container.

Also notice that the image is dynamic. Any change made to the frame region is immediately visible in the screen. Again, this includes formatting, and any overlayed object. Thus, it's much like a webcam, and not just a snapshot image.

Finally, when the screen is selected, you see a formula indicating the address of the frame. When you double-click the screen, the frame region is automatically selected.


The Problems

If you have played long enough, you will have already encountered the main problem with the camera tool: it's slow. One-second delays in screen refresh are frequent, whether your are manipulating the screen or changing information in the frame. This is because Excel needs to recalculate the intermediate metafile (or metafiles) after each and every modification.

These delays are especially problematic when VB macros are used to modify the workbook's contents. Normally, the macro can turn off "screen updating" to speed up processing, but this has no effect on "picture links". If you want to use the camera tool to create a dashboard or a report for a VB intensive workbook, you will need to create it in a different file. "Picture links" work across files, so you simply need to make sure the "dashboard file" is closed while you run the macro. When done, open the file and refresh the links.

Another problem occurs when the frame is overlayed with the screen. It's much like when you are filming a monitor showing what you are filming. The result can be spectacular, but it isn't fast. Something similar occurs when a "screen" is part of the frame of another camera. Anyway, when you reach a dozen frames and screens visible at the same time, your worksheet becomes unusable.

As fun as it is (and it really is), the exercise is quite ridiculous. Why would you place a screen so close to the frame for any serious reason? In real-life situations, you should follow this simple rule of thumb:

Always place the "screen" in another worksheet than the "frame", possibly in a different file

This removes the main problem: the redraw cycle. When working on the frame, Excel will leave the screen alone, because it's not visible at the time. When working on the sheet with the screen, its content doesn't need to be recalculated, since it comes from another static sheet.

A lesser problem is that many users are not familiar with shapes. A shape is anchored in the cell under its top left corner, and can move and resize with the cells underneath. Since a camera screen shape can also automatically resize according to the frame, this can become very confusing. The same can be said about image controls: brightness, contrast, transparency, etc. This leads to the second guideline:

If you intend to use the camera tool, it's very useful to acquire a good understanding of shapes and pictures.

Finally, the camera tool isn't exposed to the macro recorder. Manipulating "Picture Links" from code is of course possible, but it requires advanced knowledge of the Shapes Object Model. Going into details would be beyond the scope of this article, but here is a tiny primer. When run on an empty sheet, without any existing shapes, the following code will create a picture object aligned with the cell D2, linked to the range A1:B4.

Sub CameraTool()
    
    Range("A1").CopyPicture   ' place any picture on the clipboard
    Range("D2").PasteSpecial   ' paste it on the sheet
    ActiveSheet.Shapes(1).DrawingObject.Formula = "=A1:B4"   ' create the link
    
End Sub
                                    

1:
2:
3:
4:
5:
6:
7: