Actual Charts vs Images

In a blog post last week, I was looking at the image formats to use when I snapped a picture of a chart. I'm kind of glad that Jon agreed with my assessment on EMF being the best image format for this, as it at least means I might be on the right track.

Jon also asked a question thought "Why not just use charts".

Good question, and sorry I didn't respond earlier, Jon. I got wrapped up in the course I was teaching. 😉

Main Purpose:

Just to get it out there, the main purpose of me building these charts was to show them as micro charts on a dashboard. Some inspirational dashboards I was modeling on can be found at Charely Kyd's site. Here's just one of them.

Now, it's ENTIRELY possible that I'm going about this the wrong way, and am about to get schooled. I'm totally okay with that if it's the case. I haven't done any dash-boarding in Excel before, so this is a big experimentation for me. With that in mind, please feel free to offer suggestions or critique. I'm really trying to find the best mix of results and maintainability here.

I think the best approach to explain why not just use charts is to look at what I did along the way. So here's the history of why I went where I did.

Original Chart

I created my original chart and got it looking exactly as I wanted. I got the colours the way I wanted, set up my scales to work for me, and I basically got it to where I like the overall display. What I ended up with is the chart below (shown at actual size):

Scaling Attempt 1:

From there, I tried to scale the original chart down to micro chart size, and ended up with this nasty looking thing:

I had a quick look through all the options on the chart, but couldn't find anything that dealt with the scale preservation when shrinking. At least, I did find the "Size" on the Chart ToolsàFormat contextual tab on the ribbon, but it didn't seem to help. I could have tried to build it smaller, I guess, but then it would be hard to read on screen to see if it looked okay. In addition, I don't know if I could get everything to the right size. (Font sizes of 1.5, 1.2 or lower maybe?)

Basically, since the scale went all nasty, I gave up and reached toward the camera tool.

Scaling Attempt 2:

I reached towards the camera tool to create snapshots of the charts. Actually, this had another bonus in that I need to create different dashboards from the same data, so I will end up using the same chart more than once. I figured that the camera tool would be a great solution for that, since I would only have to maintain the source chart once.

But the camera tool is flaky. About 6 different charges into the project, I ended up with all the charts suddenly turning grey, and I couldn't get them to come back. Naturally, if you can't rely on it, you really can't use it, so I ditched it and moved on (after wasting a bunch of time trying to make it work.)

Scaling Attempt 3:

This is where I opted to go with copy the chart and doing a PasteSpecialàPicture. Using the EMF version I got a nicely scaled micro chart:

This added another benefit as well, actually, in that the dashboard page could be built on a separate sheet from the original data. Maybe it was the way that I set it up, but this seemed difficult to do with the camera tool. (In all fairness, I did NOT try to create a chart with source data on another sheet, but I can't see that being an issue.)

Potential/Known Issues:

I'm not going to say that this is all roses, either though. There are issues here, obviously, maintenance probably being the biggest of them. The image is static, so I had to knock up some VBA code to remove all the existing pictures on the sheet and rebuild them. That actually wasn't a big deal in the grand scheme of things. And at least the charts look consistent with the originals and print well.

In the process, I discovered that you can name charts in Excel 2007 through the UI. (Whooppee, you'd expect that) But I also discovered that you can name multiple charts the exact same thing. That sucks. Once I'd set up one series of charts, I copied them all to use with my next set of stuff. (You see Food above, but I also have about 9 other product lines to deal with.) I figured I'd just change the source data, but I also had to be very careful to make sure I got the chart names, or I'd have some weird things happening when I referred to those names in code. (I'm floored that we'd be able to name multiple charts on the same sheet with the same name, but there you go.)

I'm also not happy with the difficultly this presents in creating the dashboard for the first time. How do you get the code to recognize the insertion point of the picture on the sheet? The dashboard has numeric data as well, with some (basic) conditional formatting, and the column widths are not consistent. So how do you line up the images with the grid? The short answer is that you can't. So then we get into issues of how to get the image to the correct place.

Thoughts that are currently still cooking

I'm still trying to work my way through this, but I'm starting to wonder if I won't have to force consistent column widths. If I did, I can see a couple of potential solutions to my issues:

• I could then set up a table of chart names and cell references. A little code could then retrieve the chart and place it at the coordinate of the cell specified.
• Rob van Gelder made spark lines by creating a shape via a UDF. This might work if I could feed in the chart name as a parameter but I think it's a non-starter. While you seem to be able to create simple line shapes, I'm running into issues trying to create get the full chart into a (new) shape.

I think I'd actually prefer the latter, but this still kind of sucks as it would probably need consistent column widths to avoid overlapping charts. While it would work well there, I'm not sure I want the rest of my report bound to having extra whitespace.

At any rate, that's what led me into pictures from real charts.

Suggestions, thoughts and comments all welcome.