Error check VBA

Billdub417

New Member
Joined
Nov 5, 2019
Messages
45
Hello,
I have some code that checks for errors in some cells before proceeding.

I need to use this error checking code in another VBA, however, rather than duplicate the code, is there a way to split this into a separate Sub, "CALL" it from both codes where it is being used, and if it flags an error, cancel the original VBA.

When I have tried splitting this out previously, the original code continued despite any errors (as if there were errors, it's just set to "Exit Sub")

Glad of any advice, Many thanks.

VBA Code:
 Sub CheckErrors()

     With Sheet5
      Dim cell As Range

Dim i As Integer
        i = 1
        For Each cell In .Range("D620:o620").Cells
            If cell.value <> 0 Then
            If cell.value <> "" Then
                MsgBox ("Sheet" & i & " does not balance")
               Exit Sub
            End If
            End If
          
            i = i + 1
        Next
    End With

      With Sheet27
     Dim cell1 As Range

Dim j As Integer
        j = 1
        For Each cell1 In .Range("e108:p108").Cells
            If cell1.value <> 0 Then
                MsgBox ("Line" & j & " does not balance")
                Exit Sub
            End If
            j = j + 1
        Next
    End With
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You can create a common proc in a module and then pass the sheet names to it. For example (Untested)

VBA Code:
Sub CheckErrors(shA As Worksheet, shB As Worksheet)
    Dim i As Long
    Dim cell As Range
   
    With shA
        i = 1
        For Each cell In .Range("D620:o620").Cells
            If cell.Value <> 0 Then
                If cell.Value <> "" Then
                    MsgBox ("Sheet" & i & " does not balance")
                    Exit Sub
                End If
            End If
         
            i = i + 1
        Next
    End With

    With shB
        i = 1
        For Each cell In .Range("e108:p108").Cells
            If cell.Value <> 0 Then
                MsgBox ("Line" & i & " does not balance")
                Exit Sub
            End If
            i = i + 1
        Next
    End With
End Sub

Usage would be like

VBA Code:
Sub Sample()
    CheckErrors Sheet5, Sheet27
End Sub
 
Upvote 0
Solution
Thanks, sorry, should have clarified, this is for use in 2 different modules. Would you have to enter this in both?
Thanks for the quick reply.
 
Upvote 0
If they are in the same workbook then you need to put the code
VBA Code:
Sub CheckErrors(shA As Worksheet, shB As Worksheet)
in a module and then you can call them from any module using

VBA Code:
CheckErrors Sheet5, Sheet27
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top