VBA ENVIRON

What is Excel VBA ENVIRON (Environment)?

In VBA, the Environ function is used to retrieve the value of an environment variable on the host operating system. An environment variable is a dynamic-named value that can affect the way running processes behave on a computer. The Environ function takes a single argument, which is the name of the environment variable whose value you want to retrieve. Consider the following example:

The Environ function is used to get the value of the “username” environment variable, which typically contains the username of the currently logged-in user.

Here, a subroutine to find the VBA Environ userprofile is created. The environment variable of the Username is found and displayed in a message box, as shown below.

Syntax

The syntax to find the VBA Environ Variables list, is as shown.

Environ(environment-variable)

Where,

• environment-variable: Here, environment-variable is a string expression representing the name of the environment variable whose value you want to retrieve. The function returns a string containing the value of the specified environment variable. If the environment variable does not exist, an empty string is returned.

How to View Environment Variables?

These environment variables can be viewed in your system if you search for them in the Windows Search option.

Select “Environment Variables.”

You can use the VBA Environ function to find its value with the “System Variables” shown. Simply type the string in the “Variable” section in the VBA Environ function to return its value. With this, you can find the value for the VBA Variables list.

How to Use Environ Function in VBA?

To learn how to acquire the VBA Environ variables list, follow the steps below. Here, the VBA Environ function is used to get the different environment variables available in your system.

Step 1: Select the “Developer” section in the Excel toolbar. You can see it once you open the Excel Workbook.

Then, select “Visual Basic” in the Developer tab. This will open the VBA (Visual Basic for Applications) Editor.

In the VBA Editor, select “Insert” in the VBA Toolbar. Then, in the dropdown, select “Module”.

This opens a new module or a blank editor where you can start coding.

Step 2: Declare a sub-procedure to find the VBA Environ variables list.

Step 3: Use the VBA Environ function to return the first environment variable value.

In the context of the Environ function, using an index (e.g., Environ(1)) retrieves the value of a specific environment variable based on its numerical position. However, the meaning of Environ(1) specifically is not standardized across all systems.

Step 4: Similarly, find the environment variable value of the one in the second index.

Step 5: Find the VBA Environ temp variable. This is an environment variable available across all systems.

Step 6: Call the VBA Environ function with some variables available in the system.

Step 7: Find the value of program files using the Environ function.

You can find the available variables by going through your environment variables in your Windows system.

Step 8: Similarly, call other environmental variables common across systems. One of them is the “Path” variable.

Step 9: Call a variable with different values in different systems. This is to find the number of processors.

Code:

Sub ShowExamples()

    Debug.Print Environ(1) ‘ProgramData

    Debug.Print Environ(2) ‘AppData Roaming

    Debug.Print Environ(“Temp”)

    Debug.Print Environ(“ALLUSERSPROFILE”)

    Debug.Print Environ(“CommonProgramFiles”)

    Debug.Print Environ(“Path”)

    Debug.Print Environ(“NUMBER_OF_PROCESSORS”)

End Sub

Step 10: Run the sub-procedure by pressing the “F5” button or clicking the green arrow button on the Excel VBA toolbar. The output will be printed in the Immediate tab, as shown.

See some interesting examples of how to use the VBA Environ function by referring to the examples below.

Examples

In this section, you can go through some creative ways to use the VBA Environ function below.

Example #1

Suppose you want to create a way to view all your environment variables with a simple index number instead of searching for them in the Environment variables in the section.

Given the column heading, you’ll need to fill in the values. You can do this using the VBA Environ function by following the steps below. Then, given the index number, you can find the value of that index, removing the guesswork of what index calls which value. Note: All these values may differ depending on your system.

Step 1: Create a subroutine to print all the Environment variable values in your system. Then, you can search for it using the input box function in VBA.

Step 2: Define an iterative variable and run it in a FOR-loop over 50 times (depending on how many environment variables you have).

Step 3: In the FOR-loop, give the index value in the first column. Since you want to start from the second row, increment all the ‘i’ values with 1 in the Cells function.

Step 4: Print the values of these indices by calling the VBA Environ function and printing it in the second column using the Cells function.

Step 5: Continue the loop.

Step 6: Get an input variable from the user from numbers 1 to 48 (the system this was tested on has only 48 variables).

Step 7: Get the current date and store it in a variable.

Step 8: Assign the result to a variable using the VBA Cells function.

Here, the input is incremented by one since all the rows are added by 1 to handle the headers.

Step 9: Print the result in a MsgBox function.

The vbInformation is a variable constant used to print an icon of a blue “i” in the MsgBox function.

Code:

Sub GetAllEnvVar()

    Dim i As Long

    For i = 1 To 50

        Cells(i + 1, 1).Value = i

        Cells(i + 1, 2).Value = Environ(i)

    Next i

    Dim inp As Integer

    inp = InputBox(“Enter the number (from 1 to 48) to view the Environment variable”)

    res = Cells(inp, 2).Value

    MsgBox “The environment value of ” & inp & ” is: ” & res

End Sub

Step 10: Run the subroutine by pressing “F5” or by going to the Excel Workbook and selecting “Macros” under the “Developer” section.

Then, select the Macro you want to run and select “Run.”

This prints the values in a worksheet.

And open an Input Box, as shown. Enter the number you want to see the value, then click “OK.” This prints the value of the environment variable in the 8th index.

Example #2

Here, we must create a User Form, which, if we click a button, will give you information about your system, such as the user name, the system type, and so on. This can be done by using the VBA Environ, which finds the values of the environment variables. You can follow the steps on how to do this, which are explained below.

Step 1: In the VBA Editor, select “Insert” and then select “UserForm.”

This will create a blank user form, as shown below.

You can customize this UserForm to your preferences. Change the Caption of the user form.

Step 2: Add a “Label” to the UserForm and customize its properties.

Step 3: Now, add a command button and customize its properties.

Manipulate the fonts and their appearance by clicking on the “…” near the Font option in Properties.

Now, the UserForm should look like this.

Step 4: Double-click on the command button to open the VBA Editor to code what will happen if you click the “Click Here” button. It creates a private subroutine with the name of the command button.

Step 5: Find the Environment value of the operating system using the “OS” variable.

Step 6: Find the currently active user of the system with the “username” variable. Remember that variables in the Environ function are not case-sensitive.

Step 7: Find the number of processors in your system with the variable “NUMBER_OF_PROCESSORS”.

Step 8: Store all of the values in a string variable.

The vbCrLf constant in VBA is used to represent a newline character sequence. It is commonly used to insert a line break or carriage return in strings when you want to create multiline text or format the output in a way that includes line breaks.

Here,

• “vb” stands for Visual Basic.
• “CrLf” stands for Carriage Return and Line Feed, which are control characters used in text files and communication protocols to indicate the end of a line and move the cursor to the beginning of the next line.

Step 9: Print this message string in a MsgBox function. Here, use the vbInformation constant for the “i” icon in the Message Box. Also, type out the title of the message box you want it to be displayed.

Step 10: Close the UserForm by calling the Unload function.

Code:

Private Sub CommandButton1_Click()

    Dim os As String

    os = Environ(“OS”)

    Dim username As String

    username = Environ(“USERNAME”)

    Dim no_of_processors As String

    no_of_processors = Environ(“NUMBER_OF_PROCESSORS”)

    Dim message As String

    message = “Operating System: ” & os & vbCrLf & _

              “Logged-in User: ” & username & vbCrLf & _

              “Number of processors in the system: ” & no_of_processors

    MsgBox message, vbInformation, “System Information”

    Unload UserForm1

End Sub

Step 11: Run the UserForm by clicking the green arrow on the VBA Editor’s toolbar. Click on the button to view your system’s information.

After clicking on the “Click Here” button, you will get a Message Box popup showing you your system information.

With this, you have successfully created a UserForm to view your system settings.

Important Things To Note

• Check the length of the returned string to verify the existence of the environment variable. An empty string indicates that the variable does not exist.
• Use the correct and case-insensitive name of the environment variable. Environment variable names are not case-sensitive.
• Be aware that the availability and behavior of environment variables can vary between different operating systems. Test your code on the target systems.
• Avoid trying to hardcode environment variable names without verification. Variable names may vary across systems, and hardcoding may lead to errors.
• Don’t overlook error handling in VBA when working with the Environ function.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a Guide to VBA ENVIRON. Here we learn How to use Environ Function in VBA, with step by step examples & how to view environment variables? You can learn more from the following articles –

• VBA Screen Update
• VBA ISNULL
• VBA RoundUp