Mysterious workbooks.open crashes excel

[martingaleh]

I have this wb with code in a module called LoopThroughFiles. I set it to a on click button event. One day, I open the file and click the button and it crashes. No reason. I go into the vba thingy and hit play. Still crashes. I copy the code into a new sub called hello() and press play on hello. It works. So I save the file, open it again, click the button and lo and behold. It works again. Obviously the thing to do is to just mess with copying the code around to and fro when it starts crashing, but for the people that make their money writing programs in excel: How do you handle this intermittent behavior? There's no stack trace or anything to even begin to debug it.

here's the code with the extra hello sub.

Dim counter As Integer
Dim output As Worksheet
Sub hello()
    Dim StrFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    counter = 2
    Set output = ActiveWorkbook.Worksheets("Output")
    Set rng = SelectActualUsedRange(output.Range("a2"))
    rng.Delete
    StrFile = Dir(ThisWorkbook.Path & "\*.xls*")
    Do While Len(StrFile) > 0
        If StrFile <> ThisWorkbook.name Then
            Debug.Print StrFile
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & StrFile)
            If SheetExists("s", wb) Then
                Set ws = wb.Worksheets("S")
                processSheet ws, "S", StrFile
            End If
            If SheetExists("H", wb) Then
                Set ws = wb.Worksheets("H")
                processSheet ws, "H", StrFile
            End If
            wb.Close SaveChanges:=False
        End If
        StrFile = Dir
    Loop
End Sub
Sub LoopThroughFiles()
    Dim StrFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    counter = 2
    Set output = ActiveWorkbook.Worksheets("Output")
    Set rng = SelectActualUsedRange(output.Range("a2"))
    rng.Delete
    StrFile = Dir(ThisWorkbook.Path & "\*.xls*")
    Do While Len(StrFile) > 0
        If StrFile <> ThisWorkbook.name Then
            Debug.Print StrFile
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & StrFile)
            If SheetExists("s", wb) Then
                Set ws = wb.Worksheets("S")
                processSheet ws, "S", StrFile
            End If
            If SheetExists("H", wb) Then
                Set ws = wb.Worksheets("H")
                processSheet ws, "H", StrFile
            End If
            wb.Close SaveChanges:=False
        End If
        StrFile = Dir
    Loop
End Sub


Sub processSheet(ws As Worksheet, legacy As String, StrFile As String)
    Dim region As String
    Dim parent As String
    Dim child As String
    Dim documentno As String
    Dim p As Range
    
    Set p = ws.Range("e11:t11")
            While p.Cells(1, 1).Value <> "Grand Total"
                If Len(p.Cells(1, 1).Value) > 0 Then
                    region = p.Cells(1, 1)
                    parent = ""
                    child = ""
                    documentno = ""
                 End If
                If Len(p.Cells(1, 2).Value) > 0 Then
                    parent = p.Cells(1, 2)
                    child = ""
                    documentno = ""
                    End If
                If Len(p.Cells(1, 3).Value) > 0 Then
                    child = p.Cells(1, 3)
                    documentno = ""
                End If
                If Len(p.Cells(1, 5).Value) > 0 Then documentno = p.Cells(1, 5)
                If Len(p.Cells(1, 12).Value) > 0 Or Len(p.Cells(1, 13).Value) > 0 Or Len(p.Cells(1, 14).Value) > 0 Or Len(p.Cells(1, 15).Value) > 0 Then
                    writeln StrFile, legacy, region, parent, child, documentno, p.Cells(1, 11).Value, p.Cells(1, 9).Value, p.Cells(1, 12).Value, p.Cells(1, 13).Value, p.Cells(1, 14).Value, p.Cells(1, 15).Value
                End If
                Set p = p.Offset(1, 0)
            Wend
End Sub
Sub writeln(file As String, legacy As String, region As String, parent As String, child As String, documentno As String, invoice As Double, cmv As Double, risk As Variant, cm As Variant, opportunity As Variant, promisedate As Variant)
  output.Cells(counter, 1).Value = file
  output.Cells(counter, 2).Value = legacy
  output.Cells(counter, 3).Value = region
  output.Cells(counter, 4).Value = parent
  output.Cells(counter, 5).Value = child
  output.Cells(counter, 6).Value = documentno
  output.Cells(counter, 7).Value = invoice
  output.Cells(counter, 8).Value = cmv
  output.Cells(counter, 9).Value = risk
  output.Cells(counter, 10).Value = cm
  output.Cells(counter, 11).Value = opportunity
  output.Cells(counter, 12).Value = promisedate
  counter = counter + 1
End Sub


Function SelectActualUsedRange(rng As Range) As Range
  Dim FirstCell As Range, LastCell As Range
  rng.Worksheet.Select
  Set SelectActualUsedRange = rng.Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
End Function




Function SheetExists(SheetName As String, Optional wb As Excel.Workbook)
   Dim s As Excel.Worksheet
   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set s = wb.Sheets(SheetName)
   On Error GoTo 0
   SheetExists = Not s Is Nothing
End Function