Runtime Error - Subscript out of range despite On Error statement

I based the following code on Bob Philips' code from June 30,2006 but I get a
"Subscript out of range" error at "Set lwOpenWorkbook = Workbooks(lcFile)"
only when the lcFile is not open. Which is what the On Error statement should
cover, right? What might be wrong and cause a runtime error despite having
the On Error statement in place???


Public Sub test()

Dim lwOpenWorkbook As Workbook

lcFolder = "G:\Tables"
lcFile = "Projects.xls"

lcCurrWorkbook = ThisWorkbook.Name

On Error Resume Next
Set lwOpenWorkbook = Workbooks(lcFile)
On Error GoTo 0

If Not lwOpenWorkbook Is Nothing Then
MsgBox "Workbook is already open"
Else
Set lwOpenWorkbook = Workbooks.Open(lcFolder & "\" & lcFile)
End If

Workbooks(lcCurrWorkbook).Activate

End Sub

I could not duplicate your problem, but you are correct. The error should
have been successfully trapped.

Perhaps the ERR object is not being properly cleared? Try inserting an
'Err.Clear' statement on the line before your 'On Error Resume Next'.

This is unlikely to fix your problem, as any 'On Error' statement should
automatically issue an Err.Clear, but its worth a shot.
--
Les Torchia-Wells


"DoctorG" wrote:

> I based the following code on Bob Philips' code from June 30,2006 but I get a
> "Subscript out of range" error at "Set lwOpenWorkbook = Workbooks(lcFile)"
> only when the lcFile is not open. Which is what the On Error statement should
> cover, right? What might be wrong and cause a runtime error despite having
> the On Error statement in place???
>
>
> Public Sub test()
>
> Dim lwOpenWorkbook As Workbook
>
> lcFolder = "G:\Tables"
> lcFile = "Projects.xls"
>
> lcCurrWorkbook = ThisWorkbook.Name
>
> On Error Resume Next
> Set lwOpenWorkbook = Workbooks(lcFile)
> On Error GoTo 0
>
> If Not lwOpenWorkbook Is Nothing Then
> MsgBox "Workbook is already open"
> Else
> Set lwOpenWorkbook = Workbooks.Open(lcFolder & "\" & lcFile)
> End If
>
> Workbooks(lcCurrWorkbook).Activate
>
> End Sub
>

Les, thanks a lot for your answer, as it made me think a bit. And thinking
got me to the Tools > Options > General > Error Trapping section where I saw
that the "Break on All Errors" option was selected. As soon as I changed it
to "Break on Unhandled Errors" everything worked fine.

I'm getting to know this thing, slowly but steadily!! Thanks again.

"Les" wrote:

> I could not duplicate your problem, but you are correct. The error should
> have been successfully trapped.
>
> Perhaps the ERR object is not being properly cleared? Try inserting an
> 'Err.Clear' statement on the line before your 'On Error Resume Next'.
>
> This is unlikely to fix your problem, as any 'On Error' statement should
> automatically issue an Err.Clear, but its worth a shot.
> --
> Les Torchia-Wells
>
>
> "DoctorG" wrote:
>
> > I based the following code on Bob Philips' code from June 30,2006 but I get a
> > "Subscript out of range" error at "Set lwOpenWorkbook = Workbooks(lcFile)"
> > only when the lcFile is not open. Which is what the On Error statement should
> > cover, right? What might be wrong and cause a runtime error despite having
> > the On Error statement in place???
> >
> >
> > Public Sub test()
> >
> > Dim lwOpenWorkbook As Workbook
> >
> > lcFolder = "G:\Tables"
> > lcFile = "Projects.xls"
> >
> > lcCurrWorkbook = ThisWorkbook.Name
> >
> > On Error Resume Next
> > Set lwOpenWorkbook = Workbooks(lcFile)
> > On Error GoTo 0
> >
> > If Not lwOpenWorkbook Is Nothing Then
> > MsgBox "Workbook is already open"
> > Else
> > Set lwOpenWorkbook = Workbooks.Open(lcFolder & "\" & lcFile)
> > End If
> >
> > Workbooks(lcCurrWorkbook).Activate
> >
> > End Sub
> >

In the VBE under options in the tools menu, General Tab, do you have Break
on Unhandled errors checked?


If not, that's your huckleberry.

--
Regards,
Tom Ogilvy


"DoctorG" wrote:

> I based the following code on Bob Philips' code from June 30,2006 but I get a
> "Subscript out of range" error at "Set lwOpenWorkbook = Workbooks(lcFile)"
> only when the lcFile is not open. Which is what the On Error statement should
> cover, right? What might be wrong and cause a runtime error despite having
> the On Error statement in place???
>
>
> Public Sub test()
>
> Dim lwOpenWorkbook As Workbook
>
> lcFolder = "G:\Tables"
> lcFile = "Projects.xls"
>
> lcCurrWorkbook = ThisWorkbook.Name
>
> On Error Resume Next
> Set lwOpenWorkbook = Workbooks(lcFile)
> On Error GoTo 0
>
> If Not lwOpenWorkbook Is Nothing Then
> MsgBox "Workbook is already open"
> Else
> Set lwOpenWorkbook = Workbooks.Open(lcFolder & "\" & lcFile)
> End If
>
> Workbooks(lcCurrWorkbook).Activate
>
> End Sub
>

In the VBE under options in the tools menu, General Tab, do you have Break
on Unhandled errors checked?


If not, that's your huckleberry.

--
Regards,
Tom Ogilvy


"DoctorG" wrote:

> I based the following code on Bob Philips' code from June 30,2006 but I get a
> "Subscript out of range" error at "Set lwOpenWorkbook = Workbooks(lcFile)"
> only when the lcFile is not open. Which is what the On Error statement should
> cover, right? What might be wrong and cause a runtime error despite having
> the On Error statement in place???
>
>
> Public Sub test()
>
> Dim lwOpenWorkbook As Workbook
>
> lcFolder = "G:\Tables"
> lcFile = "Projects.xls"
>
> lcCurrWorkbook = ThisWorkbook.Name
>
> On Error Resume Next
> Set lwOpenWorkbook = Workbooks(lcFile)
> On Error GoTo 0
>
> If Not lwOpenWorkbook Is Nothing Then
> MsgBox "Workbook is already open"
> Else
> Set lwOpenWorkbook = Workbooks.Open(lcFolder & "\" & lcFile)
> End If
>
> Workbooks(lcCurrWorkbook).Activate
>
> End Sub
>