excelvbalong-long

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


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?


Solution

  • 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