Shell and Wait - 32 bit and 64 bit

I am using a shell and wait routine that looks like the following. When it says "kernel32" what are the possible limitations? For example, will this not work on OS's that are 64 bit or Office versions that are 64 bit? Or should it work for both. It seems that it works for a 64 bit OS, but not sure about a 64 bit Office version since I have not test bench for that at the moment.


-----

Code:

---

'Libraries (DLL's)
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

'Function MeShellAndWait(szCommandLine As String, Optional iWindowState As Integer = vbHide) As Boolean
'VBA Shell Options
Function MeShellAndWait(szCommandLine As String, Optional iWindowState As Integer = vbNormalFocus) As Boolean

---

Hello ronjon65,

Windows Vista, 7, 8, and 10 support 32 and 64 bit programs. The 32-bit and 64-bit versions of Office programs aren’t compatible, so you can’t install both on the same computer.

Office 2010 introduced a new version of VBA known as VBA 7.0. This works with both 32 and 64 bit machines. There is compilation constant VBA7 used to test for this new version.

Here are the 32 and 64 bit versions of the two API calls you are using. The 64 bit API will only work if the 32 bit DLL is loaded on the 64 bit machine.
This macro will load the correct API calls for machines using 64 bits and VBA 7.0 using conditional compilation statements.

Code:

---

#IF VBA7 = True Then
    ' 32 Bit API
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" Alias "OpenProcess" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" Alias "GetExitCodeProcess" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long
#ELSE
    ' 64 bit API
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long      Private Declare Function
    GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If

---

@Leith

I think you reversed ' 32 Bit & ' 64

Thanks for catching that snb. The code is correct but the labels were reversed.

Code:

---

#If VBA7 = True Then
    ' 64 Bit API
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long
#Else
    ' 32 bit API
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If

---

Quote:

---

Originally Posted by Leith Ross

The 32-bit and 64-bit versions of Office programs aren’t compatible, so you can’t install both on the same computer.

---

FYI, you can in fact have both 32 and 64bit Office on the same computer, just not of the same version. So you can have say 32 bit 2010 and 64 bit 2016, but not 32 and 64bit of 2010.

Hello Aflatoon,

Thanks for the correction. I quoted what Microsoft currently has on it's MSDN knowledge base. Personally, I think it would be a very unstable environment to have 32 and 64 bit Office versions installed on the same machine, especially if you are doiing code development.

Great stuff guys :)

Curious, was there no 64 bit version before 2010? In other words, any issues with 2003?

Looking forward to trying this out soon. Yeah, I need a new machine for testing the 64 bit version. It can be tricky with all the various versions to get things to be solid for general use by anyone.

Hello ronjon65,

Windows 2003 is a 32 bit platform. Vista was the first version of Windows to run on a 64 bit platform. Until the release of Office 2010, there were no 64 bit Office programs.

Couple questions:

- Why is the # needed? I have never used that before.

- Using Excel 2003 32 bit on Win10 64 bit and the following two lines appear red. It still runs OK, but not sure what that means?

Code:

---

Private  Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal  dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId  As Long) As LongPtr

    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long

---

Hello ronjon65,

The # (pound sign) is used in VBA to signal the statement is conditionally compiled. It will execute during compilation but not during run-time.

As I mentioned, if the operating system does not support 64 bits then the API code for the 64 bit environment will error. The reason is statements like PtrSafe and LongPtr do not exist in VBA6 and earlier. VBA7 was released with Windows 7 and supports both.

Leith,

"if the operating system does not support 64 bits then the API code for the 64 bit environment will error". Do you mean the office/VBA version? I have a 64 bit OS and it shows red. But Excel is 2003 and 32 bit.

But it still runs with the code you have. So should code work for all Excel 2003 and later and both 32bit and 64 bit office versions? I guessing that it may but I'm out of town and can't try with later Office versions right now.

I am going to get a cheap laptop and put a 64bit Office on it as well to test things out. Lots of combinations to consider.

Hello ronjon65,

If your running VBA in Office 2003, which is VBA6, then you only need the code below:

Code:

---

    ' 32 bit API
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

---

These lines of code will only compile in VBA7:
Note: The following words PtrSafe and LongPtr are part of VBA7 only and will not compile in earlier versions of VBA.

Code:

---

    ' 64 Bit API
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As LongPtr
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long

---

Leith,

Exactly what happens when you have lines that do not compile? For general cross platform compatibility, I want to use the if/else structure you originally stated. In Excel 2003, it shows the 2 lines that do not compile (as expected), but everything works fine and I don't get an error. I guess because the if/else catches it so I can just ignore the fact that it does not compile? Or could that have other undesirable consequences that I am not aware of?

You can simply ignore the fact that the lines are highlighted in red by syntax checking. It will not affect the actual run time execution as you have found.

Quote:

---

Originally Posted by Leith Ross

Personally, I think it would be a very unstable environment to have 32 and 64 bit Office versions installed on the same machine, especially if you are doiing code development.

---

I ran such a setup for the best part of a year with no issues. The only drawbacks for me were that my favourite add-ins didn't have 64bit versions so I tended to stick to 32bit for development anyway, but I had no stability problems. I can appreciate that most people would probably prefer to use VMs for this rather than go through what is a slightly fiddly installation process though. ;)

I finally got a 64 bit system to test on and added the code as in post #4 (works on 32 bit office). But it throws an error and highlights the word Function after the ' 32 bit API comment line

"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."

So what gives?

Hello ronjon65,

An operating system that is 64 bit only will not compile 32 bit system API calls, even if they are in a conditional compilation statement.

Hey Leith...so what can be done about this? I really want just one file to cover both scenarios, but I suppose it may need to be one file for 32 bit and one for 64 bit systems? That is rather clunky and unfortunate though.

If two files is the only option, is there a way for each file to indicate that they have the "wrong" version via message box?

Are you saying you ran the code in 64 bit office and got an error?

Right.

Office 2013 64bit + Win 10 64bit gives error.
Office 2003 32bit + Win 10 64bit does not give error.

I see the problem. The code should read either:

Code:

---

#If VBA7 = 1 Then

---

or:

Code:

---

#If VBA7 Then

---

or even:

Code:

---

#If CBool(VBA7) = True Then

---

but not:

Code:

---

#If VBA7 = True Then

---

Thanks, after I made that adjustment, I got a "type mismatch" compile error. However, if also adjust the 64bit code to not have "LongPtr" (only Long) then it seems to work on both 32 bit and 64 bit (as follows). Is this OK?

Code:

---

#If VBA7 Then
    ' 64 Bit API
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#Else
   
    ' 32 bit API
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If

---

Hello ronjon65,

VBA has the capability to load Modules into the VBProject. This can be done either by storing the code on worksheets that are hidden or by saving the code in Notepad files.

I have found a way to determine the whether the OS is 32 or 64 bit and if 32 bit files are supported on a 64 bit platform. The macro below will tell you. without using the API.

Currently, I am writing code to use this test and read files into the VB Project.

Code:

---

Sub OSInfo()

  ' Written:    January 07. 2016
  ' Author:    Leith Ross
 
    Dim colOperatingSystems As Object
    Dim Folder32            As Object
    Dim msg                As String
    Dim objOS              As Object
    Dim strComputer        As String
   
        With CreateObject("Shell.Application")
            Set Folder32 = .Namespace(Environ("HOMEDRIVE") & "\Program Files (x86)")
        End With
       
        strComputer = "."
       
        Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
       
        For Each objOS In colOperatingSystems
            With objOS
                msg = .Caption & "  " & .Version & vbLf & .OSArchitecture
                If Not Folder32 Is Nothing And .OSArchitecture = "64-bit" Then
                    msg = msg & vbLf & "32 bit Programs are supported"
                End If
            End With
            MsgBox msg
        Next objOS

End Sub

---

Leith, that is interesting and might be useful.

Let me ask you this though. Once I modified the code from post #4 to the code in post #22, it seems to work at intended. Do you see any problems with code in #22? I shortened the LongPtr to Long and Aflatoon showed how to adjust the IF statement. This is a simple approach, but not sure how stable it is (did I make an incorrect mod)?

Hello ronjon65,

The attached workbook will create or update the VBA module "API_Calls" from the 2 worksheets "32-bit API" and "64-bit API". "Sheet1" has instructions and the button to run the macro.

Here is the code for Module1.

Code:

---

Sub LoadAPICalls()

  ' Written:    January 07. 2016
  ' Author:    Leith Ross
 
    Dim BitSize            As String
    Dim colOperatingSystems As Object
    Dim Folder32            As Object
    Dim objOS              As Object
    Dim strComputer        As String
   
        With CreateObject("Shell.Application")
            Set Folder32 = .Namespace(Environ("HOMEDRIVE") & "\Program Files (x86)")
        End With
       
        strComputer = "."
       
        Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
       
        For Each objOS In colOperatingSystems
            With objOS
                BitSize = .OSArchitecture
               
                If BitSize = "32-bit" Then
                    CreateUpdateAPIModule BitSize
                    Exit Sub
                End If
               
                If Not Folder32 Is Nothing And BitSize = "64-bit" Then
                    CreateUpdateAPIModule "32-bit"
                    Exit Sub
                End If
               
                If Folder32 Is Nothing And BitSize = "64-bit" Then
                    CreateUpdateAPIModule BitSize
                    Exit Sub
                End If
            End With
        Next objOS

End Sub

Sub CreateUpdateAPIModule(ByVal BitSize As String)

    Dim Cell        As Range
    Dim Rng        As Range
    Dim Text        As String
    Dim VBMod      As Object
    Dim Wks        As Worksheet
   
        Set Wks = Worksheets(BitSize & " API")
        Set Rng = Wks.UsedRange.Columns(1).Cells
       
        For Each Cell In Rng
            Text = Text & Cell.Value & vbCrLf
        Next Cell
       
        On Error Resume Next
            Set VBMod = ThisWorkbook.VBProject.VBComponents.Item("API_Calls")
       
            If Err = 9 Then
                Set VBMod = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
                VBMod.Name = "API_Calls"
                VBMod.CodeModule.AddFromString Text
            End If
           
            If Err = 0 Then
                With VBMod.CodeModule
                    .DeleteLines 1, .CountOfLines
                    .AddFromString Text
                End With
            End If
        On Error GoTo 0

End Sub

---

Leith, that is quite clever. In this case, I am trying to keep it as simple as possible and the code on post #22 seems to be working fine. Unless there is an inherent issue there, I will go with that for now. Thanks.

Hello ronjon65,

The code in Post #22 will work on 64 bit systems that have 32 bit Windows installed. If not then you will receive an error. You will also receive an error if the code is run on a 32 bit platform because PtrSafe and LongPtr are not part of the system's syntax.

The solution I have offered circumvents these problems because no API code is needed to test the system and then load the correct code. True, it is more complex but the complex part has been done for you. You only need to copy the API code and other associated macro code to the correct worksheet. The rest is automatic. The button can removed and a call placed in the Worksheet_Open() event module to perform the update when the workbook opens. Short code is not always the best code.

I catch your drift. I will give it a go. FYI, the code in #22 would seem to work in all but one scenario I guess?

64 bit OS with 32 bit Office - Tested and Works
64 bit OS with 64 bit Office - Tested and Works
32 bit OS with 64 bit Office - Not possible
32 bit OS with 32 bit Office - Untested but won't work per post #27?

Hello ronjon65,

Nice table of results you made. Yes, you are correct.

Leith, so I was able to test on a 32 bit OS with 32 bit Office (2010) and the code on #22 works. But you stated it would not. Would it fail on a different version of Excel then (i.e 2003)?

So I am not sure what gives, but I can't find a scenario where #22 does not work. I do want to try the code on #25, but if #22 works fine (and no evidence so far that it will not) then that is the more direct approach.

Test cases:
Win 10 (64 bit) with Office 2013 (64bit) - Works
Win 10 (64 bit) with Office 2002 (32 bit) - Works
Win 10 (64 bit) with Office 2010 (32 bit) - Works
Win 7 (32 bit) with Office 2010 (32 bit) - Works

So all possible combinations of bit configurations work (3 possible) and works on 2003, 2010, 2013 (though not tested each on all 3 bit combinations). Exactly what scenario would fail and maybe I can test this?

You should absolutely not use Long instead of LongPtr. Where did the type mismatch occur?

The whole point of conditional compilation is to make the code run on any system.

Within the Shell Function, the following lines exist. So this gets tripped up on 64 bit systems if LongPtr is used (best guess). If I comment out Option Explicit, then everything runs OK with LongPtr as part of the code. So maybe I should just remove the Option Explicit and leave the code with the LongPtr?

Code:

---

Dim lResult As Long
Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long

---

No - there is a reason for the error so you shouldn't simply try and ignore it. Please post the full code you have since those variables are not in anything posted to date. You need to use conditional compilation in the function too to ensure the correct types are passed to and returned from the API calls.

Here ya go. I don't know how to implement the conditional statement within the function though. So what is the risk of previous two methods, which are clearly not "correct"? So far, it works on everything I have tried. While I know it is not strictly correct, is it really an issue?

Code:

---

Function ShellWait(szCommandLine As String, Optional iWindowState As Integer = vbHide) As Boolean

Dim lResult As Long
Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
   
    On Error GoTo ErrorHandler

    lTaskID = Shell(szCommandLine, iWindowState)
   
    'Check for errors in the command line variable.
    If lTaskID = 0 Then Err.Raise 9999, , "Shell function error."
   
    'Get the process handle from the task ID returned by Shell.
    lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)
   
    'Check if process was started
    If lProcess = 0 Then Err.Raise 9999, , "Unable to open Shell process handle."
   
    'Loop while the shelled process is still running.
    Do
        'lExitCode will be set to STILL_ACTIVE as long as the shelled process is running.
        lResult = GetExitCodeProcess(lProcess, lExitCode)
        DoEvents
    Loop While lExitCode = STILL_ACTIVE
   
    MeShellAndWait = True

Exit Function
   
ErrorHandler:
    gszErrMsg = Err.Number
    MeShellAndWait = False
End Function

---

Code:

---

#If VBA7 Then
Dim lProcess As LongPtr
#Else
Dim lProcess As Long
#End If

---

If you pass/return the wrong data types with API calls the best you can hope for is a runtime error. More likely Excel will simply terminate and in extreme cases Windows will crash.

Well that did the trick. Thanks very much!

Aflatoon, do you have any comments about why Leith says the "simple" code (Post #4 with modified IF statement) will not work on all systems? Per my trials on post #30, I have tried it on every possible bit combination with success. My only question is if there is a bit combination + Excel version that it might possibly fail on?

It won't work on a non Windows machine but other than that it will be fine for any combination of OS and Office.

Great, I was hoping you might say that :)