There might be a requirement, you have built a VBA macro or excel vba tool and it will be used by a group of people or different teams. While, once you have delivered your macro or tool, you need to track the usability. In order to track it, we will need how many users are using your macro or tool, how frequently they are using your macro or tool, in this case we will need date time stamps too.
We will first learn how to get the login user name, whoever is the current logged in user in the system. We will try this first in excel sheet and once we know, we will write a macro which will create a text file and write the usage details on every execution or opening of the file.
There is inbuilt function in Excel vba to get user name which is : Environ(“UserName”)
Below is the vba code, which we will use to get logged in user id:
–Code start
Sub UserName()
‘declare GetName as string variable, we will use this to assign the login id
Dim UserName As String
‘assign the user id to our variable GetName
UserName = Environ(“username”)
‘write a message box code to display a pop-up message to show the line mentioned below, with the logged in user name
MsgBox “Logged in User Name is : ” & UserName
End Sub
–Code End
Now we can test and run the code, we will receive a msgbox screenshot as shown below.
Now that we have tested the above code. We now know how to get the logged in or current user id. We will now write a macro which will create a log using the above code.
To create a log we will have to create a macro, which will create a text file and keep writing lines, whenever the macro is used. For this we will create a sub procedure, which will run along with above code.
–Code start
Sub UserName()
Dim UserName As String
UserName = Environ(“username”)
MsgBox “Logged in User Name is : ” & UserName
‘I have removed all green comments from above code, rest is same. The only change is below line, to run as sub procedure.
Logtracker UserName & “|UserNameMacro|” & Date & ” ” & Time
‘Below are details for above line:
‘Logtracker = sub procedure name
‘UserName = the variable with log in user id
‘ UserNameMacro = the macro name, to track
‘Date = will provide current date
‘ Time = will provide current time
‘ we are using & to concatenate and | as separator
End Sub
–Code end
Below is the sub procedure code to create text file and keep appending lines, whenever we run the above macro.
–Code start
Sub Logtracker(LogMessage As String)
‘declare the path to save file and create log
Const LogFileName As String = “C:\Users\xyz\Desktop\tracker_log.txt”
‘declare FileNum as integer, which will be used check Log File
Dim FileNum As Integer
‘ assign next file number, on every run
FileNum = FreeFile
‘Below code creates the file if it doesn’t exist. If exist then opens the file to write the log
Open LogFileName For Append As #FileNum
‘Below code will write our log message and append at the end in text file
Print #FileNum, LogMessage
‘Below code will close the file
Close #FileNum ‘ close the file
End Sub
–Code end
Now try to run the macro UserName() and test the working of above codes. There will be a text file created and saved on the path we have mentioned, in above example it is on desktop. Below is the screenshot of the text file tracker_log.txt, for reference.
prassann.daphal|UserNameMacro|8/22/2018 9:30:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:30:47 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:41 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:55 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:11 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:25 AM
Thank you for reading ! Happy Learning !
Please write feedback comments if you like my posts and also let me know if you want me to write any specific articles which want to learn or need help on.