• 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:
    • Microsoft Office username (default) as defined in Tools|Options|General
    • Microsoft Windows logon name
    • Computer name (as defined in your system properties)

    Examples of where this function shines:
    • When designing templates for use by multiple users --> Automate the task of entering the user's name(s) on the worksheet!
    • When building macros --> 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):
    • The Microsoft Office username must be entered correctly (defined in Tools|Options|General) or it will return incorrect results
    • 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
    • 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 a variety of Excel versions, from Excel 97 through Excel 2010. It will work with Excel 97 or higher.

    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)
         '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
    • etc...

    Example File:
    Be aware that this file does contain the full VBA code, so a macro warning will most likely present itself.

    Also, Did you know?
    This code has also been submitted to the VBAExpress Knowledge Base!


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!