Archive for the ‘Miscellany’ Category.

Happy birthday Excel!

September 30, 1985 Excel was launched and I therefore wish Excel a very happy birthday and many healthy years to come!

Debra Dalgleish over at the contextures blog has gathered a nice set of stories on how people first “met” Excel.

To celebrate this great event, I’m offering a 30 percent discount on my products for 30 days.
Redeem your discount on RefTreeAnalyser and The Excel File Remediation tool by entering this coupon code: EXCEL30

So have a piece of cake with your coffee today and have one of those “those were the days” moments.

Jan Karel Pieterse

Summer sale at

Hi there!

Now that summer has arrived I’ve planned a summer sale. From July 1st up to July 10th I offer a 25% discount on both products I sell:
The Excel File Remediation Utility
To get your discount, just go through the purchasing process and enter this coupon code to redeem your discount:

Regards and have a great summer!

Jan Karel Pieterse

The Amsterdam Excel Summit Last-minute discount

Hi everyone,

Our event is coming real soon now and we’re very much looking forward to it. We have outstanding speakers and excellent content, so everything is lined up to make this a superb Excel event.

To entice the undecisive Excel lovers to make up their minds and subscribe after all, we decided to make it even more attractive to attend.

As of March 27st, 2015 we offer a € 200 last-minute discount per attendee for both days and € 100 for one day!

Register now at and meet us on April 13th and 14th in Amsterdam.


Jan Karel Pieterse

The Amsterdam Excel Summit 2015

Hi Excel lovers!

Last year we had a terrific Excel event in Amsterdam in May. This year we’re in for a repeat!

I have just opened registration for what is going to be the place to be for anyone Excel-minded. We have two days full of excellent subjects. An impression:

  • Three in-depth Power Query sessions
  • Two sessions on improving your spreadsheet quality
  • Two sessions on charting, making your life easier and enabling you to build charts you didn’t even know you could
  • Two sessions on pivot tables and formulas
  • A session on how to build UDFs

So why don’t you book your flights and hotels and join us on April 13th and 14th for an unsurpassed Excel experience!


Jan Karel Pieterse

Recent Update of Office causes problems with ActiveX controls


Yesterday, I installed a host of updates, including some of Office.
As it happens, I tried to add an ActiveX control to a worksheet and received an error.
After some research I discovered the cause of the error to be two-fold:

1. The controls were updated by the update
2. Excel did not clean up after itself properly and left some temporary files behind.

The solution is to:
– Quit Excel
– Open Explorer
– Select C: drive
– Search for *.exd
– Remove all files found.

Hope this helps other people who might be suffering from the same problem.


Jan Karel Pieterse

#####UPDATE Dec 22, 2014#####
Microsoft has published a so-called Fixit to make resolving this matter easier:

Celebrating my MVP award: Discount offer

Hi everyone!

Every quarter Microsoft announces who are the lucky ones to receive their Most Valuable Professional Award. An MVP award lasts a year, so for a quarter of the MVPs, October 1st is an important day.

I got re-awarded!

And to celebrate that I am offering a 3 day 50 percent discount on my Formula auditing tool: RefTreeAnalyser

From October 8, 2014 to October 10, 2104 you receive 50 % off of the list price when you enter this coupon code: MVP2014

Head over to my website and download the tool, you can try it for free!

Jan Karel Pieterse

Friday Thoughts

I need this procedure automatically called from every entry point procedure I run:

Public Function PreventHavingToKillExcelAndLoseWork() As Boolean
    Dim lCountEOF As Long
    Dim lCountMoveNext As Long
    lCountEOF = CountInstanceOfStringInAllModules(".EOF")
    lCountMoveNext = CountInstanceOfStringInAllModule(".MoveNext")
    PreventHavingToKillExcelAndLoseWork = lCountEOF <= lCountMoveNext
End Function

When I refer to properties, I say “The x property of the y object”, as in “The Value property of the Range object”. I’m updating the DDoE style guide to reflect a change a recent editorial decision. From now on, I will refer to properties as “The y.x property”, as in “The Workbook.FullName property” or “The Collection.Add method”. Please make a note of it.

Chandoo is polling his readers to find their favorite Excel feature. There’s almost 50 comments as of this writing and a lot of good features mentioned. A couple of people, including Chandoo, named formulas as a top feature. You can’t say formulas are the best feature of Excel. That’s like saying wetness is the best feature of the Ocean. Or cheesiness is the best feature of the Moon. Formulas are Excel. They are Excel’s essence. Aren’t they?

For what it’s worth, I’ve been using the 2010 UI for a couple of months now (formerly mostly using VBA). The thing I’m most grateful for is autocomplete of Table references. I prefix all my Table name with tbl, so I can type =SUMPRODUCT((tbl {tab} [#A {tab} and half my formula is written for me. I should make a video of that, because typing it doesn’t do it justice. Or I’ll just go find one on Contextures since Debra has created every possible Excel video.

I don’t know if that qualifies as my favorite Excel feature, but I smile a little every time I use it.

Here’s a picture of my new grill. Summer is here.

For Whom the RSS Tolls

As you are no doubt aware, Google Reader is going away. The public outcry hasn’t brought it back, nor open sourced it, by now so that’s not going to happen. There are some options, but I decided that I wasn’t going to switch until May 15th. That will give the smart people an opportunity to vet the options and tell me what to do. I haven’t tried the options, obviously, so I can’t comment on them. Instead, I want to reflect on what I want from an RSS reader.

But first, a word about RSS. I’ve read and listened to a lot about Reader’s demise lately. A lot of people are characterizing RSS a a two-faced beast (most notably John Gruber, but I can’t remember if it was The Talk Show or some podcast on which he was a guest). One face is the user-interface like Google Reader. It’s how I read web pages. I almost never read a website that doesn’t have a feed. In fact, I read less than a handful of sites that don’t publish the full content in their feed.

The other face is this back-end plumbing, this architecture, this infrastructure that is RSS. How you read websites is a personal choice. But whether you pop over to DDoE to see what’s new or you check a feed reader, this site still publishes an xml file. You could write some software that parses that file. You could buy some software that does it. You could do something interesting like the Spreadsheet Page’s Excel Blog Headlines Page. The XML file is out there. Use it, don’t use it, but don’t get rid of it. It has value. Someone needs to build an interface that gets the masses interested in reading pages from a single source. Just don’t call it RSS and it will be fine.

What do I want from a reader? Here’s what I love about Google Reader:

  • Great syncing – Google updates quickly. Really quickly.
  • No duplicates – Google does a good job figuring out which posts I’ve read and which I haven’t. There have been a few glitches along the way but all-in-all it’s been solid.
  • Good rendering – For the most part, the feeds render the web pages really well. I hate the way it shows DDoE code, but other than that, it’s good.
  • Shortcut keys – Google respects the mouse-a-phobes and provides keyboard shortcuts for easy navigation. I can learn new shortcuts, but if they’re not there it’s a deal breaker.
  • Ubiquity – I like Reader on the web, in Chrome for iOS, and everywhere else I’ve used it.

Here’s what I don’t like:

  • Screen Real Estate – Nearly 1/3 of the screen is used up by non-value added bullshit. Some of that is Firefox, but everyone has to do their part.
  • No sorting – My folders are sorted alphabetically, which is the just about the worst way you could organize them. Let me assign a number to each folder that determines the order. Some feeds I read no matter what and some I read when I get the chance. I want the former at the top.

Every feed that I read falls into one of four categories.

News is the least important, the highest volume, the most skimmed (as opposed to read), and the least missed. I have some news feeds, but if they ever amassed too many unread entries, I have no problem blowing away large swaths. Some of my news feeds are:

  • AP Top Headlines
  • A couple Reuters feeds
  • Stack Overflow feeds
  • Netflix new releases

You see that they don’t have to be news in the traditional sense of the word. In fact, I should stop calling them that. Their most defining characteristic is that I don’t care if I miss something.

Content are feeds I read because they’re damn good. If I thought I missed something through one of these feeds, I would go visit that site to make sure. I’m going to read every entry posted to these feeds. That may be because I know the feed has short entries that take no time to read (web comics) or the content is just so good that it’s worth my effort to avoid missing any entries (kottke). Content feeds include:

  • Family memeber’s blogs (let’s call this content ‘highly relevant’ rather than ‘good’
  • Web comics like xkcd, Oatmeal, Savage Chickens, Dilbert, NIH, Scenes from a Multiverse
  • Signal vs. Noise
  • Street Smarts

Not only do I not want to miss a single post, I want to read these right away. When xkcd publishes, I don’t save that gem for later, I read it right now.

Some feeds I read out of obligation. I feel it’s my job to know what’s happening in the world of Excel, so I read a whole crap load of Excel blogs. Some are great and some aren’t. If it’s not a blog that solely exists to sell a product, I’m probably reading it whether I find it currently useful or not. I also read feeds about manufacturing and petroleum. I’m not going to miss a post on these either, but I don’t need to read them today. Mike’s Data Explorer post was awesome, but if I read it five days after he posted, it would still be awesome. I’m not going to list all the Excel blogs I read, you can read my OPML file if you’re so inclined.

Saves are either long reads or things that I wish I were interested in. The Harvard Business Review has some nice articles, but I save them for a rainy day. Same with Kahn’s Corner, Lowering the Bar, and a host of other sites. I may get to them someday or I may not. But I want them there.

My reader should have two panes. The left pane holds Content feeds. The ones I want to read right now. The right pane holds News feeds. It will be pretty full, but I’ll blow through them in no time. When I have no content feeds, the left pane turns to Obligaton feeds. The Saves feeds never show up unless I specifically ask. I can put any single post into Saves while still keeping the feed in another category.

I can set an expiration on any pane. I may say, for instance, that entries from News feeds go away if I don’t read them for five days. For me, personally, every other pane will be set at ‘never expire’, but others may want their Obligation entries to fall off after some time. The panes show all of the unread entries from the feeds assigned to them. If you want to see entries you’ve already read, you can search for them. Entries can be shown in chronological order or the reverse. Feeds can be rated 1 to 5 stars. A combination of age and rating determines what’s on top in a pane. The feeds inherit all these features from their pane, but can be overridden on a feed-by-feed basis. For example, I read almost everything chronologically so that’s how my news pane will be ordered. A notable exception is Stack Overflow, where I prefer to see the newest (least likely to have been answered) first and I will set that feed to be reverse chronological. All other feeds in the News pane will inherit the chronological property from the pane.

So there you have it. Give me two big panes, keyboard shortcuts, effortless syncing, and a semi-consistent experience across web and iOS and I’ll be fine. Oh, one more thing: I’ll pay $100 per year for the service.