Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 106
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
Les Les is offline
external usenet poster
 
Posts: 240
Default Runtime Error - Subscript out of range despite On Error statement

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 106
Default Runtime Error - Subscript out of range despite On Error statem

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default Runtime Error - Subscript out of range despite On Error statement

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error 9 subscript out of range Nastack628 Excel Worksheet Functions 1 February 2nd 12 08:41 PM
Runtime Error 9, Subscript Out of Range. When linking between word Darren H Excel Discussion (Misc queries) 0 July 26th 07 04:34 PM
Getting Excel Runtime Error 9: Subscript out of range Ph8te Excel Programming 6 July 14th 06 06:12 AM
Subscript out of range error - save copy error bg18461[_15_] Excel Programming 1 June 13th 06 04:36 PM
"Subscript out of range Runtime Error 9" teresa Excel Programming 1 December 14th 04 10:22 PM


All times are GMT +1. The time now is 10:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"