Why an Excel VBA Function Cannot Set a Cell Value in WPS Spreadsheets
Question details
The user wants to understand why an Excel VBA user-defined function can run from the Visual Basic Editor but does not write a value to cell A2 when called from a worksheet cell, and how to fix it safely.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A custom VBA function attempts to set cell A2 from a worksheet function call.
- Observed behavior
- The function appears to work when run from the Visual Basic Editor but does not update the target cell when used as a worksheet formula.
Open the workbook in WPS Spreadsheets and press Alt+F11 to review the code in the Visual Basic Editor. Check whether the code is written as a Function or a Sub procedure and whether the target range is fully qualified with a worksheet name.
Use a Sub Procedure or Worksheet Event to Set Cell A2 Instead of a Worksheet Function
Use this when you need VBA code to change another cell; a Sub procedure can modify the workbook safely without breaking worksheet recalculation rules.
In WPS Spreadsheets and Excel, a user-defined function called from a worksheet cell runs during recalculation and should only return a value to the cell that contains the formula. It should not modify other cells. A Sub procedure can write to any cell when run manually or through an event.
In WPS Spreadsheets, open the workbook and press Alt+F11 to open the Visual Basic Editor.
In the Project Explorer, double-click the relevant worksheet module or go to Insert > Module to add a standard module.
Add code such as Sub WriteA2() Worksheets("Sheet1").Range("A2").Value = "not working" End Sub, replacing Sheet1 with your actual sheet name.
Run it from View > Macros > View Macros > Run, assign it to a button or shape, or use a worksheet event such as Worksheet_Change.
Rewrite the Worksheet Function to Return a Value Without Changing Other Cells
Use this when you want the custom function to work from a worksheet cell and you do not actually need it to write to another cell.
Use a Sub Procedure in WPS Spreadsheets to Set a Cell Value from VBA
In WPS Spreadsheets, move cell-writing logic from a worksheet function to a Sub procedure. Open the Visual Basic Editor, add a macro that writes to A2 on the correct sheet, then run it from the Macro dialog or attach it to a button or worksheet event.
- 1. Download and open WPS Office: Install WPS Office, open your workbook in WPS Spreadsheets, and enable macros if prompted.
- 2. Open the Visual Basic Editor: In WPS Spreadsheets, press Alt+F11 to open the Visual Basic Editor.
- 3. Create a Sub procedure: In the Project Explorer, insert a module or open the worksheet module, then add a Sub that writes to the target cell using a qualified range: Worksheets("Sheet1").Range("A2").Value = "not working".
- 4. Run or assign the macro: Go to View > Macros > View Macros, select the macro, and click Run, or assign it to a button or worksheet event.
Frequently Asked Questions
Why does a VBA function work in the Visual Basic Editor but not when called from a worksheet cell?
Worksheet functions run during recalculation and should only return a value to the cell that contains the formula. The Visual Basic Editor runs code directly, which is why the function may appear to work there. Use a Sub procedure for code that changes other cells.
How do I make a VBA macro write to cell A2 in WPS Spreadsheets?
Open the Visual Basic Editor with Alt+F11, insert a module, and add a Sub procedure such as Sub WriteA2() Worksheets("Sheet1").Range("A2").Value = "not working" End Sub. Run it from View > Macros > View Macros > Run.
Can a worksheet user-defined function return a value without changing other cells?
Yes. Edit the Function in the Visual Basic Editor, remove assignments to other cells, and set the function name to the result value. Then re-enter the worksheet formula to recalculate.
How do I prevent VBA from writing to the wrong sheet?
Qualify the range with a worksheet object, for example Worksheets("Sheet1").Range("A2").Value instead of Range("A2"). Use the actual sheet name in the code.
What is the difference between a Function and a Sub in Excel VBA?
A Function can return a value and is often called from a worksheet formula, so it should not modify the workbook. A Sub performs actions such as writing to cells and is run manually, from a button, or through an event.
