How can I declare a PtrSafe Sub in VBA? Windows 7, Excel 2016, 64-bit

2
votes

I am trying to upgrade my large number VBA application from Long datatype to LongLong or LongPtr to be able to handle more digits. But I can't figure out how to call this PtrSafe Sub... Some assistance would be very helpful.

I tried a simple example Sub to isolate the problem. But don't know which Lib I should call and on top of that: should this Sub be Private or Public?

Public Declare PtrSafe Sub Example Lib "??????" (ByVal x, y, z As LongPtr)

Dim x, y, z As LongPtr

x = 123456789
y = 123456789
MsgBox z = x * y

End Sub

Could anyone explain to me step-by-step how to call a Sub that is PtrSafe?

2
excelvbalong-long

2 Answers

3
votes

PtrSafe

Use the PtrSafe just to enable 32bit API calls on 64bit systems like this:

Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hDC As LongPtr) As Long

Public Sub TestScreenResolution()
    Debug.Print ScreenResolution
End Sub
Private Function ScreenResolution() As Double
     Dim hDC As LongPtr
     hDC = GetDC(0)
     ScreenResolution = GetDeviceCaps(hDC, 88)
     ReleaseDC 0, hDC
End Function

Conditional Compilation

You may use both via conditional compilation

#If Win64 Then
    Private Declare PtrSafe Function MakeSureDirectoryPathExists _
        Lib "imagehlp.dll" (ByVal DirPath As String) As Boolean
#Else
    Private Declare Function MakeSureDirectoryPathExists Lib _
        "imagehlp.dll" (ByVal DirPath As String) As Boolean
#End If

Your own Sub or Function with 64bit variables

These can be declared like this: 

Public Sub TestMySub()
    Call MySub(123456789, 123456789)
End Sub

Private Sub MySub(ByVal x As LongLong, ByVal y As LongPtr)
    Dim z As LongLong
    z = x * y
    MsgBox z
End Sub

I prefer Private as long as possible and Public only if "external" access is needed.

Data types LongLong vs. LongPtr vs. Decimal

On 64bit LongLong and LongPtr are both a "LongLong Integer" with 8 Byte:
-9.223.372.036.854.775.808 to 9.223.372.036.854.775.807

But be aware: But if you use LongLong, it will ONLY work on 64bit, where LongPtr on 32bit would be handled simply as a Long in 4 bytes, which results in
-2.147.483.648 to 2.147.483.647

So if you really need a high value on both systems and Long is not enough, consider to use a Double (8 Bytes, including rounding effects!) or Decimal (14 Bytes, has to be declared as Variant first):

Private Sub DataTypeDecimal()
    ' Decimal only via declaration as Variant and type cast as Decimal
    Dim d As Variant
    d = CDec("79.228.162.514.264.337.593.543.950.335")
    Debug.Print d
    d = CDec("-79.228.162.514.264.337.593.543.950.335")
    Debug.Print d
End Sub
-1
votes

The example I gave you is working allright, thank you for this :-).

The real application however now says "type mismatch" at the moment I try to fill Arr(z) with 'True'-Booleans (excerpt) (it worked fine with Long datatype):

Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hDC As LongPtr) As Long

Public Sub TestScreenResolution()
    Debug.Print ScreenResolution
End Sub
Private Function ScreenResolution() As Double
     Dim hDC As Long
     hDC = GetDC(0)
     ScreenResolution = GetDeviceCaps(hDC, 88)
     ReleaseDC 0, hDC
End Function

Public Sub TestMySub()
    Call MySub(999999999)
End Sub
Private Sub MySub(ByVal x As LongLong)

Dim z As LongLong
Dim Max, Min As LongLong

Max = x * x
Min = (x - 2) * (x - 2)

Dim Arr() As Boolean 'Default Boolean type is False
ReDim Arr(Min To Max)

For z = Max To Min Step -2
    Arr(z) = True
Next z

End Sub