vba 除非我按 Enter，否则 Excel 不会更新值

Excel doesn't update value unless I hit Enter

提问by Thanasis

I have a very annoying problem in one of my worksheets in a workbook. I am using EXCEL 2007. Any cell's value will not be updated unless I hit ENTER. Either if the formula in the cell includes an if condition, or a VLOOKUP function or even an AVERAGE function. All the calculations are set to automatic, Application.Calculation = xlAutomatic, and even the calculations for the specific worksheet are enabled, like : ws.EnableCalculation = TRUE.

我在工作簿中的一张工作表中有一个非常烦人的问题。我正在使用 EXCEL 2007。除非我按 ENTER，否则不会更新任何单元格的值。如果单元格中的公式包含 if 条件，或者 VLOOKUP 函数甚至 AVERAGE 函数。所有计算都设置为自动，Application.Calculation = xlAutomatic，甚至启用特定工作表的计算，例如：ws.EnableCalculation = TRUE。

Furthermore, the ScreenUpdating is set to TRUE. After I hit the ENTER or I drag down the right corner, the cells will be updated, and they will keep being updated if I make any change. However, after saving the file and reopening it again they will be frozen again. I haven't figured out exactly when they will stop being updated again. All the formatting are set to General or number.

此外，ScreenUpdating 设置为 TRUE。在我按下 ENTER 或向右下角拖动后，单元格将被更新，如果我进行任何更改，它们将继续更新。但是，在保存文件并再次重新打开后，它们将再次被冻结。我还没有弄清楚他们什么时候会再次停止更新。所有格式都设置为常规或数字。

Especially in IF conditions, when I check the calculations through the evaluate feature, the result is correct but it is not passed on the screen.

特别是在 IF 条件下，当我通过评估功能检查计算时，结果是正确的，但没有在屏幕上传递。

Any suggestion? This thing is driving me crazy.

有什么建议吗？这件事让我发疯。

回答by AndyGneiss

Executive summary / TL;DR:
Try doing a find & replace of "=" with "=". Yes, replace the equals sign with itself. For my scenario, it forced everything to update.

执行摘要 / TL;DR：
尝试使用“=”查找并替换“=”。是的，用它自己替换等号。对于我的场景，它强制所有内容更新。

Background:
I frequently make formulas across multiple columns then concatenate them together. After doing such, I'll copy & paste them as values to extract my created formula. After this process, they're typically stuck displaying a formula, and not displaying a value, unless I enter the cell and press Enter. Pressing F2 & Enter repeatedly is not fun.

背景：
我经常在多列中创建公式，然后将它们连接在一起。完成此操作后，我会将它们复制并粘贴为值以提取我创建的公式。在此过程之后，除非我输入单元格并按 Enter，否则它们通常会卡在显示公式中，而不显示值。反复按 F2 和 Enter 并不好玩。

回答by Carl Kevinson

It sounds like your workbook got set to Manual Calculation. You can change this to Automatic by going to Formulas > Calculation > Calculation Options > Automatic.

听起来您的工作簿已设置为手动计算。您可以通过转到“公式”>“计算”>“计算选项”>“自动”将其更改为“自动”。

Manual calculation can be useful to reduce computational load and improve responsiveness in workbooks with large amounts of formulas. The idea is that you can look at data and make changes, then choose when you want to make your computer go through the effort of calculation.

手动计算可用于减少计算负载并提高具有大量公式的工作簿的响应能力。这个想法是您可以查看数据并进行更改，然后选择何时让您的计算机进行计算。

回答by Thanasis

I have the same problem with that guy here: mrexcel.com/forum/excel-questions/318115-enablecalculation.htmlApplication.CalculateFullsold my problem. However I am afraid if this will happen again. I will try not to use EnableCalculationagain.

我和这里的那个人有同样的问题：mrexcel.com/forum/excel-questions/318115-enablecalculation.htmlApplication.CalculateFull出售了我的问题。但是，我担心这种情况会不会再次发生。我会尽量不再使用EnableCalculation。

回答by Mathew

This doesn't sound intuitive but select the column you're having the issue with and use "text to column" and just press finish. This is the suggested answer from Excel help as well. For some reason in converts text to numbers.

这听起来并不直观，但选择您遇到问题的列并使用“文本到列”，然后按完成。这也是 Excel 帮助中的建议答案。由于某种原因，将文本转换为数字。

回答by Kamol Singh

Found the problem and couldn't find the solution until tried this.

找到了问题，直到尝试过才找到解决方案。

1. Open Visual Basic from Developer tab (OR right-click at any sheet and click 'View code')
2. At upper left panel, select 'ThisWorkbook'
3. At lower left panel, find 'ForceFullCalculation' attribute
4. Change it from 'False' to 'True' and save it

1. 从“开发人员”选项卡打开 Visual Basic（或右键单击任何工作表，然后单击“查看代码”）
2. 在左上面板，选择“ThisWorkbook”
3. 在左下面板，找到“ForceFullCalculation”属性
4. 将其从“假”更改为“真”并保存

I'm not sure if this has any side-effect, but it is work for me now.

我不确定这是否有任何副作用，但它现在对我有用。

回答by user9742503

Select all the data and use the option "Text to Columns", that will allow your data for Applying Number Formatting ERIK

选择所有数据并使用“文本到列”选项，这将允许您的数据应用数字格式 ERIK

回答by java-addict301

I ran into this exact problem too. In my case, adding parenthesis around any internal functions (to get them to evaluate first) seemed to do the trick:

我也遇到了这个确切的问题。就我而言，在任何内部函数周围添加括号（让它们首先评估）似乎可以解决问题：

Changed

改变了

=SUM(A1, SUBSTITUTE(A2,"x","3",1), A3) 

to

到

=SUM(A1, (SUBSTITUTE(A2,"x","3",1)), A3) 

回答by Alex Chuan

I Encounter this problem before. I suspect that is some of ur cells are link towards other sheet, which the other sheets is returning #NAME? which ends up the current sheets is not working on calculation.

我以前遇到过这个问题。我怀疑您的某些单元格是否链接到其他工作表，而其他工作表正在返回#NAME？最终导致当前工作表无法进行计算。

Try solve ur other sheets that is linked

尝试解决您链接的其他工作表