Check for Existance of sheet - problem with UDF
 

Hi,

I'm using a udf to test for the existance of a worksheet and to perform an
action if the sheet is found. The code for the function is as follows:


Function WksExists(ByVal wksName As String) As Boolean
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function


In my procedure I call the function using:


If WksExists("1 (2)") = True Then ' Test for existance of sheet
Sheets("1 (2)").Select ' If true then select sheet
Sheets("1 (2)").Name = i + 1 ' Rename sheet with the value of i
+1
End If


This works fine if the sheet "1 (2)" actually does exist. The probelm is if
the sheet "1 (2)" doesn't exist I get a "Run-Time Error 9" - "Subscript out
of Range".

Any idea why this is happening and how I would over come it.

Thanks in advance

...pc

Check for Existance of sheet - problem with UDF
 

Hi,
the error occurs when you try to examine a property of a sheet that
doesn't exist. Try this instead...

PC Apr 8, 1:30 am show options

Newsgroups: microsoft.public.excel.programming
From: "PC" <paulm dot c @ iol dot ie - Find messages by this author
Date: Fri, 8 Apr 2005 09:30:06 +0100
Local: Fri, Apr 8 2005 1:30 am
Subject: Check for Existance of sheet - problem with UDF
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hi,


I'm using a udf to test for the existance of a worksheet and to perform
an
action if the sheet is found. The code for the function is as follows:


Function WksExists(ByVal wksName As String) As Boolean
On Error Resume Next
Let wksName = Worksheets(wksName).*Name
If Err = 0 Then WksExists = True
End Function

Hth,
OJ

Check for Existance of sheet - problem with UDF
 

That did the trick OJ.

Thanks

...pc


"OJ" wrote in message
oups.com...
Hi,
the error occurs when you try to examine a property of a sheet that
doesn't exist. Try this instead...

PC Apr 8, 1:30 am show options

Newsgroups: microsoft.public.excel.programming
From: "PC" <paulm dot c @ iol dot ie - Find messages by this author
Date: Fri, 8 Apr 2005 09:30:06 +0100
Local: Fri, Apr 8 2005 1:30 am
Subject: Check for Existance of sheet - problem with UDF
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hi,


I'm using a udf to test for the existance of a worksheet and to perform
an
action if the sheet is found. The code for the function is as follows:


Function WksExists(ByVal wksName As String) As Boolean
On Error Resume Next
Let wksName = Worksheets(wksName).*Name
If Err = 0 Then WksExists = True
End Function

Hth,
OJ

Check for Existance of sheet - problem with UDF
 

You already have a solution. However, FWIW, below is a more general
purpose one:

Option Explicit

Function SheetExists(SheetName As String, _
Optional WBName As String) As Boolean
On Error Resume Next
If WBName = "" Then _
SheetExists = _
Not ActiveWorkbook.Sheets(SheetName) Is Nothing _
Else _
SheetExists = _
Not Workbooks(WBName).Sheets(SheetName) Is Nothing
End Function

Sub testIt()
MsgBox SheetExists("sheet1") & "," & SheetExists("sheet 1")
MsgBox SheetExists("sheet1", "findall.xls") & "," _
& SheetExists("sheet 1")
MsgBox SheetExists("sheet1", "findall.xls") & "," _
& SheetExists("sheet 1", "findall.xls")
End Sub



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "PC" <paulm dot c @
iol dot ie says...
Hi,

I'm using a udf to test for the existance of a worksheet and to perform an
action if the sheet is found. The code for the function is as follows:


Function WksExists(ByVal wksName As String) As Boolean
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function


In my procedure I call the function using:


If WksExists("1 (2)") = True Then ' Test for existance of sheet
Sheets("1 (2)").Select ' If true then select sheet
Sheets("1 (2)").Name = i + 1 ' Rename sheet with the value of i
+1
End If


This works fine if the sheet "1 (2)" actually does exist. The probelm is if
the sheet "1 (2)" doesn't exist I get a "Run-Time Error 9" - "Subscript out
of Range".

Any idea why this is happening and how I would over come it.

Thanks in advance

..pc