Calling Range into another subroutine

[crashcb18]

All,
I am trying to pass a range (single cell) from a master subroutine into another subroutine that is called. That "child" subroutine is going to act off the passed range (single cell) using the ActiveCell.Offset() command. A few questions:

Master Sub:

Sub master()
Call ActualTimeTest()
End Sub

- I want to make the child subroutine be executed on each cell in a row of cells. For example, for a range(B10:H10), I'd like to call the subroutine for each cell. What is the syntax or command I use in the master subroutine?

- What syntax do I put in paranthesis in the master subroutine to pass the correct range cell?:

- In the child subroutine, how do I take whatever is in the subroutine name() paranthesis and correctly name it into a variable or whatever so that the offset command is used appropriately? In the child subroutine code below, the second line of Range.Select is just there while I manually execute the child subroutine.

- Is there another option besides "ActiveCell" to use so that the user doesn't have to watch excel select each cell as it executes the child subroutine.

Here is the child subroutine below. Any help greatly appreciate figuring out the best syntax to use. I'd like to get rid of Activecell so excel doesn't highlight each cell as the code is executed.

Sub ActualTimeTest()
    Range("I15").Select
    
    Select Case ActiveCell.Value
        Case "": Exit Sub                               'If Scheduled Value is blank, quit routine
        Case Else                                          'If there is a Scheduled Event
            Select Case ActiveCell.Offset(2, 0).Value    'Look if an Actual Value is inputted
                Case "":                                     'If There is no Actual Value yet
                    
                    If ActiveCell - Range("A9") > 0.25 / 24 Then   'If Scheduled Time is Greater than 00:15 after Now
                        ActiveCell.Offset(2, -1) = "Now 15 min before Scheduled"
                        With ActiveCell.Offset(1, 0).Interior                                     'Make Cell fill color clear
                            .ColorIndex = 2
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                        Exit Sub
                    
                    ElseIf ActiveCell - Range("A9") <= 0.25 / 24 And ActiveCell - Range("A9") >= 0 Then 'If Scheduled Time is Less than 00:15 After Now
                        ActiveCell.Offset(2, -1) = "Now 15 min within Scheduled"
                        With ActiveCell.Offset(1, 0).Interior                                     'Make Cell fill color yellow
                            .ColorIndex = 6
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                    
                    ElseIf ActiveCell - Range("A9") < 0 Then  'If Scheduled Time is before Now Time, i.e. if Now has passed scheduled time
                        ActiveCell.Offset(2, -1) = "Now has passed scheduled time"
                        With ActiveCell.Offset(1, 0).Interior                                     'Make Cell fill color red
                            .ColorIndex = 3
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                    End If
                
                Case Is <> ""                           'If Actual is not Blank and there is a value in the Actual cell
                    If ActiveCell + 0.25 / 24 - ActiveCell.Offset(2, 0) >= 0 Then                      'If the Actual Time is before Scheduled time +/- 00:15
                        ActiveCell.Offset(2, -1) = "Actual time is on time and complete"
                        With ActiveCell.Offset(1, 0).Interior                                     'Make Cell fill color green
                            .ColorIndex = 4
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                        
                        
                    ElseIf ActiveCell + 0.25 / 24 - ActiveCell.Offset(2, 0) < 0 Then                    'If the Actual Time is late and executed at least 00:15 past Scheduled Time
                        ActiveCell.Offset(2, -1) = "Actual time is late, past Scheduled + 00:15"
                            With ActiveCell.Offset(1, 0).Interior                                     'Make Cell fill color red
                                .ColorIndex = 3
                                .Pattern = xlSolid
                                .PatternColorIndex = xlAutomatic
                            End With
                    End If
            End Select
    End Select
    
    End Sub

 

[GTO]

Hi There,

I did not follow exactly what you are trying to do, but see if this is a start...

All,
I am trying to pass a range (single cell) from a master subroutine into another subroutine that is called. That "child" subroutine is going to act off the passed range (single cell) using the ActiveCell.Offset() command. A few questions:

...

- What syntax do I put in paranthesis in the master subroutine to pass the correct range cell?:

- In the child subroutine, how do I take whatever is in the subroutine name() paranthesis and correctly name it into a variable or whatever so that the offset command is used appropriately? In the child subroutine code below, the second line of Range.Select is just there while I manually execute the child subroutine.

- Is there another option besides "ActiveCell" to use so that the user doesn't have to watch excel select each cell as it executes the child subroutine...

Option Explicit
    
Sub CallingSub()
Dim rngInitialCell As Range
Dim dblVal As Double
    
    With ThisWorkbook.Worksheets("Sheet1")  '<---Change to actual sheet name
        Set rngInitialCell = .Range("I15")
        dblVal = .Range("A9").Value
    End With
    
    Call ActualTimeTest(rngInitialCell, dblVal)
    
End Sub
    
Sub ActualTimeTest(rng As Range, MyVal As Double)
    
    '// Just a safety   //
    If rng.Count > 1 Then Exit Sub
    
    Select Case rng.Value
        Case "": Exit Sub                               'If Scheduled Value is blank, quit routine
        Case Else                                          'If there is a Scheduled Event
            Select Case rng.Offset(2, 0).Value    'Look if an Actual Value is inputted
                Case "":                                     'If There is no Actual Value yet
                    
                    If rng.Value - MyVal > 0.25 / 24 Then   'If Scheduled Time is Greater than 00:15 after Now
                        rng.Offset(2, -1) = "Now 15 min before Scheduled"
                        With rng.Offset(1, 0).Interior                                     'Make Cell fill color clear
                            .ColorIndex = 2
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                        Exit Sub
                    
                    ElseIf rng.Value - MyVal <= 0.25 / 24 And rng.Value - MyVal >= 0 Then    'If Scheduled Time is Less than 00:15 After Now
                        rng.Offset(2, -1) = "Now 15 min within Scheduled"
                        With rng.Offset(1, 0).Interior                                     'Make Cell fill color yellow
                            .ColorIndex = 6
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                    
                    ElseIf rng.Value - MyVal < 0 Then   'If Scheduled Time is before Now Time, i.e. if Now has passed scheduled time
                        rng.Offset(2, -1).Value = "Now has passed scheduled time"
                        With rng.Offset(1, 0).Interior                                     'Make Cell fill color red
                            .ColorIndex = 3
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                    End If
                
                Case Is <> ""                           'If Actual is not Blank and there is a value in the Actual cell
                    If rng.Value + 0.25 / 24 - rng.Offset(2, 0).Value >= 0 Then                        'If the Actual Time is before Scheduled time +/- 00:15
                        rng.Offset(2, -1).Value = "Actual time is on time and complete"
                        With rng.Offset(1, 0).Interior                                     'Make Cell fill color green
                            .ColorIndex = 4
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                        End With
                        
                        
                    ElseIf rng.Value + 0.25 / 24 - rng.Offset(2, 0).Value < 0 Then                      'If the Actual Time is late and executed at least 00:15 past Scheduled Time
                        rng.Offset(2, -1).Value = "Actual time is late, past Scheduled + 00:15"
                            With rng.Offset(1, 0).Interior                                     'Make Cell fill color red
                                .ColorIndex = 3
                                .Pattern = xlSolid
                                .PatternColorIndex = xlAutomatic
                            End With
                            
                    End If
            End Select
    End Select
End Sub

 

[crashcb18]

Thanks for the quick response and the start. I'll try that. Huge help since I'm familiar with Matlab syntax but am still at beginning to learn VBA syntax.

 

[crashcb18]

Ok, initial test works great as far as passing one range using the master subroutine of:

Sub CallingSub()
Dim rngInitialCell As Range
Dim dblVal As Double
    
    With ThisWorkbook.Worksheets("Sheet1")  '<---Change to actual sheet name
        Set rngInitialCell = .Range("I15")
        dblVal = .Range("A9").Value
    End With
    
    Call ActualTimeTest(rngInitialCell, dblVal)
    
End Sub

Now, when i made Range("I15") into Range("I15,M15"), then it formatted the M15 cell as well as I15. However, it formatted the M15 according to the Case of I15. Is there an Excel command like "For Each" or some command to perform the Case constructs individually on each cell? Does that make sense?

 

[GTO]

...Now, when i made Range("I15") into Range("I15,M15"), then it formatted the M15 cell as well as I15. However, it formatted the M15 according to the Case of I15. Is there an Excel command like "For Each" or some command to perform the Case constructs individually on each cell? Does that make sense?

Hi again,

I am following sort of... but am not making sense of this. If you are passing just one cell, then we can check the .Value of it. But if you are passing a (contiguous) range of cells, then I think we'd want to have the For Each wrap the Select Case. Something like:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ActualTimeTest(rng <SPAN style="color:#00007F">As</SPAN> Range, MyVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>)<br><SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#007F00">'// Just a safety   //  DELETE this if a multi cell range</SPAN><br>    <SPAN style="color:#007F00">'If rng.Count > 1 Then Exit Sub</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> rng<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> rCell.Value<br>            <br>            <SPAN style="color:#007F00">'remainder of code</SPAN><br>    </FONT>

Does that seem like what is wanted?

Mark

 

[crashcb18]

That works... thanks! slowly learning all of these new tricks and syntax.... much appreciation.

 

[GTO]

You are most welcome