Explain use of function property

[Guest]

My goal is to check for the existence of a worksheet named "Invoice" within
an open workbook. If it exists, then print it. If not, continue with the
existing macro which prints another worksheet tab which will always exist. I
need to know if I can use a function to test whether the "Invoice" worksheet
exists by returning a true or false value, instead of just attempting to
select the worksheet and taking a risk of it not existing then writing error
handling code.

 

[Guest]

Public Function bExists(s as String)
for each sh in ActiveWorkbook.worksheets
if lcase(s) = lcase(sh.name) then
bExists = True
exit function
end if
next
bExists = False
End Function



usage
If bExists("sheet1") then
worksheets("sheet1").Printout
else

End if

 

[Guest]

I do not understand how the relationship works between the Public Function
code and the usage portion of your sample code. Can I not use the Function
within a sub procedure? If I attempt to run the sub below I get an error
which reads: Compile error: Expected End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub InvoiceCheck()
Public Function bExists(s As String)
For Each sh In ActiveWorkbook.Worksheets
If LCase(s) = LCase(sh.Name) Then
bExists = True
Exit Function
End If
Next
bExists = False
End Function

'usage
If bExists("sheet1") Then
Worksheets("sheet1").PrintOut
Else

End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[Guest]

Tom, if you're pressed for time, can you direct me to some reference to see
sample code on the use of "Public Function" along with a Sub procedure?

 

[Guest]

Public Function bExists(s as String)
for each sh in ActiveWorkbook.worksheets
if lcase(s) = lcase(sh.name) then
bExists = True
exit function
end if
next
bExists = False
End Function



usage
If bExists("sheet1") then
worksheets("sheet1").Printout
else

End if

 

[Tom Ogilvy]

Sub InvoiceCheck()
'usage
If bExists("Invoice") Then
Worksheets("Invoice").PrintOut
End If
End Sub

Public Function bExists(s As String)
Dim sh as Worksheet
For Each sh In ActiveWorkbook.Worksheets
If LCase(s) = LCase(sh.Name) Then
bExists = True
Exit Function
End If
Next
bExists = False
End Function