JWalk PUP lives again!

rlv01

Well-known Member
Joined
May 16, 2017
Messages
863
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,811
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:
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
863
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

VBA Code:
'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
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,811
'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:
VBA Code:
#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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,898
Messages
5,655,858
Members
418,248
Latest member
JinxedCaspa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top