my vba function returns crazy results! I commonly use a summation like this: =su

Question

my vba function returns crazy results!

I commonly use a summation like this:

=sum(b3:offset(b48,-1,0))

where b3 typically has text and b48 is the cell in which the formula appears. I can insert a new row at b4 through b47 and the formula is immediately adjusted. I tried to write a VBA function to simplify this via:

Public Function addup(r As Range, s As Range)
    Application.Volatile
    Set MyRange = Range(Cells(r.Row() + 1, r.Column()), Cells(s.Row - 1, s.Column))
    addup= Application.WorksheetFunction.Sum(MyRange)
End Function

My thought was to replace my formula above by

=addup(b3,b48)

However, the value is often incorrect. If I double-click on a column header or select "Calculate Now" on the formulas tab, it gets the right answer. What am I doing wrong???

Thanks !

Explanation / Answer

The MyRange variable is not declared.
The function skips the first row in the r range... "rRow() +1"
See if this slightly modified version does what you want:
'---
Public Function AddUp(r As Range, s As Range) As Variant
Dim MyRange As Range
Application.Volatile
Set MyRange = Range(r(1, 1), s(0, 1))
AddUp = Application.WorksheetFunction.Sum(MyRange)
End Function

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.