Yes. VBA can change an Excel cell by assigning a value to its Range.Value property, usually from a macro (a Sub). A VBA Function can also write to a cell when called by other VBA code, but a function used as a worksheet formula should not change other cells; it should return a result to the cell containing the formula.
What “change a cell value” means in VBA
In VBA, a cell or group of cells is represented by a Range object. To replace a cell’s contents, put the target range on the left side of an assignment and the new value on the right. For example, Range("B2").Value = "Done" puts the text Done in B2. The assignment changes the cell itself; it is not an Excel worksheet formula.
A Sub performs an action and does not return a value. A Function calculates and returns a value, although VBA code can also make a function perform other actions. This distinction matters because Excel treats a function entered into a worksheet differently from a procedure run as a macro. For ordinary cell updates, a Sub is usually the clearest and least surprising choice.
When a macro writes to a cell, it replaces the existing contents there. It does not automatically preserve a formula that was in the target cell. It generally leaves formatting in place, because changing a value is different from changing the cell’s formatting.
Set a cell value with a simple macro
This example writes a fixed text value to B2 on the active worksheet. Before running a macro, save a copy of the workbook: macro changes cannot be reversed with Ctrl+Z.
Sub SetB2Value()
Dim ws As Worksheet
' Use the worksheet currently selected in Excel.
Set ws = ActiveSheet
ws.Range("B2").Value = "Done"
End Sub
To add the code in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Return to Excel, select the worksheet you intend to change, and run the macro from the editor or through Developer > Macros. If the Developer tab is not visible, enable it in Excel’s ribbon settings.
On Mac, open the Visual Basic Editor from Developer > Visual Basic; the keyboard shortcut can vary with keyboard and function-key settings. Excel for the web cannot create or run VBA macros, so use the desktop Excel app. If the workbook must retain its macro, save it in a macro-enabled format such as .xlsm, and only enable macros in files you trust.
Can a VBA Function change a cell?
It can when another VBA procedure calls it. For example, this function accepts one target cell and one value, writes the value, and returns True to indicate that it completed. It is deliberately written for calls from VBA, not for entry as a formula in a worksheet.
Function WriteCellValue(ByVal target As Range, _
ByVal newValue As Variant) As Boolean
' Accept one cell so the write has an unambiguous target.
If target Is Nothing Then Exit Function
If target.Cells.CountLarge <> 1 Then Exit Function
target.Value = newValue
WriteCellValue = True
End Function
Sub CallWriteCellValue()
Dim ws As Worksheet
Dim wasWritten As Boolean
Set ws = ThisWorkbook.Worksheets("Sheet1")
wasWritten = WriteCellValue(ws.Range("C3"), "Ready")
End Sub
Change Sheet1 to the actual worksheet tab name in your workbook. This example shows why a function can be useful inside VBA, but it does not make the function a suitable worksheet formula. Calling a function from a macro is a controlled VBA operation; entering =WriteCellValue(...) in a worksheet asks Excel to calculate a result, and worksheet user-defined functions are not permitted to change other cells as a side effect. Excel may report an error or fail to perform the write. For worksheet formulas, return a value instead; for a deliberate write, run a Sub.
Use another cell or a variable as the new value
The right side of the assignment does not have to be a literal such as text or a number. It can refer to another cell, a variable, or the result of a calculation. In the following example, the macro copies the displayed underlying value from A2 to B2 on the active sheet.
Sub CopyA2ToB2()
Dim ws As Worksheet
' Read the source cell and write its value to the destination.
Set ws = ActiveSheet
ws.Range("B2").Value = ws.Range("A2").Value
End Sub
This copies the value, not the source cell’s formatting or its formula text. If A2 contains a formula, B2 receives the formula’s current result. To copy the formula itself, assign the source’s Formula property to the destination instead. A macro can also calculate a value first and then assign it—for example, set a declared variable to a total and write that variable to a report cell.
Using a worksheet variable makes the target explicit and helps prevent writing to the wrong sheet. ActiveSheet means whichever sheet is selected when the macro runs. By contrast, ThisWorkbook.Worksheets("Sheet1") refers to the workbook containing the VBA code and a sheet with that tab name. Choose the reference that matches the intended workflow, especially if users may have several workbooks open.
Choose between Value, Value2, Formula, and ClearContents
For most straightforward assignments, .Value is readable and appropriate. .Value2 also reads or writes a cell’s value, but does not use VBA’s special Currency and Date data types when transferring values. Excel stores dates as serial numbers and displays them according to cell formatting, so a value that looks like a date can be a number underneath. Use .Value2 when you specifically want the underlying value representation; use .Value when its familiar handling is more suitable.
Use .Formula when the goal is to put an Excel formula into a cell, rather than its result. For example, with the active sheet as the target and B2 as the destination, Range("B2").Formula = "=SUM(A1:A5)" puts a formula in B2. Excel recalculates it as appropriate. A formula assigned this way is still a formula in the worksheet, unlike a fixed value assigned with .Value.
To remove contents while keeping formatting, use ClearContents on the target range. The Clear method removes more than contents, including formatting, so it is not interchangeable. Select the operation based on the desired result: overwrite with a value, insert a formula, clear only contents, or clear contents and formatting.
Run macros carefully and target the right sheet
A macro writes wherever its range reference points. A short, unqualified reference such as Range("B2") is tied to the active sheet, which can be a problem if the user changes tabs before running the code. Qualifying the range with a worksheet variable makes the target easier to verify. For example, ws.Range("B2").Value = "Done" states both the worksheet object and the cell reference.
Before running a procedure that changes cells, consider these safeguards:
- Save a separate copy of the workbook first. VBA changes are not undone by Ctrl+Z.
- Check the worksheet name and destination address, and confirm the target does not contain information you need.
- Test a new macro on a copy or an unimportant sample range before applying it to a large data set.
- Use a macro-enabled workbook when you need to keep VBA code, and follow your organization’s macro-security rules.
These precautions matter even for a one-cell change: the assignment can overwrite a formula, a manually entered value, or data that another process expects to remain unchanged.
Common problems and how to fix them
If VBA reports “Subscript out of range,” check that the worksheet name in the code exactly matches a tab name in the workbook being referenced. If the macro runs but appears to change the wrong sheet, check whether the code uses ActiveSheet or an unqualified Range. Replace ambiguous references with a worksheet variable where possible.
If a cell’s formula disappears, the code probably assigned to that cell’s Value property. That is expected: an assignment replaces the cell’s contents. If you intended to insert a calculation, assign a formula through .Formula. If you meant to preserve an existing formula, change a different destination cell instead.
A protected worksheet may prevent a macro from writing to locked cells. Check the sheet’s protection settings and ensure the macro is allowed to make the intended change. Also check whether the macro is actually running: macros may be blocked, the workbook may be open in Excel for the web, or code may be in a location that is not available to the current workbook.
Finally, a function that works when called by a VBA Sub may not work as a worksheet formula. That difference is not a typo in the assignment. Worksheet functions are expected to return calculation results, not to edit other cells, so use a macro procedure for the write.
Which approach should you use?
Use a direct assignment in a Sub when a user action or automated routine should change a cell. It is simple to read, easy to qualify with a worksheet reference, and fits VBA’s procedural model. Use a VBA Function when you need to calculate and return a result to VBA code; it may also perform a controlled write when called by VBA, but that side effect should be made clear to anyone maintaining the code.
Use an ordinary worksheet formula when the answer should update automatically as its input cells change. A formula belongs in the cell where its answer should appear and should return that answer rather than write into other cells. If you need automation across many rows or repeatable user actions, a macro can be appropriate, but it should have a clearly defined range and be tested on a copy first.
Frequently Asked Questions
Can I change a cell value with a VBA function?
Yes, if the function is called by other VBA code. For a function entered as a worksheet formula, return a result instead of trying to write to a different cell; use a Sub macro for that write.
What is the VBA code to put text in cell A1?
In a procedure, use Range("A1").Value = "Your text" for the active sheet, or qualify the range with a worksheet such as ws.Range("A1").Value to specify the target sheet.
Does assigning a value keep the cell’s formula?
No. Assigning to .Value replaces the contents, including any formula in that cell. Assign to a different cell if you need to retain the formula, or use .Formula to place a formula in the target.
How do I copy a cell’s value without copying its formula?
Assign the source cell’s .Value to the destination cell’s .Value. The destination receives the source’s current result or entry, not its formula or formatting.
Can I undo a VBA cell change with Ctrl+Z?
Usually not. Running VBA clears Excel’s normal undo history, so save a copy before running code that changes workbook data.
Can I run VBA in Excel for the web?
No. Open the workbook in the desktop Excel app to create or run VBA macros. Excel for the web can work with a macro-enabled workbook, but it does not run the VBA code.
Related
- Dynamic Checklist in Excel: Build One That Updates
- Excel Dependent Dropdowns: How to Create Linked Lists
- Excel Forecast Sheet: Does It Use AI and How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find the Last Row of Data in a Column EasilyTo find the last populated row in column A with Excel VBA, use Cells(Rows.Count, "A").End(xlUp).Row, with the worksheet…
- Excel VBA Active Range: What Selection Really MeansExcel VBA does not have a built-in ActiveRange property. To refer to the range currently selected in Excel,…
- Excel VBA Delete Row: Methods, Examples, and Best PracticesTo delete a worksheet row with Excel VBA, reference the worksheet and call its Rows collection’s Delete method—for…
- Excel VBA Module: How It Works, Types, and When to Use OneAn Excel VBA module is a container for Visual Basic for Applications code, such as macros, helper procedures,…
- Excel VBA Find Cell: Code, Options, and ExamplesIn Excel VBA, use the Range.Find method to locate a cell containing specified text or a value. Set…
- Excel VBA GoTo: Syntax, Examples, and Error HandlingIn Excel VBA, GoTo transfers execution to a named label elsewhere in the same procedure. It is most…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.