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:

Hurray!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

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?

Thanks!
Jan Karel Pieterse
www.jkp-ads.com

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:

=IF(PicsOn=1,Sheet2!$A$1:$C$5,“”)

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.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

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

Regards,
Jan Karel Pieterse
www.jkp-ads.com

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

To:

    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.

See:

Declaring API functions in 64 bit Office

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

Regards,

Jan Karel Pieterse
www.jkp-ads.com

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:

http://support.microsoft.com/kb/973475

and

http://support.microsoft.com/kb/973593

There seem to be two hotfixes to this problem:

KB973475 Excel 2003 hotfix package :

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=978908&kbln=en-us

KB973593 Excel 2007 hotfix package :

http://support.microsoft.com/default.aspx?scid=kb;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:

http://www.jkp-ads.com/Articles/StartupProblems.asp

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Using controls on worksheets

Hi all,

I decided I need a bit more content on my site for the average to intermediate Excel users:

Using controls on worksheets

Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your sheet to offer choices to the user. Moreover, you can add scroll bars and spinners to quickly control values in cells. These elements are called controls because they enable your user to control the content of your model. This article shows how to put controls to use in your model.

Enjoy the read and let me know what you think!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Preventing auto_open and Workbook_Open events from running

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