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.

Posted in Uncategorized

14 thoughts on “Get the UserName in VBA

  1. 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.

  2. 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.

    –

  3. 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

  4. 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?

  5. 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.”

  6. 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 “”

  7. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.