Environment Variables, Part 2: Adding and Changing

In Environmental Variables, Part 1, I answered the question "How do we get to environmental variable values, e.g. UserName and ComputerName, in VBA ?"  The short answer is "The Environ() functionor a Shell."

This led me to ask,  "Is it possible to set the Environment Variables from inside an Excel VBA subroutine?"

While the question is straightforward and is answered below, some of the findings are unexpected as are the opportunities and risks implied by those findings.

Setting Environmental Variables

Method 1: Environ() Function (Failed)

After a bit of research into the table of Environment Variables, I had hoped the Environ() function could also be used to establish new Environment Variables and/or update existing Environment Variables.

That is not possible, but the good news (or so it seemed) was that the Environ() function gave exactly the values see in the Windows UI for inspecting and changing Environment Variables.

Method 2: APIs with Get and Set 

In my research I found discussion regrading using APIs for setting, resetting and reading Environment Variables, a Get and Set pair of methods.   What was established by the "Set" was retrievable with the "Get".

Private Declare Function GetEnvironmentVariable Lib "kernel32" _
    Alias "GetEnvironmentVariableA" _
    (ByVal lpName As String, _
     ByVal lpBuffer As String, _
     ByVal nSize As Long) As Long

Private Declare Function SetEnvironmentVariable Lib "kernel32" _
    Alias "SetEnvironmentVariableA" _
    (ByVal lpName As String, _
    ByVal lpValue As String) As Long

Sub AddAndMsgAPIEnvironVar()

    SetEnvironmentVariable "TestValue", "This is something new!".

    MsgBox "GetEnvironmentVar: " & GetEnvironmentVar("TestValue")

End Sub

Surprise  #1

However, some testing and additional research showed there is a disconnect between this pair of methods and what I had known to this point to be the Environment Variables,  

• The "Set" method did not change what was in the Windows UI for inspecting and updating Environment Variables.
• The Get method did not return the same values as the Environ() method.

The Assumption that when people discuss VBA and Environment Variables they all mean the same thing.  Therefore, we need to differentiate these two

• Windows Environment Variables
• <Mystery> Environment Variables

Surprise #2

Further testing show these <Mystery> Environment Variables were remarkably persistent.  

• If the Set method is invoked in a called subroutine ends, and thus returning to the calling subroutine, these <Mystery> Environment Variables are as set in the called subroutine and no parameters are used.  This is true to the top of the calling ladder
• When the initially triggered subroutine has completed and a new macro is triggered, the <Mystery> Environment Variables are shown to have retained their values
• If changing to a subroutine in another workbook (not called, but trigger separately) the values for the <Mystery> Environment Variables are still there.
• When running VBA in a second workbook, one in which the <Mystery> Environment Variable had not been set, the Get method still returns the property set previously.

For lack of finding a different name for these Environment Variables, I now refer to them as 

• Windows Environment Variables 
• Excel Session environment Variables

Method 3: Shell Object

The third method to change or add Windows environment variables is to use a Shell object to communicate to the Windows environment.  

Sub ChangeWindowsUserEnvVar()

    Dim objShell As Object

    Dim colSystemEnvVars ' Used for referencing/changing/adding System Environment variables

    Dim colUserEnvVars ' Used for referencing/changing/adding User Environment variables

    Set objShell = CreateObject("WScript.Shell") 

    Set colSystemEnvVars = objShell.Environment("System") 

    Set colUserEnvVars = objShell.Environment("User")

    colUserEnvVars.item("TestValue") = "Now is good"

    colUserEnvVars.item("TestValue2") = "Later is better"

    MsgBox "colUserEnvVars(TestValue): " & colUserEnvVars.item("TestValue") _

           & Chr(10) & "colUserEnvVars(TestValue2): " & colUserEnvVars.item("TestValue2") _

End Sub

WARNING!!!

This method has an added opportunity, with inherent risks, of course: The  statements getting the value from the Windows Environment Variables table entry and putting it into a VBA variable can be reversed

colSystemEnvVars.item("ComputerName")" = strComputerName

Assuming the person running the code is authorized to change Windows System Environment Variables, the computer could be made inop, or at least not able to communicate on corporate networks that permit only computers with registered names to log in. 

Therefore changing/adding Windows Environment Variables should be limited to only Windows User Environment Variables, and then very carefully.

Testing

To see if the changes made using the Shell method really did occur (remember the surprise when API method did not), I looked at the Windows UI for Environment Variables.  Sure enough, new Environment variables were created and existing ones changed.

Surprise #3

The next test was to see if the Environ() function would return the new values.  It did not.  Further research indicated that the Windows Environment Variables are read into the Excel session at start-up, meaning that changes to a Windows Environment Variable can only be seen when using the Shell Object method.  

That means there are 3 types of Environment Variables

• Live Windows Environment Variables 
• Loaded Windows Environment Variables 
• Excel Session Environment Variable

What does this mean?  It means the SetEnvironmentVariable function could be used to establish global variables or constants.  Not recommended for several reasons, but it could be done.