Excel VBA formula calculation problem summary

First of all, we recommend a very good Excel programming learning site: http://club.excelhome.net/

Problem Background: Recently, my colleagues encountered two problems, which are described as follows.

Scenario 1: Excel tool. A cell is a complex formula, which is derived from data in other worksheets and calls a custom function. When the value of the data source is changed, the value of the cell in the formula changes to # Value !, Select the formula cell and press enter to find that the value of the formula cell is correct. The problem is inevitable.

Scenario 2: Use the VB tool to query and compute part of the data, and fill in the data source in the Excel template file. The Excel Formula cell is also a complex formula. When the data source is filled, the calculation result of the formula cell is automatically updated. However, when VB reads the Excel file, it finds that the value of the formula cell is # Value !. If you use VB to call the workbook and save it, then you can obtain the value of the cell in the formula.

To address the above two problems, I cannot solve them. After searching and sorting on the Excel learning website, the basic solution is as follows:

1. Make sure that the automatic calculation option in Excel is selected.

2. Try to change the format of the formula cell to regular.

3. Try to select all cells in all worksheets and set the format to regular.

4. Use the VBA code to modify the formula .... Fm = cell. formula, cell. formula = FM...

5. search and replace: replace all "=" search with "= ". Similar to function 4, the formula is refreshed.

6. Fixed the Excel file.