Use VBA in WPS Office Spreadsheets to Associate a MAC Address with a Department
Question details
The user needs to identify a computer's MAC address through VBA in WPS Office and map it to a user or department, preferably using a maintainable worksheet lookup table.
- Product
- WPS Office Spreadsheets (VBA/Macro)
- Device & OS
- Windows
- Scenario
- An administrator or analyst wants to run a VBA macro that reads the local network adapter address and returns the responsible department for reporting, device checks, or asset tasks.
- Observed behavior
- The macro should return the associated department from a mapping table rather than relying on hard-coded If statements.
Enable the Developer tab in WPS Spreadsheets and save the workbook as a macro-enabled .xlsm file. Also create the worksheet columns that will hold the normalized MAC addresses and department names.
Retrieve the MAC Address with VBA and Look Up the Department from a Worksheet Table
Use this method when you want a maintainable mapping that avoids hard-coded If statements and is easy to update.
WMI's Win32_NetworkAdapterConfiguration class returns adapter MAC addresses. Normalizing the value with UCase and Trim before lookup prevents mismatches, and a named worksheet range is easier to maintain than editing VBA code.
In WPS Spreadsheets, click the Developer tab and select Visual Basic to open the VBA editor.
In the VBA editor, click Insert > Module to create a new code module.
Paste a VBA function that uses WMI to query Win32_NetworkAdapterConfiguration and returns the MAC address of the first active network adapter.
In a worksheet, list normalized MAC addresses in one column and department names in the next column, then select the range and define the name DepartmentTable.
Add a function that applies UCase and Trim to the MAC value, then uses Application.VLookup to return the matching department from DepartmentTable.
Run the macro or enter the function in a cell to verify it returns the correct department for the current computer.
Use the getmac Command and Parse Its Output
Use this alternative when you prefer to call the built-in Windows getmac command instead of using WMI directly.
Map MAC Addresses to Departments with a WPS Office VBA Macro
In WPS Office Spreadsheets, you can use the built-in VBA editor to query the local network adapter via WMI, normalize the MAC address, and return the department from a worksheet lookup table.
- 1. Enable the Developer tab: Open WPS Spreadsheets, go to File > Options > Customize Ribbon, and enable Developer to access the Visual Basic editor.
- 2. Insert a VBA module: Click Developer > Visual Basic, then Insert > Module to create a code module.
- 3. Add the MAC lookup function: Paste the VBA function that retrieves the MAC address through WMI and uses Application.VLookup to map it to a department.
- 4. Prepare the department lookup range: Enter MAC addresses and departments in a worksheet and define a named range such as DepartmentTable for the lookup.
- 5. Run and verify the macro: Use the function in a cell or run the macro to confirm the correct department is returned.
Frequently Asked Questions
Does WPS Office support VBA macros that use WMI?
Yes, on Windows WPS Office Spreadsheets can run VBA projects that use WMI through the standard VBA editor, subject to your local macro security settings.
Why should I use a worksheet lookup table instead of hard-coded If statements?
A table is easier to update and audit. You can change MAC-to-department mappings without editing code and use named ranges with Application.VLookup.
Can a single computer have multiple MAC addresses?
Yes. Laptops often have Ethernet, Wi-Fi, Bluetooth, and virtual adapters, so the macro should filter by adapter type or return all active MAC addresses before mapping.
Are MAC addresses a reliable way to identify users or departments?
Not for strict security. MAC addresses can be spoofed, changed, or hidden; for reliable identification prefer account-based authentication or device-management tools.
Does this macro work on macOS?
The WMI/getmac method is Windows-specific. On non-Windows systems, use a different command or a fixed mapping file.
