CODEWITHSUNDEEP
excelvba
Kevin P.
Kevin P.

Reputation: 1053

Ignore string prefixed with special character

I have a function that will add up all dollar amounts within an Excel comment box. However I have some notes written in the comment box that causes an error due to the fact that it does not start with $XX.xx is there a way to either ignore entire strings (separated by enter) or possibly make a "comment out string" special character? For example if I start a string with ; then ignore all text after that util the next line?

Here are my current functions:

Function CleanString(strIn As String) As String
Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "[^0-9" & Application.DecimalSeparator & "]"
        CleanString = .Replace(strIn, vbCrLf)
    End With
End Function

Function commentSum(cmt As Comment) As Double
Dim vDat As Variant
Dim i As Long
Dim res As Double

    vDat = Split(CleanString(cmt.Text), vbCrLf)
    For i = LBound(vDat) To UBound(vDat)
        If Len(vDat(i)) > 0 Then
            res = res + CDbl(vDat(i))
        End If
    Next i
    commentSum = res
End Function

Upvotes: 0

Views: 35

Answers (1)

Mikku
Mikku

Reputation: 6654

Replace:

If Len(vDat(i)) > 0 Then

With:

If Len(vDat(i)) > 0 And Not Left(vDat(i), 1) = ";" Then

then it will ignore any line starting with ;

Upvotes: 1

Related Questions