VBA & Macro Problems

How to Add Vertical Bars Around Excel Cell Values Using VBA

Huma Ashraf ChHuma Ashraf Ch Nov 2, 2026 871 views

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.

How to Add Vertical Bars Around Excel Cell Values Using VBA
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

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.

2
Insert a New Module

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.

3
Paste the Macro Code

Copy and paste the AddPipes macro code into the window. Ensure the core transformation line uses the Text property: rng.Value = "|" & rng.Text & "|".

4
Run the Macro

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 a VBA Macro with the rng.Text Property
Format Preserved: The macro will successfully append and prepend the pipe character to all constant, non-empty cells while retaining your custom time and date formatting.
Efficient Spreadsheet Management

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. 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
  2. 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file containing your data.
  3. 3. Run Your VBA Code: Navigate to the Developer tab, open the VBA Editor, and run your scripts just as you normally would.
Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .xlsm)Built-in robust support for VBA macros and advanced developer toolsLightweight architecture for fast performance on large datasetsFree to download with a familiar user interface for seamless migration
microsoft office alternative - wps office

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.