JWalk PUP lives again!

[rlv01]

I've had this set of tools for a very long time and have come to rely on certain functions, and have carried PUP along with me over the years as I've upgraded newer versions of Excel. So back in March when I upgraded to the current O365 Pro Plus version of Office I was disappointed to find that PUP v7 would not load for the 64bit version of Excel. I finally remembered that I had purchased the source code license eons ago but had never really looked at it. Today I did some poking around and it turns out that it is just a matter of adding PtrSafe to a couple of declare statements and it is installed and functional again.

This post is just to alert anyone who may also have a legally purchased source code license that PUP v7.x can be restored. Please don't ask me where you can get a copy of the source code or if I will sell/give you a copy. Not gonna happen.

 

[John_w]

What were the API functions you needed to change? As well as adding the PtrSafe keyword, it's likely you need to change some Long function arguments to LongPtr, depending on the function, and the same in Type statements (data structures). This page is useful:

Declaring API functions for 64 bit Office (and Mac Office)

JKP Application Development Services, Microsoft Office Application Development

jkp-ads.com

 

[rlv01]

Here are the 3 changes. The locations easily findable to anyone who has the code. I hear what you are saying about LongPtr, but it's been my experience that unless the API call actually returns an 8 byte LongLong then adding PtrSafe is usually enough. If this had been for a workbook I was going to distribute to others instead of just doctoring my copy of PUP for my own use (which I did not realize how much I relied on for a lot of little functions, until my Office upgrade) I might have done more along those lines.

'Declarations
#If Win64 Then
    '  Code is running in 64-bit version of Microsoft Office
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    '  Code is running in 32-bit version of Microsoft Office
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If


'Declarations
#If Win64 Then
    '  Code is running in 64-bit version of Microsoft Office
     Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
#Else
    '  Code is running in 32-bit version of Microsoft Office
     Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
#End If


'Declarations
#If Win64 Then
    '  Code is running in 64-bit version of Microsoft Office
    Private Declare PtrSafe Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As Long
#Else
    '  Code is running in 32-bit version of Microsoft Office
    Private Declare Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As Long
#End If

 

[John_w]

'Handle' arguments and return values should be LongPtr. Using the VBA7 compiler constant will allow the code to compile on 32-bit and 64-bit Office. You only need Win64 for functions which are only available in 64-bit Office, e.g. SetWindowLongPtr.

I'd declare them like this:

#If VBA7 Then
    'New VBA version 7 compiler, therefore >= Office 2010
    'PtrSafe means function works in 32-bit and 64-bit Office
    'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
    Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As Long) As Long
    Private Declare PtrSafe Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As LongPtr, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As LongPtr
#Else
    'Old VBA version 6 or earlier compiler, therefore <= Office 2007
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
    Private Declare Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As Long
#End If

 

[ZVI]

There were at least 7 API declarations in PUP v7.
And the PUP v7.2 code supported both 32- and 64-bit versions of Excel with modified API declarations as well as code modifications.

 

[h2ouup2]

If you have the source code, you will need to add the following which will allow it to run in both 32 and 64 Excel.
__________________________________________________________________________________________________________________________



#If VBA7 And Win64 Then
Private Declare PtrSafe Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As LongPtr
Private Declare PtrSafe Function RegOpenKeyA Lib "ADVAPI32.DLL" (ByVal hKey As LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long
Private Declare PtrSafe Function RegCloseKey Lib "ADVAPI32.DLL" (ByVal hKey As LongPtr) As Long
Private Declare PtrSafe Function RegSetValueExA Lib "ADVAPI32.DLL" (ByVal hKey As LongPtr, ByVal sValueName As String, ByVal dwReserved As Long, ByVal dwType As Long, ByVal sValue As String, ByVal dwSize As Long) As Long
Private Declare PtrSafe Function RegCreateKeyA Lib "ADVAPI32.DLL" (ByVal hKey As LongPtr, ByVal sSubKey As String, ByRef hkeyResult As LongPtr) As Long
Private Declare PtrSafe Function RegQueryValueExA Lib "ADVAPI32.DLL" (ByVal hKey As LongPtr, ByVal sValueName As String, ByVal dwReserved As Long, ByRef lValueType As Long, ByVal sValue As String, ByRef lResultLen As Long) As Long
#Else
Private Declare Function HtmlHelp Lib "HHCtrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Long) As Long
Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" (ByVal hKey As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long
Declare Function RegCloseKey Lib "ADVAPI32.DLL" (ByVal hKey As Long) As Long
Declare Function RegSetValueExA Lib "ADVAPI32.DLL" (ByVal hKey As Long, ByVal sValueName As String, ByVal dwReserved As Long, ByVal dwType As Long, ByVal sValue As String, ByVal dwSize As Long) As Long
Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" (ByVal hKey As Long, ByVal sSubKey As String, ByRef hkeyResult As Long) As Long
Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" (ByVal hKey As Long, ByVal sValueName As String, ByVal dwReserved As Long, ByRef lValueType As Long, ByVal sValue As String, ByRef lResultLen As Long) As Long
#End If
Public Const PUPNAME As String = "Power Utility Pak"
Public Const SCMENUFILE As String = "shortcut menus.pup7"
Public LastUtility As String
Public MyRibbon As IRibbonUI