Environment Variables in VBA

VBA Environment Variables

Environment Variables in VBA

We have an environment block that contains a set of environment variables and their values. You can get at all the system information using VBA by using environment variables. Here are the different Environment variables, you can find the below table. These Environment variables especially useful when you want to know different types of access depending on our requirements. We can also create custom environment variables.

List of Environment Variables in VBA:

Here are the list of environment variables which are shown in below specified table. We have some more environment tables, which is depending on operating system.

N0 Environment Variable N0 Environment Variable
1 ALLUSERSPROFILE 23 PROCESSOR_ARCHITEW6432
2 APPDATA 24 PROCESSOR_IDENTIFIER
3 CommonProgramFiles 25 PROCESSOR_LEVEL
4 CommonProgramFiles(x86) 26 PROCESSOR_REVISION
5 CommonProgramW6432 27 ProgramData
6 COMPUTERNAME 28 ProgramFiles
7 ComSpec 29 ProgramFiles(x86)
8 DriverData 30 ProgramW6432
9 FPS_BROWSER_APP_PROFILE_STRING 31 PSModulePath
10 FPS_BROWSER_USER_PROFILE_STRING 32 PUBLIC
11 HOMEDRIVE 33 RegionCode
12 HOMEPATH 34 SESSIONNAME
13 LOCALAPPDATA 35 SystemDrive
14 LOGONSERVER 36 SystemRoot
15 NUMBER_OF_PROCESSORS 37 TEMP
16 OneDrive 38 TMP
17 OnlineServices 39 USERDOMAIN
18 OS 40 USERDOMAIN_ROAMINGPROFILE
19 Path 41 USERNAME
20 PATHEXT 42 USERPROFILE
21 platformcode 43 windir
22 PROCESSOR_ARCHITECTURE

Macro to get Environment Variables using VBA

Here is the procedure to show all the environment variables. You can find below specified procedure to know all about environment variables. You can display output in the immediate window or any Specified worksheet. The below macro procedure will print out the value of the environment variables and its name.

'List of Environment Variables in VBA
Sub vbaf1_List_Environment_Variables()

    'Variable declaration
    Dim iCnt As Integer
    Dim sEnvVariable As String
    
    'loop through all environ values from 1 to 250
    For iCnt = 1 To 250
        ' Get the environment variable
        sEnvVariable = Environ(iCnt)
        
        'Check for the Environment variable
        If Len(sEnvVariable) > 0 Then
            Debug.Print iCnt, Environ(iCnt)
        Else
            Exit For
        End If
    Next
End Sub

Output: You can check output of above code in Immediate Window.
Shortcut to View Immediate Window: ‘Ctrl + G’
Please find the screen shot of the immediate window for your reference.
Environment Variables List

Examples on Environment Variables

You can find one more simple example to get the Environment variable names based on their input.

Get location of all users using Environ function

Here is the example to get location of all users using Environ function.

'Get location of all users using Environ Function
Sub Get_AllUsers_Location()

    MsgBox Environ("ALLUSERSPROFILE"), vbInformation, "Windows Directory"

End Sub

Output: Here is the output screenshot for the above specified code for your reference.
All Users Location

Get location of all applications temporary files using Environ function

Here is the example to get location of all applications temporary files using Environ function.

'Get location of all applications temporary files
Sub Get_Local_App_Data()

    MsgBox Environ("LOCALAPPDATA"), vbInformation, "Location of all applications temporary files"

End Sub

Output: Here is the output screenshot for the above specified code for your reference.
Application Temporary Files

Get location of system folder using Environ function

Here is one more example to get location of system folder using Environ function.

'Get location of system root folder
Sub Get_System_Root_Folder()

    MsgBox Environ("SystemRoot"), vbInformation, "Location of System Root Folder"

End Sub

Output: Here is the output screenshot for the above specified code for your reference.
System Root Folder

Get location of windows directory using Environ function

Here is another example to get location of windows directory using Environ function.

'Get Windows Directory using Environ Function
Sub Get_Windows_Directory()

    MsgBox Environ("windir"), vbInformation, "Windows Directory"

End Sub

Output: Here is the output screenshot for the above specified code for your reference.
Windows Directory

Know More about VBA Environ Function:

Please click on below link to Know more details about the VBA Environ Function.

VBA Environ Function

Leave a Comment

Your email address will not be published. Required fields are marked *