The Mystery of the Disappearing Range Name

Hi all,

As some of you may be aware, Excel 2007 has been exhibiting an obscure but very annoying bug. On some rare occasions, Range Names may disappear from your workbook after saving it.
I have never been able to detect any pattern in this behaviour, it just happens on and off.

I’m happy to say Microsoft created a hotfix for this bug.

###EDIT 2009-07-06 15:10 GMT: fixed a small mistake, you need SP1, NOT SP2###
Make sure you have Office 2007 updated to at least SP1; visit Microsoft Update to check if you need it.
After that, visit the hotfix page and request your hotfix download.

For more information about range names, read my series of articles on them here.

And if you use range names frequently (or plan to in future) do yourself a favour and download Name Manager.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Adding RibbonX code to an Office OpenXML file using VBA

My previous post went a bit unnoticed, but I bet this one might interest some of the Office developers…

In that post, I showed how to access and modify existing parts of an Office OpenXML package. This opens up the path for us to add ribbon customisation code to an Office file. For this to happen, a couple of modifications were needed to the code in the class module I showed earlier. Fellow Excel MVP Ken Puls was kind enough to make some modifications to the class module, which I refined a little. The results are on this page

Enjoy!
Regards,
Jan Karel Pieterse

My own Euler problem

Hi everyone,

With all these Euler posts I thought, why not post my own?
Suppose the following:
I have a products table set up like this:

CodeProdName Price
0001Product1 556.68
0002Product2977.41
0003Product3350.62
0004Product4509.16
0005Product5748.4
0006Product6802.96

(list goes on to as much as 5000 products)
And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two columns, which may be unsorted).
Currently I load the list into a variant and add the items one at the time into a three column listbox.
As the user enters a search string, I have an If statement inside the loop to detect if an item needs to be added to the listbox or not. This (as you can imagine) is a slow process.
My task for you: how would you solve this problem so the filtering is as fast as possible?

Regards,

Jan Karel pieterse
www.jkp-ads.com

Styles in Excel

Hi all,

I’ve just published a new article on my website. Here is the introduction:

“This article explains how you can use styles to ease maintenance of your spreadsheet models.

Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.

By consistently using cell styles (instead of changing parts of the cell’s formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.

I therefore consider Styles as being underused, underestimated and under exposed.”

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

XML and Excel

Hi everyone,

I have had a Dutch article on XML and Excel on my site for quite some time now, but never got round to translating the thing into English.

Well, yesterday I found a bit of time during a long train-ride, so here it is:

XML And Excel

From the intro:

Microsoft Office 2003 Professional was the first Office version that took the XML standard seriously. The XML standard has been devised to ease the markup of data (especially on the web). A well known example of the use of XML are RSS feeds with which one can gather news from web pages. In reality, these so-called RSS-feeds are nothing less than XML files. This article introduces XML and shows some things that can be done with it (specifically in Excel).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

www.jkp-ads.com Anniversary and Website Update

Hi everyone,

Already 5 years have passed since I founded my company. And I must say those years passed in the blink of an eye.

I’ve never enjoyed my work as much as in these past years. Even if at times things were hectic and I worked crazy hours. I’ll never start working for a boss again (if I can help it)!

My conclusion after my first half-a-decade: If you think you’re good at something (even if it is outside of your current field of work), strongly consider becoming self-employed. It’ll take you a year or two to get up and running, but if you’re an independent kind of person you’ll love the “being in control” feeling it will give you. Very rewarding.

I felt a 5 year anniversary needed more than just posting about it here. My website hadn’t been redone in the same amount of time (except maybe for some bells ‘n whistles) and I had already thought I might give this new-fangled Microsoft Expression Web (EW) a whirl.

So I downloaded and installed the thing and imported my current Frontpage web into EW. Sheesh, what a huge difference in UI. EW seems much more aimed at the web developer than at the casual let-me-build-me-a-site-for-my-club kind of user.

Luckily I bumped into a course which seemed tailored at what I was about to do:
Migrating from FrontPage to Expression Web
I subscribed, followed the course and here is the result:

www.jkp-ads.com

Kudos to Tina Clarke and Patricia Geary (both Frontpage MVP’s) who were the course writers and -instructors. Excellent job.

So, have a look at my new site and let me know what you think.

Regards,

Jan Karel Pieterse
JKP Application Development Services