1
\$\begingroup\$

I have a sub which should only continue running if 4 specific worksheets are present. From online reading, such as @Tim Williams's comment in Test or check if sheet exists, I know that using an error thrown, to determine a course of action, can be frowned upon, but I found similar usage to mine in both that question and here.

Here is a simplified version:

Private Sub Test()

 If AllSheetsPresent Then MsgBox "Doing something" 

End Sub

Private Function AllSheetsPresent() As Boolean

  AllSheetsPresent = True

  Dim SheetsArr()

  On Error GoTo errhand
  SheetsArr = ThisWorkbook.Sheets(Array("Response Times", "Incidents", "Calls", "Resources", "NoC, CPR"))

  Exit Function

errhand:
  If Err.Number = 9 Then
      AllSheetsPresent = False
      MsgBox "Sheets are missing from..."
      End
  End If

End Function

Notes:

For the purposes of testing I am using ThisWorkbook. It would otherwise be a workbook variable passed to the function.

Questions:

  1. Is there a better way to perform this validation?

  2. Related to 1) are there any risks associated with this approach I should be aware of?

  3. If AllSheetsPresent Then MsgBox "Doing something" feels in some way superfluous as if False the code has stopped anyway. Again, this feels like an extension of 1) can this be re-written in a better way?

As this is working code I have posted here in the hope it is the right place to ask.

\$\endgroup\$
0
\$\begingroup\$

There are no risks because you are trapping the errors.

A better way to write the code would be to pass the target Workbook and the array of Sheets into the function for testing. In the Function itself use On Error Resume Next to turn off the Error notification, Err.Number <> 0 to test if an Error was thrown, and On Error Goto 0 to reset any Errors. I would also log the missing Sheets to the Immediate Window.

If you need to this pattern often, consider saving it in your Personal Macro Workbook.

Private Sub Test()
    Dim SheetsArr As Variant
    SheetsArr = Array("Response Times", "Incidents", "Calls", "Resources", "NoC, CPR")
    If AllSheetsPresent(ThisWorkbook, SheetsArr) Then MsgBox "Doing something"
End Sub

Private Function AllSheetsPresent(WB As Workbook, SheetsArr As Variant) As Boolean
    Dim msg As String, sh As Variant
    For Each sh In SheetsArr
        On Error Resume Next
        Set sh = WB.Sheets(sh)
        If Err.Number <> 0 Then msg = msg & sh & vbCrLf
        On Error GoTo 0
    Next

    If Len(msg) > 0 Then
        msg = Left(msg, Len(msg) - 1) 'Remove the last line return vbCrLf
        Debug.Print "These Sheets are not in " & WB.Name & ": " & Replace(msg, vbCrLf, ", "); ""

        msg = "These Sheets are not in " & WB.Name & vbCrLf & msg
        MsgBox msg, vbCritical, "Missing Sheets"
    Else
        AllSheetsPresent = True
    End If
End Function
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ This looks like an unfamiliar use of Call: Call WB.Sheets(sh).Name How does this work? \$\endgroup\$ – QHarr Jan 19 '18 at 6:04
  • \$\begingroup\$ I believe modular code is a good thing so presumably it is common practice to have these functions etc in different modules and declared as public? \$\endgroup\$ – QHarr Jan 19 '18 at 6:19
  • \$\begingroup\$ I have modified at present to circumvent the Call bit. \$\endgroup\$ – QHarr Jan 19 '18 at 6:33
  • \$\begingroup\$ Call is generally used when you just need to test if a collection will throw an error. In this case, there is not much downside to using Public. Unlike a Global variable that can be accidentally modified by another procedure this Function will return the same result. \$\endgroup\$ – user109261 Jan 19 '18 at 11:38
  • \$\begingroup\$ If I run as is, when the worksheets are present, I get False. When I changed to Call line and put Dim tempSht As Worksheet: Set tempSht = wb.Worksheets(sh) then I get True. Have I missed something? \$\endgroup\$ – QHarr Jan 19 '18 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.