List All Environment Variables with VBA Environ

The VBA Environ function grabs information about your operating system and returns the information as a string. The Environ function is useful for customizing your macros so they behave differently based on your user’s operating system configuration.

For example, you can query the username of the person logged in to restrict unauthorized users from running your macro. Your macro might look something like this:

Sub MyMacro()
If Environ$("USERNAME") <> "Ryan" Then
    MsgBox "You are not authorized to run this macro.", vbCritical, "Unauthorized access"
    Exit Sub
End If
'
'your macro goes here
'
End Sub

Similarly, if your macro requests your user specify preferences, you can store these preferences in a consistent place on each user’s machine by creating your own subfolder and file under their default APPDATA folder. Simply create a folder using the Environ$("APPDATA") function and write their preferences to a file.

To list all the operating system environment variables on your computer, run this VBA Environ macro:

List All VBA Environ Variables

Sub AllEnvironVariables()
    Dim strEnviron As String
    Dim VarSplit As Variant
    Dim i As Long
    For i = 1 To 255
        strEnviron = Environ$(i)
        If LenB(strEnviron) = 0& Then GoTo TryNext:
        VarSplit = Split(strEnviron, "=")
        If UBound(VarSplit) > 1 Then Stop
        Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = i
        Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1).Value = VarSplit(0)
        Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1).Value = VarSplit(1)
TryNext:
    Next
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

VBA Environ Tips

This macro lists all the environment variables associated with your operating system. It returns the index number of each environment variable, the name of the environ variable, and the string associated with your particular operating system.

The Environ variable is printed in Column B. This is the column you should look at to find out what other environment variables you’d like to call. The results you get are highly dependent on the configuration of your operating system. Two different computers could export different queriable variables.

Excerpt from the VBA Environ Variables List

To get you started, let’s say we wanted to store the name of a user’s computer into the variable str1. To do that, we’d simply write a line of code, like this:

str1 = Environ$("COMPUTERNAME")

The dollar sign $ placed after the Environ keyword simply tells the macro to return a string. Imagine you were performing string manipulation on the Environ output, but your computer name was numeric. Forcing the VBA Environ function to return a string (by using the dollar sign) helps prevent errors.

In future tutorials, we’ll start tackling several of these VBA Environ variables and we’ll show you how you can use them to write better macros.

How do you plan on using the VBA Environ function? These shortcuts can be really helpful!

I hope you’ll take a minute to subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.