|
楼主 |
发表于 2019-1-21 22:36
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
最近新编了一段金额大写的代码(心得而已,个人想法),使用多种场合
带原始数据\带数字小写\带原始计算式\原始数据放到方括号内
各位大佬看看有修改的么
- Function 大写(Y, Optional 格式 As String = 5) ' 金额大写
- If Y = "" Then 大写 = "": Exit Function
- C = Y: C = 去字符(C, "方括号"): C = 去字符(C, "空格"): C = 去字符(C, "中文"): C = 去字符(C, "字母"): Y = C
- On Error Resume Next '忽略代码错误
- Dim x As Currency
- x = 计算(Y, 2)
- With Application.WorksheetFunction
- '-----------------------------------------------------------------
- ' 格式形式
- Select Case 格式
- Case 0
- x = .Round(x, 0)
- 前缀 = .Text(x, "0.00元(大写:")
- 后缀 = ")"
- Case 10
- x = Round(x, 0)
- 后缀 = .Text(x, """[""0.00元]")
- Case 100
- x = Round(x, 0)
- A = .IsError(Y * 1)
- Select Case A
- Case ""
- 前缀 = Y & "=" & .Text(x, "0.00元(大写:")
- Case Else
- 前缀 = .Text(x, "0.00元(大写:")
- End Select
- 后缀 = ")"
- Case 110
- x = Round(x, 0)
- A = .IsError(Y * 1)
- Select Case A
- Case ""
- 后缀 = "[" & Y & "=" & .Text(x, "0.00元]")
- Case Else
- 后缀 = .Text(x, """[""0.00元]")
- End Select
- Case 1
- 前缀 = .Text(x, "0.00元(大写:")
- 后缀 = ")"
- Case 11
- 后缀 = .Text(x, """[""0.00元]")
- Case 101
- A = .IsError(Y * 1)
- Select Case A
- Case ""
- 前缀 = Y & "=" & .Text(x, "0.00元(大写:")
- Case Else
- 前缀 = .Text(x, "0.00元(大写:")
- End Select
- 后缀 = ")"
- Case 111
- A = .IsError(Y * 1)
- Select Case A
- Case ""
- 后缀 = "[" & Y & "=" & .Text(x, "0.00元]")
- Case Else
- 后缀 = .Text(x, """[""0.00元]")
- End Select
- Case 2
- A = .IsError(Y * 1)
- Select Case A
- Case ""
- 前缀 = "[" & Y & "=" & .Text(x, "0.00元](大写:")
- Case Else
- 前缀 = .Text(x, """[""0.00元](大写:")
- End Select
- 后缀 = ")"
- End Select
- '-----------------------------------------------------------------
- ' 空值情况
- If x = 0 Then 大写 = 前缀 & "零元" & 后缀: Exit Function
- '-----------------------------------------------------------------
- ' 判断正负数
- If x < 0 Then 正负 = "负"
- B = Abs(x)
- '-----------------------------------------------------------------
- ' 零元表达
- If x = 0 Then 大写 = 前缀 & "零元" & 后缀: Exit Function
- '-----------------------------------------------------------------
- ' 整数表达
- If Int(x) = x Then 大写 = 前缀 & 正负 & .Text(Int(B), "[DBNUM2]") & "元整" & 后缀: Exit Function
- '-----------------------------------------------------------------
- ' 小数表达
- 大写 = 前缀 & 正负 _
- & .Text(Int(B), "[DBNUM2]") & "元" _
- & .Text(Left(Right(B * 100, 2), 1), "[DBNUM2]") & "角" _
- & .Text(Right(B * 100, 1), "[DBNUM2]") & "分" _
- & 后缀
- End With
- End Function
复制代码
|
|