VBA & Macro Problems

Can an Excel VBA Function Write to Another Cell?

Rana GarciaRana Garcia Nov 18, 2026 870 views

Question details

Determine whether an Excel VBA user-defined function can write a value to a different cell when called from a worksheet formula.

Can an Excel VBA Function Write to Another Cell?
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants a VBA function called from a worksheet formula to update another cell.
Observed behavior
The code works from the Immediate window but cannot write to another cell when Excel calls it as a worksheet function.
Before you start

Identify the cell containing the formula and the cell you want to update. Check whether you need an automatically calculated result or a macro that writes a value when you run it.

Solution 1Recommended

Return the result from the worksheet function

Use this approach when the result can appear in the cell containing the formula.

A VBA function called from a worksheet formula cannot modify arbitrary cells during recalculation. Instead, it should return a result to the formula cell. Although the code may work when run from the Immediate window, worksheet calculation applies different restrictions.

A function can also return an array that spills into adjacent cells in supported Excel versions, but this does not let it write to an arbitrary cell.

1
Open the VBA editor

Open the VBA editor in Excel, then select Insert > Module to create a standard module.

2
Create a returning function

Enter this example in the module: Function DoubleValue(ByVal x As Double) As Double DoubleValue = x * 2 End Function

3
Enter the worksheet formula

Type a number in A2, then enter =DoubleValue(A2) in the cell where you want the result to appear.

Worksheet function limitation: Do not use a worksheet-called function to assign values to other cells. This restriction prevents side effects during recalculation, including potential circular-reference problems.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday workbooks. It does not remove Excel's worksheet-function restrictions; check macro support and compatibility before moving a VBA-dependent workbook.

Open and edit Excel XLSX spreadsheetsUse spreadsheet formulas for calculated resultsNavigate a familiar spreadsheet interfaceMove everyday Office documents into a lightweight suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my function work in the Immediate window but not in a worksheet formula?

When Excel calls a function from a worksheet formula, it restricts changes to other cells during recalculation. Running code from the Immediate window is a different execution context.

Can an Excel UDF return results to multiple cells?

Yes. In supported Excel versions, a UDF can return an array that spills into adjacent cells. It cannot use that mechanism to write to an arbitrary cell elsewhere.

How can I update a different cell with VBA?

Create a Sub procedure in the VBA editor that assigns a value to the target Range, then run it from Excel's Macros dialog. It will not run automatically just because a worksheet input changes.

What file format should I use for a workbook containing VBA?

In Excel, choose Save As and select Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code. Enable macros only when you trust the workbook.