VBA help - Check sht exists if not exit sub

[Mallesh23]

Hi Team,

I am checking sheetname "Main" in Thisworkbook.
if sheetName not found... Macro to throw error MsgBox "Sheet doesn't exist"

But macro is giving SubScript out of Range for below code in Function. on error resume not working
ShtExists = (LCase(wbk.Sheets(ShtName).Name) = LCase(ShtName))

Option Explicit
Sub testSheedName()

Dim wbk As Workbook
Set wbk = ThisWorkbook

   If Not ShtExists("Main", wbk) Then
      MsgBox "Sheet doesn't exist"
      Exit Sub
   End If
End Sub


Public Function ShtExists(ShtName As String, Optional wbk As Workbook) As Boolean
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

Thanks
mg

 

[Kenneth Hobson]

Sub Test_WorkSheetExists()
  MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), _
    vbInformation, "ActiveWorkbook.ActiveSheet"
    
   MsgBox "WorksheetExists? " & WorkSheetExists("ken", "ken.xlsm"), _
    vbInformation
End Sub

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

 

[Mallesh23]

Hi Ken,

Tested , if matching sheetName found in Thisworkbook or Activeworkbook macro will not throw an error,

if I pass incorrect sheet name to get boolean value, I get subscript out of range, On error resume next not working.



Thanks
mg

 

[Kenneth Hobson]

In VBE, be sure to select Tools > Options > General > Break on Unhandled Errors

 

[Mallesh23]

Hi Kenn,

Perfect ! thanks a lot its working. ?



Thanks
mg