totn Excel Functions

MS Excel: How to use the ENVIRON Function (VBA)

This Excel tutorial explains how to use the Excel ENVIRON function with syntax and examples.

Description

The Microsoft Excel ENVIRON function will return the value of an operating system environment variable.

The ENVIRON function is a built-in function in Excel that is categorized as an Information Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

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.

Returns

The ENVIRON function returns a string value.

Applies To

  • Excel for Office 365, Excel 2019, 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.

Share on: