You can get at all sorts of system information within Visual Basic for Applications by using environment variables. This article will show you how to get at your user's name, computer name and much more besides.

An environment variable has the form Environ("VariableName").

Let's start with an example to do with the thing most precious to you: your good name, as held in the UserName environment variable.

Detecting a user's name

Suppose you - somewhat frivolously - want to greet users of your workbooks by name, and customise the appearance of their worksheets too. Here's how to do this. First attach code to the open event for the workbook:

In VBE, double-click on ThisWorkbook for a workbook to attach code to its events.

Choose to attach events to the workbook object. You can then choose Workbook from the list which appears at the top of the module:

You could now write some code to detect the name of the person opening the workbook, and react accordingly. Here's a suggestion:

Private Sub Workbook_Open()


'the name of the current user


Dim UserName As String


'on opening, find out who this is (and convert to lower case)


UserName = LCase(Environ("UserName"))


'if this is one of two specified users...


Select Case UserName


Case "stephen.smith", "tom.jerry"


'display a greeting and change default cell colour


MsgBox "Hello, Tom/Jerry"


Styles("Normal").Interior.Color = RGB(240, 255, 255)


Case Else


'otherwise, just continue


End Select


End Sub

If Tom or Stephen log on, there will be a MsgBox that will welcome, by a greeting message, these users.

A typical use of the UserName environment variable would thus be to get a workbook to behave differently for different people.

Listing other environment variables

Now that we've caught the environment variable bug, what else is available? Let's find out by running this code to loop over the collection of all environment variables:

Sub ListEnvironmentVariables()


'each environment variable in turn


Dim EnvironmentVariable As String


'the number of each environment variable


Dim EnvironmentVariableIndex As Integer


'get first environment variables


EnvironmentVariableIndex = 1


EnvironmentVariable = Environ(EnvironmentVariableIndex)


'loop over all environment variables till there are no more


Do Until EnvironmentVariable = ""


'get next e.v. and print out its value


Debug.Print EnvironmentVariableIndex, EnvironmentVariable


'go on to next one


EnvironmentVariableIndex = EnvironmentVariableIndex + 1


EnvironmentVariable = Environ(EnvironmentVariableIndex)


Loop


End Sub

This will list out all of the environment variables in your immediate window. If you can't see this, choose this menu option:

Choose the following menu option, in the following figure, (Immediate window) to show another window within VBA, to which you can write information using the Debug.Print statement.

Here's the end of some typical output from running this procedure:

Other useful environment variables

So what else is useful? Here is a selection of environment variables that you may find useful:

The last one is particularly useful when choosing where to open files from, or where to save them to. For example, the following macro will ask you to open an Excel workbook from the desktop of the current user:

Sub ChooseDesktopWorkbook()


'the path to user's desktop


Dim DesktopPath As String


'find out where user's desktop is


DesktopPath = Environ("UserProfile") & "\Desktop\"


'create a new file dialog box


Dim fd As FileDialog


Set fd = Application.FileDialog(msoFileDialogOpen)


'tell it where to look for files initially


fd.InitialFileName = DesktopPath


'dialog box caption


fd.Title = "Choose Excel workbook to open"


'look just for Excel workbooks


fd.Filters.Clear


fd.Filters.Add "Excel workbooks", "*.xlsx"


'show the dialog box on screen


fd.Show


End Sub

Clever stuff - this is what you might see after running this macro:

You could replace the path with the following to use MY DOCUMENTS instead:

'find out where user's MY DOCUMENTS folder is


MyDocumentsPath = Environ("USERPROFILE") & "\Documents\"