You might be working on a workbook or database that is intended for the use of multiple end-users, and you require each user to have a personalized view or specific welcome message when they open the workbook. There is a way to get this done, using Functions in Excel VBA.

A VBA Function is a code in VBA that returns just one response. VBA Functions differ from VBA Subs (the kind of macros mainly featured on this blog) as they only work when they are entered into a cell, the way a regular Excel Function like =SUM, =COUNT, etc. VBA Subs, on the other hand, can be triggered multiple ways – by assigning the macros to shapes or cells, using the Run Macro (Alt + F8) feature, to name a few.

The VBA Function returns its output into a cell. In this scenario, the output required is the name of the Excel user, which is usually the enterprise user ID. The applicable code to extract this information is below:

ExcelUser = Application.UserName

To get the Excel user name, place the below code into a new module, and type =ExcelUser() into the target cell (say E8):

Function ExcelUser()
ExcelUser = Application.UserName
End Function

With this VBA Function, you can then use =IF() or =CHOOSE() statements to specify the welcome message or information. This statement will read something like what is below:

=IF(E8 =”John Green”,”Great to have you here John!”,IF(E8=”May Baker”,”Hi May!”,IF(E8=”Gerald Doe”,”Top of the day to you, Gerald!”,””)))

What if you need more than a welcome message to be personalized? How about a scenario that requires each user to have specific access to information? The second part of this post will address this use case.