run-time error '9': Subscript out of range - WHY??

[pastotnikr]

I use the following code to control program flow on startup. It works fine on
2 other PCs (and worked on my PC last week), but (now) I get "run-time error
'9': Subscript out of range" when it runs on my PC.

Any ideas what might be different between the two PCs that causes it to
bomb? I have tried rebooting and compared vba references between the two PCs
-can't see anything obvious. Would appreciate any suggestions.
*****

Sub Workbook_Open()
If WorkbookExists("UPE_Period.xls") Then
BuildALL
Else
Sheets("Reports").Select
End If
End Sub
******
Public Function WorkbookExists(WorkbookName As String) As Boolean
On Error Resume Next
If Application.Workbooks(WorkbookName) Is Nothing Then
WorkbookExists = False
Else
WorkbookExists = True
End If
End Function

[JE McGimpsey]

Looks to me like there's no sheet named "Reports" in the active workbook.


In article <[email protected]>,
pastotnikr <[email protected]> wrote:

> I use the following code to control program flow on startup. It works fine on
> 2 other PCs (and worked on my PC last week), but (now) I get "run-time error
> '9': Subscript out of range" when it runs on my PC.
>
> Any ideas what might be different between the two PCs that causes it to
> bomb? I have tried rebooting and compared vba references between the two PCs
> -can't see anything obvious. Would appreciate any suggestions.
> *****
>
> Sub Workbook_Open()
> If WorkbookExists("UPE_Period.xls") Then
> BuildALL
> Else
> Sheets("Reports").Select
> End If
> End Sub
> ******
> Public Function WorkbookExists(WorkbookName As String) As Boolean
> On Error Resume Next
> If Application.Workbooks(WorkbookName) Is Nothing Then
> WorkbookExists = False
> Else
> WorkbookExists = True
> End If
> End Function

[pastotnikr]

Sorry - should have been more explicit in my original post -
1) there is a sheet named "Reports" in the active workbook
2) it is failing on the call to the WorkbookExists function
3) I run the same exact worksheet on two different PCs - it fails on one but
NOT the other

Thanks for the input though.


"JE McGimpsey" wrote:

> Looks to me like there's no sheet named "Reports" in the active workbook.
>
>
> In article <[email protected]>,
> pastotnikr <[email protected]> wrote:
>
> > I use the following code to control program flow on startup. It works fine on
> > 2 other PCs (and worked on my PC last week), but (now) I get "run-time error
> > '9': Subscript out of range" when it runs on my PC.
> >
> > Any ideas what might be different between the two PCs that causes it to
> > bomb? I have tried rebooting and compared vba references between the two PCs
> > -can't see anything obvious. Would appreciate any suggestions.
> > *****
> >
> > Sub Workbook_Open()
> > If WorkbookExists("UPE_Period.xls") Then
> > BuildALL
> > Else
> > Sheets("Reports").Select
> > End If
> > End Sub
> > ******
> > Public Function WorkbookExists(WorkbookName As String) As Boolean
> > On Error Resume Next
> > If Application.Workbooks(WorkbookName) Is Nothing Then
> > WorkbookExists = False
> > Else
> > WorkbookExists = True
> > End If
> > End Function
>

[pastotnikr]

Sorry - should have been more explicit in my original post -
1) there is a sheet named "Reports" in the active workbook
2) it is failing on the call to the WorkbookExists function
3) I run the same exact worksheet on two different PCs - it fails on one but
NOT the other

Thanks for the input though.


"JE McGimpsey" wrote:

> Looks to me like there's no sheet named "Reports" in the active workbook.
>
>
> In article <[email protected]>,
> pastotnikr <[email protected]> wrote:
>
> > I use the following code to control program flow on startup. It works fine on
> > 2 other PCs (and worked on my PC last week), but (now) I get "run-time error
> > '9': Subscript out of range" when it runs on my PC.
> >
> > Any ideas what might be different between the two PCs that causes it to
> > bomb? I have tried rebooting and compared vba references between the two PCs
> > -can't see anything obvious. Would appreciate any suggestions.
> > *****
> >
> > Sub Workbook_Open()
> > If WorkbookExists("UPE_Period.xls") Then
> > BuildALL
> > Else
> > Sheets("Reports").Select
> > End If
> > End Sub
> > ******
> > Public Function WorkbookExists(WorkbookName As String) As Boolean
> > On Error Resume Next
> > If Application.Workbooks(WorkbookName) Is Nothing Then
> > WorkbookExists = False
> > Else
> > WorkbookExists = True
> > End If
> > End Function
>

[pastotnikr]

Just in case someone looks at this later ... I found my problem ...
Under Tools / Options - General Tab (in the VBA programming window)

Error Trapping - Break on All Errors was selected
changed to
Break on Unhandled Errors (to match the working PC)
now works the same on both!

"pastotnikr" wrote:

> Sorry - should have been more explicit in my original post -
> 1) there is a sheet named "Reports" in the active workbook
> 2) it is failing on the call to the WorkbookExists function
> 3) I run the same exact worksheet on two different PCs - it fails on one but
> NOT the other
>
> Thanks for the input though.
>
>
> "JE McGimpsey" wrote:
>
> > Looks to me like there's no sheet named "Reports" in the active workbook.
> >
> >
> > In article <[email protected]>,
> > pastotnikr <[email protected]> wrote:
> >
> > > I use the following code to control program flow on startup. It works fine on
> > > 2 other PCs (and worked on my PC last week), but (now) I get "run-time error
> > > '9': Subscript out of range" when it runs on my PC.
> > >
> > > Any ideas what might be different between the two PCs that causes it to
> > > bomb? I have tried rebooting and compared vba references between the two PCs
> > > -can't see anything obvious. Would appreciate any suggestions.
> > > *****
> > >
> > > Sub Workbook_Open()
> > > If WorkbookExists("UPE_Period.xls") Then
> > > BuildALL
> > > Else
> > > Sheets("Reports").Select
> > > End If
> > > End Sub
> > > ******
> > > Public Function WorkbookExists(WorkbookName As String) As Boolean
> > > On Error Resume Next
> > > If Application.Workbooks(WorkbookName) Is Nothing Then
> > > WorkbookExists = False
> > > Else
> > > WorkbookExists = True
> > > End If
> > > End Function
> >

[ALF918]

I just wanted to thank pastotnikr for posting the solution for why you would get a run-time error '9': Subscript out of range on one computer but not another. I spent 6 hours trying to figure out why my macro stopped working on my PC all of a sudden but still worked on my coworker’s machine.