You are here:

Excel/Unexpected loop behavior in VBA

Advertisement


Question
Hello Jan:
I received a great push from Bob Umlas with following procedure and I eventually solved the issue I was asking him, but a new bug has appeared and he was unavailable for a follow up so let me bother you with my case.
I have a WB with about 40 sheets, all have the same structure as a bill or invoice; which is a list of items with prices and they are filled as per user needs. Each sheet is for a different office, each sheet can have different items from a master products list. At some time, it is needed to print a bill and I have a Sub that does some preparations in the bill as deleting blank (unused) rows, writing a few text cells and then it is printed without any problem. What I want to accomplish here is to print a group of manually selected sheets with just one click in one procedure. I pretty much have succeeded as you can see in the code but, I am getting a double pass in my loop after writing the word "Spiga". Excel just repeats the last part after the label "base2" and then it goes to work with next sheet in the loop. This only happens with the second sheet in the group and on following sheets until the end of the loop.
I have an odd(ugly) trick to reset the Error handler in VBA, because I was having problems with it. I was using Go to 0, and Err.Clear, but Excel kept ignoring the error handler after the second loop and the only way I could solve it was using Resume <label>, in this case "ResetErrorHandler".
I really can't explain this Excel behavior, much less how to avoid it. Please let me know if you actually need my WB to see it working.
Here is the code:

Sub BillingPrintingSelectedSheetsPassingArray()

Dim N As Long
Dim M As Long
Dim Arr() As String
Dim ws As Worksheet
Dim CurSheet As String

With ActiveWindow.SelectedSheets
       ReDim Arr(1 To .Count)
       For N = 1 To .Count
         Arr(N) = .Item(N).Name
       Next N
   End With

ActiveSheet.Select
For t = LBound(Arr) To UBound(Arr)
   
   Worksheets(Arr(t)).Activate
   Application.GoTo Reference:="body"
   On Error GoTo base2
   Selection.SpecialCells(xlCellTypeBlanks).Select
   'On Error GoTo 0
   Selection.EntireRow.Delete
   
base2:

   Application.GoTo Reference:="Print_Area"   'reloop starts here
   ActiveCell.Offset(0, 1).Range("A1").Select
   ActiveCell.FormulaR1C1 = "Spiga"
   'On Error GoTo 0
   Resume ResetErrorHandler 'execution goes to reloop in 2nd pass
ResetErrorHandler:
Next



   ' This prints all selected sheets.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Sheets(Arr).PrintOut

Sheets(Arr).Move Before:=Sheets(2)


End Sub

Answer
An error handler MUST be "finished" by either a resume or a Resume next statement, otherwise further error handling is disabled.

I have changed your code using a different method:

Sub BillingPrintingSelectedSheetsPassingArray()

   Dim N As Long
   Dim Arr() As String
   Dim ws As Worksheet

   With ActiveWindow.SelectedSheets
       ReDim Arr(1 To .Count)
       For N = 1 To .Count
         Arr(N) = .Item(N).Name
       Next N
   End With

   For Each ws In ActiveWindow.SelectedSheets
       ws.Activate
       Application.Goto Reference:="body"
       On Error Resume Next
       Err.Clear
       Selection.SpecialCells(xlCellTypeBlanks).Select
       If Err.Number = 0 Then
         Selection.EntireRow.Delete
       End If
       On Error GoTo 0
       Application.Goto Reference:="Print_Area"   'reloop starts here
       ActiveCell.Offset(0, 1).Range("A1").Select
       ActiveCell.FormulaR1C1 = "Spiga"
   Next
   ' This prints all selected sheets.
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Sheets(Arr).PrintOut
   Sheets(Arr).Move Before:=Sheets(2)
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.