This post is in response to recent discussions at Daily-Dose-of-Excel where many and various worksheet formulas have been suggested to carry out tasks that are (it seems to me) better done with a simple User Defined Function (UDF):
Adding Every Other Cell (also at the Microsoft Office Blog: Adding Every Other Cell in a Column )
and
Summing the Digits of a Number
My solutions to these, and also another at Eng-Tips: Transposing data from columns, can be dowloaded here: Sum Tab.xls
The spreadsheet includes full open source code as usual:
SumSkip Function
The SumSkip function will sum every n’th row or column of a range, starting from any specified cell.
Function SumSkip(SumRange As Variant, Optional NumSkip _As Long _
= 2, Optional StartCell As Long = 1, _
Optional DirSkip As String) As Double
Dim Numrows As Long, NumCols As Long, Sums As Double
Dim i As Long, j As Long, k As Long
If TypeName(SumRange) = "Range" Then SumRange = SumRange.Value2
Numrows = UBound(SumRange)
NumCols = UBound(SumRange, 2)
If DirSkip = "" Then
If Numrows > NumSkip Then
DirSkip = "V"
ElseIf NumCols > NumSkip Then
DirSkip = "H"
End If
End If
DirSkip = UCase(DirSkip)
Select Case DirSkip
Case Is = "V"
For i = StartCell To Numrows Step NumSkip
For j = 1 To NumCols
Sums = Sums + SumRange(i, j)
Next j
Next i
Case Is = "H"
For j = StartCell To NumCols Step NumSkip
For i = 1 To Numrows
Sums = Sums + SumRange(i, j)
Next i
Next j
End Select
SumSkip = Sums
End Function
SumDig Function
The SumDig function sums the digits of a value or string (either including or excluding values to the right of the decimal point). I have incorporated the use of a Byte array to extract the numeric characters without tripping over non-numeric characters, thanks to Charles Williams who provided a neat UDF in the DDoE thread using this technique.
Function SumDig(SumVal As String, _
Optional SumFract As Boolean = False) As Long
Dim NumDig As Long, i As Long, DPPos As Long, ByteA() As Byte
DPPos = InStr(1, SumVal, ".") * 2 - 1
ByteA = CStr(SumVal)
NumDig = Len(SumVal) * 2 - 1
If DPPos > 0 Then
For i = 0 To DPPos Step 2
SumDig = SumDig + Val(Chr(ByteA(i)))
Next i
If SumFract = True Then
For i = DPPos + 1 To NumDig Step 2
SumDig = SumDig + Val(Chr(ByteA(i)))
Next i
End If
Else
For i = 0 To NumDig Step 2
SumDig = SumDig + Val(Chr(ByteA(i)))
Next i
End If
End Function
Tabulate Function
The tabulate function creates a table based on row and column numbers and data listed in a 3 column range. It was pointed out in the Eng-Tips thread that this could also be done with a pivot table, but the UDF solution seems simpler to me.
Function Tabulate(TabA As Variant) As Variant
Dim Numrows1 As Long, NumRows2 As Long, NumCols As Long, Tab2A() As Variant
Dim i As Long, j As Long
TabA = TabA.Value2
Numrows1 = UBound(TabA)
NumRows2 = TabA(Numrows1, 1)
NumCols = TabA(Numrows1, 2)
ReDim Tab2A(1 To Numrows1, 1 To NumCols)
For i = 1 To Numrows1
Tab2A(TabA(i, 1), TabA(i, 2)) = TabA(i, 3)
Next i
Tabulate = Tab2A
End FunctionDoes a straightforward UDF beat a convoluted worksheet function, or are UDFs best avoided?
What do you think?
Newton Excel Bach, not (just) an Excel Blog 
I think UDFs are as a rule the best solution for anything which is going to be used often enough to make them worthwhile. I usually find complicated logic easier to parse in code than in a worksheet function with nested calls. The one thing I think it’s important to note with UDFs is that dependencies need to be managed carefully, so that the UDF is always recalculated when it needs to be.
LikeLike
Here is a slightly shorter “sum digits” function which, as far as I can tell, duplicates the results of the function you posted…
Function SumDigits(N As Variant, Optional SumFract As Boolean) As Long
Dim X As Long, Char As String
For X = 1 To Len(CStr(N))
Char = Mid(N, X, 1)
If Not SumFract And Char = “.” Then Exit Function
If Char Like “#” Then
SumDigits = SumDigits + CLng(Char)
End If
Next
End Function
LikeLike
Rick – yes, it seems to work the same. Very neat 🙂
LikeLike
If you think that is neat, I can’t wait to hear what you say about this. If we restrict the argument to the function to be valid integer or floating point numbers (that is, no embedded characters that would make the argument a non-number), then the SumDigits function can be reduced to a single line of code (albeit a long one)…
Function SumDigits(N As Variant, Optional SumFract As Boolean) As Long
SumDigits = Evaluate(Replace(StrConv(IIf(SumFract, Abs(Replace(N, _
“.”, “0”)), Abs(Fix(N))), vbUnicode), Chr(0), “+”) & “0”)
End Function
LikeLike
Rick – well it’s very clever, but in the spirit of the thread, I prefer your slightly longer one, because I can see how the damn thing works.
Still, it’ll give me a nice little puzzle for theChristmas break, trying to work out the logic of the one liner 🙂
LikeLike
And, of course, the longer one does not have the restriction on the argument having to be a number. As for the readability problem… yeah, I know what you mean. You have to understand one thing about me… originally, I came over to Excel from the compiled version of VB where I earned my original MVP status at. Over in the VB newsgroups, I developed a reputation for posting one-liners and readability was usually the major complaint about them, so I fully understand what you mean. However, many liked trying to figure out how the damned things worked… just like you are planning to do. So, enjoy the puzzle.
LikeLike
Continuing on about my propensity for one-liners, perhaps you will find this one that I sent to John Walkenbach not all that long ago interesting…
http://spreadsheetpage.com/index.php/tip/is_a_particular_word_contained_in_a_text_sring/
LikeLike
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog