vba自定义功能函数

财务金额小写转大写：

1 先在vbe里新建模块，并写入代码：

Function xxzdx(M)
y = Int(Round(100 * Abs(M)) / 100)
j = Round(100 * Abs(M) + 0.00001) - y * 100
f = Round((j / 10 - Int(j / 10)) * 10)
A = IIf(y < 1, "", Application.Text(y, "[DBNum2]") & "元")
b = IIf(j > 9.4, Application.Text(Int(j / 10), "[DBNum2]") & "角", IIf(y < 1, "", IIf(f > 0.4, "零", "")))
c = IIf(f < 1, "整", Application.Text(Round(f, 0), "[DBNum2]") & "分")
ldy888 = IIf(Abs(M) < 0.005, "", IIf(M < 0, "负" & A & b & c, A & b & c))
End Function

然后保存再将打开excel，在某单元格内输入：

=ldy888(1111)，或者：=ldy888(a1)

 

另一写法：

 ' 试编写数字金额转中文大写的函数
 ' lin jin xiang 08-07-2004
 ' 重编辑 23-01-2005

Function DaXie(ByVal Num)       ' 人民币中文大写函数
    Application.Volatile True
    Place = "分角元拾佰仟万拾佰仟亿拾佰仟万"
    Dn = "壹贰叁肆伍陆柒捌玖"
    D1 = "整零元零零零万零零零亿零零零万"
    If Num < 0 Then FuHao = "（负）"
    Num = Format(Abs(Num), "###0.00") * 100
    If Num > 999999999999999# Then: DaXie = "数字超出转换范围!!": Exit Function
    If Num = 0 Then: DaXie = "零元零分": Exit Function
    NumA = Trim(Str(Num))
    NumLen = Len(NumA)
    For J = NumLen To 1 Step -1     ' 数字转换过程
      Temp = Val(Mid(NumA, NumLen - J + 1, 1))
      If Temp <> 0 Then             ' 非零数字转换
         NumC = NumC & Mid(Dn, Temp, 1) & Mid(Place, J, 1)
      Else                          ' 数字零的转换
         If Right(NumC, 1) <> "零" Then
           NumC = NumC & Mid(D1, J, 1)
         Else
           Select Case J            ' 特殊数位转换
                Case 1
                  NumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1)
                Case 3, 11
                  NumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & "零"
                Case 7
                  If Mid(NumC, Len(NumC) - 1, 1) <> "亿" Then
                     NumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & "零"
                  End If
                Case Else
           End Select
         End If
      End If
    Next
    DaXie = FuHao & Trim(NumC)
End Function

 

转成英文：

'此过程为西人所写，谨推荐给大家.
 
 
 '****************' Main Function *'****************
Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Application.Volatile True
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "     ' String representation of amount
    MyNumber = Trim(Str(MyNumber))     ' Position of decimal place 0 if none
    DecimalPlace = InStr(MyNumber, ".")
    'Convert cents and set MyNumber to dollar amount
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
        End If
    Count = 1
    Do While MyNumber <> ""
       Temp = GetHundreds(Right(MyNumber, 3))
       If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
          If Len(MyNumber) > 3 Then
             MyNumber = Left(MyNumber, Len(MyNumber) - 3)
             Else
            MyNumber = ""
            End If
            Count = Count + 1
            Loop
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
        Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = " and No Cents"
        Case "One"
            Cents = " and One Cent"
        Case Else
            Cents = " and " & Cents & " Cents"
    End Select
    SpellNumber = Dollars & Cents
    End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)     'Convert the hundreds place
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
        End If
    'Convert the tens and ones place
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
        Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
        End If
    GetHundreds = Result
    End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
    Dim Result As String
    Result = ""           'null out the temporary function value
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19
        Select Case Val(TensText)
        Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
            End Select
      Else                                 ' If value between 20-99
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
         Result = Result & GetDigit _
            (Right(TensText, 1))  'Retrieve ones place
            End If
      GetTens = Result
      End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

 

函数调用：

可以变通一下，函数属性仍然为Public，但是代码放在工作表对象中，这样在工作表中插入时就看不到这个函数，但是其它模块仍然可以调用
'---------------------------------------------------------Sheet1---------------------------------------------------------

Function aa(a As Integer) As Integer
aa = a + 1
End Function
'--------------------------------------------------------Module1--------------------------------------------------------
Sub macro()
MsgBox Sheet1.aa(5)
End Sub

 

 

实现,"四舍六入五单双",即,如舍去位数为5时,要看它前面的数是双数还是单数,是双数时舍去,是单数时进位.

如:5.38舍入后为5.4

5.34舍入后为5.3

5.35舍入后为 5.4, 而5.45舍入后为 5.4

Function myRound(ByVal num As Single, ByVal dec As Integer) As Single With Application.WorksheetFunction num = num * .Power(10, dec) num1 = .RoundDown(num, 0) num2 = num - num1 If num2 > 0.5 Or (num2 = 0.5 And Abs(num1 / 2 - Round(num1 / 2, 0)) > 0.00001) Then num1 = num1 + 1 num1 = num1 / .Power(10, dec) End With myRound = num1 End Function

 

 

繁体与简体互相转换的代码
Private Declare Function LCMapString Lib "kernel32" Alias "LCMapStringA" (ByVal Locale As Long, _
ByVal dwMapFlags As Long, ByVal lpSrcStr As String, ByVal cchSrc As Long, ByVal lpDestStr As String, ByVal cchDest As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As String) As Long
Dim STf As String    '繁体字符串
Dim STj As String    ' 简体字符串
Dim STlen As Long    ' 待转换字串长度

Sub Test1()    'Gb码简体转繁体
    STj = "中华人民共和国"
    STlen = lstrlen(STj)
    STf = Space(STlen)
    LCMapString &H804, &H4000000, STj, STlen, STf, STlen
    Debug.Print STf
End Sub

Sub Test2()    'Gb码繁体转简体
    STf = "中華人民共和國"
    STlen = lstrlen(STf)
    STj = Space(STlen)
    LCMapString &H804, &H2000000, STf, STlen, STj, STlen
    Debug.Print STj
End Sub