Environment Variables, Part 3 Applying the knowledge

Worksheet Functions

On thing that can be done with theis knowledge is to create worksheet functions.

...using Environ()

Function fn_strEnviron(EnvVarName As String) As String
    fn_strEnviron = Environ(EnvVarName)
End Function

... using the Shell method

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

Public 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

Universal, Transcendent Data

Data can be local (within a Function or Sub), scoped to a moduule (allowing persistence within a userform) and Global meaning it is available to all Subs and Functions as long as the code is running.  Names allow data to persist when no code is running.  See Manage Data Scope for more on this.

The APIs with the Get/Set for data creates atomic data  that that persists beyond the execution-cycle for some code.  The data exists for other workbooks and modules in other workbooks to use.

That makes it bigger than global, ergo universal.

Beyond that, the workbook that created the data can be closed and the data still be available to workbooks and code.   The workbooks that use it don't even need to be open before the creating workbook is closed. This data is available as long as the Excel session persists.

It's transcendent.

Now, for practical applications, I don't see those yet.  

The data, as  mentioned, is "atomic", meaning it has no other components...no Var types, no data structures, no objects passing from the beginning of the day to the end...only String types.