Excel VBA: Function to get logged in user name (plus the Environ Function)
One commonly asked task is how can you get the name of the current user of an Excel spreadsheet into a cell in the spreadsheet. A lot of people quickly stumble across the UserName property in VBA and create a function similar to this:
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
But as you know, that only returns the name of the user according to the registration information of Office. A lot of companies set that at something generic like “User”or “Registered Owner”. That's not what you really want though right? You really want the user's name based on their Windows login. How do you get that ?.... well it's a little complicated with an API call from VBA as shown here is below:
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long
Function UserNameWindows() As String
Dim lngLen As Long
Dim strBuffer As String
Const dhcMaxUserName = 255
strBuffer = Space(dhcMaxUserName)
lngLen = dhcMaxUserName
If CBool(GetUserName(strBuffer, lngLen)) Then
UserNameWindows = Left$(strBuffer, lngLen - 1)
Else
UserNameWindows = ""
End If
End Function
Then all you have to do in the cell of choice is enter the formula:
=UserNameWindows()
But as *Mike* reminded me in a comment on my original post (this is the updated version)....
There is the Environ Function in VBA that makes this a walk in the park without the API hassles:
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
Thanks *Mike* for bringing that up....
I remember using Environ to get the current location of the “My Documents“ folder for the current user:
MsgBox Environ("USERPROFILE") + "\My Documents"
So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was... here is what it looks like: Environ Help. Not too useful I thought... So I decided to figure it out on my own and loop thru all the arguments possible with Environ. Copy and run this little routine to see all that Environ offers:
MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()
Dim nCount As Integer
nCount = nCount + 1
Do Until Environ(nCount) = ""
Debug.Print Environ(nCount)
nCount = nCount + 1
Loop
End Sub
There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more... Hopefully you will find it useful and I won't forget about it again.
****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around :)
Here's a complete list (that I know of) of the named arguments for the Environ Function:
| Environ arguments |
| ALLUSERSPROFILE |
| APPDATA |
| AVENGINE |
| CLIENTNAME |
| CommonProgramFiles |
| COMPUTERNAME |
| ComSpec |
| FP_NO_HOST_CHECK |
| HOMEDRIVE |
| HOMEPATH |
| INCLUDE |
| INOCULAN |
| LIB |
| LOGONSERVER |
| NUMBER_OF_PROCESSORS |
| OS |
| Path |
| PATHEXT |
| PROCESSOR_ARCHITECTURE |
| PROCESSOR_IDENTIFIER |
| PROCESSOR_LEVEL |
| PROCESSOR_REVISION |
| ProgramFiles |
| SESSIONNAME |
| SystemDrive |
| SystemRoot |
| TEMP |
| TMP |
| USERDOMAIN |
| USERNAME |
| USERPROFILE |
| VS71COMNTOOLS |
| WecVersionForRosebud.FF0 |
| windir |