0 votes
in VBA by Beginner (3 points)
I have seen examples of the 64bit version of the URLDownloadToFile function that used the "LongPtr" data type for some of the arguments.  Can you confirm that Long data types are valid for the function in both 32 and 64bit environments?  Thanks
by Beginner (3 points)
Thanks, but the reason I raised the issue was the original example DID NOT use LONGPTR for any of the arguments, despite the example was written for 64bit Excel.  I also have tried the various methods you describe and they do APPEAR to work, at first.  However, after testing with those other data types, I had Excel CRASH at numerous intermittent times.  I have seen multiple other examples using some combination of Long, LONGPTR, and LongLong.  I really need an authoritative source for this.  Even reviewing Microsoft's own documention on the URLDownloadToFile function is not clear to me as it's written the way the function is used in C+.  Thanks again, but unfortunately I'm still on the fence.

1 Answer

+1 vote
by Expert (911 points)

I assume your question refers to Cory Sarver's tutorial Download Files with VBA URLDownloadToFile. I normally use Dennis Wallentin's Windows API Viewer for MS Excel to answer questions like yours, but that app doesn't include URLDownloadToFile in its library. Therefore, Compatibility Between the 32-bit and 64-bit Versions of Office 2010 is my alternate source.

Here is URLDownloadToFile Syntax

HRESULT URLDownloadToFile(
             LPUNKNOWN            pCaller,
             LPCTSTR              szURL,
             LPCTSTR              szFileName,
  _Reserved_ DWORD                dwReserved,

Since pCaller and lpfnCB are both pointers, I believe the correct VBA declaration for 64-bit Windows is:

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

Notice all parameters are ByVal, so we don't need to worry about any of them being changed by the function. Using VBA you will almost certainly be setting pCaller and lpfnCB to zero as NULL pointers, so it probably doesn't matter if they are declared Long instead of LongPtr. And since dwReserved must be set to zero, it probably can be declared Long instead of LongLong.

The function returns S_OK=0 if the download started successfully; otherwise, it returns E_OUTOFMEMORY=0x8007000E or INET_E_DOWNLOAD_FAILURE=0x800C0008. You will likely test whether or not the return value is zero (only), so it probably doesn't matter if the return value is declared Long instead of LongLong.

I have tried this declaration in 64-bit Windows with the various combinations of Long, LongPtr, and LongLong described above and it worked in every case.

When you post a question, please oblige us by responding to comments and answers. Also, you might be interested in My Excel Toolbox.

by Expert (911 points)

1. Here is another good article on the subject: VBA for 32 and 64 bit systems.

2. When I referred to 64-bit Windows in my answer above, I should have referred to 64-bit Office/Excel instead, assuming you intend to develop VBA for Office/Excel. (Of course, 64-bit applications require 64-bit Windows.) But if you have 32-bit applications running on 64-bit Windows, always use Long instead of LongLong; in this case, LongPtr still applies to pointers.

by Beginner (3 points)
Thank you very much for the detailed reply. Looks great!
by Expert (911 points)

As you have noted, this is a confusing subject with too many inconsistent "answers". After further consideration, I believe most (perhaps all) Windows API declarations refer to Win32 API procedures. In this case, it should NOT be necessary to use LongLong instead of Long in a VBA Declare statement for 64-bit Office/Excel. LongPtr still applies to pointers and handles, but use String for pointers like LPCTSTR.

BTW, I believe there is still no Win64 API.

by Beginner (3 points)
Thank you for the clarification.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.