VBA Express : Excel - Function to return various Environment Names

Excel

Function to return various Environment Names

Ease of Use

Easy

Version tested with

97, 2003

Submitted by:

Ken Puls

Description:

This function allows a user to easily retrieve the Computer Name, Windows Login Name or MS Office Username in a worksheet cell. It can also pas the Name to another VBA routine as well.

Discussion:

When building templates, this function can be entered in a cell so that the user never has to enter their name in a "prepared by" type field. When building macros, you may wish to have your code execute a certain way depending upon the logged in user, and/or the machine that they are logged in on. TECHNICAL NOTE: The function was designed with a default value of the MS Office Username, allowing the function to be used without supplying a paramater value.

Code:

instructions for use

			Option Explicit 
 
Function GetName(Optional NameType As String) As String 
     '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

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

In a worksheet cell, enter the formula in the cell as =GetName([param]), where [param] is optional. Acceptable paramters are shown below (separated by commas, but include quotes where shown):
MS Office username: [param] = "", "office", blank or 1
Windows login name: [param] = "windows" or 2
Full computer name : [param] = "computer" or 3
eg. =getname("windows") or =getname(2) will return the Windows login name
In VBA call the function using any of the above forms
eg. msgbox getname("windows") or msgbox getname(2) will return the Windows login name

Sample File:

GetName demo.zip 8.01KB

Approved by mdmackillop

This entry has been viewed 253 times.