VBA & Macro Problems

Why an Excel VBA Function Cannot Write to Another Cell

Emma BrownEmma Brown Nov 20, 2026 869 views

Question details

The user wants VBA code to calculate a value and write a separate value to another Excel cell.

Why an Excel VBA Function Cannot Write to Another Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet formula calls a VBA user-defined function that attempts to update another cell directly or through a Sub procedure.
Observed behavior
The function can return a calculated value, but attempting to change another cell can cause a #VALUE! error.
Before you start

Identify the sheet and cell you want to update, and make sure macros are enabled for a trusted workbook. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA code.

Solution 1Recommended

Use a separate VBA Sub to write to another cell

Run a Sub independently when your code needs to change a worksheet cell, and keep the worksheet function focused on returning a calculation.

A VBA function called from a worksheet formula cannot modify other cells or change the Excel environment. Calling a Sub from that function does not bypass this restriction.

1
Open a VBA module

Open the Visual Basic Editor from Developer > Visual Basic, then select Insert > Module.

2
Add the cell-writing macro

In the module, enter: Sub abc() ActiveSheet.Cells(10, 5).Value = 18 End Sub This writes 18 to cell E10 on the active sheet.

3
Run the Sub separately

Activate the target worksheet, select Developer > Macros, choose abc, and select Run. You can also assign the Sub to a button or call it from another macro that is not being evaluated as a worksheet function.

4
Keep calculations in the function

If you still need a worksheet function, remove the cell-writing statement and any call to abc. For example, use Function tryon(x, y) tryon = x * y End Function, then enter =tryon(A1,B1) in a worksheet cell.

Use a separate VBA Sub to write to another cell
Check the target sheet: ActiveSheet refers to the sheet selected when the macro runs. If the target is a specific sheet, replace ActiveSheet with a reference such as ThisWorkbook.Worksheets("Sheet1").
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. It can help you move everyday spreadsheet work to WPS, but it does not change Excel's restrictions on worksheet VBA functions; VBA support may vary.

Open and edit common Microsoft Excel formats, including .xlsx filesUse a familiar spreadsheet interface for formulas and cell editingMove everyday spreadsheet work with a lightweight office suiteUse WPS Office free for common document and spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my worksheet VBA function return #VALUE!?

A worksheet function can return a result but cannot directly change another cell or the Excel environment. Remove statements that write to cells and run that work in a separate Sub.

Can a worksheet function call a Sub that writes to a cell?

No. Calling the Sub from a function being evaluated by a worksheet formula does not bypass the restriction. Run the Sub independently through Developer > Macros, a button, or another macro.

How do I write to E10 on a specific sheet?

In a Sub, use a qualified reference such as ThisWorkbook.Worksheets("Sheet1").Range("E10").Value = 18. Replace Sheet1 with the target worksheet's name, then run the Sub separately.

How do I keep my VBA code in the workbook?

Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). Enable macros only when you trust the workbook.