Function to Return Various Environment Names

Function Purpose:

This function can be used to retrieve one of three types of usernames, and can be used in a worksheet cell or in VBA. The usernames that it can return are:

1. Microsoft Office username (default) as defined in Tools|Options|General
2. Microsoft Windows logon name
3. Computer name (as defined in your system properties)

Examples of where this function shines:

1. When designing templates for use by multiple users
   1. Automate the task of entering the user's name(s) on the worksheet!
2. When building macros
   1. Have your code execute a certain way depending upon the logged in user, and/or the machine that they are logged in on!

Function Weakness(es):

1. The Microsoft Office username must be entered correctly (defined in Tools|Options|General) or it will return incorrect results
2. Running in an environment where people log in sharing a Windows profile doesn't work well, as the computer can't identify who the user really is
3. Must use the Application.Volatile keywords to force recalculation, or the function won't recalculate when the template is opened (unless force-coded to do so.)

Versions Tested:

This function has been tested with Excel 97, and Excel 2003, and should also work with Excel 2000 and Excel 2002 (XP) without any modifications

VBA Code Required:

The following should be copied into a standard code module:

Option Explicit

Function GetName(Optional NameType As String) As String
     'Author          : Ken Puls (www.excelguru.ca)
     'Function purpose: To return the following names:
     'Defaults to MS Office username if no parameter entered

     'Formula should be entered as =GetName([param])

     'For Name of Type           Enter Text    OR    Enter #
     'MS Office User Name        "Office"             1 (or leave blank)
     'Windows User Name          "Windows"            2
     'Computer Name              "Computer"           3

     'Force application to recalculate when necessary. If this
     'function is only called from other VBA procedures, this
     'section can be eliminated. (Req'd for cell use)
     Application.Volatile

     'Set value to Office if no parameter entered
     If Len(NameType) = 0 Then NameType = "OFFICE"

     'Identify parameter, assign result to GetName, and return
     'error if invalid
     Select Case UCase(NameType)
          Case Is = "OFFICE", "1"
               GetName = Application.UserName
               Exit Function
          Case Is = "WINDOWS", "2"
               GetName = Environ("UserName")
               Exit Function
          Case Is = "COMPUTER", "3"
               GetName = Environ("ComputerName")
               Exit Function
          Case Else
               GetName = CVErr(xlErrValue)
     End Select

End Function

Referencing The Formula:

The formula can be referenced in worksheet cells as shown in the example below:

The formula can also be called in VBA in the same manner. For example:

• msgbox getname("windows") or msgbox getname(2) will return the Windows login name
• msgbox getname("computer") or msgbox getname(3) will return the Computer name

Example File:

An xlsm file with the full setup shown above can be downloaded by clicking here. Be aware that this file does contain the full VBA code, so a macro warning will most likely present itself.

Did you know?

This code has also been submitted to the VBAExpress Knowledge Base!