User Initials in Excel

In Excel, you can get the Username by using Application.UserName. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user’s initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via that object model. That seems a little extreme to me.

This is what I’m using. It gets the first character of, up to, the first three words in Application.Username.

Public Function UserInitials() As String
   
    Dim vaNames As Variant
    Dim sInit As String
    Dim lMax As Long
    Dim i As Long
   
    vaNames = Split(UCase(Application.UserName), ” “)
   
    lMax = Application.WorksheetFunction.Min(2, UBound(vaNames))
   
    For i = 0 To lMax
        sInit = sInit & Left$(vaNames(i), 1)
    Next i
   
    UserInitials = sInit
   
End Function

Some testing:

Posted in Uncategorized

22 thoughts on “User Initials in Excel

  1. And in the off chance that you need to do a whole bunch of names stored in a worksheet, here’s a formula solution:

    =LEFT(A1)&IFERROR(MID(A1,FIND(” “,A1)+1,1),””)&IFERROR(MID(A1,FIND(” “,A1,FIND(” “,A1)+1)+1,1),””)

    This uses the Excel 2007 IFERROR function. The equivalent formula for earlier versions is a lot messier.

  2. Dick, this doesn’t work in many corporate environments, where the Excel username is set to the company name. Even if it isn’t, PCs get swapped around, and users don’t bother resetting the name, so the username may not be the current user anyway.

    The code below uses an API call to get the Windows login name,which should always give you the actual user name.

    Declare Function WNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

    Function GetUser() As String
    Dim S As String * 255
    S = Space(255)
    Call WNetGetUser(vbNullString, S, 255&)
    GetUser = Left$(S, InStr(S, vbNullChar) – 1)
    End Function

  3. dermot: That’s just what I need. After I wrote that someone tried to “sign” a worksheet from a different computer and they were shocked when it didn’t use their initials. If I can get the Windows name, it will follow people around.

  4. I use this (UserFullName) to get the users full name from Active Directory. Just remember to change the :

    Private Declare Function apiGetUserName Lib “advapi32.dll” _
        Alias “GetUserNameA” (ByVal lpBuffer As String, nsize As Long) As Long

    Function fOSUserName() As String
      Dim lngLen As Long, lngX As Long
      Dim strUserName As String
       
      strUserName = String$(254, 0)
      lngLen = 255
      lngX = apiGetUserName(strUserName, lngLen)
      If lngX  0 Then
        fOSUserName = Left$(strUserName, lngLen – 1)
      Else
        fOSUserName = “”
      End If
    End Function

    Function UserFullName()
      Dim MyUser As IADsUser
     
      On Error Resume Next
       
      Set MyUser = GetObject(“WinNT:///” & fOSUserName & “,user”)
         
      UserFullName = MyUser.FullName
    End Function

    – Asser

  5. Up to 3? What about people with 4 names? What about hyphenated names? What about two co-workers who use their common names Beth Sawyer and Bob Smith – not ambiguous – but distinguish their initials by using their actual first names and/or including their middle initials, so maybe ES and RAS? Not to mention that both might be touchy about their documents being littered with BS.

    All shortcuts have pitfalls. This one is likely to have larger pitfalls than most.

    Dermot’s udf returns the system user ID, which does identify the user, but usually not in a way that mere humans can use. E.g., my own user ID at work includes decimal numbers and none of the letters from my actual name. IOW, it’s closer to my car’s license plate number than my name.

    But if that’s all you want, and you’re in a corporate environment, you’d usually be safe using either of the following and avoiding API calls.

    Function userid1() As String
      Dim s As String
      s = Environ(“USERPROFILE”)
      userid1 = Mid$(s, InStrRev(s, “”) + 1)
    End Function

    or

    Function bar() As String
      bar = CreateObject(“WScript.Network”).UserName
    End Function
  6. 1) For Jazzer’s code to work you need to set a reference to
    – Active DS Type Library (in activeds.tlb)

    But, on my system (on corporate network), both functions return empty strings.

    2) fzz’s function returns
    C:Documents and Settingsjohn_dowe.ACMEPLC

    3) In view of (2) Dick’s code needs to split on “_” for me.

    Interesting discussion …

  7. Dermot:

    “The code below uses an API call to get the Windows login name,which should always give you the actual user name.”

    Well, at one company I had a windows login name like “e013408? and at another I was Peter somebody, because the IT genius didn’t know how to change the ID or create a new one for me.

  8. Jon Peltier said “…at one company I had a windows login name like “e013408? and at another I was Peter somebody, because the IT genius didn’t know how to change the ID or create a new one for me.”
    Well, that would still be your actual [b]user[/b] name wouldn’t it?
    As long as the name is assigned to one user and not being shared the name itself doesn’t have to be the same as the users [i]real[/i] name…
    Next, you just need to know who belongs to each username!

  9. Dick said:
    “If I can get the Windows name, it will follow people around.”
    I already have enough people following me, and not because I’m a leader.

    Also, in an office using distributed files, the version you query might not be from the user who originated the file, or last actually updated it, only the person whose document store it was found in.

    Or am I missing the point?

  10. @Mpemba – I’d guess you mean the userid1 function. Unless you’re using Excel 97 or prior and use your own InStrRev, I can’t see how Excel wouldn’t return 27, and therefore how Excel wouldn’t have returned john_dowe.ACMEPLC. If thisn’t your actual user ID, then fine, this wouldn’t work.

    My second function, which I had meant to rename as userid2 but left as bar, DOES return the user ID of the current user as long as Windows Script Host is installed on the machine. Since that’s been the default since IE5, it’s a reasonable assumption anyone in a corporate, governmental or academic environment where they replace PCs every 3 or 4 years has WSH on their work machine.

    @Al – the USERNAME environment variable could be modified by the user, so it’s not absolutely guaranteed to be correct. But it’s simpler than parsing it from USERPROFILE, which may have other problems.

    There’s always querying the regisrty. The current user’s ID would always be in the VALUE

    HKCUSoftwareMicrosoftWindowsCurrentVersionExplorerLogon User Name

  11. If you go to the extent of read the registry you can get at the Initials that the OM does not expose.

    HKCUSoftwareMicrosoftOfficeCommonUserInfoUserInitials

  12. Thanks for the registry tip Fzz:
    This is much better easier that the Windows API route.

    Function GetLoggedUser()

    Dim myWS As Object
    Dim strUser As String

    Set myWS = CreateObject(“WScript.Shell”)

    strUser = myWS.RegRead _
        (“HKCUSoftwareMicrosoftWindows” & _
        “CurrentVersionExplorerLogon User Name”)

    End Function

  13. Not too happy about the way the code shows here. Hopefully this is cleaner>

    Function GetLoggedUser()

    Dim myWS As Object
    Dim strUser As String

    Set myWS = CreateObject(“WScript.Shell”)

    strUser = myWS.RegRead _
        (“HKCUSoftwareMicrosoftWindowsCurrentVersionExplorerLogon User Name”)

    MsgBox strUser

    End Function

  14. So many ways to get the logged user name. For years, I’ve used API calls to do this. Dermot and Fzz both demonstrate ways that are much simpler than my old way of doing this.

    Any thoughts on which method may be the most efficient/effective?

  15. I did not know about that method to read the registry. Much simpler than API calls.

    Isn’t it true that Windows Scripting Host is often disabled for security reasons?

  16. Security. It’d be nice if Windows could provide it.

    Unless organizations’ IT departments are sophisticated enough to use alternative credentials for logon scripts when users log on, or just don’t use logon scripts, there’s some scripting engine somewhere on users’ systems which users can run. Maybe in a fully locked down environment users can only run .EXEs under Program Files and a few .EXEs under Windows or WinNT. In such circumstances, API calls may be all there is, but that begs the question whether greater security is achieved forcing users to make API calls rather than providing an easier to use scripting facility like WSH.

    This also raises the question whether, from a security perspective, it’s a good thing VBA can make API calls. And if Microsoft adds a security feature to disable API calls in some future VBA version or successor scripting facility, then what?

  17. Word can’t do without userinitials.
    When my username in Word is Hans Schraven it automatically creates my initials: HS.
    If I empty my username (options), Word automatically takes the computername as username and creates the initials accordingly.
    To retrieve the userinitals in Excel:

      Dim c0 As String
      With CreateObject(“Word.Application”)
        c0 = UCase(.userinitials)
        .Quit
      End With
  18. Hans, I posted codes similar to that last week but my comments are not shown (probably in the spam folder due to including a link for further details)…

    Word and Visio seem to be the only 2003 apps that have complete access to (application) User Name and Initials.

  19. @Simon, sorry to ‘overwrite’ you.
    The nice thing about Word is that does your work: for instance

    Dim c0 As String
      With CreateObject(“Word.Application”)
        .username=“Simon Herbert”
        c0 = UCase(.userinitials)
        .Quit
      End With
  20. Hans – no problem…

    Trying to post my code again:

    Dim objword As Object
    Set objword = CreateObject(“Word.Application”)
    MsgBox “Name: “ & objword.UserName
    MsgBox “Initials: “ & objword.UserInitials
    objword.Quit

    (Dick, could you check if my previous posts are in your spam bin? Thanks!)


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

Leave a Reply

Your email address will not be published.