Archive

Posts Tagged ‘vba’

VBA function MoneyFromNote

February 10th, 2010 No comments

Function MoneyFromNote(ByVal note As String)
    
    Dim RE As RegExp, REMatches As MatchCollection, REMatch As Match
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "([\+0-9\.,]+)([MK])"
    End With
    
    Set REMatches = RE.Execute(note)
    r = 0
    For i = 0 To REMatches.Count – 1
        Set REMatch = REMatches.Item(i)
        unit = REMatch.SubMatches(1)
        num = REMatch.SubMatches(0)
        
        num = Replace(num, ",", ".")
        If unit = "M" Then
            va = num * 1000000
        ElseIf unit = "K" Then
            va = num * 1000
        End If
        If Mid(num, 1, 1) = "+" Then va = 0 – va
        'MsgBox num & " " & unit & " = " & va
        r = r – va
    Next
    'MsgBox r
    MoneyFromNote = Int(r)
End Function

‘Usage example
‘=MoneyFromNote(“Quần đùi: 30K; Áo ba lỗ: 25K; Thịt ba chỉ: 15K”)
‘Sẽ thu được: -70000

Minh họa hàm MoneyFromNote trong Excel

Categories: Chỉ dẫn lập trình Tags: ,