Get VBA Environment Variables in Excel
By VBA environment variables, it actually means the Windows system environment variables. These environment variables have these properties:
- Set at operating system level.
- Can be used by any application during run time.
- Loaded automatically at each login.
The well known example is the “PATH” variable that has all the path for major software executable files and reference files.
For example: Assume we type a command or file name in cmd or Run. Windows will saerch for a matching file in all the folders specified in “PATH” environment variable.
If this variable is not defined, then we have to type the complete path of each exe file in every program & command. Similar to this PATH, there are other environment variables which serve different purpose.
Since, we got some basic idea on this topic, lets see how to read environment variables using Excel vba code.
List of all Excel VBA Environment Variables
We can read a environment variable using the command VBA.Environ or VBA.Environ$.
Both serve the same purpose. We can either pass the index number of the Environment variable or its actual name.
Sub VBA_Environment_Variables() Dim strEnvironment As String, i As Double 'Get Environment Variable String & Path For i = 1 To 255 strEnvironment = Environ(i) ThisWorkbook.Sheets(1).Cells(i, 1) = strEnvironment Next 'Get Environment Value using String MsgBox Environ("PATH") End Sub
All these variables can be seen directly using the command prompt itself. in Windwos + R (Run command) type cmd. This in open the command prompt.
Now type “set” & press enter.
This will list all the system variables defined in your OS.
SET System Environment Variables using Excel VBA
when we are coding, we might want to set existing or create new environment variable for the program to work properly.
As far as I researched, no method does this perfectly. May be Microsoft has kept this as secure so that no program changes these settings accidentally.
If you are still looking for a good answer, please read these page. It has some good solid code.
This code snippet will set the new value to the mentioned Environment variable.
Most used environment variables in VBA are:
- Temp – to store temporary log files or data files created while run time
- Username – get windows logged in username
- Desktop – get path for desktop
- Onedrive – get path for the onedrive folder
- Document – path for the documents folder