Excel VBA - 解释“N/A”值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4624861/
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 VBA - Interpret "N/A" Values
提问by Tom
I am traversing a spreadsheet which contains a column of prices, in the form of doubletypes. I am trying to locate a missing value which is shown on the spreadsheet as "n/a", but it is not letting me interpret this as a stringtype.
我正在遍历一个电子表格，其中包含一列价格，以double类型的形式。我试图找到在电子表格中显示为 的缺失值"n/a"，但它不允许我将其解释为一种string类型。
The cell containing "n/a"seems to be an integertype; how can I read this?
包含的单元格"n/a"似乎是一种integer类型；我怎么能读到这个？
采纳答案by Charles Williams
A cell containing #N/A is retrieved by VBA as a variant containing an error code
VBA 将包含 #N/A 的单元格检索为包含错误代码的变体
In general its usually best to assign Excel cells to Variants because a cell can contain a number(double), logical, string or error and you cannot tell in advance what the cell wil contain.
一般来说，通常最好将 Excel 单元格分配给变量，因为单元格可以包含数字（双精度）、逻辑、字符串或错误，并且您无法提前知道单元格将包含什么。
回答by barrowc
If all you want to do is to check for the error value then:
如果您只想检查错误值，则：
Application.WorksheetFunction.IsNA(rngToCheck.Value)
Application.WorksheetFunction.IsNA(rngToCheck.Value)
where rngToCheckis the cell which you want to check for the #N/Aerror value
rngToCheck您要检查#N/A错误值的单元格在哪里
(There's a list of the worksheet functions which can be called from Excel VBA here)
（有可以从Excel VBA调用的工作表函数的列表在这里）
You could also examine rngToCheck.Textas this will contain the string "#N/A"
您还可以检查，rngToCheck.Text因为这将包含字符串“#N/A”
If instead, you want to read the formula in the cell which generated the #N/Athen rngToCheck.Formulawould do that
相反，如果您想读取生成的单元格中的公式，#N/A则rngToCheck.Formula可以这样做
回答by Matt
You can prepare the spreadsheet you like to check as described below and evaluate the special cells containing the IS Functions, it is easy to check them for Trueor Falsein VBA. Alternatively, you can write your own VBA function as shown below.
您可以按如下所述准备您喜欢检查的电子表格并评估包含IS 函数的特殊单元格，很容易在 VBA 中True或False在 VBA 中检查它们。或者，您可以编写自己的 VBA 函数，如下所示。
There are Excel functionswhich check cells for special values, for example:
有一些Excel 函数可以检查单元格的特殊值，例如：
=ISNA(C1)
(assumed that C1 is the cell to check). This will return Trueif the cell is #N/A, otherwise False.
（假设 C1 是要检查的单元格）。True如果单元格是#N/A，这将返回，否则返回False。
If you want to show whether a range of cells(say "C1:C17") has any cell containing #N/Aor not, it might look sensible to use:
如果您想显示一系列单元格（例如“ C1:C17”）是否包含任何单元格#N/A，使用以下方法可能看起来很明智：
=if(ISNA(C1:C17); "There are #N/A's in one of the cells"; "")
Sadly, this is not the case, it will not work as expected. You can only evaluate a single cell.
可悲的是，情况并非如此，它不会按预期工作。您只能评估单个单元格。
However, you can do it indirectlyusing:
但是，您可以使用以下方法间接执行此操作：
=if(COUNTIF(E1:E17;TRUE)>0; "There are #N/A's in one of the cells"; "")
assuming that each of the cells E1through E17contains the ISNAformulas for each cell to check:
假设每个小区E1通过E17包含ISNA式针对每个小区来检查：
=ISNA(C1)
=ISNA(C2)
...
=ISNA(C17)
You can hide column Eby right-clicking on the column and selecting Hidein Excel's context menu so the user of your spreadsheet cannot see this column. They can still be accessed and evaluated, even if they are hidden.
您可以E通过右键单击列并在 Excel 的上下文菜单中选择隐藏来隐藏列，这样电子表格的用户就看不到该列。即使它们被隐藏，它们仍然可以被访问和评估。
In VBAyou can pass a range object as RANGEparameter and evaluate the values individually by using a FOR loop:
在VBA 中，您可以将范围对象作为RANGE参数传递并使用 FOR 循环单独评估值：
Public Function checkCells(Rg As Range) As Boolean
Dim result As Boolean
result = False
For Each r In Rg
If Application.WorksheetFunction.IsNA(r) Then
result = True
Exit For
End If
Next
checkCells = result
End Function
This function uses the IsNA() function internally. It must be placed inside a module, and can then be used inside a spreadsheet like:
此函数在内部使用 IsNA() 函数。它必须放在一个模块中，然后可以在电子表格中使用，例如：
=checkCells(A1:E5)
It returns True, if any cell is #N/A, otherwise False. You must save the workbook as macro-enabled workbook (extension XLSM), and ensure that macros are not disabled.
True如果有任何单元格，则返回#N/A，否则返回False。您必须将工作簿保存为启用宏的工作簿（扩展名XLSM），并确保未禁用宏。
Excelprovides more functions like the above:
Excel提供了更多类似上面的功能：
ISERROR(), ISERR(), ISBLANK(), ISEVEN(), ISODD(), ISLOGICAL(),
ISNONTEXT(), ISNUMBER(), ISREF(), ISTEXT(), ISPMT()
For example, ISERR()checks for all cell errors except #N/Aand is useful to detect calculation errors.
例如，ISERR()检查所有单元格错误，除了#N/A可用于检测计算错误。
All of these functions are described in the built in helpof Excel (press F1 and then enter "IS Functions"as search text for an explanation). Some of them can be used inside VBA, some can only be used as a cell macro function.
所有这些函数都在Excel的内置帮助中进行了描述（按 F1，然后输入“IS 函数”作为搜索文本以获取解释）。其中一些可以在 VBA 中使用，一些只能用作单元宏函数。
