64 bit Excel and PtrSafe

Installing my Office 365 subscription on a new computer I discovered it had decided (without asking) to upgrade itself to the 64 bit version, which resulted in some of my spreadsheets not working on the new system.  The main problem is that where VBA code calls an external dll or xll file with a Declare statement, the Declare must be followed by PtrSafe for 64 bit Excel.

A comprehensive article dealing with this problem can be found at: Declaring API functions in 64 bit Office.

The following article deals with one simple example, how to deal with calls to the microtimer function, which is the main offender amongst my spreadsheets.

Revised code from the RC Design Functions spreadsheet is shown below:

#If VBA7 Then
  Private Declare PtrSafe Function getFrequency Lib "kernel32" _
  Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
  Private Declare PtrSafe Function getTickCount Lib "kernel32" _
  Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
  Private Declare Function getFrequency Lib "kernel32" _
  Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
  Private Declare Function getTickCount Lib "kernel32" _
  Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Note that:

  • “PtrSafe” must be inserted for 64 bit Excel, and will work with recent 32 bit versions (those using VBA7), but will raise an error with earlier versions of VBA.
  • With 64 bit Excel the VBA editor will show the lines without PtrSafe in red, and may raise an error during editing, but the code will run without problems with all VBA versions.

The updated version of the spreadsheet can be downloaded from: RC Design Functions8.zip

I will be progressively updating my spreadsheets as I use them, but please let me know if you are using 64 bit Excel and find a spreadsheet that has problems.

 

This entry was posted in Excel, VBA and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.