興趣嗜好 今日: 3 | 主題: 124515

積分: 1530

1^#

發表於 09-4-27 15:05 |只看該作者 |正序瀏覽 |打印

如題,如係B2輸入HKD1200.25,點樣係A2自動變成:HONG KONG DOLLARS ONE THOUSAND TWO HUNDRED AND CENTS TWENTY FIVE ONLY. 搵食艰難啊,請幫下忙,唔該!

0 0 0

私人傳訊

積分: 1366

28^#

發表於 09-5-1 00:15 |只看該作者

嘩....似乎好多人都好熱衷呢個Topic咁喎！

好啦，我就將我教樓主修改版嘅巨集Post出黎同大家分享。

另，goofyszesze要出嘅字串
可以寫成：="Say Hong Kong Dollars "&SpellNumber(A1)　當然，A1格一定要有數字。

再另外多謝spmok1999大哥幫忙答其他有需要嘅網友。

（好似講到我自己好威咁，哈哈！請勿介意）

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
If Cents <> "" Then
Cents = " and " & IIf(Cents = "One", "Cent ", "Cents ") & Trim(Cents)
End If

SpellNumber = Dollars & Cents & " Only"

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

' Retrieve ones place.
Result = Result & GetDigit(Right(TensText, 1))
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

私人傳訊

goofyszesze

子爵府

積分: 13590

27^#

發表於 09-4-30 22:33 |只看該作者

spmok1999

唔該哂你

私人傳訊

積分: 5436

26^#

發表於 09-4-30 21:02 |只看該作者

原帖由 goofyszesze 於 09-4-30 20:51 發表
我知道....
因為之前既資料copy到好亂，我想小必理痛清楚我想出咩英文字。所以寫得詳細d‧

佢依家離線喎, 咁你等唔等佢丫?

如果唔等可以試下我呢個版本:

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 & ""
End Select
Select Case Cents
Case ""
Cents = ""
Case "One"
Cents = " and Cent One "
Case Else
Cents = " and" & " Cents " & Cents
End Select
SpellNumber = "Hong Kong Dollars " & Dollars & Cents & " Only"
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

呢樣唔得, o個樣唔得, 呢樣唔准, o個樣唔准......牛耕田,馬食穀,老豆賺錢女享福。

私人傳訊

goofyszesze

子爵府

積分: 13590

25^#

發表於 09-4-30 20:51 |只看該作者

我知道....
因為之前既資料copy到好亂，我想小必理痛清楚我想出咩英文字。所以寫得詳細d‧

原帖由 spmok1999 於 09-4-30 20:47 發表

數字係要自己打入去o架喎!

私人傳訊

積分: 5436

24^#

發表於 09-4-30 20:47 |只看該作者

原帖由 goofyszesze 於 09-4-30 16:45 發表
小必理痛

我有D亂可唔可以完整一個程式比我COPY?

123456.78 轉成

SAY HONG KONG DOLLARS ONE HUDNRED TWENTY THREE THOUAND FOUR HUNDRED FIFTY SIX AND CENTS SEVENTY EIGHT ONLY

唔該哂 ...

數字係要自己打入去o架喎!

呢樣唔得, o個樣唔得, 呢樣唔准, o個樣唔准......牛耕田,馬食穀,老豆賺錢女享福。

私人傳訊

goofyszesze

子爵府

積分: 13590

23^#

發表於 09-4-30 16:45 |只看該作者

小必理痛

我有D亂可唔可以完整一個程式比我COPY?

123456.78 轉成

SAY HONG KONG DOLLARS ONE HUDNRED TWENTY THREE THOUAND FOUR HUNDRED FIFTY SIX AND CENTS SEVENTY EIGHT ONLY

唔該哂

私人傳訊

積分: 1366

22^#

發表於 09-4-30 14:06 |只看該作者

原帖由 kittymam 於 09-4-30 13:45 發表
搞掂咗了,真係好開心有咁多好好的人幫我呢種電腦白痴.

好多謝多謝啊!

所以話世界係美好,咁多好人熱心幫人!

小必理痛你的意思係埋話如果我係一個file度設著個巨集,無得開出來改返,要重新開個再設個?但我之前因為 ...

唔係，我嘅意思係話如果你係某一個Excel file裡面設左巨集，咁就只限於呢個file使用，如果去到另一個Excel file，你想再用呢個巨集嘅話，就唔得囉，因為另一個Excel file係冇呢個巨集架嘛！除非你識得將巨集抄過去啦。

但其實Excel嘅巨集係可以存放o係一個叫做「個人巨集活頁簿」裡面，作為Excel嘅常駐程式使用，我就係咁樣做。

公司每一日都會有固定嘅report以Excel嘅形式生產出黎，但老闆們要將佢地summarize，我冇理由日日都要重複咁做幾廿個動作先可以做到佢地要嘅野，亦更加冇理由下下都要重寫巨集去執行呢Ｄ煩複嘅工作架！所以我就將寫好嘅巨集放o係「個人巨集活頁簿」裡面，隨時都可以用。

私人傳訊

積分: 1530

21^#

發表於 09-4-30 13:45 |只看該作者

搞掂咗了,真係好開心有咁多好好的人幫我呢種電腦白痴.

好多謝多謝啊!

所以話世界係美好,咁多好人熱心幫人!

小必理痛你的意思係埋話如果我係一個file度設著個巨集,無得開出來改返,要重新開個再設個?但我之前因為設錯咗(都係我已較好位置各個開支票file),我一入speelnumber(a1)佢就自動彈出來話我輸入錯誤,跟住彈出我之前設各個巨集,點解會咁啊?

唔該晒!

原帖由 小必理痛 於 09-4-30 12:54 發表
唔好咁講，咁岩得咁橋出正一Ｄ我有興趣嘅問題，咪大家一齊玩一齊研究下囉！呵呵！

私人傳訊

積分: 1366

20^#

發表於 09-4-30 12:54 |只看該作者

唔好咁講，咁岩得咁橋出正一Ｄ我有興趣嘅問題，咪大家一齊玩一齊研究下囉！呵呵！

私人傳訊

積分: 5436

19^#

發表於 09-4-30 12:47 |只看該作者

原帖由 小必理痛 於 09-4-30 12:27 發表

樓主係話唔要出Dollar(s)呢個字眼，開頭我都以為連Cent(s)呢個字眼都唔要，但原來係要將Cents呢個字眼放係前面，最後要有Only呢個字眼。

我冇睇清楚，先至搞到要一改再改。 ...

其實我咩都唔識o架, 咁我見大家咁熱心咪參與埋一份o羅, 我都係睇下網頁參考下,抄下, 改下再試下, 其實可能仲有未知的問題呢? 真係多謝各位師兄, 其實仲有好多o野要向各師兄學習(偷師)的!

呢樣唔得, o個樣唔得, 呢樣唔准, o個樣唔准......牛耕田,馬食穀,老豆賺錢女享福。

私人傳訊

積分: 1366

18^#

發表於 09-4-30 12:27 |只看該作者

原帖由 spmok1999 於 09-4-30 12:01 發表

按樓主要求的話, 係咪多o左呢個字呢? (紅色 hightlight 部份)
同埋 & Cents & " Cents" 係咪應該調一調個次序呢? (紅色 hightlight 部份)

*我個人覺得將官方網頁的原版修改少少會較容易呢!
...

樓主係話唔要出Dollar(s)呢個字眼，開頭我都以為連Cent(s)呢個字眼都唔要，但原來係要將Cents呢個字眼放係前面，最後要有Only呢個字眼。

我冇睇清楚，先至搞到要一改再改。

私人傳訊

積分: 5436

17^#

發表於 09-4-30 12:01 |只看該作者

原帖由 小必理痛 於 09-4-30 11:46 發表
我發覺原來我都係一個幾差嘅人，我冇睇清楚你嘅requirement，真係抱歉！

再黎：

將原先最開初話有No Cent個段，即係如下：
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

改成如下：
If Cents <> "" Then
Cents = " and " & IIf(Cents　= "One", "Cent ", "Cents ") & Trim(Cents)
End If
SpellNumber = Dollars & Cents & " Only"

按樓主要求的話, 係咪多o左呢個字呢? (紅色 hightlight 部份)
同埋 & Cents & " Cents" 係咪應該調一調個次序呢? (紅色 hightlight 部份)
仲有 " and One Cent" 改成 " and Cent One"

*我個人覺得將官方網頁的原版修改少少會較容易呢!

[ 本帖最後由 spmok1999 於 09-4-30 12:38 編輯 ]

呢樣唔得, o個樣唔得, 呢樣唔准, o個樣唔准......牛耕田,馬食穀,老豆賺錢女享福。

私人傳訊

積分: 1366

16^#

發表於 09-4-30 11:46 |只看該作者

我發覺原來我都係一個幾差嘅人，我冇睇清楚你嘅requirement，真係抱歉！

再黎：

將原先最開初話有No Cent個段，即係如下：
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

改成如下：
If Cents <> "" Then
Cents = " and " & IIf(Cents　= "One", "Cent ", "Cents ") & Trim(Cents)
End If
SpellNumber = Dollars & Cents & " Only"

測試過，呢趟應該冇問題啦卦？

私人傳訊

積分: 1530

15^#

發表於 09-4-30 11:11 |只看該作者

多謝晒啊!

唔好意思,但宜家又變成:如125.05出來係one hundred twenty five and five only. (無cents ),點算?

而我係話我原本個file設好晒d位,又編咗模組,宜家點去改各個模組啊?

唔好意思啊,人蠢無辦法,麻煩晒大家!多謝晒!

原帖由 小必理痛 於 09-4-30 10:43 發表
噢，我明白了，我講漏左少少。

你搵番我昨日叫你修改嘅語句，佢應該係o係一句叫End Function嘅樓上。

而呢句End Function對上有一句係咁：
SpellNumber = Dollars & Cents

將呢一句刪除，因為我教你修改嘅語句，其實已經做左 ...

私人傳訊

積分: 5436

14^#

發表於 09-4-30 10:54 |只看該作者

原帖由 kittymam 於 09-4-30 09:59 發表
小必理痛:

多謝晒,但又有嘢請教啊,唔該!

唔好意思,我真係好白痴,我唔知去邊度開返舊有的模組來改,要點架?

我唔識唯有另開一個新的excel來設巨集,但copy你設的去改,當我打15.05佢出"fifteenfive",咁又唔啱了,點算? ...

copy 原本的貼上後將原本的...

Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select

...改成...以下

Select Case Cents
Case ""
Cents = ""
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and" & " Cents " & Cents
End Select

以下是全部要改的:

Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & ""
End Select
Select Case Cents
Case ""
Cents = ""
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and" & " Cents " & Cents
End Select
SpellNumber = Dollars & Cents & " Only"
End Function
_____________________________________

*如果之前要加 "Hong Kong Dollars" 的話 last 一句要改成咁"
End Select
SpellNumber = "Hong Kong Dollars " & Dollars & Cents & " Only"
End Function

[ 本帖最後由 spmok1999 於 09-4-30 11:34 編輯 ]

呢樣唔得, o個樣唔得, 呢樣唔准, o個樣唔准......牛耕田,馬食穀,老豆賺錢女享福。

私人傳訊

積分: 1366

13^#

發表於 09-4-30 10:43 |只看該作者

噢，我明白了，我講漏左少少。

你搵番我昨日叫你修改嘅語句，佢應該係o係一句叫End Function嘅樓上。

而呢句End Function對上有一句係咁：
SpellNumber = Dollars & Cents

將呢一句刪除，因為我教你修改嘅語句，其實已經做左你要嘅野，但當佢行呢句SpellNumber = Dollars & Cents嘅時候，會再修改你嘅答案，岩變成唔岩，所以必須要刪除。

再試試。

P.S. 仲有，你所寫嘅巨集係包含o係你嘅Excel檔案裡面，即係話，如果你有第二個Excel檔案都係要執行呢個SpellNumber巨集，咁你就要重新跟我教你嘅step去做先得。不過Excel本身亦有個人巨集活頁簿，可以將寫左嘅巨集搬去該處當公共程式執行，但就只限o係你嘅Excel檔案上出答案，就唔可以好似你寫一句：=SpellNumber(A1)　咁嘅樣架啦。

[ 本帖最後由小必理痛於 09-4-30 10:53 編輯 ]

私人傳訊

積分: 1530

12^#

發表於 09-4-30 09:59 |只看該作者

小必理痛:

多謝晒,但又有嘢請教啊,唔該!

唔好意思,我真係好白痴,我唔知去邊度開返舊有的模組來改,要點架?

我唔識唯有另開一個新的excel來設巨集,但copy你設的去改,當我打15.05佢出"fifteenfive",咁又唔啱了,點算?
當我打150.50佢又出ONE HUNDRED FIFTY FIFTY,啱樣又唔啱,點算啊?而且有無辦法係所有字後面有only.

其實我係要將金額變大寫:115.05變為 ONE HUNDRED FIFTEEN AND CENTS FIVE ONLY.
因為開支票用,要點改個程式啊?

唔該晒!

原帖由 小必理痛 於 09-4-29 18:08 發表
咁就要修改程式啦。

1. 入番去Visual Basic 編輯器。

2. 大約去到第三十幾行左右，搵呢句：Select Case Dollars。佢個段程式句係咁：

Select Case Dollars
Case ""
Dollars = " No Dollars"
Case "One"
Do ...

私人傳訊

積分: 1366

11^#

發表於 09-4-29 18:08 |只看該作者

咁就要修改程式啦。

1. 入番去Visual Basic 編輯器。

2. 大約去到第三十幾行左右，搵呢句：Select Case Dollars。佢個段程式句係咁：

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

3. 將上述咁多行嘅文字改成如下：

If Dollars <> "" Or Cents <> "" Then
SpellNumber = Dollars & iif(Cents <> "", " and ", "") & Cents & " Only"
End If

咁就做到你想要嘅野，唔使怕寫錯呀，寫錯電腦會提你，只要你改番就得。

私人傳訊