VBS 数字转英文代码

如何创建名为 spellnumber 的示例函数
启动 microsoft excel。
按 alt+f11 启动 visual basic 编辑器。
在“插入”菜单上，单击“模块”。
在模块表中键入下面的代码。

option explicit
'main function
function spellnumber(byval mynumber)
dim dollars, cents, temp
dim decimalplace, count
redim place(9) as string
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

如何使用 spellnumber 示例函数
要使用该示例函数将某数字更改为书面文本，请使用下列示例中演示的方法之一：
回到顶端
方法 1：直接输入
通过将下面的公式输入单元格中，可以将 32.50 更改为“thirty two dollars and fifty cents”：
=spellnumber(32.50)
回到顶端
方法 2：单元格引用
可以引用工作簿中的其他单元格。例如，在单元格 a1 中输入数字 32.50，然后在另一单元格中键入下面的公式：
=spellnumber(a1)
回到顶端
方法 3：粘贴函数或插入函数
可以使用“粘贴函数”（在 excel 2000 和 excel 2002 中）或“插入函数”（在 excel 2003 中）来将自定义函数输入工作表中。