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

Can't get enough VBA?
Subscribe to our free wellsrPRO VBA training program for more VBA tricks. I'll share my top VBA secrets and show you how to import my entire macro library directly into your spreadsheet with just one click.

I want to join the free wellsrPRO VBA Training program

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.

VBA Environ Variables List
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.


Comments

How do you plan on using the VBA Environ function? Let me know in the comments below!

When you’re done, please share this article with your friends on Facebook,and Twitter. Sharing articles with others helps us reach more people we can continue to write free tutorials like this one.

If you’d like to save time at work, check out my VBA Cheat Sheets for some powerful VBA tips.

I hope you’ll reach out to me via my VBA Consulting page if you need more help with your macro.


Can't get enough VBA?
Subscribe to our free wellsrPRO VBA training program for more VBA tricks. I'll share my top VBA secrets and show you how to import my entire macro library directly into your spreadsheet with just one click.

I want to join the free wellsrPRO VBA Training program