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 spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
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 = 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.