Get the UserName in VBA
There are generally two user names that you can access via VBA. The first is the user name that is entered under Tools>Options>General. This one is accessed by using the UserName property of the Application object.
Function GetXLUserName() As String
‘Returns the user name entered under
‘Tools>Options>General
GetXLUserName = Application.UserName
End Function
The second is the Windows user name. To get to that one, you need an API (Application Programmer’s Interface) function call. First define the API function like this
Declare Function apiGetUserName Lib “advapi32.dll” Alias _
“GetUserNameA” (ByVal lpBuffer As String, _
nSize As Long) As Long
Then use this API function call in your own function
Function GetUserName() As String
Application.Volatile
Dim sBuff As String * 25
Dim lBuffLen As Long
lBuffLen = 25
apiGetUserName sBuff, lBuffLen
GetUserName = Left(sBuff, lBuffLen - 1)
End Function
If you want to better understand how this API function call works, read Robert Rosenberg’s excellent post on the subject.
kevboy:
I have a tip for Mr. Robert Rosenberg.
[=len(”Robert”)] yields a value of 6. That would make his explanation a little stronger. But I guess a fault of mine is getting caught up in the details. I think the rest of the explanation is very good.
Good topic for a post, by the way. That function comes in very handy when you have multiple users making changes — it’s a good way to see who’s done what.
17 June 2004, 9:21 amJamie Collins:
I’ve got a copy of a Tom Ogilvy post somewhere which returns the user’s full name e.g. ‘Jamie Collins’ rather than my JCOLLINS username. Very useful bit of code.
Jamie.
–
17 June 2004, 11:06 amJake Marx:
Jamie,
Dev Ashish has code on his site to grab the user’s full name:
http://www.mvps.org/access/api/api0066.htm
-Jake
21 June 2004, 12:34 pmJan Krooshof:
Is it also possible to read the Novell Netware username? How is that done, and how can I use that in VBA for Excel?
Thank you very much,
Jan
9 November 2004, 6:07 amMichael Doleman:
What’s up with that `Application.Volatile’ thing? I am using this code within Access 2003, and all I get with the Application.Volatile line is a `method or data member not found’ on the .Volatile part. The declaration and function pair work just fine without that line. I’ve never seen that .Volatile thing before. Should I be including some reference with my app, or is there some equivalent in Access ‘03 of which I ought to be aware?
29 November 2004, 1:41 pmKira Sofie:
I enjoy reading through this informal place. I will surely visit you again to see if anything new appears on it.
20 December 2004, 2:16 amGood luck for the future.
Janine Marie:
I like your site. And I wish you luck, success and a lot of visitors here. Have a nice day.
14 February 2005, 3:56 amDave:
The windows logon name can also be grabbed by
MsgBox Environ(”username”)
Cheers
Dave
14 February 2005, 4:31 amanon:
Michael, Application.Volatile is an Excel-specific function. You can ignore it safely if using this function in Access VBA.
From the MS help file for Application.Volatile: “Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it’s not inside a user-defined function used to calculate a worksheet cell.”
9 March 2005, 9:30 amgopal sharma:
i used the same code in xp, but failed obtain username
28 May 2005, 8:02 amRob van Gelder:
Gopal,
How do you mean “failed” ?
When copying code from this post you’ll need to manually convert the quotes for VB - something about this site loves to make the quotes pretty:
eg. “” is not the same as ""
28 May 2005, 8:32 pmMorten:
I’m a complete noob at this. But I know my way around the computer so if someone would do me the honour of setting up a 1-2-3 description, I would ver much appreciate it.
2 February 2006, 8:28 amBo:
MsgBox Environ(”UserName”) does the trick.
(Case matters.)
2 July 2008, 1:43 am