Article 36 - VBA - User Names in Excel

Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here.

I am going to discuss the various type of User Names which we may be required to extract.

First, for beginners in VBA, you need to follow below instructions to make these work.

1. Make a backup of your workbook just to be on safer side.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Save your file as .xlsm if you intend to reuse Macro.
8. If your function name is MyFunction, in a cell just put =MyFunction() to get the output. Hence, for the Function, GetWindowsUser, just put =GetWindowsUser() to get the name of the user who is logged into Windows.

1. Get the Name of the User who is Logged into Windows

You use the combination of User Name and Password to log into your windows. This Function retrieves that User Name which is used by you to log into your windows. The same user name password combination you use when the screen saver lock is put or you lock your computer by CTRL+ALT+DEL.

To get Windows User Name, use GetWindowsUser function. To get this User Name, put following in a cell

=GetWindowsUser()

Function GetWindowsUser() As String
    Application.Volatile
    GetWindowsUser = Environ$("UserName")
End Function

2. Get the Name of User Name of the Application

Excel may have a user name which is called Application User Name and it may be different than the user who is logged into the Windows. This is found under File > Options > General. This particular application name is very important as this is what gets logged by Excel under Created By and Last Saved by users.

To get Application User Name, use GetExcelUser function. To get this user name, put following in a cell –

=GetExcelUser()

Function GetExcelUser() As String
    Application.Volatile
    GetExcelUser = Application.UserName
End Function

3. Get the User Name of the Current User of Outlook

Since Outlook is an integrated part of MS Office, hence sometimes, you may need to retrieve the User Name of the Current User of Outlook.

To get Outlook Current User Name, use GetOutlookCurrentUserName function. To get this user name, put following in a cell –

=GetOutlookCurrentUserName()

Function GetOutlookCurrentUserName() As String
    Dim NameSpace As Object
    Application.Volatile
    Set NameSpace = CreateObject("Outlook.Application").GetNameSpace("MAPI")
    GetOutlookCurrentUserName = NameSpace.CurrentUser
End Function

4. Get the E Mail ID of Current User of Outlook

Another information, you may need may be E Mail ID of Current User of Outlook.

To get Outlook Current User E Mail ID, use GetOutlookCurrentUserEMail function. To get this user name, put following in a cell –

=GetOutlookCurrentUserEMail()

Function GetOutlookCurrentUserEMail() As String
    Dim NameSpace As Object
    Application.Volatile
    Set NameSpace = CreateObject("Outlook.Application").GetNameSpace("MAPI")
    GetOutlookCurrentUserEMail = NameSpace.Accounts.Item(1)
End Function

5. Get the Active Directory User ID

In case, you need to retrieve Active Directory User ID, use GetADUserDN function. To get this user name, put following in a cell –

=GetADUserDN()

Function GetADUserDN() As String
    Application.Volatile
    GetADUserDN = CreateObject("ADSystemInfo").UserName
End Function

6. Get Active Directory Full Name of the User

In case, you need to get Full Name of the User from Active Directory, use GetADFullUserName function. To get this user name, put following in a cell –

=GetADFullUserName()

' Source - http://www.mrexcel.com/forum/excel-questions/2252-get-windows-user-name-2.html
Function GetADFullUserName() As String
  Dim objUser
  Dim strName
  Dim arrLDAP
  Dim intIdx
  Dm strLDAP
  Application.Volatile  
  On Error Resume Next
  strLDAP = CreateObject("ADSystemInfo").UserName
  strName = ""
  Set objUser = GetObject("LDAP://" & strLDAP)
  If Err.Number = 0 Then
    strName = objUser.Get("givenName") & Chr(32) & objUser.Get("sn")
  End If
  If Err.Number <> 0 Then
    arrLDAP = Split(strLDAP, ",")
    For intIdx = 0 To UBound(arrLDAP)
      If UCase(Left(arrLDAP(intIdx), 3)) = "CN=" Then
        strName = Trim(Mid(arrLDAP(intIdx), 4))
      End If
    Next
  End If
  Set objUser = Nothing
  
  GetADFullUserName = strName
  
End Function

7. User Who Created the Excel Document

To get the information about the Excel User (This is the same type User Name which you retrieved in point 2 i.e. Application User Name) who created this particular Excel Document, you can use GetCreatedByUser() function. Just put following in a cell –

=GetCreatedByUser()

This particular information can be derived manually by right clicking an Excel document > Properties > Details tab

Function GetCreatedByUser() As String
    Application.Volatile
    GetCreatedByUser = ThisWorkbook.BuiltinDocumentProperties(3)
End Function

8. User Who Last Modified (Updated) the Excel Document

To get the information about the Last Excel User (This is the same type User Name which you retrieved in point 2 i.e. Application User Name) who modified this particular Excel Document, you can use GetLastModifierUser() function. Just put following in a cell –

=GetLastModifiedByUser()