Three UDFs

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 )
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

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

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
For i = 0 To NumDig Step 2
SumDig = SumDig + Val(Chr(ByteA(i)))
Next i
End If
End Function

Tabulate 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 Function

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

What do you think?

This entry was posted in Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

8 Responses to Three UDFs

  1. geoffness says:

    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.


  2. Rick Rothstein (MVP - Excel) says:

    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
    End Function


  3. dougaj4 says:

    Rick – yes, it seems to work the same. Very neat🙂


  4. Rick Rothstein (MVP - Excel) says:

    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


  5. dougaj4 says:

    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🙂


  6. Rick Rothstein (MVP - Excel) says:

    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.


  7. Rick Rothstein (MVP - Excel) says:

    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…


  8. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s