error handling in VBA

Question

i have a sub that looks like this:

Sub open_esy(filename, p As String, p1 As Integer)

Dim fileLocation As String
Dim iFileNum As Integer, findblank
Dim letter_temp0 As String, letter0 As String, letter1 As String
Dim i As Integer
Dim j As Integer

i = 16

If Dir("\\Tecan3\output\" & filename & "*esy") <> "" Then
  fileLocation = "\\Tecan3\output\" & Dir("\\Tecan3\output\" & filename & "*esy")
ElseIf Dir("\\Tecan_2\output on tecan 2\" & filename & "*esy") <> "" Then
  fileLocation = "\\Tecan_2\output on tecan 2\" & Dir("\\Tecan_2\output on tecan 2\" & filename & "*esy")
ElseIf Dir("\\Tecan1\tecan #1 output\" & filename & "*esy") <> "" Then
  fileLocation = "\\Tecan1\tecan #1 output\" & Dir("\\Tecan1\tecan #1 output\" & filename & "*esy")
Else
  MsgBox "file " & filename & "not found"
  Exit Sub
End If

'open the batch file


   ''''old iFileNum = FreeFile()
   ''''old Open fileLocation For Input As #1

   ''''old Do While Not EOF(iFileNum)
    ''''old Line Input #iFileNum, stext

        Dim fso As New FileSystemObject
    Dim fld As Folder
    Dim ts As textstream

    Set ts = fso.OpenTextFile(fileLocation, ForReading)


    While Not ts.AtEndOfStream
        stext = ts.ReadLine

        letter0 = Mid(stext, 1, 3)

        If letter0 <> "A01" And letter0 <> "B01" And letter0 <> "C01" And letter0 <> "D01" And letter0 <> "E01" And letter0 <> "F01" And letter0 <> "G01" And letter0 <> "H01" And letter0 <> "I01" Then

        'letter1 = Mid(stext, 7, InStr(8, stext, " ") - 7)

        letter1 = Mid(stext, 7, InStr(8, stext, " ") - InStr(1, stext, "   ") - 3)

         Windows("Batch_XXXX revised.xlsm").Activate

        Call ProcessVialPosition(letter0, i)
        Cells(i, 3) = letter1
        i = i + 1
        End If
    Wend

    ts.Close

   ''''old Loop

   ''''old Close #1



   Cells(2, 2) = filename


   Cells(1, 2) = p

   Cells(1, 4) = p1


   save_template ("\\Centos5\ls-data\Interface\TF1\THC worklists\" & filename & "_THC" & ".txt")

End Sub

and for some reason it exists out of it at seemingly random points

how do i catch where it exists this sub and how do i catch the error?

Answer 1

Are you asking how to track an error that seemingly is not being raised? If so to disable all error handling in the IDE click Tools->Options->General->Break on all Errors

Failing that you will need to set a breakpoint and step throught the code.

Answer 2

You need some error handling code!

Sub open_esy(filename, p As String, p1 As Integer) 
On Error Goto Err_open_esy

... your sub here ...

Exit_open_esy:
  Exit Sub

Err_open_esy:
  ... your error handling code here ...
  ... you can grab line numbers too if you insert them above ...
  MyUniversalErrorHandler(Err.Number, Err.Description, Erl)
'Erl is the error line number from the above sub/function
End Sub