EVALUATE function crashing

[louiserace]

I have a couple of macros that have been running weekly for over 2 years. However, the first time they have been run this year the EVALUATE line crashes in the following piece of code:

    Dim deleteStudent As Boolean
    deleteStudent = Evaluate("IsError('Student Data'!1:1)")
    If deleteStudent = False Then
        Application.DisplayAlerts = False
        Worksheets("Student Data").Delete
        Application.DisplayAlerts = True
    End If

with the error:

Run-time error '13':​

Type mismatch​

I have tried old versions of the spreadsheets that ran perfectly last year and they crash as well.

What can have changed?

By the way, I know that the method I used is not the best way, but I had not been coding long and I would definitely do it another way now but I would still like to know why it has suddenly stopped working.

 

[Fluff]

What version of Xl are you running?

 

[louiserace]

It is crashing on Excel 365 and it worked fine on Excel 2016. I have only just found out that the Excel version has been upgraded for the users. Does EVALUATE not work in Excel 365?

 

[Fluff]

Do you have Dynamic Arrays.
If your not sure try typing =seq into a cell, does it suggest the function SEQUENCE?

 

[louiserace]

Yes I do have Dynamic Arrays.

 

[Fluff]

I think that's what's causing the problems, as it now tries to spill the formula, try this instead

   Dim deleteStudent As Boolean
   deleteStudent = Evaluate("Isref('Student Data'!A1)")
   If deleteStudent = True Then

 

[louiserace]

Thanks

It worked perfectly but also using IsError and replacing the 1:1 with A1 worked.

Thanks for solving the mystery.

 

[Fluff]

You're welcome & thanks for the feedback