How to Add a Military Rank or Suffix Before a Surname Using Excel VBA
Question details
The user needs a VBA macro to detect specific military ranks or titles and append them before a user's surname extracted from the Application.UserName property.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating signature generation, logs, or timestamp entries in an Excel worksheet where specific military ranks (e.g., A1C, TSgt) must accurately prefix the user's surname.
- Observed behavior
- By default, the Application.UserName property returns the raw system username. Custom logic is required to parse the surname, identify the correct rank, and concatenate them into a formatted string.
Ensure that the Developer tab is enabled in your Excel ribbon and that macros are permitted in your Trust Center settings so the VBA code can execute without interruption.
Use a VBA Macro to Extract the Surname and Match Known Ranks
Implement a VBA script that parses the username, detects specific military titles from a predefined array, and concatenates the detected title with the extracted surname.
This approach leverages the `Application.UserName` property to retrieve the system username. It splits the text at the comma to isolate the surname and loops through a custom array of known ranks. If a match is found, it constructs the final string and outputs it to the target cell.
Press `Alt + F11` on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.
In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and click on 'Module' to create a new blank script window.
Copy and paste the following VBA code into the module window: Sub find_last_plus_3() Dim LastRow As Long Dim wName As String, wSurname As String, Title As String Dim x As Variant LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row wName = Replace(Application.UserName, "GS-05", "MR") wSurname = UCase(Split(wName, ",")(0)) For Each x In Array("MR ", "SrA ", "SSgt ", "A1C ", "TSgt ", "Amn ") If InStr(wName, x) > 0 Then Title = UCase(x): Exit For Next x Range("A" & LastRow + 3).Value = "UPDATED BY: " & Title & wSurname End Sub
Close the VBA editor. Navigate to the 'Developer' tab, click on 'Macros', select 'find_last_plus_3' from the list, and click 'Run'. The updated rank and surname will be printed three rows below the last used cell in Column A.
Run VBA Macros Seamlessly with WPS Office
WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks, manipulate text strings, and format surnames and ranks just as you would in Microsoft Excel.
- 1. Download and Install: Get the latest version of WPS Office from the official website and ensure you install the VBA module if prompted during the setup.
- 2. Open Your Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the rank extraction macro.
- 3. Execute the Macro: Navigate to the Developer tab, click on Macros, select your text manipulation script, and click Run to automatically append ranks to your user data.
Frequently Asked Questions
Why is my VBA macro returning a blank title instead of the rank?
If the rank or suffix in the `Application.UserName` does not exactly match the text inside the VBA array (including trailing spaces and capitalization, unless handled by `UCase`), the `InStr` function will not detect it. Ensure your array strings exactly match the format of your system usernames.
How can I extract the surname if my username format does not use commas?
If your username format separates the first name and surname with spaces instead of commas, you will need to adjust the `Split(wName, ",")(0)` function. Change the delimiter to a space, for example, `Split(wName, " ")(1)`, depending on whether the surname appears first or second in the string.
Will this VBA code overwrite existing data in column A?
No. The code uses `ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row` to find the last dynamically populated row in Column A. It then adds `+ 3` to place the output three rows below your existing data, ensuring nothing is overwritten.
