楼主
kevinchengcw |
Q: 如何自定义将中文大写数值转换为阿拉伯数值的函数？
A: 自定义中文大写数值转换为阿拉伯数值的函数代码如下：- Function CtoA(ByVal Rng)
- Dim Arr, Arr2, Dic
- Dim Str As String
- Dim M, N, I As Long
- Set Dic = CreateObject("scripting.dictionary") '创建字典用于存储转换值
- Dic.Add "千", "*1000+"
- Dic.Add "仟", "*1000+"
- Dic.Add "百", "*100+"
- Dic.Add "佰", "*100+"
- Dic.Add "十", "*10+"
- Dic.Add "拾", "*10+"
- Dic.Add "九", "9"
- Dic.Add "玖", "9"
- Dic.Add "八", "8"
- Dic.Add "捌", "8"
- Dic.Add "七", "7"
- Dic.Add "柒", "7"
- Dic.Add "六", "6"
- Dic.Add "陆", "6"
- Dic.Add "五", "5"
- Dic.Add "伍", "5"
- Dic.Add "四", "4"
- Dic.Add "肆", "4"
- Dic.Add "三", "3"
- Dic.Add "叁", "3"
- Dic.Add "二", "2"
- Dic.Add "贰", "2"
- Dic.Add "一", "1"
- Dic.Add "壹", "1"
- Arr = Dic.keys '将字典的keys赋值给数组，便于取用
- If Rng <> "" Then
- Str = Rng
- Str = Replace(Str, "亿", vbTab) '将万和亿替换成分隔符便于分开计算
- Str = Replace(Str, "万", vbTab)
- For M = LBound(Arr) To UBound(Arr) '循环替换对应的大写数字为对应阿拉伯数字
- Str = Replace(Str, Arr(M), Dic(Arr(M)))
- Next M
- Str = Replace(Str, "零", "") '替换掉多余的字符
- Str = Replace(Str, "+*", "*")
- Arr2 = Split(Str, vbTab) '拆分字符串为数组，方便各数量级的数字累加
- I = 0
- For M = LBound(Arr2) To UBound(Arr2)
- If Right(Arr2(M), 1) = "+" Then Arr2(M) = Left(Arr2(M), Len(Arr2(M)) - 1) '替换两种可能存在的情况
- If Left(Arr2(M), 1) = "*" Then Arr2(M) = Right(Arr2(M), Len(Arr2(M)) - 1)
- If Arr2(M) = "" Then
- If M < UBound(Arr2) Then I = I + 10000 ^ (UBound(Arr2) - M)
- Else
- If InStr(Arr2(M), "*") > 0 Or InStr(Arr2(M), "+") > 0 Then
- I = I + Evaluate(Arr2(M)) * 10000 ^ (UBound(Arr2) - M)
- Else
- I = I + Arr2(M) * 10000 ^ (UBound(Arr2) - M)
- End If
- End If
- Next M
- CtoA = I '返回结果
- End If
- Application.Volatile '设置为易失性函数，可以即时更新
- Set Dic = Nothing '清空项目
- End Function
附示例文件。
 中文大写数值变阿拉伯数值.rar
|