In VBA Tips, Tricks and Best Practices, Part One of Four I posted a method for checking the values of several environment variables. Here I will present a method for checking any available environment variable.
First, you'll want to visit that blog post and run the EnvironListing procedure, to get the names of every available environment variable on your local system. On my system here's what I got:
ALLUSERSPROFILE
APPDATA
CommonProgramFiles
COMPUTERNAME
ComSpec
FP_NO_HOST_CHECK
HOMEDRIVE
HOMEPATH
INCLUDE
LIB
LOGONSERVER
NUMBER_OF_PROCESSORS
OS
path
PATHEXT
PROCESSOR_ARCHITECTURE
PROCESSOR_IDENTIFIER
PROCESSOR_LEVEL
PROCESSOR_REVISION
ProgramFiles
SESSIONNAME
SYBASE
SYBASE_OCS
systemDrive
SystemRoot
temp
TMP
USERDNSDOMAIN
USERDOMAIN
username
USERPROFILE
WecVersionForRosebud8DC
windir
COMPAT_LAYER
Now I put these values into a custom enumeration:
Public Enum environVariable ALLUSERSPROFILE = 1 ' start at one APPDATA CommonProgramFiles COMPUTERNAME ComSpec FP_NO_HOST_CHECK HOMEDRIVE HOMEPATH INCLUDE LIB LOGONSERVER NUMBER_OF_PROCESSORS OS path PATHEXT PROCESSOR_ARCHITECTURE PROCESSOR_IDENTIFIER PROCESSOR_LEVEL PROCESSOR_REVISION ProgramFiles SESSIONNAME SYBASE SYBASE_OCS systemDrive SystemRoot temp TMP USERDNSDOMAIN USERDOMAIN username USERPROFILE WecVersionForRosebud8DC windir COMPAT_LAYER End Enum
The following function will return the given value, depending on the parameter:
Function GetEnviron(environName As environVariable) As String
Dim defaultEnviron As environVariable
defaultEnviron = username
On Error Resume Next
GetEnviron = Split(environ(environName), "=")(1)
If Len(GetEnviron) = 0 Then ' bad number passed to function
GetEnviron = GetEnviron(defaultEnviron)
End If
End Function
The benefit of the custom enumeration is the Intellisense we get from it. But that doesn't prevent us from selecting an invalid value. So we'll need to handle cases when an invalid value is passed to the function.
In that case, what we do is recursively call the function with a default value.
In case you were wondering, we start the custom enumeration at one instead of zero because the Environ function starts with position one (i.e. "Environ(0)" throws an error).
Get Enumeration Values
Suppose you did not know what computer you were going to use this code on, so you do not know what environment values are available. The following code will return an array of possible environment variables and their current values.
Function GetEnvironVariables() As String()
' adapted from
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=217
Dim i As Integer
Dim tempString() As String
i = 1
Do Until Environ(i) = ""
' (re)size the array
ReDim Preserve tempString(1 To i)
' assign environ value to highest array position
tempString(i) = Environ(i)
i = i + 1
Loop
GetEnvironVariables = tempString
End Function
Sample Usage
Sub test() Dim i As Long Dim results() As String results = GetEnvironVariables For i = LBound(results) To UBound(results) ' display environment variables Debug.Print Split(results(i), "=")(0) ' display variable values Debug.Print Split(results(i), "=")(1) Next i End Sub
