VBA Environ in Excel — Read Windows Paths Without Hardcoding a User Folder

TL;DR — Environ("USERPROFILE"), Environ("TEMP"), Environ("USERNAME") read the variables Windows gives every program, so you never hardcode C:\Users\John\... — a path that breaks on every other machine. The catch is silent: a missing or misspelled name returns an empty string, not an error, so Environ("TMEP") & "\out.txt" becomes "\out.txt" and lands at the drive root. Treat every Environ result as possibly empty.

Sub EnvironDemo()
    Debug.Print Environ("USERPROFILE")   ' C:\Users\Ann
    Debug.Print Environ("TEMP")          ' C:\Users\Ann\AppData\Local\Temp
    Debug.Print Environ("USERNAME")      ' Ann
    Debug.Print Environ("TMEP")          ' "" — misspelled, no error, just empty
End Sub

Every Windows program is handed a small table of NAME=VALUE strings — the environment block — that records where the user's profile lives, where temporary files go, who is logged in, and more. Environ is VBA's window onto that table. It is the correct, portable way to find machine-specific folders, and it has exactly one habit you must respect: when you ask for a name it does not have, it answers with emptiness rather than complaining.

What you'll learn

The mental model — Environ reads Windows' live NAME=VALUE table for this program
The whole point: portable paths instead of hardcoded C:\Users\<name>\...
Why a missing or misspelled variable returns "" — the silent bug — and how to guard it
The two call forms: Environ("NAME") by name vs Environ(n) by numeric index
Why the block is a snapshot taken when Excel started, and what stale means here
What Environ cannot do — write variables, or read one changed after launch

The mental model: Environ reads Windows' name=value table

When Windows starts a program, it copies a set of NAME=VALUE pairs into that program's memory — the process environment. It holds entries like USERPROFILE=C:\Users\Ann, TEMP=C:\Users\Ann\AppData\Local\Temp, COMPUTERNAME=DESK-01, and dozens more. Environ simply looks a name up in that table and returns its value as text:

Dim tempDir As String
tempDir = Environ("TEMP")     ' asks the table for the value stored under "TEMP"

That is the entire model. Environ is a read of a lookup table Windows filled in before Excel opened. It is not a live query of Windows settings, not the registry, and not the current working directory — those are different things. Once you see it as "look up a name in a table," the rules below follow.

Replacing hardcoded user paths — the whole point

The reason to use Environ at all: stop writing paths that only work on your machine. This line is a classic macro that breaks the moment someone else runs it:

Open "C:\Users\John\AppData\Roaming\MyApp\log.txt" For Append As #1   ' only works for John

There is no user named John on anyone else's PC. Rebuild the path from the environment and it works everywhere:

Dim p As String
p = Environ("APPDATA") & "\MyApp\log.txt"      ' C:\Users\<whoever>\AppData\Roaming\MyApp\log.txt
Open p For Append As #1

The variables you will reach for most: USERPROFILE (the user's home folder), APPDATA and LOCALAPPDATA (per-user app data), TEMP (scratch files), USERNAME (who is logged in), COMPUTERNAME, and PUBLIC. Feed the result into Open, Dir, or a Shell command and your macro travels to any machine without edits.

A missing variable returns empty, not an error

Here is the trap that produces the most confusing failures, and it is a direct consequence of Environ being a table lookup: ask for a name that is not there, and you get an empty string — no error, no warning. A one-character typo is enough:

Dim p As String
p = Environ("TMEP") & "\out.txt"   ' TMEP doesn't exist -> "" & "\out.txt" = "\out.txt"
Open p For Output As #1            ' creates C:\out.txt at the drive root — not what you meant

Nothing complains at the Environ line, because "not found" is a legitimate answer for it. The damage shows up later and somewhere else — a file at the drive root, a path that fails to open, a folder created in the wrong place — far from the typo that caused it. Guard the ones that matter:

Dim tempDir As String
tempDir = Environ("TEMP")
If tempDir = "" Then
    MsgBox "TEMP is not set - cannot continue.": Exit Sub
End If

The rule to carry away: an empty Environ result is normal, not exceptional — check for it whenever a missing value would send a file to the wrong place. This is the environment-block echo of the same silent failure that makes so many outside-Excel calls dangerous: the answer to a mistake is quiet emptiness, not a loud stop.

Two call forms: by name and by index

Environ has two shapes, and they return different things. Pass a string and you get that variable's value. Pass a number and you get the whole raw NAME=VALUE pair at that position, which lets you enumerate the entire block:

Debug.Print Environ("PATH")     ' by name  -> the value only: C:\Windows;C:\Windows\System32;...

Dim i As Integer, entry As String
i = 1
Do
    entry = Environ(i)          ' by index -> the raw pair: "USERPROFILE=C:\Users\Ann"
    If entry = "" Then Exit Do  ' empty string marks the end of the list
    Debug.Print entry
    i = i + 1
Loop

The by-index form is how you discover what variables exist on a machine — useful for diagnostics. Note the same empty-string signal doing double duty: for a name it means "not found," and for an index it means "past the end of the list." Both loops and lookups lean on that one convention.

The snapshot trap, and what Environ can't do

Two limits catch people who expect Environ to be a live view of Windows.

It is a snapshot from startup. Excel copied the environment block when it launched. If you change a variable afterward — through System Properties, a setx command, or another tool — Environ in that same Excel session will not see the new value. It reflects the world as it was when Excel opened; a restart is what refreshes it.

It only reads; it cannot write. There is no Environ("X") = "Y". To set a variable, or to read one that was changed after startup, you need the WScript.Shell object's Environment collection via CreateObject:

Dim sh As Object
Set sh = CreateObject("WScript.Shell")
Debug.Print sh.Environment("Process")("TEMP")   ' the live process value
' sh.Environment("User")("MYVAR") = "hello"      ' this form CAN write a user variable

Environ also cannot tell you which scope a value came from — user, system, or process. Windows merges them into the one block you see, and Environ reports only the merged result. For everyday portable paths that is exactly enough; reach for WScript.Shell.Environment only when you must write a variable or read a freshly changed one.

The honest verdict: the portable-path tool, treated as possibly empty

Environ earns its place for one job and does it well: turning machine-specific folders into paths that travel. The mistakes all trace back to forgetting that a lookup can miss quietly. Four rules cover it:

Use it for portable paths → Environ("APPDATA") & "\MyApp\...", never a hardcoded C:\Users\<name>\....
Treat every result as possibly empty → a missing or misspelled name returns "", not an error. Guard the values whose absence would send a file somewhere wrong.
Know the two forms → Environ("NAME") returns the value; Environ(n) returns the raw NAME=VALUE pair and lets you enumerate the block.
It is a startup snapshot, and read-only → it will not see a variable changed after Excel launched, and it cannot set one. For that, use WScript.Shell.Environment.

Get those right and a whole family of "works on my PC, breaks on theirs" path bugs disappears — you stop naming a specific user and start asking the machine where its folders actually are.

How ExcelMaster helps

The Environ bugs that waste real time are the silent-path ones: a macro hardcoded to C:\Users\John that fails for everyone else, a typo'd variable name that drops a file at the drive root with no error, a path built from TEMP that assumed the variable was set. Each comes from either not using the environment or trusting it without checking.

ExcelMaster writes the portable-path logic correctly. Describe the destination — "save a log in the user's AppData folder," or "write scratch files to their Temp directory" — and it builds the path from the right Environ variable, validates that the value came back non-empty, and only then opens or creates the file. You describe which folder, not which user, and it produces code that runs unchanged on every machine.

Frequently asked questions

How do I get the current Windows username in VBA?

Use Environ("USERNAME") — it returns the login name of the current user, for example Ann. This is the lightweight way to identify who is running the macro without a Windows API call. If you need the full display name or domain instead of the login name, that requires an API such as GetUserNameEx, but for stamping a log or building a per-user path, Environ("USERNAME") is the standard answer.

How do I get the path to the user's Temp or AppData folder in VBA?

Environ("TEMP") returns the per-user temporary folder (typically C:\Users\<name>\AppData\Local\Temp), and Environ("APPDATA") returns the roaming application-data folder. Both are user-specific, so building paths from them avoids hardcoding C:\Users\<name>. Always check the result is not empty before using it: t = Environ("TEMP") : If t = "" Then Exit Sub, since a missing variable returns an empty string rather than raising an error.

Why does Environ return an empty string in VBA?

Because the variable name you asked for is not present in the process environment — usually a typo (TMEP instead of TEMP), a variable that only exists in a different scope, or one that was created after Excel started. Environ treats "not found" as a normal answer and returns "" with no error. Add a guard: If Environ("NAME") = "" Then .... If the variable was set after Excel launched, restart Excel or read it live through CreateObject("WScript.Shell").Environment("Process")("NAME").

Can VBA Environ set or change an environment variable?

No. Environ is read-only — there is no assignment form. To create or change a variable, use the WScript.Shell object: CreateObject("WScript.Shell").Environment("User")("MYVAR") = "value" sets a persistent user variable. Note that any change you make will not appear in Environ for the current Excel session, because Environ reads a snapshot taken when Excel launched; a restart is needed for it to see new values.

How do I list all environment variables in VBA?

Call Environ with a number instead of a name — Environ(1), Environ(2), and so on — and each returns the raw NAME=VALUE pair at that position. Loop until it returns an empty string, which marks the end of the list: i = 1 : Do : e = Environ(i) : If e = "" Then Exit Do : Debug.Print e : i = i + 1 : Loop. This enumerates the entire environment block, which is handy for diagnostics when a path is not resolving as expected.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-30.