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:

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

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

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 Function

Does a straightforward UDF beat a convoluted worksheet function, or are UDFs best avoided?

What do you think?

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