vba 如何确定公式或手动输入是否已写入单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22207540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine if a formula or a manual entry has been written in a cell
提问by ENGR024
I have a cell/ entire column that has a formula(s) in it. Sometimes I go into that cell and manually change that value to a different value. The formula goes away and is being over written by the manual entry. How can someone determine later down the line that that cell was being entered automatically with the formula or entered manually? Is there some rule or trick within conditional formatting or elsewhere that can indicate to the viewer that this cell was overwritten or that cell is being formulated by the generic formula.
我有一个单元格/整列，其中有一个公式。有时我会进入该单元格并手动将该值更改为不同的值。公式消失并被手动输入覆盖。稍后有人如何确定该单元格是使用公式自动输入还是手动输入？条件格式或其他地方是否有一些规则或技巧可以向查看者表明此单元格已被覆盖或该单元格正在由通用公式制定。
An example....
一个例子....
if the formula adds a selected range and gives a value and that cell is red now. If I enter a value manually in that cell, that cell now sees that value and turns it 'blue' because it was manually entered. Is there logic in excel that will differentiate this? Thanks
如果公式添加了一个选定的范围并给出了一个值，并且该单元格现在是红色的。如果我在该单元格中手动输入一个值，该单元格现在会看到该值并将其变为“蓝色”，因为它是手动输入的。excel中是否有逻辑可以区分这一点？谢谢
采纳答案by Dmitry Pavliv
UPD:
更新：
1) add UDF:
1）添加UDF：
Function hasFormula(r As Range) As Boolean
hasFormula = r.hasFormula
End Function
2) Select all cellsand apply following CF rules:
2）选择所有单元格并应用以下CF规则：
- Blue CF (for values):
=AND(A1<>"",NOT(hasFormula(A1))) - Red CF (for formulas):
=hasFormula(A1)
- 蓝色 CF（用于值）：
=AND(A1<>"",NOT(hasFormula(A1))) - 红色 CF（用于公式）：
=hasFormula(A1)
For Excel 2013you could use built-in function ISFORMULA:
对于 Excel 2013，您可以使用内置函数ISFORMULA：
Select all cellsand apply following CF rules:
选择所有单元格并应用以下 CF 规则：
- Blue CF (for values):
=AND(A1<>"",NOT(ISFORMULA(A1))) - Red CF (for formulas):
=ISFORMULA(A1)
- 蓝色 CF（用于值）：
=AND(A1<>"",NOT(ISFORMULA(A1))) - 红色 CF（用于公式）：
=ISFORMULA(A1)
Alternative way(for higlighting all cells with formulas):
替代方法（用于使用公式突出显示所有单元格）：
Press CTRL+G, then select "Special..."->"Formulas"and press "OK". But it highlights cells only temporary, untill you select any other cell.
按CTRL+ G，然后选择“特殊...”->“公式”并按“确定”。但它只是暂时突出显示单元格，直到您选择任何其他单元格。
