Hacking the Google Chart API from Excel
a bit of code on a Saturday night ...I've
written before about a simple way to measure and report IT value to the business - quantifying alignment with strategic initiatives project spend in context. It all culminated with a single, simple slide - numbers, with some Tufte-esque Sparklines thrown in.
Click on the picture for a full-size image! 
Well, technologies come and go, and without going into the boring details, I've had to come up with a new way to generate the mini-bar charts along the left side there. It ended up being a relatively straightforward task in Excel VBA - yes, of course the table of data is being driven from a spreadsheet.
Here's the macro that does the trick - I just create a little HTML file that generate the bar charts in series (please excuse the hard-coding) ...
Sub CreateSparklinesDisplayFile()
Dim sOutFile As String
Dim iStartRow, iStopRow As Integer
Dim iStartCol, iStopCol As Integer
Dim i, j As Integer
Dim sDataString As String
sOutFile = "C:\Temp\BizUpdates.html"
iStartRow = 45 ' First row of data to be graphed <<< Evil hard coding!
iStopRow = 51 ' Last row of data to be graphed
iStartCol = 12 ' First column of data to be graphed (includes column of series names
iStopCol = 24 ' Last column of data to be graphed
Open sOutFile For Output As #1
Print #1, "<html><head><title>BizUpdate Sparklines</title></head>"
Print #1, "<body>"
Print #1, "<p>Sparklines for last 12-months spend, IT Projects, by Initiative</p>"
' Loop thru the lines in the table to generate the separate sparklines
For i = iStartRow To iStopRow
Print #1, "<P>" & Cells(i, iStartCol).Value & "</P>"
Print #1, "<img src='http://chart.apis.google.com/chart?"
Print #1, "chs=100x35" ' Size (length x height) of final graphic
sDataString = "&chd=t:"
For j = (iStartCol + 1) To (iStopCol - 3)
sDataString = sDataString & Cells(i, j).Value & ","
Next j
sDataString = sDataString & "0,0,0|0,0,0,0,0,0,0,0,0"
For j = (iStopCol - 2) To (iStopCol)
sDataString = sDataString & "," & Cells(i, j).Value
Next j
Print #1, sDataString
Print #1, "&cht=bvs"
Print #1, "&chbh=a,2"
Print #1, "&chco=CCCCCC,FF3300"
Print #1, "&chds=0,100,0,100'"
Print #1, "title='" & Cells(i, iStartCol).Value & "' />"
Print #1, ""
Next i
Print #1, "</body>"
Print #1, "</html>"
Close #1
End Sub
The output file looks something like this (a simplified version ...)
<html><head><title>BizUpdate Sparklines</title></head>
<body>
<p>Sparklines for last 12-months spend, IT Projects, by Initiative</p>
<P>Cost Reduction</P>
<img src='http://chart.apis.google.com/chart?
chs=100x35
&chd=t:52.25,65.3,72.15,33.15,33.95,33.65,47.7,92.88,79.49,0,0,0|0,0,0,0,0,0,0,0,0,70.57,87.85,55.25
&cht=bvs
&chbh=a,2
&chco=CCCCCC,FF3300
&chds=0,100,0,100'
title='Cost Reduction' />
<P>Growth</P>
<img src='http://chart.apis.google.com/chart?
chs=100x35
&chd=t:67.05,88.25,85.61,95.25,86.70,55.49,54.75,81.19,65.62,0,0,0|0,0,0,0,0,0,0,0,0,55.65,42.05,18.41
&cht=bvs
&chbh=a,2
&chco=CCCCCC,FF3300
&chds=0,100,0,100'
title='Growth' />
</body>
</html>Some things I noted when constructing this stuff ...
- The Google Chart API seems to be picky about the order of the various parameters. I had some troubles getting the charts to work unless I output things just so
- I can control a lot about these graphs, but I couldn't get rid of the x-axis. Yes, there is a chart type for "sparklines" (cht=ls), but that's for line graphs only
- I am calling out the last three months spend in the table, so I want to highlight them in the charts, hence the little hiccup in the j loop
I can publish a version of my spreadsheet that puts it all together, just let me know ...
Previously ...Technorati Tags: application, business value of IT, development, documentation, hands on, presentations, Excel, VBA,
Invisible Technorati Tags: cazh1, James P. MacLennan,
jpmacl, MacLennan,
Labels: application development, business value of IT, development, excel, hands on, presentations, technology, vba, visualizations
Excel 2007 is a BOB system (Bag'O'Bugs)
I am getting seriously aggravated with the flighty behavior of Excel 2007. Yes, I acknowledge that the spreadheets in question is quite complicated ...
- shared VBA modules in an XLA file
- database read/write using ADO
- array-manipulation for ranges of cells (see this post)
- lots of macro (VBA) logic to control look/feel of the tabs
- code to automatically insert formulae into cells
... and so on. However, some of the observed flightiness is specific to the
UberSheet, while other weirdness happens with even the simplest files.
Obtuse Errors that Don't Break in Excel 2003: One of the big, complicated macros does a lot of formatting, so tables of data look nice when printed out. And yes, I freely admit that some chunks of my code are derived from recorded macros, and not 100% hand-crafted goodness. So, when a certain set of circumstances are in place, I get the
Run-Time Error 1004: Unable to Set the LineStyle Property of the Border Class error message. A Google search turned up
this little item, calling out a known bug in Excel 2002. The macro recorder generates something like this:
With Range(Cells(iRow, iDataStartCol), Cells(iRow, iDataStopCol))
.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
End With
... but if I make this minor change ...
With Range(Cells(iRow, iDataStartCol), Cells(iRow, iDataStopCol))
.Borders.Item(xlEdgeBottom).LineStyle = xlLineStyleNone
End With
... the errors cease. Again - the
original error is from Excel 2002, it never breaks in Excel 2003, but fails intermittently in Excel 2007. Nice.
Taking Features Away: I can be a stickler for sharp-looking presentations, and I've written about one of my favorite
PowerPoint best practices - creating complex pictures or tables using a separate application like Excel or Visio, and then pasting into the PPT as a
metafile. Unfortunately, Excel 2007 has broken this handy feature - by inexplicably making it unbelievably difficult to
copy to the clipboard, and then
ruining the metafile that gets pasted. It's not PowerPoint 2007's fault - the metafiles I cut and paste using Visio still work perfectly!
The Pause that Refreshes ... Something: When loading a file from shared folder, extra stuff happens that I can't explain
- Lower left on the status bar - message saying Contacting \\my.domain.server for information. Press ESC to Cancel appears, and we wait a bit ...
- Then, a message appears lower right on status bar - Opening filename.xls - with a nice little progress bar.
- When I save the file, a dialog pops up - Saving filename.xls - with a nice little progress bar. (Why the marked difference between Load and Save status indicators?)
The Pause that Annoys: This last one is the most frustrating (and disturbing!) for me. Among heavy Excel users, I've seen 50/50 split - some hate the ribbon, some are ambivalent - but all long time users love their keyboard shortcuts - especially the oldsters among us that remember
Lotus 123 and
Quattro Pro (first with tabs - rah!). For ages, Microsoft has supported the "/ menu" - a throwback to 123, and a boon to those speed demons who would eschew the
rodent. For years now, I've deleted rows using
/edr[enter]. I can slow that down a bit for clarity ...
- Edit (menu)
- Delete (menu option)
- Entire row (radio button on a dialog)
- (Enter key makes the row disappear)
... but when I'm in the zone, I can type
/edr[enter] /edr[enter] /edr[enter] /edr[enter] and nail a number of rows very quickly. Well, ok, I
used to be able to do that; unfortunately, Excel 2007 (
Bob) has introduced a weird little delay between steps 2 and 3. This means that the default option on the dialog (
Shift cells up) does not change to
Entire row. The r keystroke is lost to the bit bucket, I hit enter - and now my spreadsheet is ruined, because I've taken a cell out (and "broken" the rows below) instead of taking the whole row out. This can be a very tricky problem, because you may not notice it right away, and it can be difficult to see the cells you've deleted (instead of rows) until the sheet is a real mess.
No idea how to get to root cause on these issues, and I'm certainly not
blaming Excel for the latest Wall Street
problems. Still, folks need to be mindful of very subtle differences in this "new" tool.
Previously ...Technorati Tags: development, hands on, presentations, technology,
Invisible Technorati Tags: cazh1, James P. MacLennan, jpmacl, MacLennan
Labels: development, excel, hands on, powerpoint, presentations, technology
Does Excel 2007 VBA have Sneaky Hidden Issues?
Does Excel 2007 VBA have Sneaky Hidden Issues?
I've done a lot of coding over the past few years, on a number of platforms - but since I'm not a full time developer, it's typically limited to VBA, PHP, SQL, some simple web stuff. Still, I've developed some tools that people use - and need to carry forward as the underlying technology moves along. And so, as I've finally made the leap to Office 2007, I had to come to terms with yet another VBA upgrade.
I remember the move from Office 2000 to 2003 - the large number changes in Excel's VBA object model was surprising. I didn't have to rewrite as much this time around, but I did make some interesting discoveries ...
Macro Security
They certainly don't make it easy to lighten up your macro security! By default, Excel 2007 comes with macro security set to High (or harsh, or Suspicious, or something like that). For most of my stuff to work, we need security set to Low - friendly, trusting, etc. The step-by-step process is comical - they are really trying to hide this stuff ...
- From your Excel spreadsheet, click on the Office Button - the nice, round, logo'd guy in the upper left corner
- Click on the Excel Options button (at the bottom of the dialog that pops up)
- In the left-hand column, click on Trust Center
- Click on the Trust Center Settings button
- In the left-hand column, click on Macro Settings
- In the Macro Settings sections, select the radio button for Enable all macros ...
- Exit the workbook, then open it up again - the spreadsheet should process just fine
Workbook_Open()
This is a strange problem - the Workbook_Open() event just does not want to fire. This always worked perfectly for me in Excel 2003, but not in the newest version. I'm not too wrong here - this has previously been seen "in the wild" (here, here, here, and here ... Google your problems away ...)
The Fix: Copy the contents of the Workbook_Open() event into a new Public subroutine, called Auto_Open. All will work as you expect it.
Confusion: this one frustrates me to no end - I haven't found a reasonable explanation of why this happens - but this afternoon, I saw the Workbook_Open() event work as expected in one spreadsheet, yet fail in another. There are some tantalizing clues in this post (something to do with ambiguous names?), or maybe this post (objects copied from one project to another having mysterious interactions?)
VBA cell operations slow down
If you use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. This is not the fastest way to do things, and for Very Large Ranges, you will see some performance degradation. (Walkenbach, of course, has the best write up here). Well, I thought my code was reasonable, but my slow-ish code got much worse under Excel 2007. Looping through a range of cells actually slowed down as you progress through bigger and bigger ranges - I put some breakpoint messages in there, and it was sad to see, I was you will be forced to rewrite these sections as outlined in Walkenbach's method - note that his post is many years old, and this method works in Excel 2003 as well. After I did my rewrites, because the slowdown was brutal, and goodness! My routines are an Order of Magnitude faster! I felt the need to drop a quick note to the users, letting them know that the spreadsheets still worked - the instantaneous response time was not indicative of skipping code.
Hmmm: This behavior, combined with the previous problems with Workbook_Open(), plus a few 1004 bugs (mysterious code that never seems to indicate what the real problem is ...) makes me think there are subtle memory management problems in this version of Excel VBA. Nothing that makes me swear off VBA forever - you just need to be smart about your VBA projects. Excel is for light-duty programming. If you think your project is getting unwieldy, consider another platform - don't let Excel be the hammer for every nail that you see.
AutoFilter
Columns that allowed "selectivity" via the AutoFilter command act differently when brought up under Excel 2007. Not necessarily an issue - unless your macros try to manipulate these things programmatically. I do this a lot, but I found I've been overcomplicating things ...
Before
' Clear AutoFilter settings
wst.Activate
With ActiveSheet
If .AutoFilterMode Then
For i = 1 To .AutoFilter.Filters.Count
Selection.AutoFilter Field:=i, Criteria1:="="
Selection.AutoFilter Field:=i, Criteria2:="="
Next i
End If
End With
After
If ActiveSheet.AutoFilterMode Then Selection.AutoFilter ' take off autofilter
Selection.AutoFilter ' put it back on again
Ribbon vs. Menu
The new Ribbon approach for commands across the top of your documents means the Menu meme is dead. After you get used to finding your way, it's not really a big problem - unless you have coded custom menus for your macros. They won't entirely disappear - they get shuffled off to the Add-Ins tab - just not as convenient.
All in all ...
Am I surprised by the glitches? Not really, but I am pleasantly surprised by the relatively small number of problems my past automation efforts experienced. When they moved from Office 2000 to Office 2003, I had many more problems - Microsoft made some basic changes to some of the Excel objects, and the fixes were not backward compatible. This time, however, folks can use either Excel 2003 or Excel 2007 to use most of the stuff I've created for them - but seriously, shouldn't you get around to converting already? You are running out of excuses.
Guaranteed To Work Under Excel 2007 (?!?!)
What, are you kidding me? I tested my spreadsheets a lot, but it won't surprise me when someone comes up with a use case / scenario that I didn't check for. Just tell your end users to continue with that terrific patience they have shown over the years, and to let you know if/when you see things that don't look right. Hint: encourage them to call you over to their desk when issues occur, so you can take a look at the screen. Screen prints cut and paste into emails, or lengthy explanations over the phone just don't cut it.
Previously ...
- The good and the bad about being a hands-on tech manager (January 25, 2005)
- If you want to be more than a programmer, stop programming (April 8, 2005)
- Fighting with MS Access and version incompatibility (September 26, 2005)
- Code Mover / Transport: Managing source, environments, and deep-diving into multiple technologies (November 6, 2005)
- Look your best with little effort - Excel, Project VBA for Page Formatting (June 11, 2006)
- Three Best TLAs of all time, the hegemony of Excel, and the Intuitive Front End (August 12, 2006)
- Excel vs. RDBMS: Choosing the Technology, Winning the Arguments (March 11, 2007)
- The Joy of Programming, the Challenge of KM (May 5, 2007)
- Stop Thinking, Start Doing - Else Your Career/Skills/Network Will Fade Away ... (January 2, 2008)
- Stretching Your User Interface Design Muscles (April 16, 2008)
- There ain't much IT in IT Management (May 7, 2008)
- Home Development Workstation - Part 1 (May 13, 2008)
Technorati Tags:
application,
development,
hands on,
vba,
technology
Invisible Technorati Tags:
cazh1,
James P. MacLennan,
jpmacl,
MacLennan,
Labels: application development, development, excel, hands on, technology, vba
