This code is from Charles Maxson’s blog (http://blogs.officezealot.com/charles/archive/2004/12/10/3574.aspx) and is posted here for my reference.

Excel VBA: Function to get logged in user name (plus the Environ Function)

One commonly asked task is how can you get the name of the current user of an Excel spreadsheet into a cell in the spreadsheet. A lot of people quickly stumble across the UserName property in VBA and create a function similar to this:

Function UserNameOffice() As String

     UserNameOffice = Application.UserName

End Function

But as you know, that only returns the name of the user according to the registration information of Office. A lot of companies set that at something generic like “User”or “Registered Owner”. That’s not what you really want though right? You really want the user’s name based on their Windows login. How do you get that ?…. well it’s a little complicated with an API call from VBA as shown here is below:

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"(ByVal lpBuffer As String, nSize As Long) As Long

Function UserNameWindows() As String

    Dim lngLen As Long

    Dim strBuffer As String

    Const dhcMaxUserName = 255

    strBuffer = Space(dhcMaxUserName)

    lngLen = dhcMaxUserName

    If CBool(GetUserName(strBuffer, lngLen)) Then

        UserNameWindows = Left$(strBuffer, lngLen - 1)

    Else

        UserNameWindows = ""

    End If

End Function

Then all you have to do in the cell of choice is enter the formula:

=UserNameWindows()

But as *Mike* reminded me in a comment on my original post (this is the updated version)….

There is the Environ Function in VBA that makes this a walk in the park without the API hassles:

Function UserNameWindows() As String

    UserName = Environ("USERNAME")

End Function

Thanks *Mike* for bringing that up….

I remember using Environ to get the current location of  the “My Documents“ folder for the current user:

MsgBox Environ("USERPROFILE") + "\My Documents"

So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was… here is what it looks like: Environ Help. Not too useful I thought… So I decided to figure it out on my own and loop thru all the arguments possible with Environ. Copy and run this little routine to see all that Environ offers:

MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()

    Dim nCount As Integer

    nCount = nCount + 1

    Do Until Environ(nCount) = ""

        Debug.Print Environ(nCount)

        nCount = nCount + 1

    Loop

End Sub

There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more… Hopefully you will find it useful and I won’t forget about it again.

****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around

Here’s a complete list (that I know of) of the named arguments for the Environ Function:

Environ arguments

ALLUSERSPROFILE

APPDATA

AVENGINE

CLIENTNAME

CommonProgramFiles

COMPUTERNAME

ComSpec

FP_NO_HOST_CHECK

HOMEDRIVE

HOMEPATH

INCLUDE

INOCULAN

LIB

LOGONSERVER

NUMBER_OF_PROCESSORS

OS

Path

PATHEXT

PROCESSOR_ARCHITECTURE

PROCESSOR_IDENTIFIER

PROCESSOR_LEVEL

PROCESSOR_REVISION

ProgramFiles

SESSIONNAME

SystemDrive

SystemRoot

TEMP

TMP

USERDOMAIN

USERNAME

USERPROFILE

VS71COMNTOOLS

WecVersionForRosebud.FF0

windir