Troubled with VBA version difference: version 6 and version 7

Question

I'm new to VBA and use excel 2010 64bit VBA v6.0 compatible. I pasted the code, trying to download files through VBA.

Option Explicit
'Tutorial link: https://youtu.be/H4-w6ULc_qs
#If VBA7 Then
   Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
     "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal _
       szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As LongPtr
#Else
   Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
     "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
       szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub download_file()
'-----------------------------
'Thanks for downloading the code.
'Please visit our channel for a quick explainer on how to use this code.
'Feel free to update the code as per your need and also share with your friends.
'Download free codes from http://vbaa2z.blogspot.com
'Support our channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

Dim downloadStatus As Variant
Dim url As String
Dim destinationFile_local As String

url = [D3]
destinationFile_local = "C:\Users\myUserName\Downloads\" & fileName([D3])

downloadStatus = URLDownloadToFile(0, url, destinationFile_local, 0, 0)

If downloadStatus = 0 Then
    MsgBox "Downloaded Succcessfully!"
    Else
    MsgBox "Download failed"
End If

End Sub

Function fileName(file_fullname) As String

    fileName = Mid(file_fullname, InStrRev(file_fullname, "/") + 1)

End Function

However, a pop-up window says it can only run on 64-bit systems as follow:

Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.

My questions are:

I do use window and office 64-bit system. Why the window keeps popping up?
Is there any way to solve this problem?

Thanks in advance.

Have a look at the documentation [here](https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691831(v=office.14)?redirectedfrom=MSDN) resp. [here](/https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/gg264421(v=office.14)?redirectedfrom=MSDN) and follow the hint the compiler gave to you _Please review and update Declare statements and then mark them with the PtrSafe attribute._ — Storax, Jul 10 '20 at 13:16
Second fixed [link](https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/gg264421(v=office.14)?redirectedfrom=MSDN) — Storax, Jul 10 '20 at 13:21
`a pop-up window says it can only run on 64-bit systems` - no, the popup says you have not updated your code to be compatible with 64-bit systems, so it cannot run there. Which you have not, because you haven't added `PtrSafe` and have not correctly change the parameter types to `LongPtr` *only where needed*. — GSerg, Jul 10 '20 at 13:52
Hi @GSerg, now I understand the meaning of the pop-up window. Thank you for the explaination. — Justin Smith, Jul 11 '20 at 00:29
Hi @Storax, I did refer to the MS document as provided by you before I asked the question here. The only problem is that I am not good at English and choose the language to Chinese but it doesn't provide a Chinese version. That's why I am here to see if someone can explain it in a more simple way. — Justin Smith, Jul 11 '20 at 00:47

JAlex · Accepted Answer · 2020-07-10T15:30:24.487

1

As the error tells you, add the PtrSafe keyword to the VBA7 branch

#If VBA7 Then
   Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
     "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal _
       szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long
#Else
   Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
     "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
       szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

You need to add this keyword anywhere you are using LongPtr, or LongLong.

Here is the MS Documentation on PtrSafe

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ptrsafe-keyword

Adding the PtrSafe keyword to a Declare statement only signifies that the Declare statement explicitly targets 64-bits. All data types within the statement that need to store 64-bits (including return values and parameters) must still be modified to hold 64-bit quantities by using either LongLong for 64-bit integrals or LongPtr for pointers and handles.

edited Jul 10 '20 at 15:30

answered Jul 10 '20 at 13:30

JAlex

1,486
8
19

@GSerg - yeah I am not familiar with this API call, but I have updated the answer with your suggestion. – JAlex Jul 10 '20 at 13:53
*You need to add this keyword anywhere you are using `LongPtr`* - rather, you need to add this keyword whenever you support VBA7 (which should be always these days). If a function signature happens to not have any `LongPtr`s, it will still require a `PtrSafe`. – GSerg Jul 10 '20 at 13:54
@GSerg - I am reading MS Docs, "Declare statements with the PtrSafe keyword is the recommended syntax. Declare statements that include PtrSafe work correctly in the VBA7 development environment on both 32-bit and 64-bit platforms only after all data types in the Declare statement (parameters and return values) that need to store 64-bit quantities are updated to use LongLong for 64-bit integrals or LongPtr for pointers and handles." – JAlex Jul 10 '20 at 15:29
Correct. *only after all data types in the Declare statement **that need to store 64-bit quantities** are updated*. If the number of such data types is zero, the statement is still valid. All the parameters were updated, all zero of them, so we do apply `PtrSafe`. If there are no `LongPtr` parameters and you don't apply `PtrSafe`, it will not compile on 64-bit. – GSerg Jul 10 '20 at 15:34
@JAlex, thanks for your help. I tried your code, but some of the codes turned red. Is there any way to fix this? – Justin Smith Jul 11 '20 at 00:36
@JAlex, thank you. I got it now. Even if it's red, the code can still run. – Justin Smith Jul 11 '20 at 03:12
@GSerg, many thanks to you and JAlex. Your suggestions are very helpful. – Justin Smith Jul 11 '20 at 03:14

Troubled with VBA version difference: version 6 and version 7

1 Answers1