Camera Tool Selfie

Camera Tool Selfie

Happy New Year!

This post-holiday post harkens back thematically to Pivot Table Circular References, while utilizing some of what we learned in Conditional Formatting Color Scales Based on Other Cells.

But enough self-referential posturing, let’s have some tautological photo fun!

I was reading Debra’s roundup the other day and somebody was talking about… actually, it doesn’t matter what, but it got me wondering what would happen if you used the camera tool to take a picture of itself. In case you were wondering, this is the “thematic harkening” to the first linked post above, in which I tried to base a pivot table on itself. This time the results were more colorful, if no more useful.

So what you see below is a dynamic picture taken with the camera tool of an area conditionally formatted with a color scale and the number format set to “;;;”. See the second link above for explanations of all that.

Add a little VBA automation and you can get rid of your cable subscription.

What I didn’t see coming was the infinite loopiness, where the camera tool takes a pic of itself taking a pic of itself taking a pic … Pretty cool.

camera tool selfie

Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.

Here’s the downloadable workbook. See you next time around!

Camera Tool – Now With 33% More Picture

Camera Tool – Now With 33% More Picture

I’ve posted before about Excel’s handy camera tool. I don’t use it much, but I have one monster report that relies on it extensively. It contains code that uses the Shape object’s ScaleWidth method. I went to run the report yesterday and all my camera-generated-pictures got bigger. For reasons lost in the murk of time, I was setting the pictures’ ScaleWidth’s to 1, which made them adjust nicely when the range they point at changes size.

Anyways, all of a sudden the pictures got bigger when the code ran. So I came home and reproduced it on a different computer, without code. Sure enough, the same thing. Below I list a couple simple steps to demo the problem. Both machines I tested were Windows 7, Office 2010 SP2 with the latest patches on both:

First, take a picture of a range:
camera problem 1

Second, right-click and choose Size and Properties (or Alt-JP-O for the mouse-averse). Note that the height and width are listed as 75% and the original size is listed as bigger than the current size:
Post_0047_camera_problem_2

Finally, hit the Reset button and see your picture magically grow! Note that the height and width are now shown as 100% and we’ve achieved the “Original Size”:
Post_0047_camera_problem_3

Post_0047_camera_problem_4

In my limited testing it seems to always be a factor of 75%. So now my code uses ScaleWidth 0.75. I set it as a Const, because talk about a magic number!

Anybody know the cure for this? I searched, and though the camera tool has a fairly rich history of bugs, I didn’t see this behavior mentioned anywhere.

P.S. I know that it’s really more than 33%.

Conditional Formatting Color Scales Based on Other Cells

Conditional Formatting Color Scales Based on Other Cells

With formula-based conditional formatting, it’s pretty easy to base the formats on other cells in the workbook, simply by referring to those cells in the formula. However, it’s more complicated if you want color scales derived from values in another range. In this post I discuss two ways to base color scales on another range. The first uses the camera tool while the second is a VBA subroutine that mimics conditional formatting.

Below is an example of what I’m talking about. The color formatting isn’t based on the values in the pivot table. Instead, it reflects the values in the second table, each cell of which contains the difference from the previous year in the pivot table. The colors range from red at the low end to green at the high end:

ormat with camera tool 1

So, here’s my two approaches to doing this:

Using the Camera Tool

This method uses Excel’s under-publicized camera tool, which creates a live picture linked to a group of cells. In this case the formatting is applied to a pivot table, but you can do it with any range. Here’s the steps:

  • Create the range of formulas that you’ll base the conditional formatting on.
  • Format the numbers in that range to be invisible, by using a custom format of “;;;”. All you want to see is the conditional formatting.
  • Use the camera tool to take a picture of the entire pivot table and paste it over the range you just created, lining up the conditionally formatted cells. Set the picture to be completely transparent, using the “no fill” setting. This way you can see through the picture to the conditionally formatted cells underneath.

The result will be like the illustration below. The source pivot table is in rows 11 to 18, and you can see that the picture starting in row 2 is linked to it. The cells underneath the picture contain the formulas referring to the pivot table. The conditional formatting is based on these cells, whose text is invisible because of the custom format.

format with camera tool 2

One thing to be aware of is that the picture doesn’t update until there’s a worksheet recalculation. You may have to force recalculation with F9 to have the picture update.

For one project I augmented this method by writing code that let me toggle back and forth between the values in the pivot table and the values the conditional formatting is based on.

Using VBA to Create “FauxMatting”

As the heading implies, this method attempts to replicate conditional formatting using VBA. The following subroutine takes two ranges – a source and a target range – as its arguments. It finds the highest and lowest values in the source range. It assigns each of those values a color in a scale from green to red, with white in the middle. This is done by dividing the range of values source values into 255 increments. The colors are then assigned to the target range:

Sub ConditionalFauxmatting(rngSource As Excel.Range, rngTarget As Excel.Range)
Const NUMBER_OF_INCREMENTS As Long = 255
Dim MinValue As Double
Dim MaxValue As Double
Dim ScaleIncrement As Double
Dim ScalePosition As Long
Dim var As Variant
Dim CellColor() As Long
Dim i As Long, j As Long

If Not (rngSource.Rows.Count = rngTarget.Rows.Count And rngSource.Columns.Count = rngTarget.Columns.Count) Then
    MsgBox "Source and Target ranges must be" & vbCrLf & "same shape and size"
    GoTo exit_point
End If
MinValue = Application.WorksheetFunction.Min(rngSource.Value)
MaxValue = Application.WorksheetFunction.Max(rngSource.Value)
'divide the range between Min and Max values into 255 increments
ScaleIncrement = (MaxValue - MinValue) / NUMBER_OF_INCREMENTS
'if all source cells have the same value or there's only one
If ScaleIncrement = 0 Or rngSource.Cells.Count = 1 Then
    rngTarget.Cells.Interior.Color = RGB(255, 255, 255)
    GoTo exit_point
End If
'assign all the values to variant array
var = rngSource.Value
ReDim CellColor(UBound(var, 1), UBound(var, 2))
For i = LBound(var, 1) To UBound(var, 1)
    For j = LBound(var, 2) To UBound(var, 2)
        'the scale position must be a value between 0 and 255
       ScalePosition = (var(i, j) - MinValue) * (1 / ScaleIncrement)
        'this formula goes from blue to red, hitting white - RGB(255,255,255) at the midpoint
       CellColor(i, j) = RGB(Application.WorksheetFunction.Min(ScalePosition * 2, 255), _
        IIf(ScalePosition < 127, 255, Abs(ScalePosition - 255) * 2), _
        IIf(ScalePosition < 127, ScalePosition * 2, Abs(ScalePosition - 255) * 2))
    Next j
Next i
'assign the colors stored in the array
'to the target range
With rngTarget
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
            .Cells(i, j).Interior.Color = CellColor(i, j)
        Next j
    Next i
End With

exit_point:
End Sub

The result looks like this:

format with VBA

I’m not sure how practical this is, but it was fun to figure out! Obviously, you’d want to tie this to a worksheet or pivot table event to update the formatting when the values change.

Here’s a workbook demonstrating these two methods.