Nested on error VBA

Question

The code I have made is meant to go down two columns on a spreadsheet and create folders within folders. The left column has the name of the 1st layer folder, while the second column has the folders within the first layer folder.

1st layer   2nd layer
3508023     131392
3508023     90701 - repeat
3508023     115469
3508023     358167
3705584     90701 - repeat
3705584     133214
3705584     349453

My code currently handles if a 1st layer folder already exists by skipping it and going to the next iteration, but I can't figure out how to handle an error if a 2nd layer folder already exists via a nested error statement.

Here is the code I have

Sub ProPlanner()
    '
    ' ProPlanner Macro
    '
    Dim strRout As String
    Dim strTask As String
    Dim strPath As String

    Dim RoutCellCol As Integer
    Dim TaskCellCol As Integer

    Dim RoutCellRow As Integer
    Dim TaskCellRow As Integer

    Dim NewRoutPath As String
    Dim NewTaskPath As String

    'declare staring cells in spreadsheet

    RoutCellCol = 2
    RoutCellRow = 2

    TaskCellCol = 6
    TaskCellRow = 2


    Do Until TaskCellRow > 72 'ending row

    strRout = Cells(RoutCellRow, RoutCellCol).Value

    On Error GoTo TaskInRoute

    'create first layer folder in shared file

    NewRoutPath = ("S:DEPT\IE\Employee Files\Chris\" & "Rout " & strRout)
    MkDir (NewRoutPath)

    'create the first 2nd layer folder within the 1st layer folder just created

    strTask = Cells(TaskCellRow, TaskCellCol).Value

    NewTaskPath = (NewRoutPath & "\" & strTask)
    MkDir (NewTaskPath)


IterationLoop:

    RoutCellRow = RoutCellRow + 1
    TaskCellRow = TaskCellRow + 1

Loop

Exit Sub

'error handler if 1st layer folder already exists

TaskInRoute:


    strTask = Cells(TaskCellRow, TaskCellCol).Value

    NewTaskPath = (NewRoutPath & "\" & strTask)
    MkDir (NewTaskPath) 'error occurs here if 2nd layer folder already exists...nested on error statement?



   Resume IterationLoop


Exit Sub

End Sub

Sub ProPlanner() - the fact that the method doesn't start with a verb that tells us exactly what it does, is an indication that it's doing too many things. Split things up, extract responsibilities into separate functions and procedures, each responsible for handling their own set of possible errors. I'd suggest you put your code up on Code Review once it works as intended, if you would like peer review / feedback on all aspects of the code, and tips for making it cleaner, more fail-safe and generally easier to maintain. — Mathieu Guindon
– Mathieu Guindon, Commented May 11, 2016 at 14:21
This macro is just a one time thing, but if I make a macro that is widely or frequently used I will use the code review. Thank you — Chris
– Chris, Commented May 11, 2016 at 16:45

user3598756 · Accepted Answer · 2016-05-11 14:18:04Z

1

you cold add a check like follows

TaskInRoute:

    strTask = Cells(TaskCellRow, TaskCellCol).Value

    NewTaskPath = (NewRoutPath & "\" & strTask)
    If Dir(NewRoutPath & "\" & strTask, vbDirectory) = "" Then
        MkDir (NewTaskPath) 'error occurs here if 2nd layer folder already exists...nested on error statement?
    Else
        ' subfolder already exists
        ' do other things
    End If

    Resume IterationLoop

answered May 11, 2016 at 14:18

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

++ best error handling is handle error conditions before they turn into a runtime error.

Chris Over a year ago

I don't know why I did not think of an If then statement. Thank you.

Collectives™ on Stack Overflow

Nested on error VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related