Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Activesheet for chart sheet or work sheet | Charts and Charting in Excel | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
How to check for the existance of a Sheet (or not) | Excel Programming | |||
error handling - check chart existance | Excel Programming | |||
Existance Check Fails | Excel Programming |