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

 

[Norie]

What error(s) are you trying to handle?

 

[DrSynapse]

Thanks for the reply Norie,

Feel free to offer advice, but I'm more or less implementing error handling for potential future errors. So there isn't any specific error I'm trying to handle, however, while creating this..I quite often would have the "Subscript out of range" or if the path file name is changed in the future.

Is it silly to include error handling if not for a specific error?
The work that I'm currently at is only for the summer and I hope to leave this macro behind as my legacy, so I'm trying to polish it up for my supervisors as nicely as possible.

 

[jscranton]

Anthony,

Just a couple thoughts. Like all coding, there are a lot of solutions and the "right" one depends on any number of things.

You can always exit a loop with the "Exit For" statement regardless of an error. For example, if you are looping through a large set of data and the loop finds what you need, then exiting the loop will speed things up. You can also use the Goto statement to move to another portion of the code without their being an error.

As to your code, there are only some minor things I would note (and, again, these are a bit of personal preference). When I am looping through a large workbook, I have become a fan of using a worksheet variable for all of my Range declarations.

Dim ws as Worksheet

For i = 2 to Worksheets.Count - 2

Set ws = ThisWorkbook.Worksheets(ActiveSheet.Index + 1)

'Then set your ranges to the worksheet variable
[B]ws[/B].Range("A9"). . . .

To be clear, this does the same thing as your code. However, I have found that it prevents a lot errors and is "cleaner" when you start referring to multiple variables across different sheets/workbooks.

Also, your last If statement could be simplified since the first and third condition do the same thing.

Just some random thoughts.

J.

 

[DrSynapse]

I very much agree with your suggestions, I feel like to better my coding abilities, moving towards using worksheet variables more often would be wise, so thank you.
Would you mind explaining how I could use the GoTo statement to move to the next loop generally? It'd be impossible to state a line specifically, unless I include a GoTo statement at the beginning of each loop? Is that what you meant?

Thanks for the help

 

[jscranton]

Dr,

That's correct. Before each loop you can add one word "Loop1:", "Loop2:". At any point (i.e. the satisfaction of a condition), you can simply say "Goto Loop2".

J.

 

[DrSynapse]

You're beautiful, thanks my man!

 

[Norie]

You should avoid using Goto.

 

[DrSynapse]

Care to further explain/suggest alternatives?

 

[Norie]

Anthony

Where are the 60 loops?

The code you've posted only has 1 loop.