VBA & Macro Problems

Why Excel VBA Cannot Change a Cell's Text Property & How to Fix It

Bushra Parveen Oct 21, 2026 868 views

Question details

Users attempting to modify cell contents or formatting via Excel VBA encounter an error when trying to assign a new value to the cell's Text property.

Why Excel VBA Cannot Change a Cell's Text Property

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing or executing a VBA macro intended to manipulate spreadsheet data, update cell contents, or change visual formatting.
Observed behavior: VBA prevents the macro from changing the Text property because it is strictly read-only. It calculates the final on-screen display based on the underlying value, number format, and column width rather than storing data directly.

Before you start

Before modifying your VBA script, identify whether your goal is to change the actual stored data or merely update its visual formatting, as these require different VBA properties.

Solution 1Recommended

Use the Value or Value2 Property to Change Cell Data

Since the Text property is read-only, you must assign new data to the cell's underlying value property instead.

The underlying data of a cell is stored in its Value or Value2 property. For example, a date like 'April 16, 2023' is actually stored as a serial number (e.g., 45032). Modifying the Value property directly updates this stored data without conflicting with the display logic.

Open the VBA Editor

Press Alt + F11 in your workbook to open the Visual Basic for Applications (VBA) editor.

Locate the incorrect code

Find the line in your macro that attempts to assign a value to the Text property, such as: Range("A1").Text = "New Data"

Replace Text with Value

Change the property reference from .Text to .Value or .Value2. For example, rewrite the line as: Range("A1").Value = "New Data"

Use the Value or Value2 Property to Change Cell Data

Pro Tip: Use Value2 when dealing with large datasets or dates. It skips the formatting checks for currency and dates, making your macro run faster.

Solution 2

Use NumberFormat to Change the Visual Display

If your goal is to change how the data looks (e.g., date formats or decimal places), modify the NumberFormat property.

Solution 3

AutoFit Column Width to Fix Hash (#) Characters

If you are reading the Text property and it returns hash characters (###) instead of the expected data, you need to widen the column.

Advanced Macro Support

Write and Execute VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides robust built-in support for VBA macros. You can effortlessly manage your code, utilize Value and NumberFormat properties, and automate your workflow in a highly compatible, easy-to-use interface.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm or .xls workbook.
2. Access the Developer tab: Click on the Developer tab in the top ribbon and select 'VBA Editor'.
3. Update and run your code: Replace any read-only .Text assignments with .Value, then press F5 to execute your macro successfully.

Fully compatible with Microsoft Excel macro formats (.xlsm, .xls)Built-in VBA editor for creating, modifying, and debugging scriptsFamiliar user interface requiring no steep learning curveFree, lightweight, and fast-loading office suite alternative

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between Value, Value2, and Text in Excel VBA?

Value returns the underlying data with some basic formatting applied (like converting a date serial to a VBA Date type). Value2 returns the raw underlying data (like a Double precision number for dates) and is faster. Text returns a strict String of exactly what is visually displayed on the screen, including formatting and column-width constraints.

Why does my VBA Text property return hash marks (###)?

The Text property reads exactly what is visible on the spreadsheet. If a column is too narrow to display a formatted number or date, Excel shows hash marks (###). The Text property simply reads those hash marks. You can fix this by widening the column before reading the property.

Can I force the Text property to be writable in VBA?

No, the Text property is fundamentally designed as a read-only string. It is dynamically calculated by Excel based on the stored Value, the applied NumberFormat, and the current column width. You must alter one of those three underlying factors instead.

Excel & Data Problems