VBA Code to Check if Sheet Exists and Exit Sub if Not

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for any suggestions as I will give feedback accordingly.

Part of the response to my question has previously been posted, but since I've never used Functions in VBA and I am still new with VBA coding, I need some additional assistance.
https://www.mrexcel.com/forum/excel-questions/1072316-vba-check-if-sheet-exists.html

I would like to check if the following sheet in the external workbook exists, and if not, terminate the program. I only posted the pertinent code.

Code:
Sub Check_Sheet()
Dim i as Long

For i = 3 to LastRow

'The Path is "DirFolder"
'The File Name is "File_Name"

'If "Invoice Details" does not exist then (how to write this code?)

Exit Sub

EndIf

Next i

End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Something like
Code:
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
Sub chk()
   ShtName = "Sheet1"
   Set Wbk = Workbooks(File_Name)
   If Not ShtExists(ShtName, Wbk) Then Exit Sub
End Sub
 
Upvote 0
Thank you very much Fluff! Please note I have never used Functions and I am very new to writing Macros.

First question is if I make this function Private, does that basically make it to where you look at the Macros menu, it will not appear?

Second of all, since this is a closed workbook, don't I need to specify the directory?
 
Upvote 0
The function will not appear in the Macros menu, as it needs to be passed arguments.
However if you change it to Private it must be in the same module as the code that is calling it.

Also the workbook must be open.
 
Upvote 0
Thanks Fluff!

Understood.

How can I do it where if the workbook is closed?
 
Upvote 0
You could try this
Code:
Function ShtExistsClosed(Pth As String, Fname As String, ShtName As String)
   Dim FullPth As String
   
   FullPth = "'" & Pth & "[" & Fname & "]" & ShtName & "'!R1C1"
   ShtExistsClosed = Not IsError(Application.ExecuteExcel4Macro(FullPth))
End Function
Sub chk()
   Dim Pth As String, Fname As String, ShtName As String
   Pth = "C:\Mrexcel\"
   Fname = "+book1.xlsm"
   ShtName = "List"
   Debug.Print ShtExistsClosed(Pth, Fname, ShtName)
End Sub
But if the path or filename are wrong it will open up a dialogue box.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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