CODEWITHSUNDEEP
excelvba
sethW
sethW

Reputation: 185

How do I use IsError on a function value, instead of cell value?

I have a column in a sheet with unformatted date values. There are three types, always leading off with the month abbreviation:

JAN/19
JAN02/19
JAN19

I'm writing a macro to loop through the column and format these as dates. JAN/19 and JAN19 will both just be set to the first day of the month, whereas JAN02/19 will be set as 01/02/19.

In order to error handle for column headings and potential blank rows at the top, I want to take the left three characters and see if they are a month abbreviation. I'm trying to use the below code:

If IsError(DateValue("01 " & Left(Cells(1, 1), 3) & " 19")) = True Then

     MsgBox "Oops, " & Left(Cells(1, 1), 3) & " is not a valid month abbreviation"

End If

It skips the message box if Left(Cells(1, 1), 3) is a valid month abbreviation, but then gives this error if it isn't, instead of the MsgBox message.

enter image description here

Upvotes: 3

Views: 312

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Error is a data type.

The IsError function is used to determine whether a Variant has a variant subtype Error, i.e. whether a given value is a Variant/Error.

In Excel, you get a Variant/Error value when you read a cell containing e.g. #VALUE!. Programmatically, you can get a Variant/Error using the CVErr function together with Excel errors enum values, for example:

Debug.Print IsError(CVErr(xlErrNA)) ' #N/A cell error

The problem in this expression:

If IsError(DateValue("01 " & Left(Cells(1, 1), 3) & " 19")) = True Then

..is that IsError is given a Date, so the function will always return False (= True is redundant).

But IsError doesn't even get to be evaluated: its arguments need to be evaluated first, and that's where the type mismatch error is:

Left(Cells(1, 1), 3)

If Cells(1, 1) contains a Variant/Error value, then coercing it into a String throws the type mismatch error that you're getting, because an Error can't be implicitly (or explicitly) converted to any other data type.

The solution is to pull Cells(1, 1) into its own local Variant variable:

Dim cellValue As Variant
cellValue = ActiveSheet.Cells(1, 1).Value 'note: made qualifier and member call explicit

Then you can evaluate whether that value is an Error:

If Not IsError(cellValue) Then
    If IsDate("01 " & Left(cellValue, 3) & " 19") Then
        ' we're looking at a valid date value that VBA can convert to a Date data type
    Else
        ' we're looking at a malformed date
    End If
Else
    ' cellValue is a Variant/Error that we can't use.
End If

Upvotes: 2

Vityata
Vityata

Reputation: 43585

IsDate() is a rather handy function, which should work as intended:

Sub TestMe()

    If IsDate("01 " & Left(Cells(1, 1), 3) & " 19") Then
        Debug.Print "It is a date!"
    Else
        Debug.Print "Oops, " & Left(Cells(1, 1), 3) & " is not a valid month abbreviation"
    End If

End Sub

Upvotes: 3

Greg Viers
Greg Viers

Reputation: 3523

IsError() will not catch a type mismatch. You would need to do a run-time error handler to catch an error like that after it's happened.

On the other hand, for this use case you could use IsNumeric() instead of IsError (). I recommend something like this:

If Not (IsNumeric(Left(Cells(1, 1), 3))) Then    
    MsgBox "Oops, " & Left(Cells(1, 1), 3) & " is not a valid month abbreviation"   
End If

Upvotes: 0

Related Questions