Multiple subs not running dynamically when called from one sub

[GreyUm]

I have created 12 subs ,1 for each month of the year. The months form a master grid for the year. Each sub colours Weekends and any bank holiday within the month. Each sub works perfectly if I run it separately but if I list them within a calling sub, the calling sub halts without an error and only does the first month. Any suggestions would be most gratefully received

Included below is January / febuary example

Sub fmtJan()
'Format all of January
Dim rng As Range, rngPubHol As Range
Dim RW As Integer, NumDays As Integer, col As Integer
Dim SeedDt  As String, note As String
Dim Dt As Date

On Error GoTo Err_Handler
'           Add Dates
SeedDt = "01/01/" & cnDash.Range(YEAR_TO_BUILD_YR)

Dt = CDate(SeedDt)
'Debug.Print Dt

NumDays = 31 - 1
With cnMaster
Set rng = .Range(JAN1)
For col = 0 To NumDays
rng.Offset(0, col) = Dt
Dt = Dt + 1
Next col

' Format Weekends
For col = 0 To NumDays
    If Weekday(rng.Offset(0, col), vbMonday) > 5 Then
        rng.Offset(0, col).Interior.Color = vbGreen
        rng.Offset(0, col).Locked = True
    End If
    Dt = Dt + 1
Next col

'   Format Pub Hols
RW = 0
Set rngPubHol = cnDash.Range("G10")
Set rng = .Range(JAN1)
For col = 0 To NumDays
    For RW = 0 To 8
        If rng.Offset(0, col) = rngPubHol.Offset(RW, 0) Then
            note = rngPubHol.Offset(RW, 1)
            Debug.Print note
            rng.Offset(0, col).Interior.Color = vbBlue
            rng.Offset(0, col).AddComment note
            rng.Offset(0, col).Locked = True
         End If
    Next RW
Next col

End With

Err_Exit:
Exit Sub
'end

Err_Handler:
MsgBox "There was  an error while formating the Master sheet." & vbCrLf & _
        "Error in Sub fmtJan, located in moduleModMasterFmt" & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description & vbCrLf & _
        "Cannot continue!", vbCritical + vbOKOnly, "JAN Format Failed"
        Resume Err_Exit
       
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Format all of February
Sub fmtFeb()
Dim rng As Range, rngPubHol As Range
Dim RW As Integer, NumDays As Integer, col As Integer
Dim SeedDt  As String, note As String
Dim Dt As Date

On Error GoTo Err_Handler
'           Add Dates
SeedDt = "01/02/" & cnDash.Range(YEAR_TO_BUILD_YR)

Dt = CDate(SeedDt)
'Debug.Print Dt
If cnDash.Range(LEAP_YEAR_YR) = True Then
    NumDays = 29 - 1
    cnMaster.Range("AE13:AF13").Interior.Color = vbBlack
    cnMaster.Range("AE13:AF13").Locked = True
Else
    NumDays = 28 - 1
    cnMaster.Range("AD13:AF13").Interior.Color = vbBlack
    cnMaster.Range("AD13:AF13").Locked = True
End If
With cnMaster
Set rng = .Range(FEB1)
For col = 0 To NumDays
rng.Offset(0, col) = Dt
Dt = Dt + 1
Next col

' Format Weekends
Dt = CDate(SeedDt)
For col = 0 To NumDays
    If Weekday(rng.Offset(0, col), vbMonday) > 5 Then
        rng.Offset(0, col).Interior.Color = vbGreen
        rng.Offset(0, col).Locked = True
    End If
    Dt = Dt + 1
Next col

'   Format Pub Hols
RW = 0
Set rngPubHol = cnDash.Range("G10")
Set rng = .Range(FEB1)
For col = 0 To NumDays
    For RW = 0 To 8
        If rng.Offset(0, col) = rngPubHol.Offset(RW, 0) Then
            note = rngPubHol.Offset(RW, 1)
            Debug.Print note
            rng.Offset(0, col).Interior.Color = vbBlue
            rng.Offset(0, col).AddComment note
            rng.Offset(0, col).Locked = True
         End If
    Next RW
Next col

End With

Err_Exit:
    End


Err_Handler:
MsgBox "There was  an error while formating the Master sheet." & vbCrLf & _
        "Error in Sub fmtFeb, located in moduleModMasterFmt" & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description & vbCrLf & _
        "Cannot continue!", vbCritical + vbOKOnly, "Feb Format Failed"
        Resume Err_Exit
       
End Sub

 

[FormR]

Err_Exit:
End

Hi, welcome to (posting to) the forum! In the Feb macro, don't use End as quoted above, use Exit Sub like you have in the Jan macro. End un-ceremoniously halts all code execution.

 

[Bosquedeguate]

Wow! You have a lot of redundant code!! I think you could have one that could be used for all months
If you use a Select Case / End Select set of statement for the unique parts of each month it would be a lot cleaner.
I'll take a closer look and see if this would make sense for you.

 

[GreyUm]

Wow! You have a lot of redundant code!! I think you could have one that could be used for all months
If you use a Select Case / End Select set of statement for the unique parts of each month it would be a lot cleaner.
I'll take a closer look and see if this would make sense for you.

Hi Bosquedeguate, thanks for taking the time to look at my post. I did originally have all months within one sub when developing it but found it difficult to find errors so thought it easier to split them to their own subs. With regard to using an end statement, I put this in the error handler because I didn't want the program to continue with the next sub if their was an error. Is this the wrong way to handle that situation? As you can probably determine from my coding I'm a bit of a novice when it comes to programming. Thank you once again
GreyUm

 

[FormR]

With regard to using an end statement, I put this in the error handler because I didn't want the program to continue with the next sub if their was an error. Is this the wrong way to handle that situation?

You've put it in the exit handler though! But you really shouldn't use End unless you fully understand the implications of it's use. You could handle it differently by changing your subs to functions and have them return a flag that tells you if they completed without error or not which you could test before executing the next part of your code.

 

[GreyUm]

You've put it in the exit handler though! But you really shouldn't use End unless you fully understand the implications of it's use. You could handle it differently by changing your subs to functions and have them return a flag that tells you if they completed without error or not which you could test before executing the next part of your code.

Thanks for your advice re the error handling, in future I will use your method
best regards
GreyUm

 

[Tupe77]

Untested, but I believe this is your intention.
No error -> Exit to next sub
Error - Error message -> End all code execution


Replace from your:

Exit Sub

Err_Handler:
MsgBox "There was  an error while formating the Master sheet." & vbCrLf & _
        "Error in Sub fmtJan, located in moduleModMasterFmt" & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description & vbCrLf & _
        "Cannot continue!", vbCritical + vbOKOnly, "JAN Format Failed"       

End

End Sub

 

[FormR]

It's of course up to the OP's discretion to choose which path to take, but in my opinion, the use of End should generally be avoided.