Environmental Variables, Part 1: Accessing the Info

The Question

One question to Allen was from a reader who wanted to know how to access UserName and ComputerName in a VBA procedure.  Following is my response to Allen, with editing and additional information.

Method 1: Environ() function

Sub SpecificEnviron()

    Dim strUserName As String

    Dim strComputerName As String

    strUserName = Environ("UserName") ' The argument is not case sensitive

    strComputerName = Environ("ComputerName")

End Sub

Finding Other information

Depending what one is looking for, there is additional information.  To see all of the Environment Variables available through Environ() use the following code.

Sub WhatEnviron()

    Dim ws As Worksheet

    Dim i As Double

    Set ws = ThisWorkbook.Worksheets(1)

    ws.Range("A1") = "Index"

    ws.Range("B1") = "Name"

    ws.Range("C1") = "Value"

    For i = 1 To 255 ' There is a maximum 255 Windows Environment Variables per error messages

        If Len(Environ(i)) > 0 Then

            ws.Range("A" & i + 1) = i

            ws.Range("B" & i + 1) = Left(Environ(i), InStr(1, Environ(i), "=") - 1)

            ws.Range("C" & i + 1) = Right(Environ(i), Len(Environ(i)) - Len(ws.Range("B" & i + 1)) - 1)

        Else

            Exit For

        End If

    Next i
End Sub

Method 2: Shell Object

The following code establishes a shell and to allow reading the needed value(s) from either the Windows User Environment Variables table or from the Windows System Environment Variables table.

Sub ChangeWindowsUserEnvVar()

    Dim objShell As Object

    Dim colSysEnvVars

    Dim colUsrEnvVars

    Dim strComputerUserName As String

    Dim strUserName As String

    Set objShell = CreateObject("WScript.Shell")

    Set colSysEnvVars = objShell.Environment("System")

    Set colUsrEnvVars = objShell.Environment("User")

    strComputerName = colSysEnvVars.Item("ComputerName")

    strComputerUserName = colUsrEnvVars.Item("UserName")

End Sub

Surprise!  

To see whether Method 1 and Method 2 were synonymous the following test was performed

• WhatEnviron was run
• In column D the following function was executed

Function fn_strEnvVar(EnvVarName As String, Optional SystemEnvVars As Boolean) As String
    Dim objShell As Object
    Dim colEnvVars

    Set objShell = CreateObject("WScript.Shell")    
    Set colEnvVars = IIf(SystemEnvVars, objShell.Environment("System"), objShell.Environment("User"))    
    fn_strEnvVar = colEnvVars.item(EnvVarName)

End Function

 The information sets for Method 1 and Method 2 do not match!

• On my system, the Shell Object method for Windows System Environment Variables returns 25 data items and 3 Windows User Environment Variables; Environ() 50.
• The 25 Windows System Environment Variables match, by name, with 25 of the Environ() variables.
• The  Windows User Environment Variables match one of the  Environ() variables.
•  Of the 25 Windows System Environment Variables that are in common, all 25 of the Environ() variables match in value except 
• When a Windows System Environment Variable is changed after Excel has started up, i.e the values in the Environ() variables match what was in the Windows System Environment Variables when Excel was initiated.
• When  a Windows User Environment Variable and a Windows System Environment Variable have the same name. the Windows User Environment Variable is used
• When a Windows Environment Variable, User or System, is dependent on the value of another Windows Environment Variable, e.g. "%USERPROFILE%\AppData\Local\Temp", Environ() resolves the included portion in what it returns, e.g. "C:\Users\<username>\AppData\Local\Temp, but the Shell Object method returns the literal from the Windows Environment Variable
• Environ("PROCESSOR_ARCHITECTURE") returns "x86"
  The Shell Object value for PROCESSOR_ARCHITECTURE is "AMD64"
  Environ("PROCESSOR_ARCHITEW6432") returns "x86"The Shell Object value for PROCESSOR_ARCHITEW6432 is blank, aka unused

Method 3: Application.UserName

Another possibility would be to use the value in Application.UserName.

Someone may ask, "Why not go directly to this one?  It's the simplest to access, right there on the Object Model; no shells, no arcane functions with literal character strings.

Quite simply, the reason this one is not as good as the others is that each user has the responsibility for putting this information into Excel, and other Office products.  This means there is little consistency from user-to-user and many people simply put in their first initial,