Excel

Create list of "Environ" variables for use in VBA

Ease of Use

Intermediate

Version tested with

97, 2000, 2002, 2003 

Submitted by:

mvidas

Description:

Create list of environ variables for use in VBA (and their return value for your computer) 

Discussion:

While making macros, sometimes it is important to know what the end-user's computer name, or username, or the location of their windows directory. This gives the VBA programmer list of arguments that can be used, and also an example of its use. 

Code:

instructions for use

			

Sub EnvironListing() 'Note: Usage of Environ is limited to VBA only. This macro just creates a list of ' variables and their return value, to show you what VBA can return for you ' 'Example: The following line of code, when used in a macro, will create a messagebox ' with the username signed into the computer ' Msgbox Environ("username") ' Note: using Msgbox Environ(31) will return USERNAME=computerusername, where ' Msgbox Environ("username") will return just the username Dim i As Integer, wb As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False 'To create a new workbook if nothing open, otherwise create a new sheet On Error Resume Next Set wb = ActiveWorkbook On Error GoTo 0 If wb Is Nothing Then Workbooks.Add Else wb.Sheets.Add End If 'Creates a list of environ arguments, in the form ARGUMENT=EnvironString i = 1 Do Until Environ(i) = "" Cells(i, 1) = Environ(i) i = i + 1 Loop 'Separates the column into environ argument, and return value for that argument Range("A1:A" & i - 1).TextToColumns DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="=" 'Autofit columns for easier readability Columns.AutoFit Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Sub MsgBoxCompUserName() MsgBox Environ("username") End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. From Excel, go to Tools -> Macros -> Macros...
  2. Select either EnvironListing to create a list of arguments, or MsgBoxCompUserName to show an example of its use
 

Sample File:

EnvironListing.zip 7.58KB 

Approved by mdmackillop


This entry has been viewed 253 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express