Isnin, 5 September 2011

TUTORIAL EXCEL FORMULA: =WORDNUM()

This macro was written by Chris Mead - 
www.MeadInKent.co.uk 
→ tysm bro! ←

Sekadar berkongsi macam mana kita nak tukarkan sesuatu nombor kepada perkataan dalam MS Excel. Di bawah ni aku tunjukkan dalam bentuk foto supaya mudah difahami.. insya-Allah. Memang formula nak tukarkan nombor kepada perkataan tidak akan kita jumpa punya. Alhamdulillah, setelah bertahun ber"google" terjumpalah pulak code ni. Tapi code cuma tukar nombor kepada perkataan English je la ye.. kalau Bahasa Melayu dan bahasa² lain tu.. korang otek la sendiri.. hehe..

Kalau ada yg expert tu meh la tunjukkan kat aku macam mana nak buat. Pakat la share ngan aku dan sekelian orang lain. Ok.. beginilah flashback saya...


MS EXCEL 2007

1. Gi la open satu blank excel file.

Screenshot 1


2. Gi cari plak kat menu Developer > Visual Basic. Nak pantas tahap dewa gi tekan kat keyboard butang Alt + F11.

Screenshot 2


3. Pastu kuar la cm gini.. pastu follow je mcm dlm foto bawah ni..

Screenshot 3

3.1 Ni la rupa bentuk Visual Basic yg dah masukkan module baru.
Screenshot 3.1


4. Masukkan code ni kat page Module ni.

 Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub

Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
WordNum = "Value too large"
Exit Function
End If

SetNums

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")

If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
If Temp1 <> "" Then Temp2 = Temp2 & " and "
Else
Temp2 = ""
End If

If n = 3 Then
If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)

End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = " point"
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
Next n
WordNum = WordNum & Temp1
End If

If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
WordNum = "Zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
GetTens = Numbers(TensNum)
Else
Dim MyNo As String
MyNo = Format(TensNum, "00")
GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function

Screenshot 4


5. Pastu save code. (Nama fail ikut suka hati). Perhatikan kat "Save as type", pilih "Excel Macro-Enabled Workbook", pastu baru save.

Screenshot 5


6. Lepas save tutup Visual Basic dan mari kita test apa yg kita dah buat. Kat kolum A korang letaklah apa² nilai yg korang suka. Kat kolum B, dalam cell yg pertama (B1), taipkan formula ni dan enter sekali. Selebihnya refer je kat foto bawah ni. Harap jadi la ye.. kalau kat MS Excel 2003

=wordnum(A1)

Screenshot 6
Screenshot 6.1

Screenshot 6.2

Screenshot 6.3


Nota:
utk MS Excel 2003 step sama je, cuma bila masa nak save tu tak perlu usik "Save as type".


10 ulasan:

Tanpa Nama berkata...

best

padukakerol berkata...

kalo best pakat share boss.. haha

Tanpa Nama berkata...

KALAU NAK TUKAR NOMBOR2 TU KE BM, KORANG JUST TUKAR: "One", "Two", "Three", "Four", "Five", "Six", "Seven", TU KE BM

Tanpa Nama berkata...

TP AKU NK TANYA MCMN NK GABUNGKAN NO. DGN WORD. CONTOHNYA RM200,000.00 NAK JADIKAN RINGGIT MALAYSIA DUA RATUS RIBU SHJ

Tanpa Nama berkata...

DAH JUMPA...ALHAMDULILLAH..KORANG FORMULAKAN MCM NI ="RINGGIT MALAYSIA"&WORDNUM(A1)&"SAHAJA"

padukakerol berkata...

Yezza

padukakerol berkata...

Itu den tak tau boss. Ni pun jenuh google jumpa omputeh je.

padukakerol berkata...

Ensem.

Unknown berkata...

aslamualaikom maaf ye kalau saya mencelah...saya nak tanya macam mna pulak nk buat perkataan selepas point menjadi contoh macam ni one hundred point twenty two.
Harap ada sapa2 yg dpt mmbntu sya

Unknown berkata...

Option Explicit
Public Numbers As Variant, Tens As Variant

Sub SetNums()
Numbers = Array("", "Satu", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Lapan", "Sembilan", "Sepuluh", "Sebelas", "Dua Belas", "Tiga Belas", "Empat Belas", "Lima Belas", "Enam Belas", "Tujuh Belas", "Lapan Belas", "Sembilan Belas")
Tens = Array("", "", "Dua Puluh", "Tiga Puluh", "Empat Puluh", "Lima Puluh", "Enam Puluh", "Tujuh Puluh", "Lapan Puluh", "Sembilan Puluh")
End Sub

Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
WordNum = "Value too large"
Exit Function
End If

SetNums

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")

If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
Temp2 = Numbers(Val(Left(StrNo, 1))) & " Ratus"
If Temp1 <> "" Then Temp2 = Temp2 & " and "
Else
Temp2 = ""
End If

If n = 3 Then
If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " Ribu " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " Juta " & WordNum)

End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
Temp1 = " point"
For n = DecimalPosition + 1 To Len(NumStr)
Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
Next n
WordNum = WordNum & Temp1
End If

If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
WordNum = "Zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
GetTens = Numbers(TensNum)
Else
Dim MyNo As String
MyNo = Format(TensNum, "00")
GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function

Senarai Lain² Blog/Web