Overview This module greatly extends what you can do with the Shell function in VBA or Visual Basic 5 or 6 (VB6):
Everything is Unicode, including the Shelled-to environment.
The code works “as-is” in 32 or 64-bit MS Office or in Visual Basic 5 or 6.
This module can be run on Windows XP and later.
You can start a program by specifying a document with an extension. For example, if you specify D:\MyFiles\abc.docx and MS Word is registered to open .docx files then Word will start with this file.
There is a very easy function to use to shell to CMD.EXE that takes care of all of the intricacies in setting up the commandline to CMD.
You can specify how long, if at all, the code waits for the shelled-to program to complete before returning. Specify anything from 0 to effectively forever. You can also specify a time for a pop-up window to the user if he wants the program to quit waiting any further. This is a nice escape from a program that you specify “wait for forever to return” and it gets hung up and never quits.
You can execute programs from anywhere on your PC. For example, if you are running 32-bit VBA or VB6 but are running on a 64-bit version of Windows, you stil can run any 64-bit program. You can even tell it that you want to run the 64-bit version of calc.exe and many other programs that reside in the Windows\System32 folder. Normally a 32-bit program gets redirected away from this folder “behind the scenes” but we work around that problem.
You can run programs that require elevated permissions (the UAC prompt). Normally this is not possible because the Windows CreateProcess function won’t do this but you have the option to 1) keep the privilege level the same as it currenty is, 2) run the new process elevated, 2) run the new process elevated only if it is required (avoids the UAC prompt unless it is necessary) or 3) do not run elevated (fails if elevation is required).
If your program is running elevated, you can specify that the shelled-to routine is non-elevated. Usually a shelled-to routine will be elevated if the host program is elevated.
You can run batch files (.bat and .cmd), windows scripts and PowerShell all without knowing anything other than the extension of the file to “run.”
You can execute a program if you don’t know where it is. For example, you installed Notepad++.exe in “C:\Program Files (x86)\” but as the programmer you don’t know if your users installed it there or if they even installed it at all. And to make matters worse, if they did install it, did they install the 32 or 64-bit version? This code can find the executable file.
Normally, shelling is done via the CreateProcess Windows API call, the significance of which is that the new process is independent from the calling program which makes it multi-threaded. You can have the calling program wait until the shell-to routine is complete or you can return control to the calling program and have it continue while the shelled-to program runs in the background. The other common method of shelling to an external program is to use ShellExecuteEx but that routine has limitations which we avoid with CreateProcess. ShellExecuteEx’s main advantage is being able to specify a document instead of an executable and we provide that functionality without using ShellExecuteEx. However, we do include ShellExecuteEx so you can specify it if you really want. Also, ShellExecuteEx is the routine we use to raise permissions when desired or required since CreateProcess lacks that capability.
You can use the Windows’ CreateProcess function or ShellExecuteEx functions for your external call. There are pros and cons for each approach; either will work in most instances. The differences are discussed later in this document. CreateProcess is not limited to 255 characters in the commandline. ShellExecuteEx can run programs with elevated privileges.
Most programs start a new instance of themselves when we open with CreateProcess even if an existing instance is running. However, this is not true by default for MS Excel, MS Word and Notepad++. If an instance is already running when you specify opening another one, the new document(s) will eb opened in the instance that is already running. This may be okay but know that the new documents share the same memory (if you are running 32-bit versions of these programs this could be limit) and if for some reason something causes the instance to crash then everything in it including the new documents will crash as well. If they are in a different instance they have their own memory space and do not normally crash when another instance crashes. When you use DocShellW in this module you can specify that the shell is to a new instance and if the program is MS Excel, MS Word or Notepad++ then we apply some special code to force them to start a new instance. You can keep the other behavior of putting them al into one instance by specifying NewInstance as False in the call.
This module requires the use of my core library mUCCore. It provides my error handling ystem, Unicode support, the basis for support for all flavors of VBA including 64-bit as well as VB6 (compiled or in the IDE) and it contains many routines I use all of the time. It is accompanied by a separate user guide. You don’t need to know what it does in order to use this shelling module but you may find many parts of it useful for your programs.
When I test my shell functions I can hard-code various files I want to open or programs to run. I can’t do that with the sample program I have provided because your files are different than mine and are in different locations than mine. So I have included another of my library modules, this one named FileStuffLite. It includes many things not needed for mShellW but I am using a small part of it just for the demo/test. It is not needed for mShellW to function.
The code is LARGEADDRESSAWARE. For VB6 runing in 32-bit Windows you can access up to 3 GB of memory and if run in 64-bit Windows (yes, VB6 itself is 32-bit) you can access up to 4 GB. If you are running Excel 2013 or 2016 you can acces up to 4 GB of memory. This shell by itself does not set that up but it is coded such that you won’t get memory crashes from this module when data or code is above the 2 GB mark.
Main RoutinesShellW – The main routine for running another program. DocShellW – Open a file with whatever program is registered in Windows for opening that type of file. For example, you could specify “Test.doc” and the file would be opened with Microsoft Word on most PC’s but if .doc files are set to be opened with some other program then that one is used. CMDRun – Run a batch file, a PowerShell file, a program, a document file or just a command prompt via CMD (very similar to the old DOS prompt). You control whether it goes away after executing whatever you want it to do. Support RoutinesQuoteIfNec – If the specified string (normally a path to a program or a document) has a space character in it Windows generally wants it enclosed in quotes. This routine checks if the string has a space character and if so it encloses the whole string in quotes. ShellHandlesClose – Closes process and thread handles and resets internal variables for them to 0. HasProcessClosed – Determines if a specified process has ended. IsExeRunning – Returns True if the specified EXE file is running. FindEXEPath – Returns the full path of the specified .EXE file. Can be called within ShellW. FindEXEFromDoc – Returns the program in the current PC’s registry that is set to open the specified document file. Can be called from within DocShellW. NOTE - I just uploaded the VB6 version of this (the mShellW module is the same) to the VB6 Code Bank if you have/use VB6.
Last edited by MountainMan; Jul 26th, 2018 at 05:07 AM.
Reason: Small Bug Fix.
The submission for this same code with a VB6 example on vbForums is in the VB6 Codebank at here. The code is the same although there is some difference in VB6 versus VBA. You may want to periodically check out what is going on in that link. I will ensure that whenever there are bug fixes or updates from that thread get incorporated here and vice versa.
This just occurred. Dragokas, in post #2 caught a bug in one of my Windows API Declarations. Above you will find the updated files resulting from that bug fix. The module mShellW and the Word documentation file have been modified for the bug-fix.
If you are interested in the concepts around LARGEADDRESSAWARE (which now potentially affects all VBA programmers), you can read a discussion here on vbForums or here from Microsoft.
Essentially the LARGEADDRESSAWARE is a feature that enable 32-bit programs to access memory above the 2 GB boundary (the max for a Long variable). With a 32-bit operating system you can potentially address up to 3 GB and 32-bit code running in a 64-bit operating system (quite common now) can access up to 4 GB. If your code does not use pointers (StrPtr, VarPtr, ObjPtr) or the various memory copy or move routines (RtlMoveMemory etc.) then you don't need to be concerned. However, if you do use any of these, as 32-bit programmers we use Long variables to keep track of memory addresses. But longs are signed which means that when we try to go above the max value (which causes the high bit to be set) VBA interprets this as a negative number. So for example, if you have the address of a string, you likely found that using StrPtr and if you wanted to access the 2nd character you would take the address of the start of the string and add 2 bytes. But what if the base address for the string is actually above the 2 GB boundary to start with? Now it is a negative number. It turns out that we are okay even using signed Longs but in those cases where we cross the magical 2 GB boundary the calculation goes horribly wrong and a crash is almost certain to occur.
If you don't use pointers but instead rely on VBA to keep track of where vriables are then you won't have to be concerned with this. However, if you do any of what I described above (the so-called "pointer arithmetic") then you have to make sure that adding and subtracting values from signed Longs work correctly or you will crash if/when code or data gets really large and crosses the 2 GB boundary. Since you have no idea when/if this will occur I encourage you to know a bit more about this issue. You may elect to do nothing but at least you will be making an informed decision.
I have taken the approach in all my code now that I will make it LARGEADDRESSAWARE. I can't guarantee that anyone else's code is but I can make sure mine doesn't cause inadvertent problems. I encourage you to think through this if you haven't already. Having access to very large memory can be a wonderful thing; just approach it responsibly.
BTW, the solution is relatively simple. Anywhere in your code where you do pointer arithmetic, you just want to ensure that the calculation of adding or subtracting an offset to a memory address gets done correctly across the 2 GB boundary. In other words we want to have the 32-bit Long to have its number calculated as if it were an unsigned 32-bit number instead of a 32-bit signed number. From the above referenced link to the vbForums discussion, you will see a proposed calculational technique that does exactly that. Here is a little routine that does just that.
Code:
Function UnsignedAdd(ByVal UnsignedPtr As Long, ByVal SignedOffset As Long) As Long
'Safely add *signed* offset to *unsigned* ptr for *unsigned* retval w/o overflow in LARGEADDRESSAWARE processes
UnsignedAdd = ((UnsignedPtr Xor &H80000000) + SignedOffset) Xor &H80000000
End Function
You will find this function in the module mUCCore included with the mShellW module. I have so much code with this function in it that for speed reasons I don't call this function but instead hard code the equation in the function inline. It really doesn't add much to the code size and its one less procedure call to make. But calling the function above works just as well, albeit a bit more slowly. If yo look through any of my modules you will see this unsigned pointer arithmetic scattered throughout.
It is also important that you understand that you don't need to know anything about any of this to my code. All of the Subs and Functions I provide for you to use do not require knowing any of this. As VB guys/gals we always catch crap from programmers using other languages that we don't do pointers so we are somehow inferior. I like to believe that by using pointers but doing it where you or I are not even aware of it is the best of both worlds. We can do things that we other wise can't even do with VBA code but you dno't have to deal with it. After all, that's the basis of VBA code anyway. The folks who wrote VBA used pointers all the time; they just hid that all from us to let us focus on our coding. I am trying to do the same thing.
BTW, if you code for 64-bit VBA you don't need to do any of this complicated pointer arithmetic thing. Even if you do pointer arithmetic, you use 64-bit LongPtr or LongLong to hold the memory address so your memory would have to be enormous (9,223,372,036,854,775,807 bytes to be exact) before you crossed the signed/unsigned boundary. So for 64-bit, just take the base address and add or subtract the offset value.
A word of caution - You may have noticed that starting with Office 2010 we have access to a LongPtr and it is tempting to think that this is one of the huge numbers described int he paragraph above so we don't need to worry about any of this. Don't be fooled! If your code is running in a 32-bit environment, the LongPtr is the same as a Long; it is only a 64-bit number when the code is run in a 64-bit version of Excel, Word, etc. So just having your pointer math done via LongPtr's doesn't alleviate the concerns of LARGEADDRESSAWARE in the 32-bit world.
Last edited by MountainMan; Jul 27th, 2018 at 12:50 PM.