[Excel VBA] How to Get the Value “As Displayed” in a Cell | The Difference Between .Value and .Text

Overview

In Excel VBA, users often hesitate between using .Value and .Text when retrieving cell data.

If you want to retrieve the “value including formatting” that exactly matches what you see on the screen, using .Value may not produce the desired result.

This article explains how to retrieve values in the exact format displayed in the cell and clarifies the differences between .Value and .Text with practical examples.

Sample Code

Sub ShowCellValues()

    ' Get the actual value (internal data)
    MsgBox Range("C5").Value
    
    ' Get the displayed value (formatted string)
    MsgBox Range("C5").Text

End Sub

Explanation of the Code

1. The .Value Property

Retrieves the actual value (internal data) of numbers or dates.

• Example: Even if a cell displays “2025/08/07”, .Value might retrieve it as a Date type or the serial number 45128.

2. The .Text Property

Retrieves the string actually displayed (with formatting) in the cell.

• Example: If the cell format is currency (¥#,##0) and displays “¥1,000”, using .Text will retrieve the string “¥1,000” exactly as is.

Important Notes

• Column Width: If the column is too narrow and the cell displays ####, the .Text property will return the string “####”. The cell content must be fully visible to retrieve the correct text.
• Data Type: The return value of .Text is always a String.
• Usage: Use .Value for data processing and calculations. Use .Text when the visual appearance is the priority.

Application Examples

• Transferring amounts or dates to a report exactly as they appear.
• Creating print-ready output files using VBA.
• Converting values to PDF or CSV formats while maintaining their visual formatting.

Summary

In this article, I explained the difference between .Value and .Text when retrieving cell values in Excel VBA.

By distinguishing between these two based on your purpose, you can create more flexible and accurate VBA scripts.