Description
The Microsoft Excel ENVIRON function will return the value of an operating system environment variable.
Syntax
The syntax for the ENVIRON function in Microsoft Excel is:
Environ ( numeric_position )
OR
Environ ( variable_name )
Parameters or Arguments
- numeric_position
- An integer value indicating the numeric position of the environment variable in the table.
- variable_name
- A string value representing the name of the environment variable.
Applies To
- Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- VBA function (VBA)
Example (as VBA Function)
The ENVIRON function can only be used in VBA code in Microsoft Excel.
Let's look at some Excel ENVIRON function examples and explore how to use the ENVIRON function in Excel VBA code.
Numeric Position
You can use the ENVIRON function to retrieve the value of an environment variable by providing the numeric position of the variable within the environment variable table.
For example:
Environ(1) Result: "ALLUSERSPROFILE=C:\ProgramData" Environ(2) Result: "APPDATA=C:\Users\totn\AppData\Roaming" Environ(3) Result: "CommonProgramFiles=C:\Program Files (x86)\Common Files"
When you supply the numeric position, it will return both the name of the environment variable as well as its value.
Here is an example of VBA code that uses a FOR loop to display the first 5 environment variable values.
For example:
Dim LPosition As Integer For LPosition = 1 To 5 MsgBox Environ(LPosition) Next LPosition
In this example, a message box would appear that displays each variable name and value combination for the first 5 environment variables.
Variable Name
You can also use the ENVIRON function to retrieve the value of an environment variable by passing in the name of the environment variable.
For example:
Environ("ALLUSERSPROFILE")
Result: "C\ProgramData"
Environ("APPDATA")
Result: "C:\Users\totn\AppData\Roaming"
Environ("CommonProgramFiles")
Result: "C:\Program Files (x86)\Common Files"
When you supply the environment variable name, it will only return the value for the variable. It does not include in the name in the result.
