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

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