EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Information Functions VBA Environ
Secondary Sidebar
VBA Information Functions
  • VBA Information
    • VBA Rename Sheet
    • VBA Worksheets
    • VBA IsEmpty
    • VBA Active Cell
    • VBA Workbook
    • VBA ISNULL
    • VBA Activate Sheet
    • VBA Cells
    • VBA Workbook Open
    • VBA Protect Sheet
    • VBA Unprotect Sheet
    • VBA Delete Sheet
    • VBA Editor
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Environ

VBA Environ

By Ashwani JaiswalAshwani Jaiswal

VBA Environ

Excel VBA Environ Function

VBA Environ where Environ stands for Environment is used for getting any information that is there in Operating System. The Excel VBA Environ function returns the value such as path, location, name, extension available in the system. Apart from this Environment (Operating System) also holds the information about User Name, Authorization, Folder name, etc.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

VBA Environ considers only expression as an Input. Where that expression can be anything. Below is the syntax of Environ.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of VBA Environ

How to Use Environ Function in Excel VBA?

Below are the different examples to use the Environ function in Excel VBA.

You can download this VBA Environ Excel Template here – VBA Environ Excel Template

VBA Environ – Example #1

There are different ways to get operating system information through VBA Environ. One of them we will see in this example. Below is the list of possible authorized users of my system which can be TRUE or FALSE. We have mentioned these names in Sheet2.

VBA Environ Example 1-1

Follow the below steps to use Environ function in VBA:

Step 1: Now with the help of VBA Environ, we will find which of these is the correct authorized user name of the current operating system. Sheet1 is the place where we will find the correct user name of the system in cell C3.

VBA Environ Example 1-2

Step 2: Under the header “Authorized?” We will see if the user name is authorized or not with the If and CountIf function as shown below. If the user name is valid and authorized then we will get YES, if valid but not authorized we will get No. For both unauthorized and invalid user name, we will still get No.

VBA Environ Example 1-3

Step 3: Now in excel worksheet, create a Command button that is available in the Developer tab under the Insert menu’s Active X Control as shown below.

VBA Environ Example 1-4

Step 4: Now create a command button anywhere in the sheet. We can choose any Shapes over the command button as well.

command button Example 1-5

Step 5: Right-click on the created button and go to the Properties option.

Properties Example-1-4-1

Step 6: Under that, we can change the default name to anything. Here we have chosen Run Environ as shown below.

Run Environ Example 1-6

Step 7: Now right-click on the same button and select View Code to go in the VBA window.

VBA Environ Example 1-7

Step 7: In that, we will get the pre-built sub procedure of the command button.

Code:

Private Sub CommandButton2_Click()

End Sub

VBA Environ Example 1-9

Step 8: Select the sheet where we want to apply the Environ function.

Code:

Private Sub CommandButton2_Click()

Sheets("Sheet1")

End Sub

Environ function Example 1-10

Step 9: Then select the range cells where we want to see the output.

Code:

Private Sub CommandButton2_Click()

Sheets("Sheet1").Range("C3") =

End Sub

VBA Environ Example 1-11

Step 10: Now use Environ function with the field which we want to get. Here we want to see USERNAME.

Code:

Private Sub CommandButton2_Click()

Sheets("Sheet1").Range("C3") = Environ("USERNAME")

End Sub

USERNAME Example 1-12

Step 11: Now open an IF-ELSE loop where write the condition if range cell E3 is YES then give me the message as Authorized User.

Code:

Private Sub CommandButton2_Click()

Sheets("Sheet1").Range("C3") = Environ("USERNAME")
If Sheets("Sheet1").Range("E3") = "Yes" Then
MsgBox "Authorized User!"

End Sub

VBA Environ Example 1-13

Step 12: In Else give me the message as Unauthorized User as shown below.

Code:

Private Sub CommandButton2_Click()

Sheets("Sheet1").Range("C3") = Environ("USERNAME")
If Sheets("Sheet1").Range("E3") = "Yes" Then
MsgBox "Authorized User!"
Else
MsgBox "Unathorized User"
End If

End Sub

Unauthorized User Example 1-14

Step 13: Now we will compile our code step by step by pressing the F8 function key to see if there is any error or not. If all is good, then exit from the VBA window and unselect the Design mode from the Developer tab as shown below.

Design mode Example 1-16

Step 14: Now we will run our macro by clicking on the command button named “Run Environ”. We will see, at C3, the authorized user name is printed as Game2 and then the same is confirmed twice. Once by the message box and other at cell E3.

VBA Environ Example 1-15

This means that the user name which is mention at Sheet2 as Game2 is valid and authorized both.

VBA Environ – Example #2

There is another way by which we can get the complete information about the operating system which we have. This works in the same manner as we have seen in example-1. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab.

Insert Module

Step 2: Now under Option Explicit, we will define the functions which we want to see.

Code:

Option Explicit

Function CompName()

End Function

VBA Environ Example 2-1

Step 3: Suppose, if you want to see the Computer Name, use any word which defines Computer. Here we have chosen CompName and use Environ function with “Computer Name”.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

VBA Environ Example 2-2

Step 4: Let’s open another function by which we will see the temporary file path as shown below.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
End Function

VBA Environ Example 2-3

Step 5: Now again use any word which defines the Temp file path, like TempDir and insert Environ function with TEMP.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
TempDir = Environ("Temp")
End Function

VBA Environ Example 2-4

Step 6: After that open the subprocedure in which we will use both functions which we defined above to see their details.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
TempDir = Environ("Temp")
End Function

Sub Enviro()

End Sub

VBA Environ Example2-5

Step 7: Define a variable as Long in which we will see the details.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
TempDir = Environ("Temp")
End Function

Sub Enviro()

Dim A As Long

End Sub

VBA Environ Example 2-6

Step 8: Open a For-Next loop and give the length to variable A how long will be the character limit. Here we have set it as 50.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
TempDir = Environ("Temp")
End Function

Sub Enviro()

Dim A As Long
For A = 1 To 50

End Sub

VBA Environ Example 2-7

Step 9: Now use Debug Print for variable A and with Environ function as shown below.

Code:

Option Explicit

Function CompName()
CompName = Environ("ComputerName")
End Function

Function Temp()
TempDir = Environ("Temp")
End Function

Sub Enviro()

Dim A As Long
For A = 1 To 50
Debug.Print Environ(A)
Next

End Sub

VBA Environ Example 2-8

Step 10: Compile each step of the code and open an immediate window from the View menu tab.

Immediate Window Example 1-10

Step 11: Now run the code by clicking on the Play button located below the menu bar.

Play button Example 2-11

Step 12: We will see, in the immediate window, complete operating system details related to the functions which we have declared are fetched.

VBA Environ Example 2-9

Pros of Excel VBA Environ Function

  • It seems complex, but it is easy to implement.
  • It gives the complete computer and operating system details.
  • We can list out any specific detail as well as per our requirements.
  • We can also see who are the authorized user to use and edit the system.

Things to Remember

  • The VBA Environ is not limited to the process shown in the above examples. We can get many more operating system details such as any path, location, folder or file, even any file size as well with the help of VBA Environ.
  • If any value which we input is not an environment string table, then we will end up getting Zero-length string.
  • Once we are done with coding, save the file in macro enable excel format to avoid losing the code.

Recommended Articles

This is a guide to VBA Environ. Here we discuss how to use Environ Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA IF Statements
  2. VBA Sort
  3. VBA While Loop
  4. VBA Counter
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more