2

I have a program which opens many Excel workbooks and merges data from all of them into a target workbook. After merging, it does some transformation of the data (column to row tranpose using custom logic) and then it adds some more columns using lookup data.

I have the following code

Sub consolidateFiles()
    On Error GoTO ErrorHandler

    processFiles
    transform
    addLookupData

    Exit Sub
    ErrorHandler:
    Debug.Print Err.Number & " = " & Err.Description & ", Source = " & Err.Source 
End Sub

Sub processFiles()
 'Here I open all the files in the directory and copy and paste into the target workbook. 
 'If there is an error here, I would like to know the file that caused the error 
 'and I would like to proceed to the next file after logging the error
End Sub

Sub transform()
 'Here I transform some of the data from column to year.
End Sub

Sub addLookupData()
 'Here I add some new columns by looking up the data in another sheet in the target  workbook. Here the lookups can return error
End Sub

I wanted to know what are the good programming practices to add error handling to my code. Right now, I have one global error handler which logs the error and continues the program. Is it a good idea to add error handling to each of the 3 sub-procedures that I am calling from my main procedure?

I read about using the undocumented ERL to get the line of error. How do we add line numbers in the VBA editor?

This is my first program in VBA. I have done a lot of Java programming, but there the error handling paradigm is very different. Hence, I would like to learn from experienced VBA programmers what are the good practices that I can follow.

I have read this excellent article which was linked in one of the questions related to VBA error handling in SO:

http://www.cpearson.com/excel/errorhandling.htm

Community
  • 1
  • 1
Siraj Samsudeen
  • 1,624
  • 7
  • 26
  • 35
  • http://stackoverflow.com/questions/9244633/vba-error-handling-query HTH – Siddharth Rout Oct 03 '12 at 15:19
  • @SiddharthRout, Thanks Siddharth. I read through this thread already and every single response of that. Though that thread offered some tips, it did not fully answer my questions. I am also reading your article 'To Err is human' - Can you please post some practices you follow? – Siraj Samsudeen Oct 03 '12 at 15:38
  • First things first... Download MZ Tools and install it. Link for MZ is in the above link :) – Siddharth Rout Oct 03 '12 at 15:49
  • @SiddharthRout, I have installed MZ Tools and played with it a bit. The error handler that it adds throws up a MsgBox which makes the program stop after one error. I can customize that- but that is where I need help in terms of what are the good practices to follow – Siraj Samsudeen Oct 04 '12 at 10:15
  • Most of the good practices have been covered in the above link by me as well as @brettdj. However if you show us the code that you are trying now, we can give you specific inputs if required. – Siddharth Rout Oct 04 '12 at 10:32
  • This had a good discussion and template http://stackoverflow.com/questions/6028288/properly-error-handling-in-vba-excel) – John Rees Jun 08 '14 at 00:52

1 Answers1

1

Since I did not get any answers, I have tried to research more. Here are some article that I have found useful:

http://www.techrepublic.com/blog/five-apps/five-tips-for-handling-errors-in-vba/339

http://www.jpsoftwaretech.com/vba-tips-tricks-and-best-practices-part-four/

http://www.fmsinc.com/tpapers/vbacode/debug.asp#BasicErrorHandling

Also, I have found following a SingleExitPoint in each procedure (either with error or without error) as a good practice.

Many of the ideas are stated/hinted at in the link that Siddharth provided. But they were not very clear to me until I read these articles.

Siraj Samsudeen
  • 1,624
  • 7
  • 26
  • 35