Yes, UDF's are slower, and maybe I should have mentioned that on the
off-chance the OP was not aware of this fact; but the point of my posting
was that I thought the UDF interface would be more familiar for the OP to
work with than the less obvious SUMPRODUCT formulas that were offered.
Yes, including the error message was a foolish last minute add in on my part
(done spur of the moment without having given it much thought). As for my
experience... no, it does not involve a lot of UDF development. If you
remember me at all from when you first "jumped on my case" with some
frequency about two and a half years ago when I began volunteering in the
Excel newsgroups, then you might remember that the largest part of my
programming experience was with the compiled version of VB, not VBA... and
in that environment, error messages like the one I used here made more
sense. As an aside, it might have been more beneficial to the thread if you
had simply chosen to point out the error in my approach without including
all the attendant histrionics. In any event, I appreciate, as I always have,
the benefit of your wisdom as contained in your underlying comments.
I never used Lotus 123, so I was not familiar with the particulars of its
NSUM function; rather, I relied on the OP's posted syntax which did not
suggest that a 3D component existed for this function. I have a question,
though... does the Lotus 123 NSUM function actually wrap the areas as your
code does? By that I mean, for this formula...
=NSUM(1,3,B5:C15)
the cells that will be added are B6, B9, B12, C7, C10, C13... notice the
Column B cells are offset, row-wise, from the Column A cells. Of course this
will happen whenever the loop through the rows does not finish on the
column's last processed row. Not having any personal experience with the
actual NSUM function, this looks, at first blush, to be somewhat useless to
me... my gut feeling tells me it would be more practical to have the same
row processed in each column
--
Rick (MVP - Excel)
Rick Rothstein said:
If you want something more "friendly" looking that the other solutions
....
And A LOT SLOWER if used frequently!
Function NSUM(Offset As Long, SkipAmount As Long, Rng As Range) As Double
Dim X As Long
With Rng
If .Rows.Count > 1 And .Columns.Count > 1 Then
MsgBox "This function only works with single rows or single " & _
"columns!", vbCritical, "Improper Range Specified"
End If
....
A classic and stupendous design MISTAKE!
NEVER have udfs display @#$% error dialogs! The problem is filling or
pasting formulas calling a udf into perhaps thousands of cells, but
for one reason or another, the filled or pasted formulas trigger the
error. Rather than Excel's recalc engine QUICKLY returning error
values, Excel would instead display the same STUPID error dialog for
EVERY cell that triggers the error.
For someone who supposedly knows something about VB, you don't seem to
have much practical experience with VBA udfs.
Then there's the question why this should be an error. It's useful to
know the particulars of the OP's question, which in this case means
having a 123 help file handy if not an actual installed 123 version. I
have both, and 123's @NSUM handles 3D(!!) ranges as 3rd argument. For
example, the 123 formula
@NSUM(0,3,A:C3..D:F11)
returns a numeric result, not an error. 123 iterates through its 3rd
argument in the same order as it would iterate through 3D arguments to
@NPV, which defaults to by row then by column then by sheet. Simple
enough to come up with a 2D equivalent udf, even extend it to handle
multiple area ranges. Note that 123 iterates through ranges by row
then by column, so For Each iteration through Excel ranges would
usually produce different results.
Function nsum(ByVal offs As Long, n As Long, a As Variant) As Variant
Dim x As Variant, k As Long, i As Long, j As Long, m As Long
k = -offs - 1
If offs < 0 Or n < 1 Then 'trap 123 error conditions and return
error values QUICKLY!
nsum = CVErr(xlErrNum)
ElseIf TypeOf a Is Range Then
For m = 1 To a.Areas.Count
For j = 1 To a.Areas(m).Columns.Count
For i = 1 To a.Areas(m).Rows.Count
k = k + 1
If k >= 0 And k Mod n = 0 _
And VarType(a.Areas(m).Cells(i, j).Value2) = vbDouble Then
_
nsum = nsum + a.Areas(m).Cells(i, j).Value2
Next i
Next j
Next m
ElseIf IsArray(a) Then
For Each x In a
k = k + 1
If k >= 0 And k Mod n = 0 And VarType(x) = vbDouble Then _
nsum = nsum + x
Next x
ElseIf offs = 0 And n = 1 Then 'return the same result as 123 for
degenerate ranges
nsum = a
End If
End Function
This still leaves support for variable number of arguments. You wanna
try that?
7 in Excel, and using my nsum