Excel VBA Object Required when using custom function to set a cell Value

Question

I'm getting Error '424': Object Required When I try to run the following subroutine:

Sub MySub()
    'Iterate through column, setting corresponding values
    For Each cell In Range("Table5[Name]")
        cell.Offset(, 2).Value = Avg("Table2[time]", cell.Value, "Table2[name]")
        cell.Offset(, 3).Value = StDev("Table2[time]", cell.Value, "Table2[name]", cell.Offset(, 2).Value)
    Next
End Sub

When I press debug, it highlights the second line inside the For loop. Both Avg and StDev are functions I wrote to work similarly to AVERAGEIF, but in a very specific scenario.

The Avg function works exactly as intended, and StDev is almost identical. It has one extra If statement, and the math is almost identical. Both functions return a Variant.

I can't figure out why it's doing this. So far I've tried adding Set in front of the troublesome line (I knew it wouldn't work, but I had to try anyway), and I've made sure both functions are safe (i.e. they do not divide by zero, and they always return a value).

Here's the StDev function (modified to protect work confidentiality, but has same issue):

Function StDev(rng As Range, Criteria1 As Variant, Criteria_Rng1 As String, Optional avg As Variant) As Variant
    'Call like this
    '  StDev2Ifs(Range to StDev, Match this, Range to find Criteria1, [average])

    If IsMissing(avg) Then
        avg = Avg(rng, Critera1, Criteria_Rng1)
    End If

    If avg <> "NO DATA" Then
         'Convert Strings to Ranges
        Dim c_rng, c_rng1 As Range
        Set c_rng = Application.Range(rng)
        Set c_rng1 = Application.Range(Criteria_Rng1)

        'Get Column Indices
        Dim r, r1As Long
        r = c_rng.Columns(c_rng.Columns.Count).Column
        r1 = c_rng1.Columns(c_rng1.Columns.Count).Column

        'Calculate column offsets from range to stdev
        Dim off1 As Long
        off1 = r1 - r

        'x will be used to sum the elements
        Dim x As Double
        x = 0
        'ct will be used to count number of elements
        Dim ct As Double
        ct = 0
        For Each cell In Range(rng)
            If cell.Offset(, off1).Value = Criteria1 Then
                x = x + Square(cell.Value - avg)
                ct = ct + 1
            End If
        Next

        'Divide by count
        If ct <> 0 Then
            StDev = x / ct
        Else
            StDev = "NO DATA"
        End If

        'Square Root
        If ct <> 0 Then
            StDev = StDev ^ (1 / 2)
        End If
    Else
        StDev = "NO DATA"
    End If
End Function

Anybody have any ideas?

Answer 1

cell.Offset(, 3).Value = StDev("Table2[time]", cell.Value, "Table2[name]", cell.Offset(, 2).Value)

in the first code block should be

cell.Offset(, 3).Value = StDev(Range("Table2[time]"), cell.Value, "Table2[name]", cell.Offset(, 2).Value)

if you intend to pass a range as the function expects. You will run to an error tough because in your function StDev seems to be built for it to be passed as string so I suggest that you correct your function from

Function StDev(rng As Range, Criteria1 As Variant, Criteria_Rng1 As String, Optional avg As Variant) As Variant

to

Function StDev(rng As String, Criteria1 As Variant, Criteria_Rng1 As String, Optional avg As Variant) As Variant

Quick check so be careful, I might have missed something.