According to Wikipedia: “Environment variables are a set of dynamic named values that can affect the way running processes will behave on a computer. They are part of the operating environment in which a process runs. For example, a running process can query the value of the TEMP environment variable to discover a suitable location to store temporary files, or the HOME or USERPROFILE variable to find the directory structure owned by the user running the process.
They were introduced in their modern form in 1979 with Version 7 Unix, so they are included in all Unix operating system flavors and variants from that point onward including Linux and OS X. From PC DOS 2.0 in 1982, all succeeding Microsoft operating systems including Microsoft Windows, and OS/2 also have included them as a feature, although with somewhat different syntax, usage and standard variable names”.
In this post, we will focus on Windows environment variables and how we can use them from VBA in order to determine various useful information regarding the operating system (OS). Moreover, we will see how to combine them and write our custom VBA functions.
Available environment variables
Below is a list of various environment variables along with their description:
- ALLUSERSPROFILE: Lists the location of the All Users Profile.
- APPDATA: Lists the location where applications store data by default.
- CD: Lists the current directory string.
- CLIENTNAME: List the client’s NETBIOS name when connected to the terminal server session.
- CMDCMDLINE: Lists the command line used to start the current cmd.exe.
- CMDEXTVERSION: Lists the version number of the current Command Processor Extensions.
- COMMONPROGRAMFILES: Lists the path to the Common Files folder.
- COMPUTERNAME: Lists the name of the computer.
- COMSPEC: Lists the path to the command shell executable.
- DATE: Lists the current date.
- ERRORLEVEL: Lists the error code of the most recently used command.
- HOMEDRIVE: Lists the drive letter is connected to the user’s home directory.
- HOMEPATH: Lists the full path of the user’s home directory.
- HOMESHARE: Lists the network path to the user’s shared home directory.
- LOGONSEVER: Lists the name of the domain controller that validated the current logon session.
- NUMBER_OF_PROCESSORS: Lists the number of processors installed on the computer.
- OS: Lists the name of the operating system. (Windows XP and Windows 2000 list the operating system as Windows_NT.)
- PATH: Lists the search path for executable files.
- PATHEXT: Lists the file extensions that the operating system considers to be executable.
- PROCESSOR_ARCHITECTURE: Lists the processor’s chip architecture.
- PROCESSOR_IDENTFIER: Lists the description of the processor.
- PROCESSOR_LEVEL: Lists the model number of the computer’s processor.
- PROCESSOR_REVISION: Lists the revision number of the processor.
- PROGRAMFILES: Lists the path to the Program Files folder.
- PROMPT: Lists the command-prompt settings for the current interpreter.
- RANDOM: Lists a random decimal number between 0 and 32767.
- SESSIONNAME: Lists the connection and session names when connected to the terminal server session.
- SYSTEMDRIVE: Lists the drive containing the Windows root directory.
- SYSTEMROOT: Lists the location of the Windows root directory.
- TEMP and TMP: List default temporary directories for applications that are available to users who are currently logged on.
- TIME: Lists the current time.
- USERDOMAIN: Lists the name of the domain that contains the user’s account.
- USERNAME: Lists the name of the user currently logged on.
- USERPROFILE: Lists the location of the profile for the current user.
- WINDIR: Lists the location of the OS directory.
Eight custom VBA functions were developed based on the environment variables listed above. The EnvironmentResult is the most generic one; it returns a string based on a (valid) environment variable used as an input. The three versions of Is64bitOS function demonstrate three different ways of determining if an OS is 32 or 64bit, by using three different environment variables. The UserFolder is an alternative to the USERPROFILE variable. Finally, the My+ functions return the path of “Desktop”, “My Documents” and “My Pictures” folders correspondingly. You can see a usage of the MyDesktop function in this code.
Option Explicit '------------------------------------------------------------- 'This module contains some custom functions that demonstrate 'the usage of Windows environment variables in VBA. 'Written By: Christos Samaras 'Date: 26/05/2014 'E-mail: [email protected] 'Site: https://www.myengineeringworld.net '------------------------------------------------------------- Function EnvironmentResult(EnvironmentVariable As String) As String 'Returns a string based on the (valid) environment variable that was used as an input. EnvironmentResult = Environ(EnvironmentVariable) End Function Function Is64bitOS() As Boolean 'Returns TRUE if the operating system (OS) is 64 bit. Is64bitOS = Len(Environ("PROGRAMFILES(X86)")) > 0 End Function Function Is64bitOS2() As Boolean 'Returns TRUE if the operating system (OS) is 64 bit (alternative). Is64bitOS2 = Len(Environ("PROGRAMW6432")) > 0 End Function Function Is64bitOS3() As Boolean 'Returns TRUE if the operating system (OS) is 64 bit (2nd alternative). Is64bitOS3 = Len(Environ("COMMONPROGRAMFILES(x86)")) > 0 End Function Function UserFolder() As String 'Returns the default user folder. UserFolder = Environ("HOMEDRIVE") & Environ("HOMEPATH") End Function Function MyDesktop() As String 'Returns the Desktop location. MyDesktop = Environ("USERPROFILE") & "\Desktop" End Function Function MyDocuments() As String 'Returns the path of My Documents folder. MyDocuments = Environ("USERPROFILE") & "\My Documents" End Function Function MyPictures() As String 'Returns the path of My Pictures folder. MyPictures = Environ("USERPROFILE") & "\My Pictures" 'For Windows XP: 'MyPictures = MyDocuments & "\My Pictures" End Function
All functions are quite straightforward and are based on Environ VBA function, which returns a string associated with an operating system environment variable. As you would probably imagine, it is quite easy to develop your own custom VBA functions based on Windows environment variables.
The file can be opened with Excel 2007 or newer. Please enable macros before using it, and, after opening it, press CTRL + ALT + F9 to recalculate all the formulas in the workbook.