How to use Get Username in Windows using VBA?
In VBA, we have plenty of methods available to get the Active User Details. In this article we are going to see a few of the methods with easy to use free source code.
We will be in need of this code when,
- We share any Office document with other users and we need to collect the user details whoever is accessing it or
- We need to restrict users from using this document based on their login id.
Different Method to get Windows UserName with VBA
Let’s see the easiest one first. In some cases, if the easiest one did not work out, follow the next possible method.
- Easy to Spell
- Using Windows API ‘apiGetUserName’ Function
- From System Environment Variables
- Network UserName
1. Easy to Spell – Application.Username
Well this is the easiest of all the method and does not require any references to be added to any library or DLL. Just copy-paste this code to Excel or Word VB Editor and run it.
This Excel macro code will display the current Windows username, who is also executing this Excel or Word App.
1234567Sub GetUserName_AppUser()Dim strUserName As String'Use the Application Object to get the UsernamestrUserName = Application.UserNameMsgBox "Current Logged In UserName is:" & strUserNameEnd Sub
2. Using Windows API ‘apiGetUserName’ Function
Microsoft provides many other variants of this function. So, you can choose to use this function or any other available
APIs based on your need. To use this API, declare the function before calling the function.12345678910111213141516Sub Get_UserName_WINAPI()'Declare VariablesDim Returned_Val_Length As LongDim API_Return_Status As LongDim Returned_UserName As String'Define Space for VariablesReturned_UserName = String$(254, 0)Returned_Val_Length = 255'Call the API and get the User DetailsAPI_Return_Status = apiGetUserName(Returned_UserName, Returned_Val_Length)'Remove unwanted details from Return value and get only UsernameReturned_UserName = Mid(Trim(Returned_UserName), 1, Returned_Val_Length - 1)End Sub
3. Excel VBA Get Username from System Environment Variables
Open Command prompt and type ‘Set’ and press Enter. This command will display list of all the Environment variables and values stored in it. In this list, we have an Environment variable “USERNAME” and it will have the active logged in username for that session.
We can write macro to read this Environment Variable and get the user id.
Note: This VBA macro can also be used to read all the Environment variables from system for that login session.123456789101112Sub GetUserName_Environ()Dim idx As Integer'To Directly the value of a Environment Variable with its NameMsgBox VBA.Interaction.Environ$("UserName")'To get all the List of Environment VariablesFor idx = 1 To 255strEnvironVal = VBA.Interaction.Environ$(idx)ThisWorkbook.Sheets(1).Cells(idx, 1) = strEnvironValNext idxEnd Sub
4. Network UserName in Excel VBA
The WScript Object is used here to get the Network Username for the current logged in user. Create an instance for the object Wscript.Network and use it to fetch the User and Computer Details as explained below.12345678Sub GetUserName_Environ()Dim ObjWshNw As ObjectSet ObjWshNw = CreateObject("WScript.Network")MsgBox ObjWshNw.UserNameMsgBox ObjWshNw.ComputerNameMsgBox ObjWshNw.UserDomainEnd Sub
All these combination of codes, fetch current Windows user name details to Excel.
- Get list of Tasks running in System from Task Manager.
- Fetch Details from Websites to Excel Through VBA.
- Windows API detail from Microsoft Support Website.
- Fetching detail from Environment Variable.
- Network User details from WScript.Network.
Excel VBA get Username – Code for Windows Current User Name