Excel VBA - Interpret "N/A" Values

Question

I am traversing a spreadsheet which contains a column of prices, in the form of double types. 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 string type.

The cell containing "n/a" seems to be an integer type; how can I read this?

Answer 1

If all you want to do is to check for the error value then:

Application.WorksheetFunction.IsNA(rngToCheck.Value)

where rngToCheck is the cell which you want to check for the #N/A error value

(There's a list of the worksheet functions which can be called from Excel VBA here)

You could also examine rngToCheck.Text as this will contain the string "#N/A"

If instead, you want to read the formula in the cell which generated the #N/A then rngToCheck.Formula would do that

Answer 2

A cell containing #N/A is retrieved by VBA as a variant containing an error code

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.

Answer 3

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 True or False in VBA. Alternatively, you can write your own VBA function as shown below.

---

There are Excel functions which check cells for special values, for example:

=ISNA(C1)

(assumed that C1 is the cell to check). This will return True if the cell is #N/A, otherwise False.

If you want to show whether a range of cells (say "C1:C17") has any cell containing #N/A or not, it might look sensible to use:

=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 indirectly using:

=if(COUNTIF(E1:E17;TRUE)>0; "There are #N/A's in one of the cells"; "")

assuming that each of the cells E1 through E17 contains the ISNA formulas for each cell to check:

=ISNA(C1)
=ISNA(C2)
...
=ISNA(C17)

You can hide column E by right-clicking on the column and selecting Hide in 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.

---

In VBA you can pass a range object as RANGE parameter and evaluate the values individually by using a FOR loop:

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:

=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.

---

Excel provides more functions like the above:

ISERROR(), ISERR(), ISBLANK(), ISEVEN(), ISODD(), ISLOGICAL(), 
ISNONTEXT(), ISNUMBER(), ISREF(), ISTEXT(), ISPMT()

For example, ISERR() checks for all cell errors except #N/A and is useful to detect calculation errors.

All of these functions are described in the built in help of 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.