On Error GOTO seems to be ignored.

TomCon

Active Member
Joined
Mar 31, 2011
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I hvae the following loop:
VBA Code:
While (True)
    On Error GoTo doneloop
    EnterWb.Activate
    ShtNam = "daily (" & i & ")"
    Which = ShtNam
    [COLOR=rgb(184, 49, 47)][B]Sheets(ShtNam).Select[/B][/COLOR]
    Call Process_Day_Code
'    FormSkipDays.Label1 = " Form is Modeless. End of Execution for " & _
'        ShtNam & ". Examine for correct data Update. Press OK to proceed, Cancel to end"
'    FormSkipDays.Show vbModeless
    i = i + 1
Wend


The idea is that when i is incremented beyond the value for which there exists a worksheet named "daily (i)", that the error handler instantiated takes the code out of the loop and on to the label "doneloop".

However, when the line highlighted in red is executed, VBA pops the error "Subscript out of range".

With that "on Error" statement in the code, shouldn't the action be to take the code execution out of the While loop and on to the line labeled "doneloop"?

Mystified!

Please let me know if you can explain what is going on here or how to make it work so that VBA does not throw an error and instead continues execution at the designated label in the code.

Thanks!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Your code works for me.

It is not a good practice to use the On Error Goto statement, you could have different types of errors and you would not know what the error is. It is advisable to review possible errors with code.

I also recommend using, in this case, Do While instead of While:
Try the following code.

VBA Code:
Sub test_without_error_handler()
  Dim EnterWb As Workbook
  Dim i As Long
  Dim ShtNam As String
 
  Set EnterWb = ThisWorkbook
  EnterWb.Activate
  i = 1
 
  Do While True
  
    ShtNam = "daily (" & i & ")"
    If Evaluate("ISREF('" & ShtNam & "'!A1)") Then
      Sheets(ShtNam).Select
      Call Process_Day_Code
      'FormSkipDays.Label1 = " Form is Modeless. End of Execution for " & _
      '    ShtNam & ". Examine for correct data Update. Press OK to proceed, Cancel to end"
      'FormSkipDays.Show vbModeless
      i = i + 1
  
    Else
      MsgBox "There are no more sheets"
      Exit Do
    End If
  
  Loop

' Here continues your code

End Sub

NOTE:
The other recommendation is, don't select the sheet, you should adjust your macro to reference the sheet without selecting it, but it can't help you more, because you didn't put your complete code.​


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Solution
Yes this is great, great idea to use Evaluate(ISREF) in a test like that. Works perfectly for my purpose!

Just out of curiosity, though, shouldn't VBA branch to the labeled line when that sheet no longer exists, rather than throwing the error? It almost seems like a VBA bug to me, that the ON ERROR GOTO is being ignored.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top