vba 用于检查单元格内容的Excel公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3963793/
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
Excel formula to check cell contents
提问by downwitch
I'm trying to create some conditional formatting at runtime (huzzah) for an add-in (double huzzah), and have found that, apparently, some functions cannot be used as they would in a normal worksheet. (I just get an invalid procedure call error 5 when trying to create the CF referencing a VBA function I could call in a cell, even though it's in the add-in and not the workbook; I can create the CF fine with a built-in function.) The clearest confirmation I've found for this is here, but it doesn't really explain what the problem is; that's the esoteric part, would love to hear more about what I can expect with this.
我正在尝试在运行时 (huzzah) 为加载项 (double huzzah) 创建一些条件格式，并且发现，显然，某些函数无法像在正常工作表中那样使用。（当我尝试创建引用 VBA 函数的 CF 时，我只是收到一个无效的过程调用错误 5，即使它在加载项中而不是工作簿中，我也可以在单元格中调用它；我可以使用内置的来创建 CF在函数中。）我找到的最清楚的确认是here，但它并没有真正解释问题是什么；这是深奥的部分，很想听听更多关于我对此的期望。
The rubber-meets-road part is: can I avoid VBA altogether, and use a series of Excel-only, built-infunctions to verify whether a given cell contains a constant (i.e. a value entered by a user), a formula (i.e. some kind of calculation, logical operation, etc.--pretty much starts with an =), or a link (i.e. a reference to a cell in another worksheet or another workbook)? I know Excel has this determination at its fingertips; witness the uses and speed of GoTo/Special. How can Iget at it though?
橡胶遇路部分是：我可以完全避免使用 VBA，并使用一系列仅限 Excel 的内置函数来验证给定单元格是否包含常量（即用户输入的值）、公式（即某种计算、逻辑运算等--几乎以 =) 或链接（即对另一个工作表或另一个工作簿中的单元格的引用）开头？我知道 Excel 有这种决心；见证 GoTo/Special 的使用和速度。如何我得到它虽然？
Thanks in advance for your help.
在此先感谢您的帮助。
采纳答案by downwitch
For Office versions 2013 and higher, the ISFORMULA1 function is available. Combining this with the NOTfunction, ANDfunction and either the COUNTBLANK, ISBLANKor LENfunction can produce a formula to determine whether a cell contains a constant.
对于 Office 2013 及更高版本，ISFORMULA1 功能可用。将此与NOT函数、AND函数以及COUNTBLANK、ISBLANK或LEN函数结合使用可以生成一个公式来确定单元格是否包含常量。
The standard formulas in E2:F2 are,
E2:F2 中的标准公式是，
=ISFORMULA(D2)
=AND(NOT(ISFORMULA(D2)), LEN(D2))
??????
？？？？？？
If further information on the nature of the cell value is required the TYPE functioncan be used to determine if the cell contents are a number, text, boolean, error or array.
如果需要有关单元格值性质的更多信息，可以使用TYPE 函数来确定单元格内容是数字、文本、布尔值、错误还是数组。
When used in concert the native worksheet functions discussed here can reproduce the results available from VBA's Range.SpecialCells methodand its xlCellTypeConstantsor xlCellTypeFormulasxlCellTypeenumeration.
当一起使用时，此处讨论的本机工作表函数可以重现从 VBA 的Range.SpecialCells 方法及其xlCellTypeConstants或xlCellTypeFormulas xlCellType枚举可用的结果。
1 The ISFORMULAfunction was introduced with Excel 2013. It is not available in earlier versions.
1个将ISFORMULA函数与Excel 2013年推出，是不是在早期版本。
回答by THEO
Not sure if this is what you want, but it seems to do what you are asking, at least some of it.
不确定这是否是您想要的，但它似乎可以满足您的要求，至少是其中的一些。
http://www.ozgrid.com/VBA/special-cells.htm
http://www.ozgrid.com/VBA/special-cells.htm
It's the range.specialcellsmethod.
这是range.specialcells方法。
It returns a range that contains only constants, or only formulas, etc.
它返回一个仅包含常量或仅包含公式等的范围。
An example of how this code would be used is shown below:
下面显示了如何使用此代码的示例：
Sub CheckForConstants()
Dim x As Range
Set x = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
MsgBox "address of cells that contain numbers only is " & x.Address
Set x = Selection.SpecialCells(xlCellTypeConstants)
MsgBox "address of cells that contain constant of any type is " & x.Address
End Sub
You select a range and then execute this macro and it will return the address of those cells that meet the requirements.
您选择一个范围，然后执行此宏，它将返回满足要求的那些单元格的地址。
The first x looks for cells that contains numbers only. The second x looks for cells that contains any constants
第一个 x 查找仅包含数字的单元格。第二个 x 查找包含任何常量的单元格
The range in this case was selection, but you can set to what you want, i.e. range("a1:b5"), etc.
在这种情况下，范围是选择，但您可以设置为您想要的范围，即 range("a1:b5") 等。
I went back to the worksheet and used the goto special method.
我回到工作表并使用 goto 特殊方法。
Apparently it also uses the range.special method.
显然它也使用 range.special 方法。
I used the record macro option and this is what I got.
我使用了记录宏选项，这就是我得到的。
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Range("M7").Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Range("I6:J16").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Range("L9").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("CP").Select
Application.CutCopyMode = False
Range("I21").Select
ActiveSheet.DrawingObjects.Select
Application.Goto Reference:="GoToSpecialRoutine"
The goto special feature on the worksheet uses the special cells method for some of what it does.
工作表上的 goto 特殊功能使用特殊单元格方法来执行某些操作。
It also uses others as well. In the last 5 lines of codes I changed worksheet and asked it to go to objects.
它也使用其他的。在最后 5 行代码中，我更改了工作表并要求它转到对象。
It doesn't really go to them. It just selects them.
它并没有真正属于他们。它只是选择它们。
worksheet CP contained objects and it used the code in the last 3 lines to select all the objects on the worksheet.
工作表 CP 包含对象，它使用最后 3 行中的代码来选择工作表上的所有对象。
Best bet to see the code behind goto special is to record a macro and then use the goto / special feature in the worksheet.
查看 goto special 背后的代码的最佳选择是记录一个宏，然后在工作表中使用 goto / special 功能。
When finished, Stop recording and view the macro you recorded.
完成后，停止录制并查看您录制的宏。
I don't know of any other features to select by type of cell, but I'm just a newby so it could be there very easily and not be known by me.
我不知道按单元格类型选择的任何其他功能，但我只是一个新手，所以它可以很容易地存在并且我不知道。
回答by osknows
I don't think you can avoid VBA altogether but you can create a simple UDF and use it within Excel
我认为您不能完全避免使用 VBA，但您可以创建一个简单的 UDF 并在 Excel 中使用它
Eg
例如
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function
and
和
Function IsLink(Check_Cell As Range)
If InStr(1, Check_Cell.Formula, "!", vbTextCompare) Then
IsLink = Check_Cell.HasFormula
End If
End Function
=IsFormula(A1) will return TRUE if there is a formula in A1 and FALSE otherwise =IsLink(A1) will return TRUE if there is a formula in A1 containing '!' otherwise FALSE
如果 A1 中有公式，=IsFormula(A1) 将返回 TRUE，否则如果 A1 中有包含“!”的公式，=IsLink(A1) 将返回 TRUE 否则为假
You could combine these and create a string output "Formula","Link","Value"
您可以组合这些并创建一个字符串输出“Formula”、“Link”、“Value”
