Subscript out of Range

[Steve]

I used the following two methods to determine the existence of a spreadsheet
on one system, but using the same statements on another system I get Error
9, subscript out of Range. Both systems are running Windows XP and Office
2003.
The worksheet does not exist at the time either of these statements are
executed.

If Len(Worksheets("XYZ").Name) > 0 then do this or that

Worksheets("XYZ").Activate
If Err.Number <> 0 then do this or that

Why do these work on one system and not the other? Is it possible that
there is a setting that needs to be change within Excel to make this work?
Very perplexing. Any assistance would be greatly appreciated. Thank you in
advance.

 

[dhstein]

I can't explain your situation, but maybe you'd like to use Allen Browne's
function:

Public Function FileExists(ByVal strFile As String, Optional bFindFolders As
Boolean) As Boolean
'Purpose: Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched
if no path included.
' bFindFolders. If strFile is a folder, FileExists() returns
False unless this argument is True.
'Note: Does not look inside subdirectories for the file.
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim lngAttributes As Long

'Include read-only files, hidden files, system files.
lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

If bFindFolders Then
lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as
well.
Else
'Strip any trailing slash, so Dir does not look inside the folder.
Do While Right$(strFile, 1) = "\"
strFile = Left$(strFile, Len(strFile) - 1)
Loop
End If

'If Dir() returns something, the file exists.
On Error Resume Next
FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

 

[Dave Peterson]

I bet you left off this line, too:

On Error Resume Next

And if you go into the VBE (on both pc's), you'll find a difference in a
setting.

Look at Tools|Options|General tab
One has "Break on all errors" checked (the one that failed).
and the other has one of the other options checked.

I use "break in class module"

 

[Steve]

Yes, indeed I did. Thanks for the information, it saved the little bit of
hair I have left.