Excel Error VBA Excel 2013

Question

I get the following error.

Compile error: The code in this project must be updated for use on 64-bit systems. 

VBA CODE

 Option Explicit 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 Dim Ret As Long '~~> This is where the images will be saved. Change as applicable Const FolderName As String = "C:\Temp\" 

It works great in Excel 2010.

Thanks.

EDIT

Error: Ret Variable Not defined . Here is the rest of the code.

 Sub Sample() Dim ws As Worksheet Dim LastRow As Long, i As Long Dim strPath As String '~~> Name of the sheet which has the list Set ws = Sheets("Sheet1") LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow '<~~ 2 because row 1 has headers strPath = FolderName & ws.Range("A" & i).Value & ".mp3" Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0) If Ret = 0 Then ws.Range("C" & i).Value = "File successfully downloaded" Else ws.Range("C" & i).Value = "Unable to download the file" End If Next i End Sub 

Answer 1

You must run this on the 64-bit version of Office, whereas previously you used the 32-bit version.

To convert 32Bit calls to 64Bit, you usually need to add PtrSafe to a function and convert some data types from Long to LongPtr (which is just a larger data type (see http://msdn.microsoft.com/en-us/library/ office / gg251378.aspx )

Thus, the converted function will:

 Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" _ (ByRef pCaller As LongPtr, _ ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserve As Long, _ ByRef lpfnCB As LongPtr) _ As LongPtr 

Edit: note that if you want to use this in both versions of Office 64Bit and 32Bit, you need to use the preprocessor If statement so that Office knows which function to use. I.e:

 #If Win64 Then Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon"....... #Else Private Declare Function URLDownloadToFile Lib "urlmon"....... #End If 

Answer 2

MSDN Link

Complete the error message: The code in this project must be updated to be used on 64-bit systems. Review and update Declare statements and then mark them with the PtrSafe attribute. All declarations of declarations should now include the PtrSafe keyword when working in 64-bit versions of Microsoft Office. The PtrSafe keyword indicates that the Declare instruction works safely on 64-bit versions of Microsoft Office. Adding the PtrSafe keyword in the Declare statement means only the Declare statement is explicitly targeted at 64-bit, all data types inside the statement that you want to store 64-bit (including return values ​​and parameters), you still need to change to store 64-bit values. using either LongLong for 64-bit integrals or LongPtr for pointers and handles.

Add the PtrSafe keyword.