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.
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.
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.
Jamie,
Dev Ashish has code on his site to grab the user’s full name:
http://www.mvps.org/access/api/api0066.htm
-Jake
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
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?
I enjoy reading through this informal place. I will surely visit you again to see if anything new appears on it.
Good luck for the future.
I like your site. And I wish you luck, success and a lot of visitors here. Have a nice day.
The windows logon name can also be grabbed by
MsgBox Environ(“username”)
Cheers
Dave
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.”
i used the same code in xp, but failed obtain username
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 “”
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.
MsgBox Environ(“UserName”) does the trick.
(Case matters.)
For Outlook
ThisOutlookSession.Session.CurrentUser.Name