CODEWITHSUNDEEP
vbaexcelexcel-formula
Jeff
Jeff

Reputation: 14279

Answer in Excel cell is not value while debugging

I have a custom formula that I am trying to use in my Excel 2011 worksheet. The idea is to use some custom logic to calculate the average of a range of cells. When I step through the debugger in Excel, the formula runs perfectly.

However, when I put the formula in a cell on the worksheet (i.e. =SuperAverage(K4:K17)), I get either 0 or #VALUE. I am running the formula on the same exact ranges as I do when I step through the debugger yet I am getting different answers.

Why is this happening? I have tried manually recalculating the cells but the errors remain.

Function SuperAverage(r) As Double
    Dim total_sum, total_count As Integer
    total_sum = 0
    total_count = 0

    For Each c In r.Cells
        If Not IsHidden(c) And c.value <> 0 Then 'IsHidden is another custom function irrelevant to this question
            total_count = total_count + 1
            total_sum = total_sum + c.value
        End If
    Next

    If total_count = 0 Then
        SuperAverage = 0
    Else
        SuperAverage = total_sum / total_count
    End If
End Function

'test function to step through the debugger
Function Test()
    Dim r As range
    Set r = Worksheets("Hours").range("K4:K17")

    Dim result As Double
    result = SuperAverage(r)
End Function

Upvotes: 0

Views: 200

Answers (1)

majjam
majjam

Reputation: 1326

Aye so I changed a few things, declaring variables explicitly etc and get this:

Option Explicit

Function SuperAverage(ByVal r As Range) As Double
    Dim total_sum As Integer
    Dim total_count As Integer
    total_sum = 0
    total_count = 0
    Dim c As Variant

    For Each c In r.Cells
        If Not IsHidden(c) And c.Value <> 0 Then 'IsHidden is another custom function    irrelevant to this question
            total_count = total_count + 1
            total_sum = total_sum + c.Value
        End If
    Next

    If total_count = 0 Then
        SuperAverage = 0
    Else
        SuperAverage = total_sum / total_count
    End If
End Function

Which seems to calculate the average fine for me. I wonder if it might be a hard to debug error caused by the compiler inferring variable types.

Upvotes: 1

Related Questions