How to Add Vertical Bars Around Excel Cell Values Using VBA
Question details
The user needs a way to enclose non-empty cell values with vertical bars using a VBA macro while preserving the formatted display of dates and times.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to format cell data by wrapping the existing text in pipe characters.
- Observed behavior
- Using the default rng.Value property in the VBA macro converts formatted times and dates into unformatted underlying serial numbers (e.g., 08:15:00 becomes 0.34375) when the vertical bars are added.
Before running the macro, ensure you test it on a copy of your worksheet to prevent irreversible changes to your original data, especially when dealing with custom date and time formats.
Use a VBA Macro with the rng.Text Property
This method loops through all constant cells and uses the Text property to preserve the visible format of dates and times when adding the vertical bars.
By default, VBA reads a cell's underlying value, which means dates and times are pulled as decimal serial numbers. By changing your code to use the 'rng.Text' property instead of 'rng.Value' for the extraction, the macro captures the exact formatted text displayed in the cell, preventing Excel from altering the time formatting.
Navigate to the Developer tab on your Excel ribbon and click 'Visual Basic', or press Alt + F11 on your keyboard to open the VBA editor.
In the VBA editor, right-click on your workbook name in the Project Explorer panel, then select Insert > Module to create a blank script window.
Copy and paste the AddPipes macro code into the window. Ensure the core transformation line uses the Text property: rng.Value = "|" & rng.Text & "|".
Close the VBA editor, select the worksheet containing your data, and press Alt + F8. Select the 'AddPipes' macro and click Run to apply the vertical bars.

Use an Array-Based VBA Macro for Faster Processing
For large datasets, reading the range into an array processes the data significantly faster than looping through individual cells one by one.
Manage Data and Run Macros with WPS Office
WPS Office Spreadsheet fully supports VBA macros, allowing you to seamlessly automate repetitive tasks like adding vertical bars to your data. It is a lightweight, fully compatible alternative for all your advanced spreadsheet needs.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
- 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file containing your data.
- 3. Run Your VBA Code: Navigate to the Developer tab, open the VBA Editor, and run your scripts just as you normally would.

Frequently Asked Questions
Why do my Excel times turn into decimals when using a VBA macro?
In Excel, dates and times are stored in the background as serial numbers (decimals). If your VBA macro uses the standard .Value property, it retrieves that underlying serial number instead of the formatted text. Switching your VBA code to use the .Text property retrieves the formatted display value you see on the screen.
How do I enable the Developer tab to run VBA macros?
To access the VBA editor, you need the Developer tab enabled. Go to File > Options > Customize Ribbon, and check the box next to 'Developer' in the right-hand pane. Click OK, and the tab will appear on your ribbon.
Can I undo a VBA macro execution in Excel?
No, actions performed by a VBA macro clear the Undo history and cannot be reversed using the standard Undo button (Ctrl+Z). Always run macros on a backup copy of your data or save your workbook right before executing the script to prevent accidental data loss.




