Name Manager Updated

Hi All,

I’ve just updated Charles Williams and my Name Manager utility.
New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name.

I’m now off to remove one feature request from the list.

Any wishes? For Name Manager that is :-)


Jan Karel Pieterse

A Hundred Thousand Name Managers!

Hi folks,

Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites.

Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that!

Anyway, as I was looking at my web stats today I discovered a nice feat: We’ve just passed the 100,000 download count on the tool (this excludes the downloads from Charles’ site, so we can safely assume the true number is at least 50% more than that). Time for a celebration:



Jan Karel Pieterse

Preventing Workbook_Open events from running (2)

In Preventing auto_open and Workbook_Open events from running I descibed how to do exactly that. Well, it appears that the methods I gave do not always work with Excel 2007.
A visitor of my website complained he could not prevent his Auto_Open macro from running and during a BeamYourScreen session I took over his desktop and confirmed he was right (of course he was).
The situation where we couldn’t prevent an Excel automacro from running was like this:

– You have set up a trusted folder
– The folder is on a network share
– You’re using the UNC path to that folder.

Since I don’t have a network, I can’t easily test this.

Can anyone confirm whether the shift key trick works in these cases:

– Trusted folder on network (but with an assigned drive letter)

Same as above, but for Office 2010?

Jan Karel Pieterse

Performance of linked pictures

I frequently use the camera tool to create pictures linked to cell ranges.
For example to be able to have different column widths beneath each other on one sheet.

They have one major drawback however: they can slowdown VBA performance (when updating cells) enormously (update time may go up from .2 secs to as much as 8 seconds for the same code).

My workaround:

Each camera tool object uses a defined name, defined such as:


Then I use these two tiny subs to turn the picture updating on and off:

Sub TurnOffPictures()
    ThisWorkbook.Names(“PicsOn”).RefersTo = “0”
End Sub

Sub TurnOnPictures()
    ThisWorkbook.Names(“PicsOn”).RefersTo = “1”
End Sub

Works a treat.


Jan Karel Pieterse

Importing text files in an Excel sheet

Hi there!

Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time.

Opening the file in Excel directly gave unwanted results, similar to this:
Importing CSV files into Excel can be cumbersome...

I have added an article to my website which details out how to set things up properly to save you some work:

Importing text files in an Excel sheet

Jan Karel Pieterse

Declaring API functions in 64 bit Office

Hi everyone,

The release date of Office 2010 is closing in and with this new version we’ll have a new programming challenge. Office 2010 comes in a 32 bit and a 64 bit version.
Especially API function declarations need to be adjusted for the 64 bit environment.
We’ll have to change a declaration like this one:

    Private Declare Function GetWindowLongptr Lib “USER32” Alias _
“GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long) As Long


    Private Declare PtrSafe Function GetWindowLongptr Lib “USER32” Alias _
“GetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr

So how do we know whether a Long needs to be changed to LongPtr, or to LongLong, or maybe even can be left unchanged? I decided it would be useful to gather a list of declarations on my website so we have a one-stop place where we can find the proper syntax for these things.


Declaring API functions in 64 bit Office

If you have some additional API functions I could include, please let me know!


Jan Karel Pieterse

Problems related to KB973475 and KB973593

Hi All,

Many users (including customers of mine) have experienced trouble with two recent security updates by Microsoft, see:


There seem to be two hotfixes to this problem:

KB973475 Excel 2003 hotfix package :

KB973593 Excel 2007 hotfix package :;EN-US;978522

If you experience problems with your Excel, check if you have this update and if so, download the hotfix.
For other problems related to starting and stopping Excel, see:


Jan Karel Pieterse
Excel MVP