In Excel VBA, Value is a property of a cell or range that reads or sets the data stored there. For example, Range("A1").Value reads cell A1, while assigning to that property writes a value into the cell.
What Value Means in Excel VBA
A worksheet cell is represented in VBA by a Range object. Its Value property gives your code access to the cell’s underlying value: a number, text, date, Boolean value, blank, or Excel error. When you assign a value through this property, Excel stores it in the cell.
For example, if A1 contains the number 42, reading Range("A1").Value returns that value to VBA. If your code assigns the text “Ready” to the same property, the cell displays Ready. The property returns a VBA Variant, a flexible data type that can hold different kinds of values.
Value is not the same as a cell’s formula or its displayed appearance. A formula cell’s Value is generally its calculated result; the formula itself is available through a formula property. Likewise, a number formatted to display as a date remains a numeric value in Excel, even though the cell looks like a date.
Reading a Cell’s Value
To read a cell, identify it with a Range reference and ask for its Value. The following example uses a worksheet named Sheet1 in the workbook containing the macro. Change the sheet name and address to match your workbook.
Before running a macro, save a separate copy of the workbook: macro actions cannot be reversed with Ctrl+Z.
Sub ReadCellValue()
Dim cellValue As Variant
' Read the value stored in Sheet1 cell A1.
cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
Debug.Print cellValue
End Sub
Here, Debug.Print sends the result to the VBA editor’s Immediate window; it does not write the result to a worksheet. Open the editor with Developer > Visual Basic, then show the Immediate window with View > Immediate Window. If a cell contains an Excel error, the returned Variant holds an error value, so code that expects ordinary text or numbers should check for errors before processing it.
For a blank cell, the returned value is typically the VBA value Empty. A formula that evaluates to an empty string is different: it returns a zero-length string. That distinction matters in tests. A check for an empty cell and a check for a displayed blank may need different logic, depending on whether formulas count as content in your task.
Writing a Value to a Cell
Use the same property to put data into a cell. An explicit worksheet reference helps prevent a macro from writing to whichever sheet happens to be active. In this example, the code writes text into B2 on Sheet1.
Sub WriteCellValue()
Dim target As Range
' Identify the destination before assigning its value.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
target.Value = "Ready"
End Sub
Assignments can write numbers, strings, dates, and Boolean values. The display still depends on the cell’s number format: assigning a number does not automatically apply a currency or date format. If a cell has an existing format, Excel generally keeps that format when its value changes.
Assigning a value also replaces the cell’s previous contents. If the cell held a formula, writing to Value replaces that formula with the new value. That is a common source of accidental data loss in macros, so confirm the destination range and save a copy before running code that writes or clears cells.
Value and Value2: What Is the Difference?
Value2 is another Range property for reading and writing cell data. It is similar to Value, but it does not use the VBA Date and Currency subtypes. When a cell represents a date or currency, Value2 returns the underlying number as a floating-point value instead. Excel stores dates as serial numbers, and currency formatting does not change the stored number.
For ordinary text and numeric data, the two properties often produce the same practical result. The difference is useful when code needs consistent numeric values or processes many cells. For example, a date-formatted cell read using Value may arrive in VBA as a Date, while Value2 gives the numeric serial value. The cell’s number format affects how Excel displays that number, not the basic purpose of either property.
Choose based on what the code needs rather than assuming one is universally safer. Use Value when VBA’s date or currency handling is convenient. Use Value2 when you want the underlying numeric representation and do not need those subtypes. If code performs date calculations, make the intended conversions and date handling clear rather than relying on a cell’s appearance alone.
Working with Multiple Cells
When you read Value from a multi-cell rectangular range, VBA returns a two-dimensional Variant array containing the values from that range. This can be more efficient than reading cells one at a time, especially when a macro processes a large table. The array uses row and column positions within the selected range, not worksheet row and column numbers.
For example, if the code reads A1:B3 into an array, its first row corresponds to worksheet row 1 and its second column corresponds to worksheet column B. Your code can inspect each array element in memory, then write results back in a batch. This approach can reduce repeated communication between VBA and the worksheet.
Keep the destination dimensions in mind when writing arrays. A two-dimensional array is normally assigned to a destination range of matching dimensions. If the source and destination shapes do not match, the result may not be what you intend. A single-cell range, meanwhile, produces a single value rather than a two-dimensional array. Code should account for that difference if it sometimes handles one cell and sometimes a block.
Value, Text, and Formula Are Not Interchangeable
Use Value when you want the stored or calculated result. Use Text when you specifically need the text as it is displayed in the cell, including effects of number formatting and available column width. For instance, a date-formatted number may have a numeric Value but display as a date; a narrow column may display number signs even though its Value is still a valid number.
The displayed Text is not always suitable for calculations or reliable data transfer. It can vary with formatting, column width, and regional settings. For calculations, comparisons, or data processing, the underlying Value or Value2 is usually the more appropriate choice.
To inspect or change a formula rather than its result, use the appropriate formula property, such as Formula. Reading Value from a formula cell gives the result currently calculated by Excel, not the formula text. This distinction matters when a macro must preserve formulas, audit them, or copy their logic to another location.
Common Problems and How to Avoid Them
- Writing to the wrong sheet: An unqualified reference such as
Range("A1")refers to a range on the active sheet. Qualify it with a workbook and worksheet when the macro must target a specific location. - Overwriting formulas: Assigning to
Valuereplaces the cell’s formula. Check the target range before writing, particularly in reports or calculation sheets. - Confusing a blank with an empty string: A truly blank cell and a formula returning
""are not the same. Choose a test that matches what the macro should treat as empty. - Assuming formatted text is the stored value: Currency symbols, date layouts, and rounded displays can be formats applied to the value. Use
Textonly when the displayed representation is genuinely what the task requires. - Handling errors as ordinary values: A cell containing an error such as
#N/Areturns an error Variant. Check for an error before applying operations that expect a number or string. - Using the wrong range shape: A multi-cell read returns an array, while a single-cell read returns one value. Structure loops and assignments to match the number of cells involved.
In many VBA expressions, the Range object’s default property can make an explicit .Value appear optional. Explicitly writing it is usually clearer to readers and helps distinguish cell data from the Range object itself. It also makes the intention easier to maintain when code is revised.
Running VBA Safely and Choosing the Right Platform
VBA macros run in desktop Excel, including Microsoft 365 for Windows. Excel for the web can open a macro-enabled workbook, but it cannot create, run, or edit VBA macros. Desktop Excel for Mac supports VBA, although some Windows-specific features, controls, and automation interfaces may not be available or may behave differently.
On Windows, the Visual Basic editor is available from Developer > Visual Basic; if the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Treat macros as executable code: only run them when you trust their source, and do not lower macro security settings indiscriminately. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file does not preserve VBA code.
For simple tasks such as entering a fixed value or applying a formula, a worksheet feature or formula may be easier to maintain than a macro. VBA is most useful when a task involves repeated actions, workbook-specific rules, or automation that ordinary worksheet functions do not handle conveniently.
Frequently Asked Questions
What does .Value mean in Excel VBA? It is a Range property that reads or sets the value stored in a cell or range. It can return data such as text, numbers, dates, Boolean values, blanks, or Excel errors.
What is the difference between Value and Value2? Both access cell data, but Value2 does not return VBA Date and Currency subtypes. For date- or currency-formatted cells, it returns the underlying number instead.
Does Range.Value return the formula or its result? It returns the cell’s current calculated result. Use a formula property, such as Formula, when you need to read or set the formula itself.
Can I use Value with a range of cells? Yes. Reading a rectangular multi-cell range returns a two-dimensional array of values. A single-cell range returns one value, so code handling both cases should account for the different result shapes.
Can Excel for the web run a VBA macro? No. Excel for the web can work with a macro-enabled workbook but cannot run, create, or edit VBA macros. Use desktop Excel to run VBA.
Related
- Excel Not Showing All Rows? Find and Restore Hidden Rows
- Excel Not Pulling Data From Other Sheets? Fixes
- Excel Not Pasting All Cells Copied? Fixes That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macros: How They Work and When to Use ThemYes, you can use macros in Google Sheets in a web browser. Record a sequence of spreadsheet actions…
- Excel VBA PasteSpecial: Values, Formats, and MoreIn Excel VBA, use Range.PasteSpecial after copying a source range to paste only selected parts, such as values,…
- Excel Macro Programming: What It Is and How It WorksExcel macro programming means creating instructions that automate tasks in a workbook, most commonly with Visual Basic for…
- VBA Loop Through Dictionary Items: Keys, Values, and TipsTo loop through a VBA dictionary’s entries, use For Each on its keys, then retrieve each associated value…
- Excel Macro If Statement: VBA Syntax and Examples GuideAn Excel macro uses VBA’s If...Then...Else statement to make a decision and run different instructions depending on whether…
- Excel VBA Copy Range to Another Sheet: Methods and TipsTo copy a range to another worksheet with Excel VBA, qualify the source and destination sheets and use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.