Vote for Me! (Please)

Vote for Me! (Please)

Mike (Mr. DataPig) Alexander is having a contest – Create a Dashboard in Five Minutes. I procrastinated and slipped in just under the deadline – Sunday evening after finishing my chores I figured out how to use Screencast-O-Matic for more than just animated gifs. I turned on my mic, took the black tape off my camera and recorded a five-minute screenshot. It took about thirty takes (as I mentioned to Mike I now have even greater respect for Orson Welles and Aaron Sorkin). I came away with a video that starts with some unnormalized baseball stats and ends up with two pivots and a slicer. Along the way I showcase a few of my own tools, some of which I’ve blogged about. It was fun, I love learning new things fast.

There’s just one problem, dear reader. So far my video only has two measly votes, despite Mike calling me “stately” and saying that this very blog is one of his favorites (and despite voting for myself):

My dashboard video

Now, you know I don’t ask you for much. So, if you’ve ever said to yourself, I don’t know, anything nice about this blog, or MenuRighter, or are just feeling randomly generous, click the link below, follow it to YouTube and vote for me, yoursumbuddy.

After that you should watch some of the other videos.

Editing Shape Points for No Good Reason

Editing Shape Points for No Good Reason

Over at Bacon Bits, Mike Alexander has a nice post out yesterday on editing shape points to create custom graphics. It shows how to use a shape’s Edit Points command to create interesting dashboard icons. I haven’t used Edit Points for years, but his post inspired me to fool around with them. I ended up with a bit of code for Editing Shape Points for No Good Reason.

In Mike’s post he shows how you can edit points to modify a half-circle into a more interesting shape. His edit of a half-circle looks something like this (only better):

semicircle to wavy

I thought that was pretty cool, so then I dragged the top below the bottom:

semicircle to wavy

That was fun. Now how about some facial reconstruction for Smiley?

smiley to pointy

At this point of course I had to learn how to program these edits in VBA. The result is code that randomly messes with the edit points, along with colors:

Pointless Point Editing Code

Sub EditPointMadness()
Dim shp As Shape
Dim shpNodes As ShapeNodes
Dim CenterX As Long
Dim CenterY As Long
Dim CurrXValue As Long
Dim CurrYValue As Long
Dim ws As Excel.Worksheet
Dim pointsArray As Variant
Const PointOffset As Long = 200

Set ws = ActiveSheet
If ws.Shapes.Count = 0 Then
    ws.Shapes.AddShape msoShapeSmileyFace, 300, 300, PointOffset, PointOffset
    Exit Sub
End If
Set shp = ws.Shapes(1)
CenterX = shp.Left + (shp.Width / 2)
CenterY = shp.Top + (shp.Height / 2)
Set shpNodes = shp.Nodes
With shpNodes
    .Insert WorksheetFunction.RandBetween(1, .Count), msoSegmentCurve, msoEditingAuto, _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset)
    If Timer Mod 2 = 0 Then
        pointsArray = .Item(WorksheetFunction.RandBetween(1, .Count)).Points
        CurrXValue = pointsArray(1, 1)
        CurrYValue = pointsArray(1, 2)
        .SetPosition WorksheetFunction.RandBetween(1, .Count), _
            CurrXValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset), _
            CurrYValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset)
        shp.Fill.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
        shp.Line.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
    End If
    If Timer Mod 5 = 0 Then
        .Delete WorksheetFunction.RandBetween(1, .Count)
    End If
End With
End Sub

Every time you run the code above it adds, deletes and/or modifies another point. After a couple of times you get what I like to call “Picasso Smiley”:

smiley picasso

A few more and Smiley is getting blown into the next dimension:

smiley in the next dimension

Hopefully the code above is pretty straightforward. It leaves a few of the settings unrandomized, chiefly whether the new node is straight, shaped or a corner.

One question. How to refresh Excel between shape format changes?
One version of this code had a loop that edited the points every half second. But try as I might I couldn’t get the screen to update and show those changes. The changes would only appear after the code was finished, making a loop pointless. If anybody knows how to do this, please let us know (thereby adding some useful content to this post).

Here’s a workbook with the the code and a couple of buttons to run it.

smiley edit points

My Happy Birthday Contribution

My Happy Birthday Contribution

As Debra, Mike and others have mentioned today is Excel’s birthday. Woohoo!

I’ve been busy with actual paying Excel side jobs and other life stuff (which apparently is more “important” than blogging).

I did take this picture at my workplace the other day and want to share it with you. These bumper stickers are all around the building – just more proof of the awesomeness of my job. This was the first time I’d noticed this one:

Believe in Excel

So Happy Birthday Excel.

I believe in you. And trust you – even though you’re over 30.

Jeff Weir’s Contest

Jeff Weir’s Contest

Frequent visitor to this site, soon-to-be-published Excel author, prodigious blog poster and all-around bon vivant, Jeff Weir, is having a contest over at Daily Dose of Excel. The prize is what promises to be one of the most entertaining Excel books ever – Excel for Superheroes & Evil Geniuses

The contest involves first names of Excel personalities, animal pictures and goofy captions. There’s even a “Doug” one!

You’re supposed to enter in your own name, but if I were to submit one for Jeff it would look like this:

Jeff the beaver

Thanks Jeff for all your support of this blog, your boundless enthusiasm and, of course, for all the yuks.

Check it out:

The Great Your Name Here Competition

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!

Happy International Keyboard Shortcut Day

Happy International Keyboard Shortcut Day

Over at DDOE Dick has decreed the first Wednesday of November as International Keyboard Shortcut Day. In a comment to his post I said I’m about 50-50 in my mouse-to-shortcut ratio.

A literal case in point is my use of Alt-F11, which VBA folk will recognize as the key combo that whisks you from Excel to the Visual Basic Editor. I use it all the time. Oddly, I rarely use it in the other direction.

Speaking of the VBE… it still has old-style toolbar menus, the kind you can easily modify. Whenever I get a new computer there’s a few VBE setup things I always do, like install M-Z Tools and Smart Indenter. I also get rid of many of the buttons, like Cut, Copy and Paste. Ctrl-X, Ctrl-C and Ctrl-V were among the first shortcuts I used regularly and it still gives me a sense of competence and tidiness to trash them.

Speaking of the VBE… some of my favorite shortcuts these days are those that combine the Windows key and arrows for moving windows around on your monitor(s). I use them a lot on my laptop, usually to push the VBE to the right and Excel to the left.

Extreme Keyboard Shortcuts

Poking around for pictures to pad this post, one caught my eye with its double meaning:

keyboard shortcuts 1

This one is just whacky. I’ve read the explanation, but I still don’t know what it does. I think the creator may be as fanatical as Dick:

keyboard shortcuts 1

Why Pi is Irrational – In Pictures

Why Pi is Irrational – In Pictures

I was a good math student in junior high, and I’m sure I did well on all my pi-related homework and test questions. I understood that pi was an irrational number, and knew, theoretically, that it had an endless number of non-repeating decimals. However, I had no real grasp of why it’s irrational. It was just another abstract, memorized math fact. But a few years back a realization came to me. So, in belated celebration of Pi Day on March 14, I offer my pictorial explanation of why pi is irrational.

The realization came when thinking about the formula for an area of a circle, Π r2 (pi * r2) where r is the circle’s radius. It dawned on me that the r2 referred to a real geometric square with sides and corners and all, not just a letter with a number above it, and that:

Pi represents the number of squares with a side of length r that would fill a circle with a radius of length r.

Therefore, my explanation of why pi’s digits are infinite is that no matter how many squares you put inside a circle, there will always be a smaller “corner” into which you can cram a smaller square. This leaves an even smaller corner for an even smaller square, and on and on without end.

Pi Squares 1

I don’t know whether this is a mathematically valid explanation. But transforming this abstract concept into something I could draw with a pencil pleased me no end.

Pi and VBA

A pencil is one thing, but on Pi Day I decided to illustrate my explanation with Excel and VBA. It took me a really long time! First I tried using shapes, a reasonable, but really bad, idea. I ended up using just one shape, a circle, and a bunch of tiny cells, which are, after all, squares:

Pi Squares 2

The circle above has a radius of 50. When all the squares that fit are filled in, their total area is about 3.08 times that of a square with a radius of 50. In other words, it’s fairly close to pi. If I change the settings to a radius of 100, the number climbs above 3.1, but still short of pi.

Programming this was challenging and fun. Because of the nature of the project I coded some things more loosely than normal. There’s a bunch of global variables, and even a “Select Case True” statement.

I did a lot of Unioning and Intersecting in the code. One thing I rediscovered is a major glitch with the Union statement, which gives you an incorrect cell count for the Union of two overlapping ranges:

Union wrong count

Two Miscellaneous Things

My search for a screen-capture-to-animated-GIF program continues. I had been using Cropper, but it was unable to keep up with the circle’s “exit stage left” in the animation above. As you can see, I’m now trying ScreenCast-o-Matic, which has a nice interface, and both desktop and web versions. For $15 I can upgrade and remove their logo from the video, but when I tried this morning their website refused to take my money.

Finally, on Pi Day’s website it claims to be celebrated all around the world. It must be a fairly abstract reference in countries that use the, more logical, DMY date system, e.g., 14/3/2013.


Here’s a workbook with the code.

Curbside Recycling

Curbside Recycling

Free Dirt

Here in my beloved hometown there are many things available for free out on the sidewalks. Often they are marked by a sign.

More Free Stuff

And often they’re not, like this whimsical offering of fake flowers and sparkly stuff.

I’ve been getting in on the act lately and have deposited various furnishings on the curb out front. I always put signs on them, and because I’m silly I always come up with different way to say “Free.”

My favorite so far is “Free to a good home … or yours.” A more convoluted sign said “The state I’ve achieved tastes of reality.”

If I was to do it in Excel I guess I’d go with =PMT(0,1,0)“.

Solving the NPR Sunday Puzzle – #4

Solving the NPR Sunday Puzzle – #4

Not to brag but… well, okay, to brag, I had a personal best time in solving this week’s puzzle:

“Name two different kinds of wool. Take the first five letters of one, followed by the last three letters of the other. The result will spell the first and last name of a famous actor. Who is it?”

I could contrive a way to solve this with Excel, but I’m really just looking for an excuse to tell a riddle made up years ago, which provides a clue to the answer:

Q. What do you call a piece of luggage made from wool?

Continue reading