CODEWITHSUNDEEP
excelvbaerror-handlingerror-checking
Prostoskis
Prostoskis

Reputation: 1

VBA to search a specific worksheet for errors and pass back result to main macro

I need a macro which will search for formula errors in a specific worksheet in a workbook. This needs to work in multiple different workbooks with different worksheet names in each book. Once it has completed the check, I want it to define a variable called SheetErrors as "1" if there are errors or "0" if there aren't any. If it is "1", it then needs to be passed back to a main macro as I want it to exit the sub and display a message box stating that spreadsheet contains errors.

I have written the code below to find the errors:

Sub Error_Finder()

Dim celltxt As String
Dim SheetErrors As String

celltxt = ActiveSheet.Range("A:FA").Text



If InStr(1, celltxt, "#NULL!") Or _
   InStr(1, celltxt, "#NUM!") Or _
   InStr(1, celltxt, "#REF!") Or _
   InStr(1, celltxt, "#VALUE!") Then
   SheetErrors = "1"
   Else
   SheetErrors = "0"


End Sub

I can't find anything useful to me on Google for passing the SheetErrors variable back to the main macro. Any light you can shed on it would be gratefully received, also, I'm pretty new to VBA, so if there is a better way of doing what I have done above, please let me know.

Thank you,

P

Upvotes: 0

Views: 443

Answers (2)

Tim Hough
Tim Hough

Reputation: 11

I found this useful, but wanted something slightly different. This code allows you to check every sheet and create a log of the output.

Sub Check_for_errors()
Dim shl As Worksheet, sh As Worksheet, errcount As Long, r As Long

Set shl = ActiveWorkbook.Sheets.Add
shl.Name = "Error Log"
r = 1
errcount = 0

On Error Resume Next

For Each sh In ActiveWorkbook.Sheets
    If sh.Name <> "Error Log" Then
        
        errcount = sh.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Count
        If errcount > 0 Then
            shl.Cells(r, 1) = sh.Name
            shl.Cells(r, 2) = errcount
            r = r + 1
            errcount = 0
        End If
    End If
Next sh
    
End Sub

Upvotes: 1

Jchang43
Jchang43

Reputation: 891

If you want to pass something back you could consider making it a function instead. That would look something like this:

Edit: Thanks to K.Davis and Tim Williams for the suggestions

Function SheetErrors() As String

    Dim Rng As Range

    Set Rng = ActiveSheet.Range("A:FA")
    On Error Resume Next
    If (Rng.SpecialCells(xlCellTypeFormulas, xlErrors).count = 0) Then
        SheetErrors = "0"
    Else
        SheetErrors = "1"
    End If

    On Error GoTo 0

End Function

Upvotes: 3

Related Questions