How to Fix Excel Formulas Disappearing After Running a VBA Macro in WPS Spreadsheets
Question details
The user needs to identify why formulas in a large Excel workbook are replaced by static values after a VBA macro runs and how to locate the problematic code.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user runs a VBA macro in a large workbook and later finds that cells that previously contained formulas now contain only values.
- Observed behavior
- Formulas are replaced by values after the macro completes; the user wants to locate the macro statement causing it.
Save a copy of the workbook before testing any macro, and open the Visual Basic Editor in WPS Spreadsheets so you can search the macro code for .Value assignments.
Review Macro Code for .Value Assignments That Overwrite Formulas
Use this first to quickly search the macro for statements that copy only values back into formula cells.
Statements like Range("A1:Z99") = Range("A1:Z99").Value or Cells = Cells.Value replace formulas with calculated results. Code that reads worksheet data into an array and writes it back without preserving formulas causes the same issue.
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, then locate the macro module you ran.
Press Ctrl+F, enter .Value, and inspect each result. Look for lines such as Range("A1:Z99") = Range("A1:Z99").Value or Cells = Cells.Value.
Find code that reads worksheet data into a VBA array and writes it back, for example arr = Range(...).Value and Range(...).Value = arr. This overwrites formulas with values.
Correct or comment out the suspicious statement and run the macro on a copy of the workbook; verify formulas remain in the affected cells.
Isolate the Problem by Running the Macro Line by Line
Use this if the problematic code is not obvious; stepping through the macro reveals the exact line that replaces formulas.
Fix a VBA Macro That Replaces Excel Formulas with Values in WPS Spreadsheets
Open the VBA editor in WPS Spreadsheets, search for .Value assignments that overwrite formula cells, edit the macro, and test on a copy to keep formulas intact.
- 1. Open WPS Spreadsheets and the VBA editor: Launch WPS Spreadsheets, open the affected workbook, and press Alt+F11 to enter the VBA editor.
- 2. Search the macro for .Value: Use Ctrl+F to find every .Value reference in the macro module, especially assignments like Range(...) = Range(...).Value.
- 3. Edit the overwriting statement: Change the code so it writes formulas or skips formula cells, or remove the line that copies values over formulas.
- 4. Test the macro on a copy: Run the edited macro on a copy of the workbook and verify that all formulas remain.
- 5. Save the workbook: Save the corrected macro-enabled workbook in WPS Spreadsheets or as an Excel macro-enabled file.
Frequently Asked Questions
Why does my Excel formula change to a value after I run a macro?
A macro statement is likely assigning values back to formula cells, such as Range("A1:Z99") = Range("A1:Z99").Value or Cells = Cells.Value. Search the VBA code for .Value assignments to find it.
How do I search a VBA macro in WPS Spreadsheets for the code that removes formulas?
Press Alt+F11 to open the VBA editor, select the macro module, press Ctrl+F, and search for .Value. Review every assignment where the right side ends in .Value.
Can a macro that copies worksheet data to an array delete formulas?
Yes. Code that reads a range into an array with arr = Range(...).Value and then writes it back with Range(...).Value = arr replaces formulas with their current values.
Does WPS Spreadsheets support Excel VBA macros?
WPS Spreadsheets supports VBA macros and can open Microsoft Excel macro-enabled workbooks, including .xlsm and .xls files, while maintaining familiar VBA editing tools.
How can I safely test a macro without losing my formulas?
Save a copy of the workbook, open the VBA editor, and set a breakpoint before the suspect code. Run the macro and press F8 to step through line by line while watching the worksheet.
