VBA: TypeMismatch in Cells function, when cell value exceeds 256 characters

Question

What I want to do

I have a function that iterates through cells to see if they have an #N/A error or not. If so, a cell in the same row on another column should be coloured green.

The Problem

I am getting the error Run-time error 13: Type Mismatch. This happens, as soon as it reaches certain cells which contain text. What I found out so far, is that it seems to only throw this error for cells, that exceed 256 characters.

I am using Excel as part of an Office 365 subscription in 64-bit.

What I tried so far

I tried to ignore this error with On Error GoTo [ErrorLabel] and simply move on to the next iteration. This for some reason works only once. As soon as there is another cell being checked with more than 256 characters, I get the same error again.

EDIT: maybe it has to do with the function IsNA() expecting a Variant data type. I guess, since the cell value is too large for being a string, it is not stored in a variable at all, or it is simply something different than Variant.

This is part of a bigger module with several procedures. So it should be programmatic. isError and CVErr as proposed in the comments did not resolve this issue. Still getting a Type Mismatch.

The Code

The initial code, that produces the error:

For i = 2 To rowCount
If WorksheetFunction.IsNA(Cells(i, 4)) _
    Then Cells(i, 1).Interior.Color = RGB(0, 255, 0)

The code, that I tried to fix this issue. This still throws the error (see section "What I tried so far"):

For i = 2 To rowCount
On Error GoTo Error
'based on following condition
'   1. Name does not exist in previous weeks' sheet, identified by VLOOKUP being #N/A
    If WorksheetFunction.IsNA(Cells(i, 4)) _
        Then Cells(i, 1).Interior.Color = RGB(0, 255, 0)
Error:
    Next i

How To Reproduce

Create a blank sheet with one column containing a few lines of text with varying lengths. As shown below, at least one line should exceed 256 characters to test it.

Insert following code in VBA under Sheet1:

    Sub isText()
    Dim i As Integer
        For i = 2 To 6
        If WorksheetFunction.isText(Cells(i, 1)) _
            Then Debug.Print "Is Text" _
            Else Debug.Print "Is Not A Text"
        Next i
    End Sub

With this example the error is thrown as soon as it reaches line 6.

You can use conditional formatting for this btw and avoid VBA entirely. — BigBen, Sep 21 '20 at 14:33
As mentioned, unless this exists as part of a bigger procedure, conditional formatting would be a better option. It will automatically update, forgoing the need to re-run the subroutine. — basodre, Sep 21 '20 at 14:37
Either use `IsError` as suggested by @ScottCraner or use `CVErr`. Both of these methods are shown [HERE](https://stackoverflow.com/questions/11394029/check-a-n-a-value-in-vba-into-a-range) — Siddharth Rout, Sep 21 '20 at 14:59
This is part of a bigger module with several procedures. So it should be programmatic. `isError` and `CVErr` did not resolve this issue. Still getting a `Type Mismatch`. — Fortun, Sep 21 '20 at 15:29
I am unable to reproduce the issue. I tried cells which have more than 300 characters. Poosible to see your workbook? — Siddharth Rout, Sep 21 '20 at 15:36
I cannot reproduce your problem. `WorksheetFunction.IsNA()` does not raise a type mismatch for cells that contain more than 256 characters. I've tried in Excel 2016 and Excel 2003. — GSerg, Sep 21 '20 at 15:38
@SiddharthRout and GSerg I have added a section "How to reproduce". Could you please test it and see what happens? — Fortun, Sep 21 '20 at 16:00
The closest match I found which describes your case is [HERE](https://stackoverflow.com/questions/53128145/excel-type-and-worksheetfunction-istext-fail-for-long-strings) — Siddharth Rout, Sep 21 '20 at 16:10
BTW What is your Excel Version? Also 32 bit or 64 bit? Is MS Office set to receive automatic updates? — Siddharth Rout, Sep 21 '20 at 16:12
Thx @SiddharthRout I use Office 365 ProPlus 64-Bit. Updates are automatic, set by the admins. With regards to your link, I will take a closer look and see if that helps. — Fortun, Sep 21 '20 at 16:23
@SiddharthRout The link is interesting. Actually it seems that any text with >255 characters is stored as an array data type: ("=TYPE(CellWithVeryLongText)") returns 64, which is array according to [documentation](https://support.microsoft.com/en-us/office/type-function-45b4e688-4bc3-48b3-a105-ffa892995899?ocmsassetid=type-function-45b4e688-4bc3-48b3-a105-ffa892995899&ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60134&ui=en-us&rs=en-us&ad=us). — Fortun, Sep 21 '20 at 16:38
@Fortun Please try `If WorksheetFunction.isText(Cells(i, 1).Value2) Then` - it work for me with your code and data from "How To Reproduce" (but with `.isText(Cells(i, 1))` I get error too) — JohnSUN, Sep 21 '20 at 17:18
@JohnSUN This is it! I used the `.Value` property of the `Cells` object, and it finally works. So it had to do with the data type. I will answer my own question with the findings. — Fortun, Sep 22 '20 at 08:32

score 1 · Accepted Answer · answered Sep 22 '20 at 08:47

Thanks to the commenters, the issue is resolved.

Root Cause

the reason for this error likely has to do with the WorksheetFunctions.isNA() function. According to the documentation of this function, it is expecting a parameter of type Variant. But in my excel version (office 365 (16.0.12527.21096) 64-bit) any cell with strings containing more than 255 characters it is stored internally as an Array data type. This was checked with the =TYPE Excel function. It returns 64 to imply an array (documentation).

Solution

Instead of using Cells(rowNum, colNum) I used Cells(rowNum, colNum).Value which returns a Variant data type, not an Array. This is exactly the type, that I needed.

score 0 · Answer 2 · answered Sep 21 '20 at 15:38

It seems to me that you are in vain going through all the cells in search of erroneous formulas. Excel can give you all the errors with one operator and you can only iterate over them. Hopefully, in these cells you will not find texts longer than 4 or 5 characters:

Sub FindErrorsOnActiveSheet()
Dim ws As Worksheet
Dim aErrors As Range
Dim oCell As Range
    Set ws = ActiveSheet ' or any other
    Set aErrors = ws.Cells.SpecialCells(xlCellTypeFormulas, 16)
    For Each oCell In aErrors
        Debug.Print "Cell " & oCell.Address(False, False, xlA1) & " has error " & oCell.Text
        If oCell.Text = "#N/A" Then oCell.Offset(0, 1 - oCell.Column).Interior.Color = RGB(0, 255, 0)
    Next oCell
End Sub

score 0 · Answer 3 · answered Sep 21 '20 at 16:47

using:

and this code:

Sub isText()
    Dim str As Variant
    Dim i As Integer
    For i = 2 To 6
        str = Cells(i, 1)
        On Error Resume Next
            str = Left(str, 255)
        On Error GoTo 0
        If Not IsError(str) Then
            Debug.Print "Is Text"
        Else
            Debug.Print "Is Not A Text"
        End If
    Next i
End Sub

I get: