As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running.

With Excel 97 to 2003, this was simple: hold the shift key when you click “Enable macros”.

With Excel 2007 and 2010 it is less obvious how to do this, holding shift whilst clicking the enable button does not help. In the article mentioned below I have detailed out the exact steps required to prevent these macros from running under all possible circumstances.

Link: Preventing auto_open and Workbook_Open events from running

Enjoy the read and let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

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

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

Hi Everyone!

Today I finally found time to actually post a brand new article on my website.

Wouldn’t it be useful to be able to edit the contents of an Office 2007 OpenXML file from within VBA? Well, now you can, using the sample code and explanation in this article.

Enjoy!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Hi all,

The word is out!

We now have an official name for Office 14 and some sort of a date too.

Regards,

Jan Karel Pieterse

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

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

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