CODEWITHSUNDEEP
exceluser-defined-functionsvba
BruceWayne
BruceWayne

Reputation: 23283

UDF - different results when auto-ran than when walked through

I have the following UDF, saved into a module of my workbook:

Function sumAbove2(myCell As Range)

Dim rng As Range, cel As Range
Dim topRow As Long
topRow = myCell.Offset(0, -2).End(xlUp).Row
Dim sRng As Range, eRng As Range
Set sRng = Cells(myCell.Row, myCell.Column)
Set eRng = Cells(topRow, myCell.Column)

For Each cel In Range(sRng, eRng)
    Debug.Print cel.Address
    sumAbove2 = cel.Value + sumAbove2
Next cel

End Function

The idea is to automatically sum "blocks" of information.

The UDF works just fine when I walk through it with F8. But, when running automatically, it can give unexpected results. The annoying thing is that I have placed this code in a brand new workbook, threw in sample data, and it never generated incorrect results...so for that, I apologize my SO friends, I can't quite get it to reproduce. I'm relatively new to UDF, so may be missing some key point about running them (does volatility help/hurt?)

Example of the wrong values being added. Note it catches the values from the cells above it.

And when I do this, two seconds later, with a Break in the macro, I can step through with F8, it correctly adds nothing, and returns 0.

What could be going on? I didn't specify the sheet in the code, but I can't see why that would fix it. Could it have to do with some other formulas on the page? There's no worksheet_change event, etc.

Edit: The workbook has a few sheets, with formulas in those sheets. But the sheet I'm running this on is all text, save the formula I'm trying to enter. Just thought to mention in case something in formatting could be giving the odd behavior.

Upvotes: 1

Views: 79

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

You need to fully qualify all your ranges with the correct worksheet...

Function sumAbove2(myCell As Range)

    Dim sht As Worksheet '<<<
    Dim rng As Range, cel As Range
    Dim topRow As Long
    Dim sRng As Range, eRng As Range

    Set sht = myCell.Worksheet '<<<

    topRow = myCell.Offset(0, -2).End(xlUp).Row

    Set sRng = sht.Cells(myCell.Row, myCell.Column) '<<<
    Set eRng = sht.Cells(topRow, myCell.Column)     '<<<

    For Each cel In sht.Range(sRng, eRng) '<<<
        Debug.Print cel.Address
        sumAbove2 = cel.Value + sumAbove2
    Next cel

End Function

Edit: debugging UDF #VALUE errors from the worksheet is tricky - you will get more information if you debug by calling the function from a test sub:

Sub Tester()
    Debug.Print sumAbove2(Activesheet.Range("C44"))
End sub

Upvotes: 1

Miqi180
Miqi180

Reputation: 1691

I would 1) simplify the code a bit (you're not using the rng variable for anything and you don't really need the start and end row ranges in separate variables), 2) define the data type to be returned, 3) use fully qualified references and 4) add a numeric check the following way:

Function sumAbove2(myCell As Range) As Double

    Dim actSht As Excel.Worksheet
    Dim topRow As Long
    Dim cel As Range, searchRng As Range

    topRow = myCell.Offset(0, -2).End(xlUp).Row

    Set actSht = ActiveSheet
    With actSht
        Set searchRng = .Range(.Cells(myCell.Row, myCell.Column), .Cells(topRow, myCell.Column))
    End With

    For Each cel In searchRng
        If IsNumeric(cel.Value) Then sumAbove2 = cel.Value + sumAbove2
    Next cel

End Function

Seems to work flawlessly by me.

Upvotes: 1

Related Questions