Check if Sheet and/or Range Exists Function

Check if Sheet Exists

We’ve created a function that will test if a Sheet or Range (on a particular sheet) exists. The Range test is useful if you want to check if a particular named range exists on a sheet.

'Test if a Range Exists on a Sheet.
'Leave range blank to test if sheet exists
'Inputs:
' WhatSheet - String Name of Sheet (ex "Sheet1")
' WhatRange (Optional, Default = "A1") - String Name of Range (ex "A1")
Function RangeExists(WhatSheet As String, Optional ByVal WhatRange As String = "A1") As Boolean
    Dim test As Range
    On Error Resume Next
    Set test = ActiveWorkbook.Sheets(WhatSheet).Range(WhatRange)
    RangeExists = Err.Number = 0
    On Error GoTo 0
End Function

Place the function within a VBA Code Module and you can access it by using sub procedures like these:

Check if Sheet Exists

Sub Test_SheetExists()
    MsgBox RangeExists("setup")
End Sub

Check if Range Exists on a Sheet

Sub Test_RangeExists()
    MsgBox RangeExists("setup", "rngInput")
End Sub

Adjusting the RangeExists Function

Check if Sheet Exists on Another Workbook

The above function looked at ActiveWorkbook (the currently active workbook). Instead you could adjust the Function to look at a specific workbook like this:

'Test if a Range Exists on a Sheet.
'Leave range blank to test if sheet exists
'Inputs:
' WhatBook - *Workbook Object*
' WhatSheet - String Name of Sheet (ex "Sheet1")
' WhatRange (Optional, Default = "A1") - String Name of Range (ex "A1")
Function RangeExists(WhatBook As Workbook, WhatSheet As String, Optional ByVal WhatRange As String = "A1") As Boolean
    Dim test As Range
    On Error Resume Next
    Set test = WhatBook.Sheets(WhatSheet).Range(WhatRange)
    RangeExists = Err.Number = 0
    On Error GoTo 0
End Function

Implementation:

Sub Test_WBSheet_Exists()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    MsgBox RangeExists(wb, "Sheet1")

End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!