How to fix a VBA “type mismatch” error after switching to 64-bit Excel

by

I updated the dll calls to use LongPtr everywhere instead of Long.

You should have not done that.

By adding PtrSafe to a function declaration, you promise to the compiler that you have put LongPtr in all places where it needs to be, and nowhere else.

LongPtr is a pointer-sized integer. It must be used for things that have the same size as a pointer.

To learn which Windows API types should be described as LongPtr, you must look at the original function signature, consult https://learn.microsoft.com/en-us/windows/win32/winprog/windows-data-types, track the used data types through all the typedefs down to the basic types, and use LongPtr for those that are pointers to things.

For the functions that you have shown, that would be

#If VBA7 Then
    Private Declare PtrSafe Function CreateDC Lib "gdi32.dll" Alias "CreateDCA" (ByVal lpDriverName As String, ByVal lpDeviceName As String, ByVal lpOutput As String, ByVal lpInitData As LongPtr) As LongPtr
    Private Declare PtrSafe Function CreateCompatibleBitmap Lib "gdi32.dll" (ByVal hdc As LongPtr, ByVal nWidth As Long, ByVal nHeight As Long) As LongPtr
    Private Declare PtrSafe Function DeleteDC Lib "gdi32.dll" (ByVal hdc As LongPtr) As Long
#Else
    Private Declare Function CreateDC Lib "gdi32.dll" Alias "CreateDCA" (ByVal lpDriverName As String, ByVal lpDeviceName As String, ByVal lpOutput As String, ByVal lpInitData As Long) As Long
    Private Declare Function CreateCompatibleBitmap Lib "gdi32.dll" (ByVal hdc As Long, ByVal nWidth As Long, ByVal nHeight As Long) As Long
    Private Declare Function DeleteDC Lib "gdi32.dll" (ByVal hdc As Long) As Long
#End If

When you declare variables to hold your LongPtr results, you need to use #If VBA7 too:

#If VBA7 Then
    Dim tempDC As LongPtr
    Dim tempBMP As LongPtr
#Else
    Dim tempDC As Long
    Dim tempBMP As Long
#End If

If you don’t have to support Office 2007 and older, you can ditch the #If VBA7s and only use the LongPtr branch.

More Related Contents:

  1. How can I call a macro which contains a loop but only run one iteration of that loop?
  2. VBA comma separated cells, needt last entry only
  3. How can I find last row that contains data in a specific column?
  4. Open Excel file for reading with VBA without display
  5. Wait for shell command to complete [duplicate]
  6. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  7. How to add a named sheet at the end of all Excel sheets?
  8. How to remove all non alphanumeric characters from a string except period and space in excel?
  9. Find last used cell in Excel VBA
  10. VBA general way for pulling data out of SAP [closed]
  11. Does Dir() make any guarantee on the order of files returned?
  12. Call a Sub with a String
  13. Assign on-click VBA function to a dynamically created button on Excel Userform
  14. Delete Row based on Search Key VBA
  15. How to add a button programmatically in VBA next to some sheet cell data?
  16. Using a wildcard to open an excel workbook
  17. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  18. ADODB Connection String for .csv
  19. Pivotfields multiple filter
  20. How to send mail when the .Send does not work?
  21. How to clear memory to prevent “out of memory error” in VBA?
  22. Extract Data from PDF and Add to Worksheet
  23. EXCEL VBA Check if entry is empty or not ‘space’
  24. Paste Excel Chart into Powerpoint using VBA
  25. running excel macro from another workbook
  26. Multiply Entire Range By Value?
  27. Change Name of ActiveX Command Button using VBA in Excel
  28. EXCEL VBA UserInterfaceOnly:= True not working
  29. Export multiple sheets to PDF simultaneously without using ActiveSheet or Select
  30. AutoFilter – Use of SpecialCells

Leave a Comment