Best Practices for Error Handling

[antman2988]

Hello. I have the code below that first checks to see if a certain field (header) exists before changing the formatting. Should I perform the same error checking for the fields at the bottom? Is there a better way to handle errors?

Private Sub FormatColumns(ByVal tbl As ListObject, ByVal budDt As String, ByVal acctDt As String, ByVal jrnlDt As String, ByVal issDt As String, ByVal transDt As String)
    Dim foundBudDt As Range
    Dim foundAcctDt As Range
    Dim foundJrnlDt As Range
    Dim foundIssDt As Range
    Dim foundTransDt As Range
    
    'Attempt to find string values in table header row
    On Error Resume Next
    Set foundBudDt = tbl.HeaderRowRange.Find(budDt, _
        LookAt:=xlWhole)
    Set foundAcctDt = tbl.HeaderRowRange.Find(acctDt, _
        LookAt:=xlWhole)
    Set foundJrnlDt = tbl.HeaderRowRange.Find(jrnlDt, _
        LookAt:=xlWhole)
    Set foundIssDt = tbl.HeaderRowRange.Find(issDt, _
        LookAt:=xlWhole)
    Set foundTransDt = tbl.HeaderRowRange.Find(transDt, _
        LookAt:=xlWhole)
    On Error GoTo 0
    
    'Modify formats of header data body ranges based on specified parameters
    'Set formats to dates
    With foundBudDt
        tbl.ListColumns(budDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End With
    
    With foundAcctDt
        tbl.ListColumns(acctDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End With
    
    With foundJrnlDt
        tbl.ListColumns(jrnlDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End With
    
    With foundIssDt
        tbl.ListColumns(issDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End With
    
    With foundTransDt
        tbl.ListColumns(transDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End With
    
    'Format nonessential columns
    tbl.ListColumns("Amount").DataBodyRange.NumberFormat = "$#,##0.00_);($#,##0.00)"
    tbl.ListColumns("Svc Loc").DataBodyRange.NumberFormat = "00000"
    tbl.ListColumns("Fund").DataBodyRange.NumberFormat = "0000"
    tbl.ListColumns("Activity").DataBodyRange.NumberFormat = "000"
    tbl.ListColumns("Class Code").DataBodyRange.NumberFormat = "0000"
End Sub

Thank you.

 

[Fluff]

I would recommend getting rid of these two lines

    On Error Resume Next

    On Error GoTo 0

and then change the With Statements to If statements like

    If Not foundBudDt Is Nothing Then
        tbl.ListColumns(budDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End If

 

[antman2988]

I would recommend getting rid of these two lines

    On Error Resume Next

    On Error GoTo 0

and then change the With Statements to If statements like

    If Not foundBudDt Is Nothing Then
        tbl.ListColumns(budDt).DataBodyRange.NumberFormat = "m/d/yyyy"
    End If

Thanks for the help Fluff. Should I apply the same error handling to the bottom lines? Pretty sure they'll just throw errors if they don't exist, otherwise. Right?

 

[Fluff]

It's not really error handling. Just replace the with statements as I showed.

 

[antman2988]

It's not really error handling. Just replace the with statements as I showed.

Isn't checking to see if the fields even exist considered error handling?

 

[Fluff]

Not IMO, it's preventing errors if the budDt column doesn't exist, rather than handling an error once it's occurred.

 

[antman2988]

Not IMO, it's preventing errors if the budDt column doesn't exist, rather than handling an error once it's occurred.

Ahhh, gotcha! I see what you mean. Thanks for the explanation!

 

[Fluff]

You're welcome & thanks for the feedback.