PDA

View Full Version : [SOLVED] Shell and Wait - 32 bit and 64 bit



ronjon65
12-23-2015, 04:01 PM
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.


-----


'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

Leith Ross
12-23-2015, 11:50 PM
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.



#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

snb
12-24-2015, 01:09 AM
@Leith

I think you reversed ' 32 Bit & ' 64

Leith Ross
12-24-2015, 01:48 AM
Thanks for catching that snb. The code is correct but the labels were reversed.



#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

Aflatoon
12-24-2015, 02:26 AM
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.

Leith Ross
12-24-2015, 11:14 AM
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.

ronjon65
12-24-2015, 01:03 PM
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.

Leith Ross
12-24-2015, 01:29 PM
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.

ronjon65
12-24-2015, 05:29 PM
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?


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 Ross
12-24-2015, 06:24 PM
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.

ronjon65
12-24-2015, 07:04 PM
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.

Leith Ross
12-24-2015, 07:23 PM
Hello ronjon65,

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


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


' 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

ronjon65
12-24-2015, 08:09 PM
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?

Aflatoon
12-29-2015, 05:12 AM
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.

Aflatoon
12-29-2015, 05:15 AM
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. ;)

ronjon65
01-06-2016, 08:42 PM
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?

Leith Ross
01-06-2016, 09:09 PM
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.

ronjon65
01-06-2016, 09:23 PM
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?

Aflatoon
01-07-2016, 01:35 AM
Are you saying you ran the code in 64 bit office and got an error?

ronjon65
01-07-2016, 02:23 AM
Right.

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

Aflatoon
01-07-2016, 02:39 AM
I see the problem. The code should read either:

#If VBA7 = 1 Then
or:

#If VBA7 Then
or even:

#If CBool(VBA7) = True Then

but not:

#If VBA7 = True Then

ronjon65
01-07-2016, 05:47 AM
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?


#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

Leith Ross
01-07-2016, 01:49 PM
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.



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

ronjon65
01-07-2016, 02:07 PM
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)?

Leith Ross
01-07-2016, 03:44 PM
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.


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

ronjon65
01-07-2016, 05:25 PM
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.

Leith Ross
01-07-2016, 08:45 PM
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.

ronjon65
01-07-2016, 09:10 PM
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?

Leith Ross
01-07-2016, 09:51 PM
Hello ronjon65,

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

ronjon65
01-08-2016, 06:32 AM
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?

Aflatoon
01-09-2016, 03:25 AM
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.

ronjon65
01-09-2016, 08:09 AM
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?


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

Aflatoon
01-09-2016, 11:26 AM
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.

ronjon65
01-09-2016, 11:45 AM
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?


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

Aflatoon
01-09-2016, 01:14 PM
#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.

ronjon65
01-09-2016, 01:57 PM
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?

Aflatoon
01-09-2016, 03:34 PM
It won't work on a non Windows machine but other than that it will be fine for any combination of OS and Office.

ronjon65
01-09-2016, 04:03 PM
Great, I was hoping you might say that :)