Sometimes It’s The Little Things
(stackoverflow rep: 9138, Project Euler 90/274 complete)
From time to time I trawl through my blog subscriptions: some are defunct while others may have changed their feed details sufficently that they’re no longer being picked up. I have about 270 subscriptions, which makes the job a chore and hence it doesn’t get done very frequently. The upshot is, for the case where the blog hasn’t just died, I sometimes miss something.
What should we do with tedious manual activities? Automate! I went and did some investigation.
Google Reader will, through the “manage subscriptions” link (it’s at the bottom of the subscriptions list in my browser) let you download your details in an XML (more specifically, Outline Processor Markup Language, or OPML) file. It looks like this (heavily snipped to avoid excess tedium):
<?xml version="1.0" encoding="UTF-8"?>
<opml version="1.0">
<head>
<title>mikewoodhouse subscriptions in Google Reader</title>
</head>
<body>
<outline title="misc" text="misc">
<outline text="Grumpy Old Programmer"
title="Grumpy Old Programmer" type="rss"
xmlUrl="http://grumpyop.wordpress.com/feed/" htmlUrl="http://grumpyop.wordpress.com"/>
<outline text="Google Code Blog" title="Google Code Blog"
type="rss"
xmlUrl="http://google-code-updates.blogspot.com/atom.xml" htmlUrl="http://googlecode.blogspot.com/"/>
</outline>
</body>
</opml>
Ignoring for the moment the beauties of XML, this is pretty simple: there’s an outer “outline” that matches the folder I’ve created in Reader, within which is an outline for each feed to which I’m subscribed.
What I wanted to do is something like this:
- parse the OPML, extracting the xmlUrl tag;
- download the feed using that tag;
- scan the entry listing in the feed to find the latest entry date, as a proxy for the last-known activity on that blog;
- review the blogs that seemed oldest and deadest for update or removal.
Well, with a little Googling and not much Rubying, it actually turned out to be so. John Nunemaker’s HappyMapper gem does a quick enough job of the parsing:
require 'happymapper'
module OPML
class Outline
include HappyMapper
tag 'outline'
attribute :title, String
attribute :\xmlUrl, String # remove the \ - WordPress insists on trying to make a smiley out of colon-x
has_many :\outlines, Outline # see above. Stupid WordPress. Or me. Or both.
end
end
sections = OPML::Outline.parse(File.read("google-reader-subscriptions.xml"))
sections.delete_if { |section| section.outlines.size == 0 } # remove outline children with no parents
The delete_if part is there to cater for my parse creating duplicates of the “child” outlines: once in their own right and once within their parent section. I’m pretty sure I’ve seen how to avoid that somewhere, but for now this will do, since all my subscriptions live in folders. It leaves something there for the next iteration.
And then there’s the spiffy little Feed Normalizer gem, that will parse RSS or Atom agnostically, which is good: I don’t want to have to care.
require 'feed-normalizer'
require 'open-uri'
sections.each do |section|
section.outlines.each do |feed|
list = FeedNormalizer::FeedNormalizer.parse(open(feed.xmlUrl))
latest = list.entries.map{|entry| entry.date_published}.max
puts "#{section.title} #{feed.title} #{latest}"
end
end
Job done.
OK, this is the everything-works-as-expected version, which assumes files will always exist (they won’t), date strings are present and valid (they aren’t), but nobody wants to see a pile of exception- and error-handling code. Or at least, they shouldn’t. Not in a blog post.
Buddy, Can You Paradigm?
(stackoverflow rep: 8998, Project Euler 89/273 complete
(or: “If Paradigm was half as nice”)
I started reading Coders At Work recently. In the style of a few predecessors, the book comprises transcribed (and presumably edited) interviews with fifteen programmers who have made significant contributions of various kinds. It’s a weighty tome, possibly a little overweight if I’m honest, but interesting nonetheless; it makes a change from the kind of technical tome I usually cart around on the daily commute.
Circling a little closer to, and nodding vaguely in the general direction of the point, interviewee number seven, reached just this morning, is Simon Peyton Jones, who was one of the progenitors of Haskell, one of, if not the first name that springs to mind when one hears “functional programming“. For the last decade, Jones has been a researcher at Microsoft Research in Cambridge, which suggests that he would have had at least one finger in the F# pie… There’s a DotNetRocks show number 310 features an interview with the man, by the way. I’m enviously disturbed that despite our very similar ages, he appears considerably less follicly-challenged than me, although there could be some comb-over action going on there.
In the taxonomy of programming paradigms (does that make sense?) we have Declarative and Imperative. They are opposites. In the Declarative fold, we get stuff like SQL, XSLT, all the functional languages and, I submit Excel worksheets. Actually it’s not just me: that Wikipedia link places spreadsheet cell-based programming within the Dataflow subcategory of Functional. Imperative programming, on the other hand, includes the panoply of more “traditional” languages, including good old Visual Basic and its slightly less functional sibling VBA.
Thinking about Excel and VBA developement, I’d say there are several fairly distinct uses for VBA code (usually, but not exclusively: we can write add-ins or perform automation in plenty of other ways) which include:
- New worksheet functions;
- Interface extensions (menus, toolbars, ribbons etc);
- Helpers (or wizards);
- Control and simulation
…of which I write a lot of the latter two.
(You know how some comedians talk about a string of seemingly unrelated things and then cleverly tie them all together at the end? Well, fingers crossed, stay tuned)
A while back, I was handed a workbook, developed by an external consultancy, that modelled the 20-year evolution of a financial product. We wanted to run it across a significant number of stochastic market data simulations to get a picture of the distribution of possible outcomes. About 30,000 such simulations (and about 16GB of simulated market data) would be sufficient, we thought. The workbook took about 20 minutes to perform one such calculation. We didn’t have a year.
Fortunately, there were any number of classic optimisation failures to rectify: massive VBA/worksheet interaction at the cell level (changed to use large arrays), screen update enabled at all times, lots of less painful VBA-specific stuff, Application.Calculation = xlCalculationAutomatic, you know the drill. Easy stuff. Within a day it was taking about a minute and I could look at having the whole analysis done in under a week (I have a four-CPU machine).
If it all seems straightforward, it wasn’t. I’d wrapped the workbook in a modified (hacked-up from xlUnit) test jacket to check that my changes weren’t affecting the results, pretty much a necessity for any refactoring work, and one change kept breaking. Every time I tried to switch off automatic calculation, triggering it only when I thought it was needed, the final results came out different. I finally realised that the VBA and worksheet were extremely highly-coupled – calculations were occurring as the code pushed values through: change a value, auto-calc, change another value, auto-calc again and so on. Eek. It took almost a week to unravel. Of course, since I actually enjoy doing this kind of work for the most part, it wasn’t a complete disaster, but I could have lived without the time pressure…
As a finance worker (perversely, I’m increasingly inclined to describe myself as a Banker1 these days) I have cause, from time to time, to access The Bloomberg from Excel. At least one of the functions in the (extensive and powerful, let’s be fair) API is a real functional-imperative mess. Here’s one with which I’m not at all happy:
=BDH("MXWO", "PX_LAST", "01/06/2007", "28/12/2009", "Dir=V", "Dts=S", "Sort=A", "Quote=C", "QtTyp=Y", "Days=T", "Per=cd", "DtFmt=D", "cols=2;rows=672", "FX=EUR")
Yes, there are a lot of arguments2, but most are at least not positional, which shows they’re trying. This particular example asks for the last daily quote of the MSCI World Index, in Euro, between the dates given. Even better, I guess, there’s a wizard to generate it all for you. But here’s the nasty bit: the call fills as many rows – without warning about overwrites – as it needs. Oh, and that "cols=2;rows=672" parameter? It’s rewritten if you make any change that would affect the number of cells output. This is not easy to accomplish. Try duplicating the effect in VBA, go on, I double-dare you. I think there’s a separate server process at work here, able to make asynchronous (but timely) modifications to user worksheets.
Yikes. And ugh.
To me, there’s something broken here. They’re using am apparently functional programming model but allowing the code to affect the state of the worksheet in (from the user’s perspective at least) a rather arbitrary (or at least, unpredictable) way. We’re not even getting an array formula here – the function just causes chunks of worksheet to be filled with data. In point of fact, there looks to have been some serious programming work here – I don’t think in the normal course of events that calling a function is allowed to cause areas outside the calling range to be modified. I rather suspect an external process is being asked to pump values into the sheet through some devious means like (but let’s hope it isn’t) DDE.
(This is where I try to pull it all together. A little encouragement wouldn’t go amiss here…)
The fact that we have both paradigms represented as first-class components of a single development platform may be a large part of the reason for Excel’s dominance in the marketplace. But the interface between declarative and imperative code needs to be carefully managed to avoid the kind of unnecessary side-effects that I spent so long untangling.
The messed-up simulation I whined about above failed to recognise that the worksheets should have been, in effect, a great big, super-complex function, which the code used by setting up the input conditions, firing off a Calculate and then reading the output values. Instead, it tried to implement a tightly-coupled declarative-imperative abomination, with unfortunate results.
Let’s try not to repeat that mistake, for the sake of the children.
I’ve been (and remain) grumpy – thank you and good night.
1 Wanna make something of it?
2 Many Reuters functions do something similar, but push all the key-value pairs into a long string.
Wax On, Wax Off, Wax Lirrical?
(stackoverflow rep: 8502, Project Euler 88/266 complete
One of the presentations at the Stack Overflow London DevDay (and I believe something similar occurred at several of the other events) was an introduction to Python, conducted via a dissection of a classic piece of code developed by Peter Norvig, currently Chief Scientist at Google.
The code is a “toy” spell-check routine and the talk focused on some of the “interesting” aspects of Python that help to make it powerful despite being very concise. List comprehensions, in particular.
What wasn’t so apparent at the time was how the code actually worked, and I wanted to understand it. Plus, in my competitive way, and given that I kind of left Python behind a few years back*, I wondered whether a Ruby implementation could be as terse.
So, obviously enough, given that I’m writing this, I wrote one. I didn’t quite get to Norvig’s 21 lines: Python not needing “end”s gives it a big advantage. If Ruby had significant white space I’d have made it. Actually, if I was prepared to make one really long line out of the Array creation (lines 17-20 below) I’d have squeaked under, but we’re not playing code golf and I balk at 170-character code lines.
There are several other-language implementations listed at the bottom of the page, including a Ruby one. On my WinXP machine, running straight MRI 1.8.6, my version is a gratifying 2.7 times faster, as well as being about 8 lines shorter.
Having got there, and achieved the original purpose of gaining a reasonable understanding of the algorithm in the process, I’m less than wholly satisfied with the outcome. It does the job, but it’s a long way from being idiomatic Ruby or being useful as anything more than a demonstration. Since code kata** seem to be on the menu this month, I may go back and code this up some more different ways to explore the algorithm, the language, the environment and my own personal inadequacies. For one thing, I’m curious to see how I would test-drive the development of the algorithm.
Anyway, FWIW, this is what I have so far…
Alphabet = ('a'..'z').to_a
NWORDS = begin
words = Hash.new(0)
File.read('big.txt').downcase.scan(/[a-z]+/).each { |w| words[w] += 1 }
words
end
def correct(word)
(known([word]) || known(edits1(word)) || known_edits2(word) || [word]).max{|a,b| NWORDS[a] <=> NWORDS[b]}
end
def edits1(word)
n = word.size
(0..n).map do |i|
a, b = [word[0, i], word[i-n, n-i]]
[ (a + b[1, n] unless b.empty?),
(a + b[1, 1] + b[0, 1] + b[2, n] unless b.size < 2),
(Alphabet.map { |c| a + c + b[1, n] } unless b.empty?),
Alphabet.map { |c| a + c + b } ]
end.flatten.uniq
end
def known_edits2(word)
edits1(word).map { |e1| edits1(e1).select { |e2| NWORDS.has_key?(e2) } if e1 }.flatten
end
def known(words)
list = words.select { |w| NWORDS.has_key?(w) }
list.size > 0 ? list : false
end
And yes, it does come up with the right correction for the misspelling in the title.
* Not Python’s fault – the project where I was using it ceased to be, and most of my work for the last year or so has been web-based, with a choice of Java- or Ruby/Rails frameworks to work with. I grasped the latter like a British MP grasps an expense claim.
** One of Uncle Bob’s favourite kata is prime factoring. There’s a Ruby distillation of such an algorithm at Ben Rady’s blog. I don’t know when I’ve been more tickled by an algorithm. I’d love to see the refactoring steps that got to it.
A Third Way: DNA?
(stackoverflow rep: 8417, Project Euler 87/261 complete
When we want Excel to talk to a compiled library, the list of options available to us is not long.
On the one hand, we can write a compiled library that references the Excel SDK (pre- or post-2007), setting its prefix to XLL for convenience, typically we’d grit our collective teeth and accomplish this with C or (cough, spit) C++. One big plus here is speed – we’re talking to Excel in its own language. Or something awfully close to it. Another plus, of course, is that this approach probably involves the minimum set of dependencies for non-enterprise distribution convenience. If you haven’t spotted the big minus then we should probably agree to disagree on the relative merits of programming languages.
On the other hand, we can write a library that exposes its functions through good ol’ COM, referencing it though the Tool…Add-ins dialog. That’s probably still the best-known way to integrate functionality developed in .NET, although it does involve paying a performance price as the COM interface is crossed and recrossed. This would be the place in the world most usually occupied by VSTO. Of course, you could also write your COM library in non-managed code: VB6 is easy (if you can find it) but slow, the choice from others depends on your capacity (or desire) for pain.
On the gripping hand, we have ExcelDNA, which for my money is one of the all-round cunningest things you’re likely to come across in quite a long time. (Unless you’ve already encountered its cunningness, of course, in which case you’re probably already nodding along in sage agreement). How so? How about being able to write your spiffy new functions in C# but without having to incur the needless ins and outs of the wasteful and superfluous COM middleman?
ExcelDNA provides a small XLL that can talk to managed code. There’s one attribute to set in order to make a function visible to Excel and one libary to import (in order to be able to add the attribute). Oh, and a little config file to tell the XLL what to load. That’s it. Actually, that’s the complicated version. In case you didn’t know, the CLR includes a compiler, allowing code to be created and compiled at run-time. The simplest way to talk to Excel from .NET via ExcelDNA is just to put your code directly into that little config file and let the XLL compile it at load time. OK, there’s a second or so’s overhead, but how simple – anything simpler would probably have to be involve 21st-Century language integration where VBA lives today.
By now you’re probably muttering something like “write code, fat bloke”, which is a little cruel, but a sentiment otherwise understandable. Now only the other day, Dick Kusleika posted his take on the everybody-has-one timeless RangeJoin() UDF topic. Here’s a rather simpler implementation:
<DnaLibrary Language="CS">
<![CDATA[
using ExcelDna.Integration;
using System.Collections.Generic;
public class MyFunctions
{
[ExcelFunction(Description="Joins cell values", Category="My ExcelDNA functions")]
public static object RangeJoin(object[,] cells)
{
List<string> list = new List<string>();
foreach (object o in cells)
list.Add(o.ToString());
return string.Join(",", list.ToArray());
}
}
]]>
</DnaLibrary>
To get it running, I saved it as “RangeJoin.dna”, then copied the ExcelDNA.xll into the same folder and renamed that to “RangeJoin.xll”. Because it isn’t currently anywhere on my path, I also put ExcelDna.Integration.dll in the folder. Then I double-clicked my new XLL file to start Excel and load the library. (Pictures below are from Excel 2002, but the code is tested up to 2007).
Not surprising...
There’s a tiny extra delay before the usual macro warning appears – the C# code’s being compiled, which is where compile-time failures are displayed. Then you’re up and running. The function shows up in the Insert Function dialog…
… and we can try calling it:
… with the following result:
I did notice the the =NA() output and the Euro amount came out different to what I’d have hoped. It rather looks like I’d have to dig a little deeper into the ExcelDna.Integration.dll and the ExcelReference object in particular in order to be able to access the equivalent of VBA’s Text property.
Still, not bad for a quick exercise, I’d say.
There are some alternative approaches to the dot-net-and-excel-without-going-anywhere-near-COM-add-ins topic:
For Free
Excel4Net is now free – it seems to work by implementing a single worksheet function that calls out to managed code;
The last time I looked at XLW it was focused on C++. That’s changed in the interim – C# and VB.NET are now options.
Possibly Paid-for Propositions
Len Holgate appears to be at beta test stage with JetXLL, which looks like it’ll be a commercial offering;
I also just discovered ExHale. In beta, appears current – can’t see what the terms are likely to be;
Definitely Demanding Dollars
Possibly the first implementation, ManagedXLL is (was?) a paid-for product that looks to have been similar to, but possibly broader in scope than ExcelDNA. It’s not clear whether the company is still active or the product is still available – a curious colleague recently tried to make enquiries and failed.
What Time Is It? Bah.
It’s a long time since I last wrote about Lotus Notes and the unlimited joy that is its, er, idiosyncratic interface, not least since IBM’s decision1 to host the client in Eclipse.
Too long, really – it’s such a rich source of oddness. For example, today I recevied an invitation to join some colleagues (located in Frankfurt) in a video conference. The heading in the message informed me that:
Which seemed a little odd, since a quick phone call earlier had seen some time on Wednesday morning identified as the preferred time. No matter, I opened the message to accept the invitation (it’s not clear why I couldn’t do that from the inbox/preview, but I can’t). Clicking “Accept” put the meeting into my calendar:
Whoops! Well, it was more in keeping with what I expected. To settle myself, I went for a coffee (you can tell Christmas is coming, btw: Starbucks are using the Red Cups). When I got back, Notes had been busy – the Inbox message now had this:
OK, it’s now accurate, but I’m not sure how I feel about a message being modified in any way after I’ve opened and read it.
Of course, we’re 0.5 of a release behind the current version, so maybe stuff like this has been fixed by now.
The BBC reports that, in an Australian Science magazine article, an Australian psychology expert “who has been studying emotions has found being grumpy makes us think more clearly”.
To which I can only say “hmph”.
1 I’d love to have been a fly on the wall at that meeting.
Taming The Beast
(stackoverflow rep: 7963, Project Euler 83/261 complete – slacker!)
It’s a little while back now (I’m a slow thinker) but Simon Murphy, developing on a topic found in a post by Dick Moffat, discussed some observations of Excel usage within organisations. He concluded that general user skill levels are largely unchanged but that the uses to which Excel is being put are becoming more complex and at the same time training quality is declining. As a result, it’s increasingly commonplace that such organisations are seeking to reduce or limit the degree to which user-written spreadsheets are used.
The way the future was
It’s a tricky one. We have this enabling technology that makes it possible for end-users to manipulate information in ways unheard-of before 1979 (the year Visicalc was launched). Since the introduction of the computer spreadsheet, the power and complexity (yes, ease of use too, but see below) of the products available has increased steadily, necessarily, if you think about it: if Multiplan, Lotus 1-2-3, Quattro Pro, Wingz, Excel et al didn’t offer something more then we’d still be using Visicalc.
Putting a spreadsheet program on every desk-top computer, as is commonplace nowadays, due to Microsoft’s bundling policy: most will need Word and Outlook, they’ll probably expect Powerpoint, God help us, so Excel is almost certainly going to come along for the ride. Once it’s there, all but the most dedicated non-fiddlers will find it, even if it’s only because they’re drawn in by their peers.
I’d say a not-untypical progression starts with using the electronic squared paper to make lists or tables, moving on to the occasional diagram and then one day there’s the discovery of arithmetic, at which point Pandora (which wouldn’t be a bad name for a spreadsheet program) has opened the box and we’re heading downhill on a slippery slope into the land of overused metaphors, not to mention end-user spreadsheets.
There, I’ve said it: “end-user spreadsheets”. Three words (or two, depending on how you count hyphens) to strike fear into the giblets of any spreadsheet professional. Why? Here’s a selection of risks to which an organisation may be exposed:
- the spreadsheet uses the wrong function(s): anyone know the difference between STDEV() and STDEVP(), for example? Which should you use and when? What happens when the wrong one is still not accurate enough? What other areas might be dangerous? How about the entire “Financial” category?
- dynamically-growing data ranges and fixed-size references: not all data is included in calculations
- the user spends more time playing with his spreadsheet creation than he1 should – losing focus on the job for which s/he is actually being paid
- the spreadsheet becomes so useful that users other than the creator start to use it: the creator is now part of application support. Was that what you had in mind?
- because protecting spreadsheets can be tricky, and changing cell contents to perform “what-if” calculations is easy, formulae may be overwritten with new un-noticed and un-wanted constants.
- the spreadsheet acquires a noxious sludge of user-written VBA (shudder). You know, pages of global variables, no classes, 1500-line Subs, that sort of joy.
That’s the 5-minute, off-the-top-of-my-head list. This is the sort of stuff that has IT people waking up screaming in the middle of the night and contract support people (been there, done that) reassured of continuing employment for the remainder of their working lives.
How to address the problem without flushing both baby and bathwater?
First of all, if no-one is prepared to accept that there may be some real risk here, then then whole question is moot and you’ve already irrevocably lost a few minutes of your life getting this far. Sorry about that.
A review process needs to be put in place: what XLS files are being stored on the network? Can you see who is referencing them? Reading or writing? Are there patterns that might indicate that any of the problems above are being experienced?
Can you write a “spider” that looks around the user spreadsheet “corpus“ for frequent updates, evidence of VBA, all-round horribleness? Remember you’re not spying, you’re auditing.
As candidate files are identified, review them with the relevant user representatives to determine if the workbook is an signal for action to be taken. Such actions may include
- doing nothing – proactively chossing to do nothing is a perfectly reasonable option;
- adding the workbook to the portfolio of maintained and supported spreadsheet applications;
- setting up a project to address the business need in a more rigorous way;
- a bit of business process re-engineering to address any procedural deficiency that may have driven the need a an ad hoc spreadsheet.
Again, that’s the five-minute list. What other courses of action might one take to mitigate the risks of the enthusiastic amateur running wild with a spreadsheet program in one’s business?
1 Let’s face it, folks, it’s usually a “he”
Shippity-doo-dah
(stackoverflow rep: 7576, Project Euler 83/257 complete)
In my band days we called it "Gaffer"
Reading Joel’s1 Duct-Tape Programmer article this morning (in the interests of full disclosure I should admit without additional prevarication that I have a large roll of “Duck” tape in the second drawer of my desk as I type) one sentence smacked me metaphorically between the eyes:
“Shipping is a feature”
I was transported back a couple of decades to the time when the bank for whom I was then working discovered that it was building not one but two settlement systems (the things that ensure that what traders agree should happen actually does) in two locations: London and Zurich. In London we were targeting our DEC VAX/Oracle platform, while the Swiss were designing with their local Tandem Non-Stop installation. And we’d both have gotten away with it if it hadn’t been for that meddling CEO…
It was decreed that The Wise Men (external auditors) be appointed to review the two projects and pronounce which should live and which should consign its members to the dole queue.
The Wise Ones duly decamped to Zurich to spend a few weeks working through the cabinets of meticulously-detailed standards-compliant design documentation that had been lovingly crafted over the past several months, with coding about to start. Then they came to see us. It didn’t look so good.
dried-up and crusty now...
What documentation we had was months old (from a previous, aborted start of the waterfall) and coated in Tipp-Ex. Remember the white error-correction fluid we used all the time back in the 20th Century? When we still wrote “memos”? After a week of vagueness and frustration a set of presentations were scheduled for the Friday, at which we proposed to try to fill in the gaps.
Ing-er-land!
London won.
Yay us, but how? On most objective measurements we were deficient when compared with our continental rivals, even we agreed on that. But on that Friday afternoon, I got to stand up to summarise the differences, positive and negative between the two projects, as seen by the London team. I think what may have swung it was the part where I got to say “our system has been settling trades since 3 o’clock this morning”.
In about nine months, one team had done everything by the Book (don’t know the title, but I bet it had “Structured” in it) and had reached the point where they had, well, a book. Lots of books, in fact – they’d worked really hard. In the same time, we built a system and even better, shipped it. I don’t think anyone had written any Agile books by then – even if they had, we hadn’t read them.
Our team hadn’t done an awful job by any means, you understand: there’d been a few weeks of up-front requirement-gathering/scoping. We had a massive data model that we Tipp-Exed down to the minimum needed. We had an outline architecture that, through luck or judgement, proved to be appropriate. Probably best of all, though, we sat with our users while we built their system. Better, as we built different features we moved around so we were always within speaking distance of our domain expert (I don’t think we’d done the whole “domain” thing then – we just called them “users”). So we seldom got very far off track while stuff got built, and we were, with hindsight, feature-driven and relatively lowly-coupled/highly cohesive at the component level, all Good Things. Mostly written in COBOL, too.
Looking back, we were lucky: we didn’t manage to repeat the magic and fell back into time and cost overruns with the next couple of large projects. At least we were still being paid, unlike our erstwhile colleagues in Switzerland.
1 I call him by his first name because we share so much; we’re only a few slots apart on page 13 of StackOverflow as I write this. Page-mates, don’t you know.
The Hard Way
(stackoverflow rep: 7284, Project Euler 83/252 complete)
My main work PC was upgraded to IE7 yesterday. That’s one less IE6-infected machine to worry about. Unrelated to that (I suppose) is that the Aventail VPN product that I have to use each day decided it wanted to upgrade. I’m still trying to figure out how to make that work on IE7 but fortunately I also have an older machine that seems to have been immune to the upgrade, so I switched to that.
After some back-and-forth, I saw the happy news that this was happening:
All going according to plan?
While this was cogitating, a message popped up, partially obscured by the progress dialog. So I moved it. The dialog, that is, not the message. And I saw this:
Dude, where's my progress bar?
How confused must the developer of this part of the installer have been to have built the progress bar as an entirely separate window? And how much more difficult must it have been to do it that way? I amused myself dragging the main dialog all over my desktop while the progress bar stayed resolutely where it was until the install completed.
Nothing Like A Name
(stackoverflow rep: 6622, Project Euler 82/252 complete)
I was suddenly plunged head-first back into the world of Excel/VBA this week. After a year or so of mostly Ruby coding, I was struck by how, well, wordy VBA can be by comparison. There’s a definite lower level of expressiveness, too, although that can be mitigated to some extent with extra (mostly mental) effort. While I was rapidly back up to speed and having a good time refactoring to make room for some new functions, I couldn’t help feeling that I’d moved on and was practising what had become a legacy skill. That has happened before: I left COBOL behind (without a second glance, it must be said) in 1990 and have not written a line in it since.
Anyway, I was thinking about named ranges in excel, and how they aren’t. Named ranges, I mean. Well they are, but they’re not. A name can refer to a range, but it’s really referring to a formula that references a range.
And by formula, I mean anything (pretty much) that can be input into a cell after an equals sign. The result need not be a range. Want to define a named constant, Faraday’s Constant (96485.3399) say? Just define a name with “=96485.3399” as the “Refers to” value.
In older Excels, you’d get this:
Named constant - in a Name
I’m a huge fan of using names to define OFFSET range for charting:
SwimDateCount, in turn, is =COUNT(SwimStats!$A$2:$A$500)
The excellent Professional Excel Development (I have the older edition; I’m sure the new one will be just as good when I finally start working with 2007/2010 in anger) covers this, I’m sure. I’d check, but it’s been borrowed by a colleague. Because it’s that good.
The only slightly less excellent Excel Hacks definitely does cover it. (Also borrowed, but I managed to find it). Hack #52 in my copy, if you’re interested.
A Long Time Dead
(stackoverflow rep: 6602, Project Euler 78/252 complete)
I realised just now that Visual Basic1 went from 1.0 to 6.02 in about 7 years and stopped progressing 11 years ago. Even so, if I needed to do some COM automation work in an EXE, it’s still the tool I’d reach for, assuming I could locate my old Visual Studio 98 CD-ROMS, that is.
So by now VB’s pretty much been end-of-life longer than it’s been, er, life. Golly. It really is time Microsoft came up with a viable alternative. What I can figure out about VSTA doesn’t fill me with excitement, that’s for sure. VSTO, for the power-user at least (emphasis on “user”) still looks even worse. Neither offers, as far as I can see, any route for legacy VBA code, which is, I suppose, why VBA is still expected to be around for the foreseeable future.
Still, for the little it’s probably worth, we now have Make Office Better, within which we can rant about such importances. The buzz following its appearance seems to suggest that Microsoft still have some way to go. I am looking forward to sparklines in Excel 2010 though…
Mmmmm, sparklines...
1 (the “classic” one, not the impossibly confusing – to a still-active VBA user)
2 did we ever get a point release? I can’t remember any, although some of the ActiveX stuff did get service-packed.
