• 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:

    Code:
    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
    • 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!

    Comments 1 Comment
    1. CGilbert's Avatar
      CGilbert -
      Is there a version of this that works with Excel 2016 64-bit? I copied the VBA code, but even the test case of =GetName("Windows") returns a #VALUE result.

      I'm not a VBA guy, so I don't really know what would need changed.
      Thanks, in advance.
  • MVP Logo
  • Recent Forum Posts

    AndyDuncombe

    Worksheet named from cell

    this is truly incredible! In an ideal worls I'd like to use data validation to populate the names that show in column A...

    AndyDuncombe Today, 08:58 PM Go to last post
    Jodie

    IF Formula

    Iím looking for help with a formula that will only do calculation based on if there is data in a cell. Example: ColumnA is an enrollment date but is...

    Jodie Today, 08:11 PM Go to last post
    Bob Phillips

    Worksheet named from cell

    I have made that change. I named the sheets as ddd d mmm as it takes less space, is just as clear, and means you see more roster sheets at one time. Each...

    Bob Phillips Today, 08:05 PM Go to last post
    AndyDuncombe

    Worksheet named from cell

    Which I'd like to rename as Sunday 2 Jan + sunday 9 Jan etc or whatever variant will fit. I think this would call from the app settings sheet data in...

    AndyDuncombe Today, 06:27 PM Go to last post
    Bob Phillips

    Worksheet named from cell

    The title does reflect that, do you mean the sheet name? Your example had sheet names WEEK 1 and WEEK 2....

    Bob Phillips Today, 03:30 PM Go to last post