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

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
192
Office Version
2019
Platform
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,404
Office Version
365
Platform
Windows
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
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
192
Office Version
2019
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,404
Office Version
365
Platform
Windows
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.
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
192
Office Version
2019
Platform
Windows
Thanks Fluff!

Understood.

How can I do it where if the workbook is closed?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,404
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,892
Messages
5,483,543
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top