Change value of another cell via a User Defined Function

Issue

The Function TotalHours() is designed to accept two ranges. The first range should usually be one row by seven columns (seven days of the week) and the second range is a single cell to accept a value.

I have a problem setting the value for the one cell.

The line I’m having a problem with:

rCell.Value = sngOT

I get

"Application-defined or object-defined error".

I tried rCell.Cells(1,1).Value = sngOT and get the same error.

Function TotalHours(myRange As Range, rOT As Range) As Single
Dim sngHours As Single, sngNormal As Single, sngOT As Single
Dim rCell As Range

    sngHours = 0
    sngNormal = 0
    sngOT = 0
    For Each rCell In myRange
        If rCell.Value > 8 Then
            sngOT = sngOT + rCell.Value - 8
            sngNormal = sngNormal + 8
        Else
            sngNormal = sngNormal + rCell.Value
        End If
    Next rCell
    If sngNormal > 40 Then
        sngOT = sngOT + (sngNormal - 40)
        sngNormal = 40
    End If
    sngHours = sngNormal + sngOT
    Set rCell = rOT
    rCell.Value = sngOT
    Set rCell = Nothing
    TotalHours = sngHours

End Function

Solution

Like Scott Craner, I’m assuming the error is thrown as your function is being used as a UDF (else you wouldn’t get the error). Assuming that’s true, there is a way to change other cells from within a UDF.

The following does what you want.
Note: I’ve made other changes and added code notes as to why.

Function TotalHours(rgHours As Range, rgWriteOT As Range) As Single
    
''' Declare hour values as doubles
''' Note: doubles declares as zero
    Dim dbTotalHrs#, dbNormalHrs#, dbOTHrs#, rgCell As Range

''' Get normal hours (max 8 per day) and bandwidth OT (i.e. any hours > 8 on any given day)
    For Each rgCell In rgHours
        dbNormalHrs = dbNormalHrs + WorksheetFunction.Min(rgCell, 8)
        dbOTHrs = dbOTHrs + WorksheetFunction.Max(rgCell - 8, 0)
    Next rgCell
    
''' For any given week worked more than 40 hours: All hours after 40 are OT
    If dbNormalHrs > 40 Then
        dbOTHrs = dbOTHrs + (dbNormalHrs - 40)
        dbNormalHrs = 40
    End If
    
''' Round results to 4 places (to avoid floating point rounding issues)
    dbOTHrs = Round(dbOTHrs, 4)
    dbTotalHrs = Round(dbNormalHrs + dbOTHrs, 4)
    
''' Return Total Hours
    TotalHours = dbTotalHrs

''' Write OT Hours to rgWriteOT (via SetOTValue)
    With rgWriteOT
        .Parent.Evaluate "SetOTValue(" & .Address(False, False) & "," & dbOTHrs & ")"
    End With

End Function

''' Sub to set a value in another cell
Sub SetOTValue(Target As Range, Value#)
    Target = Value
End Sub

That all covered, important to point out, you could all of that easily enough with formulas:
Assuming your daily hours are in cells A2:G2, Total Hours in is Cell H2 and OT Hours is in I2, the following does what you want:
Formula for Total Hours (H2): =SUM(A2:G2)
Formula for OT Hours (I2): {=IF(H2>40,H2-40,SUM(IF(A2:G2>8,A2:G2-8)))}

Note the opening { and closing } at I2:
o This is an array formula. You don’t enter the { or }
o Rather, you ‘commit’ the formula using Ctrl, Shift and Enter

Answered By – Spinner

Answer Checked By – Marilyn (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.