Why an Excel VBA Function Cannot Write to Another Cell
Question details
The user wants VBA code to calculate a value and write a separate value to another Excel 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.
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.
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.
Open the Visual Basic Editor from Developer > Visual Basic, then select Insert > Module.
In the module, enter: Sub abc() ActiveSheet.Cells(10, 5).Value = 18 End Sub This writes 18 to cell E10 on the active sheet.
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.
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.

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.

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.




