How can I implement error handling in this macro?

[DrSynapse]

Hello everyone,

I hope all of you have been doing great!
I've been (slowly) learning VBA recently and realized that it'd be good practice to include error handling in my code. The set up:

I'm linking a "profile" workbook to approx 60 other workbooks. As you can see from the below code, the same row (in every worksheet) in the profile will be dedicated to a specific external workbook. I have included a portion of the code, however, this is only one of the 60-ish loops that I have in the full code. The exact same loop is repeated 60-ish times, with only the row number and external references changed.

Is there a way to code VB to move to the next loop generally? I don't believe a OnError GoTo would be appropriate because depending on which of the 60+ loops in this code has an error, I'd like it to move to the next loop.

Sub ProfileUpdater_Demographics()


Dim i As Integer
Dim strLow As String
Dim strSim As String
Dim strHigh As String


strLow = "LOWER"
strSim = "similar"
strHigh = "HIGHER"


Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


For i = 0 To Worksheets.Count - 2
    Worksheets(ActiveSheet.Index + 1).Select


    Range("A9").FormulaR1C1 = "=[50HNIndicators.xlsx]2013Indicators!R4C7"
    Range("B9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R" & 6 + i & "C2"
    Range("C9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R64C2"
    
    If i = 0 Then
        Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Notes_Table!R2C4"
    Else
        Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]SigDiff!R" & 2 + i & "C2"
    
    End If


        If Range("D9") = "LOWER" Then
            Range("D9").Font.ColorIndex = 5
        ElseIf Range("D9") = "similar" Then
            Range("D9").Font.Color = vbBlack
        ElseIf Range("D9") = "HIGHER" Then
            Range("D9").Font.ColorIndex = 5
        End If


Next i


Worksheets(ActiveSheet.Index - 8).Select

I hope that this is as clear as possible! If there's any suggestions to improving my coding, better practices or words of advice from a more knowledgeable individual, I would be in great appreciation

Kind Regards,

Anthony

 

[DrSynapse]

Hi Norie,

The other 60 loops are the exact same format is the posted loop, let me give you an example of two loops for you to compare. Only the row numbers and external references are changed between the two though.

For i = 0 To Worksheets.Count - 2
    Worksheets(ActiveSheet.Index + 1).Select


    Range("A9").FormulaR1C1 = "=[50HNIndicators.xlsx]2013Indicators!R4C7"
    Range("B9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R" & 6 + i & "C2"
    Range("C9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R64C2"
    
    If i = 0 Then
        Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Notes_Table!R2C4"
    Else
        Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]SigDiff!R" & 2 + i & "C2"
    
    End If


        If Range("D9") = "LOWER" Then
            Range("D9").Font.ColorIndex = 5
        ElseIf Range("D9") = "similar" Then
            Range("D9").Font.Color = vbBlack
        ElseIf Range("D9") = "HIGHER" Then
            Range("D9").Font.ColorIndex = 5
        End If


Next i

Second Loop...

For i = 0 To Worksheets.Count - 2
    Worksheets(ActiveSheet.Index + 1).Select


    Range("A10").FormulaR1C1 = "=[50HNIndicators.xlsx]2013Indicators!R5C7"
    Range("B10").FormulaR1C1 = "=[50HNPopulation0to14R.xlsx]Summary!R" & 6 + i & "C2"
    Range("C10").FormulaR1C1 = "=[50HNPopulation0to14R.xlsx]Summary!R64C2"
    
    If i = 0 Then
        Range("D10").FormulaR1C1 = "=[50HNPopulation0to14R.xlsx]Municipality!R3C6"
    Else
        Range("D10").FormulaR1C1 = "=[50HNPopulation0to14R.xlsx]SigDiff!R" & 2 + i & "C2"
    
    End If


        If Range("D10") = "LOWER" Then
            Range("D10").Font.ColorIndex = 5
        ElseIf Range("D10") = "similar" Then
            Range("D10").Font.Color = vbBlack
        ElseIf Range("D10") = "HIGHER" Then
            Range("D10").Font.ColorIndex = 5
        End If


Next i

The idea is that if an error occurs in loop 1, that it will move onto loop 2. Or if theres an error in loop...35, it'd move to loop 36.

 

[Norie]

If the errors are being caused by files or sheets not being found you should add code to check for the relevant files and sheets before entering the loops.

For example to find if a file exists you can use Dir.

If Dir("C:\SomePath\50HNSummaryPopGrowth2011.xlsx") <> "" Then
    
    For i = 0 To Worksheets.Count - 2
        Worksheets(ActiveSheet.Index + 1).Select


        Range("A9").FormulaR1C1 = "=[50HNIndicators.xlsx]2013Indicators!R4C7"
        Range("B9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R" & 6 + i & "C2"
        Range("C9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Summary!R64C2"
    
        If i = 0 Then
            Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]Notes_Table!R2C4"
        Else
            Range("D9").FormulaR1C1 = "=[50HNSummaryPopGrowth2011.xlsx]SigDiff!R" & 2 + i & "C2"
        End If


        If Range("D9") = "LOWER" Then
            Range("D9").Font.ColorIndex = 5
        ElseIf Range("D9") = "similar" Then
            Range("D9").Font.Color = vbBlack
        ElseIf Range("D9") = "HIGHER" Then
            Range("D9").Font.ColorIndex = 5
        End If

    Next i
End If

 

[jscranton]

I don't want to speak for Norie (who is usually spot on) but I think the general consensus is that using the "Exit For" method I mentioned is strongly preferred over the GoTo method.

 

[DrSynapse]

I can see both the Exit For and Norie's suggestion working for this, I feel the limiting agent will be which of the two I'll be able to apply the easiest.

I think Norie's maybe the most suitable because I can include a msgbox to indicate what specific loop is messing up within the If statement.

I appreciate both of your help and time with this!
Thank you!

 

[Norie]

What I posted is only to handle when there's a problem with a file not being found, you could have other code within the loop that checks for the existence of sheets.