修改VBA UDF：求和括号内数字并忽略文本，修复句号问题

Hey there! Let's tackle those two issues with your Addum function. The original code's replace-then-split approach can lead to errors when dealing with non-numeric content inside parentheses, and it fails to handle periods right after closing brackets. Here's a more robust solution:

Public Function Addum(rng As Range) As Double
    Dim inputStr As String
    Dim totalSum As Double
    Dim openParenPos As Integer, closeParenPos As Integer
    Dim innerContent As String
    
    totalSum = 0
    inputStr = rng.Value
    
    ' Find the first opening parenthesis
    openParenPos = InStr(inputStr, "(")
    
    Do While openParenPos > 0
        ' Find the matching closing parenthesis after the current opening one
        closeParenPos = InStr(openParenPos + 1, inputStr, ")")
        
        ' Exit loop if no closing parenthesis is found
        If closeParenPos = 0 Then Exit Do
        
        ' Extract the content inside the parentheses
        innerContent = Mid(inputStr, openParenPos + 1, closeParenPos - openParenPos - 1)
        
        ' Only add to sum if the inner content is a valid number
        If IsNumeric(innerContent) Then
            totalSum = totalSum + CDbl(innerContent)
        End If
        
        ' Move to the next opening parenthesis
        openParenPos = InStr(closeParenPos + 1, inputStr, "(")
    Loop
    
    Addum = totalSum
End Function

How This Fixes Your Issues: #

1. Ignoring non-numeric parentheses content: Instead of splitting the string into potentially invalid elements, we directly extract content between each pair of () and check if it's a valid number. If it's not (like words or mixed text/numbers), we simply skip it—no more #VALUE! errors.
2. Handling trailing periods after parentheses: By focusing solely on the content between ( and ), any characters (like periods) outside the parentheses don't affect the extraction. For example, (123). will correctly extract 123 and add it to the sum.

Test Cases to Verify: #

• Input: Hello (123) world (abc) test (45.6). → Returns 168.6 (sums 123 +45.6, ignores (abc))
• Input: (xyz) (789) → Returns 789 (ignores non-numeric (xyz))
• Input: (100)(200).end → Returns 300 (correctly captures both numbers despite the trailing period)

内容的提问来源于stack exchange，提问作者Hayden Shirts