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
- “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.