Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to use environment variables like USERNAME in VBA |
---|
You can get at all sorts of system information within Visual Basic for Applications by using environment variables - this blog shows you how to get at your user's name, computer name and much more besides. |
In this blog
I thought I'd blog on how you can pick up on the values of environment variables in VBA, as there's some useful stuff hidden away there. Note that we offer a full range of VBA training courses.
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.
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 VBA, double-click on ThisWorkbook for a workbook to attach code to its events.
You can then choose Workbook from the list which appears at the top of the module:
Choose to attach events to the workbook object.
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 Wise Owls ...
Select Case UserName
Case "geena.davis", "susan.sarandon"
'display a greeting and change default cell colour
MsgBox "Hello, Thelma/Louise"
Styles("Normal").Interior.Color = RGB(240, 255, 255)
Case Else
'otherwise, just continue
End Select
End Sub
If Geena or Susan log on, this is what they will now see:
A greeting message ... | ... followed by coloured cells. |
A typical use of the UserName environment variable would thus be to get a workbook to behave differently for different people.
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 this menu option 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:
The last few environment variables on my machine.
So what else is useful? Here is a selection of environment variables that you may find useful:
Variable | Notes |
---|---|
Environ("COMPUTERNAME") | The name of your computer. |
Environ("USERDOMAIN") | The domain you're logged on to (eg WISEOWL). |
Environ("USERPROFILE") | The path to where your files are stored. |
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 can open up any workbooks from my desktop!
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\"
If you know VBA already, but want to become a guru, we run a two-day advanced VBA course which may help!
I hope this has helped someone!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.