user name in Excel, Windows login in Excel, user name with Excel macros, Excel function returns user name, user name with VBA

Get the user name in an Excel cell with a formula or VBA macro

If you want to get the Windows user name in an Excel cell, there are at least two ways to do that. By using an Excel formula to return the username, you will get dynamic results depending on who will open the file. Otherwise, you can return the same with VBA and a simple Excel macro in a more flexible way.

 

User name in Excel cell with the formula

The solution of getting the username in Excel with functions is based on the function INFO, which can return information from the current operating environment. By using the argument DIRECTORY you can get the path that contains the Windows user name and it is available for saved and unsaved files. Test this first, but at least it works for me.

=INFO("DIRECTORY")

The next is path processing, which enables you to extract the necessary part. In the solution, I am using the LET function, and you can read more about that here. It enables you to use variables in Excel functions and, in this case, you to write the INFO function only once.

The result of the process looks like this. The first variable returns the full path, and the second variable deletes the beginning. After that login name is extracted from the left side till the first slash symbol and returned to the Excel cell.

=LET(
fullpath, INFO("DIRECTORY"),
path, SUBSTITUTE(fullpath, "C:\Users\",  ""),

LEFT(path, FIND("\", path) - 1)
)

At this moment, it is not possible to get the login name in Excel online.

 

Username with VBA

Below is an Excel macro that can return the Windows username in the active cell. You can assign a shortcut to that. Very useful if you want to get the value that doesn’t update for a different user.

Sub UserName()

Set MyCell = ActiveCell

MyCell.Value = Environ$("UserName")

'alternative
'MyCell.Value = Application.Username

End Sub

It is also possible to get full usernames like name and surname with the Application.UserName property.

Try to create an Excel function with VBA for the same purpose. It is a more compact solution to getting the login name in the Excel cell than with standard functions previously.

Public Function MyUserName() As String

MyUserName = Environ$("UserName")

End Function

If you want to make your Excel custom function available in any workbook, use the personal.xlsb file. If the personal.xlsb contains a custom Excel function you can use it like this.

=PERSONAL.XLSB!MyUserName()

 

Here is how to add that to the personal workbook in a similar situation and use it in any Excel workbook.


Posted

in

,

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *