CODEWITHSUNDEEP
excelvba
Scarce573
Scarce573

Reputation: 21

Excel: How to apply a user-defined function to many elements and then sum them?

What I'm trying to do is apply my own function onlyDigits on all the elements in a selection and then sum them.

With array functions, you can do things like 

{=SUM(SQRT(A2:A10))}

To sum all of the square roots of a selection. What I want to do is effectively replace SQRT with my own function. Specifically, I want to be able to have something like

{=SUM(PRODUCT(onlyDigits(A2:A10), B2:B10))}

that performs onlyDigits on each element, multiplies it by the one below it, and then adds it to the sum, representing the final sum in one cell.

My function onlyDigits takes its input (a cell) and retrieves only the numbers from it, so it could be a problem with not only receiving numerical input (but I'm not sure, hence I'm here). Here's the source, I copied it from a different question:

Function onlyDigits(s As String) As String
    ' Variables needed (remember to use "option explicit").   '
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '

    ' Initialise return string to empty                       '
    retval = ""

    ' For every character in input string, copy digits to     '
    '   return string.                                        '
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    ' Then return the return string.                          '
    onlyDigits = retval
End Function

Is this possible? If so, how can I do it? Much thanks!

Upvotes: 2

Views: 67

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You need to return an array:

Function onlyDigits(s As Range) As Variant()
    ' Variables needed (remember to use "option explicit").   '
    Dim retval() As Variant    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '

    ' Initialise return string to empty                       '
    ReDim retval(1 To s.Cells.Count)

    ' For every character in input string, copy digits to     '
    '   return string.
    For j = 1 To s.Cells.Count '
        For i = 1 To Len(s(j))
            If IsNumeric(Mid(s(j), i, 1)) Then
               retval(j) = retval(j) + Mid(s(j), i, 1)
            End If
        Next i
        retval(j) = CLng(retval(j))
    Next j

    ' Then return the return string.                          '
    onlyDigits = retval

End Function

The array returned is horizontal, so you need to transpose it:

=SUMPRODUCT(TRANSPOSE(onlyDigits(A1:A5)),B1:B5)

It needs to be array entered with Ctrl-Shift-Enter.

enter image description here

Upvotes: 4

Related Questions