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

Like

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

Like

3. dougaj4 says:

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

Like

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

Like

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 🙂

Like

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.

Like

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…