UDF in Excel VBA Editor only returns zero when a Double is wanted

Question

This function is designed to calculate the area of a closed traverse. When written as a sub and assigned to a specific cell, the sub works perfectly. However, when used as a function- as shown below- it returns only zero. Why? 'Function designed to accommodate any number of traverse sides

Public Function TraverseArea() As Double
Dim Area As Double
Area = 0
Range("N2").Select
Area = (ActiveCell.Value * (Range("M2").End(xlDown).Offset(-1, 0).Value - ActiveCell.Offset(1, -1).Value))
ActiveCell.Offset(1, 0).Select

While ActiveCell.Offset(1, -1) <> ""
    Area = Area + (ActiveCell.Value * (ActiveCell.Offset(-1, -1).Value   -  ActiveCell.Offset(1, -1).Value))
    ActiveCell.Offset(1, 0).Select
Wend
If Area < 0 Then
    Area = Area * -1
End If
Area = Area / 2
TraverseArea = Area
End Function

Why are you selecting cells and using activecell in a function? From what I can see you don't need to select those cells, you can read the cell contents without selecting it. — Dan Donoghue, Feb 15 '16 at 03:08
The way i am using activecell is to step through a list of Northings and Eastings from top to bottom until the end (Which could be any number of cells down). You are absolutely right on not needing to select each individual cell, however, that still does not explain why the function only returns zero and not the calculated area. — Jon, Feb 15 '16 at 03:19
Put a stop on it and step through line by line (F8 in the VBE) and keep an eye on AREA as the code runs. I would recommend removing activecell and passing in a cell reference when you call the function. If you choose to pass in activecell at runtime then so be it but it's much better than locking a function down to a cell reference. Also I would change the While loop to a for next, looping the range as opposed to looking for a blank, this way you can reference the range without needing to offset. — Dan Donoghue, Feb 15 '16 at 03:23
A function used as a UDF runs with certain restrictions, such as not being able to change the Excel environment it runs in: this includes selecting cells or changing the values of cells other than the one(s) it's called from. — Tim Williams, Feb 15 '16 at 03:36

score 2 · Accepted Answer · answered Feb 15 '16 at 03:33

I don't have your data or sheet structure so this is all out of my head but this should give you an idea of how you can have your function without specifically using hard coded ranges in it.

Sub TestFunction()
    MsgBox TraverseArea(Range("N2"), Range("M2").End(xlDown).Offset(-1, -1))
End Sub

Public Function TraverseArea(MyRange As Range, MySecondRange As Range) As Double
Dim Area As Double, lr As Long, X as long
lr = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
Area = (MyRange.Value * (MySecondRange.Value - MyRange.Offset(1, -1).Value))
For X = MyRange.Row To lr
    If Cells(X, MyRange.Column - 1) = "" Then Exit For
    Area = Area + (ActiveCell.Value * (ActiveCell.Offset(-1, -1).Value - ActiveCell.Offset(1, -1).Value))
Next
If Area < 0 Then Area = Area * -1
Area = Area / 2
TraverseArea = Area
End Function

This will most likely need some debugging but should be enough to give you an idea of what I am trying to say in my prior comments about using cell references without selecting them.

Thank you all for the quick replies, both solutions worked with a little tweaking. Also, thank you for the reading suggestion on avoiding Select. — Jon, Feb 15 '16 at 04:16

score 1 · Answer 2 · edited May 23 '17 at 11:59

The code worked as a sub procedure because you had the correct worksheet visible as the ActiveSheet property and subs allow you to use the Range .Select method and Range .Activate methods¹. A function used on a worksheet needs to know what worksheet it is on and selecting cells is not an approved method.

Public Function TraverseArea(Optional aRNG As Variant) As Double
    Dim dAREA As Double, r As Long, rng As Range
    dAREA = 0

    With Application.Caller.Parent
        If IsMissing(aRNG) Then Set aRNG = .Range("N2")
        For Each rng In .Range(aRNG, aRNG.End(xlDown))
            If IsEmpty(rng) Or Not IsNumeric(rng) Or Not CBool(Len(rng.Offset(1, -1))) Then _
                Exit For
            With rng
                'Area = Area + (ActiveCell.Value * (ActiveCell.Offset(-1, -1).Value - ActiveCell.Offset(1, -1).Value))
                dAREA = dAREA + .Value2 * (.Offset(0, -1).End(xlDown).Offset(-1, 0).Value2 - .Offset(1, -1).Value2)
            End With
        Next rng
    End With

    If dAREA < 0 Then
        dAREA = dAREA * -1
    End If
    dAREA = dAREA / 2
    TraverseArea = dAREA

End Function

The Application.Caller helps find the parent worksheet on which the function resides. No cells are selected or activated but they are cycled through by using direct addressing through providing row numbers, column numbers and some manipulation of the Range.Offset property.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

UDF in Excel VBA Editor only returns zero when a Double is wanted

2 Answers2