CODEWITHSUNDEEP
excelvbashell
heejin ghim
heejin ghim

Reputation: 41

Excel VBA- Shell command error 5- Invalid procedure call or argument

I am troubleshooting a macro that is working on only a few workstations. The macro is designed to call out and launch an executable. All the workstations are on Windows 10 64-bit and Excel 2016 64-bit. I have isolated the issue to the Shell command and created a new blank workbook with a very basic Shell command in VBA to test and remove any additional variables that may be in play, yet the issue still persists.

I have checked the references in VBA on both workstations and they are identical. Yet on the affected workstations when I run the code I get the error : "Run-time error '5': Invalid procedure call or argument".

I have also tried saving the workbook in compatibility mode as a 97-2003 xls to no avail.

I have checked the Trust Center and confirmed they are identical on both workstations, as well as enabled ActiveX and macros.

   Private sub test()
    Dim test1 as long

    test1 = Shell("notepad",1)

    End Sub

On some workstations Notepad will open, on others I will get the above error. I suspect there might be some compatibility issues with the Shell command and Excel 2016. I have tested the same code successfully in Office 365.

Upvotes: 0

Views: 1689

Answers (1)

IqbalHamid
IqbalHamid

Reputation: 2522

This may be due to your hardware having different builds of Windows 10. The shell command still works on Windows build 1703 but no longer works going forward from build 1709.

Try this alternative:

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal LpszDir As String, ByVal FsShowCmd As Long) As Long
Private Declare Function Wow64EnableWow64FsRedirection Lib "kernel32.dll" (ByVal Enable As Boolean) As Boolean
Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, ByVal lpProcName As String) As Long
Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long
Private Declare Function GetCurrentProcess Lib "kernel32" () As Long
Private Declare Function IsWow64Process Lib "kernel32" (ByVal hProc As Long, bWow64Process As Boolean) As Long
    
Private Sub Command1_Click()
   If Is64bit Then
      Wow64EnableWow64FsRedirection False
      ShellExecute Me.hwnd, "open", "osk.exe", "", App.Path, vbNormalFocus
      Wow64EnableWow64FsRedirection True
   Else
      ShellExecute Me.hwnd, "open", "osk.exe", "", App.Path, vbNormalFocus
   End If
End Sub

Public Function Is64bit() As Boolean
   If GetProcAddress(GetModuleHandle("kernel32"), "IsWow64Process") > 0 Then
      IsWow64Process GetCurrentProcess(), Is64bit
   End If
End Function

Upvotes: 0

Related Questions