When trying to execute a third party application, such as Macro Scheduler, from VBA in Office 2007 using the Shell function the following error is returned:
“Invalid procedure call or argument”
Sometimes you might want to run a Macro Scheduler macro from Office using this method and get this error. Searching the web reveals many people reporting the same error when trying to run other applications, including other Office apps! E.g.:
Shell “c:\my macros\macroA.exe”
I suspect there’s an issue with spaces in the path but trying to embed extra quotes with chr(34) doesn’t seem to help. The Shell function also doesn’t provide a way to specify command line parameters other than including them in the entire command string.
The solution is to use the ShellExecute API function. Declare the function in the General Declarations section of your VBA module using this code:
Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Then you can use the function as follows:
ShellExecute 0&, “”, “c:\program files\macro scheduler\msched.exe”, “calculator”, “”, 1
This code runs the Calculator sample macro in Macro Scheduler.
To run an application with no command line parameters leave the fourth parameter empty:
ShellExecute 0&, “”, “c:\my macros\mymacro.exe”, “”, “”, 1
