copy

Monday, 26 May 2014



Using Windows Environment Variables In VBA


Introduction



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


VBA code



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 USERPROFILE variable. Finally, the My+ functions return the path of “Desktop”, “My Documents” and “My Pictures” folders correspondingly.

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:        xristos.samaras@gmail.com
'site:          http://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.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it, and, after opening it, press CTRL + ALT + F9 in order to recalculate all the formulas in the workbook.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info