Edit VBA UDF to sum numbers in parenthesis while i

问题:

I have a UDF that will look in a given cell for numbers inside of parenthesis then sum all numbers inside of parenthesis in a given cell, it works well 90% of the time but when I have something inside of parenthesis that is not a number i.e a word or phrase, it will return #VALUE! I'm trying to fix this so that it will ignore words etc that are inside parenthesis. Also for some reason, having a "." after parenthesis makes it so that the numbers in the parenthesis before the "." are ignored when they shouldn't be.

screenshot of problems explained above

The function is as follows

Public Function Addum(rng As Range) As Double
    Dim s As String, L As Long, temp As String
    Dim CH As String
    s = rng.Value
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Or CH = "(" Or CH = ")" Then
            temp = temp & CH
        Else
            temp = temp & " "
        End If
    Next i

    temp = Application.WorksheetFunction.Trim(temp)
    arr = Split(temp, " ")
    For Each a In arr
        If Left(a, 1) = "(" Then
            a = Mid(a, 2, Len(a) - 2)
            If IsNumeric(a) Then
                Addum = Addum + CDbl(a)
            End If
        End If
    Next a
End Function

this question is different from Excel: Sum numbers inside a text block in a cell because im asking for it to work when there are words inside of parenthesis present and when there are "." after the parenthesis.

thanks in advance for any help that you can provide!

回答1:

Public Function Addum(rng As Range) As Double
    Dim s As String, temp As String, i As Long
    Dim CH As String, inParens As Boolean, q As String
    s = rng.Value
    For i = 1 To Len(s)
        CH = Mid(s, i, 1)
        If CH = "(" Then
            inParens = True
            q = ""
        ElseIf CH = ")" Then
            inParens = False
            temp = temp & " " & q
        Else
            If inParens Then q = q & _
               IIf(CH Like "[0-9]" Or CH = ".", CH, " ")
        End If
    Next i

    temp = Application.WorksheetFunction.Trim(temp)
    arr = Split(temp, " ")
    For Each a In arr
        If IsNumeric(a) Then Addum = Addum + CDbl(a)
    Next a
End Function

回答2:

Here is a late-bound regex udf.

Function sumNums(str As String) As Double
    Dim n As Long
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If

    sumNums = 0

    With rgx
        .Global = True
        .MultiLine = False
        .Pattern = "\(\d+(\.\d{1,2})?\)"
        If .Test(str) Then
            Set cmat = .Execute(str)
            'sum the matches
            For n = 0 To cmat.Count - 1
                sumNums = sumNums + Abs(CDbl(cmat.Item(n)))
            Next n
        End If
    End With
End Function

For an excel-vba-mac that has trouble with regex this is the simplest I could come up with.

Option Explicit

Public Function Addum(str As String) As Double
    Dim i As Long, j As Long, tmp As Variant

    tmp = Split(str, Chr(40))

    For i = LBound(tmp) + 1 To UBound(tmp)
        If IsNumeric(Split(tmp(i), Chr(41))(0)) Then _
            Addum = Application.Sum(Addum, Split(tmp(i), Chr(41))(0))
    Next i

End Function