Create the GBP function
to convert numbers to words (British Pound)
1. Use the keyboard
shortcut, Alt + F11 to open the Visual Basic
Editor (VBE).
2. Click the Insert tab, and click Module.
3. Copy the following
lines of code.
Option Explicit
'Main Function
Function GBP(ByVal
MyNumber)
Dim
Pounds, Pence, Temp
Dim
DecimalPlace, Count
ReDim
Place(9) As String
Place(2)
= " Thousand "
Place(3)
= " Million "
Place(4) = " Billion "
Place(5)
= " Trillion "
MyNumber
= Trim(Str(MyNumber))
DecimalPlace
= InStr(MyNumber, ".")
If
DecimalPlace > 0 Then
Pence
= 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 Pounds = Temp & Place(Count) & Pounds
If
Len(MyNumber) > 3 Then
MyNumber
= Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber
= ""
End
If
Count
= Count + 1
Loop
Select
Case Pounds
Case
""
Pounds
= "Zero Pound"
Case
"One"
Pounds
= "One Pound"
Case
Else
Pounds
= Pounds & " Pounds"
End
Select
Select
Case Pence
Case
""
Pence
= ""
Case
"One"
Pence
= " and One Penny"
Case
Else
Pence
= " and " & Pence & " Pence"
End
Select
GBP
= Pounds & Pence & " 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
4. Paste the lines of code into the Module1 (Code) box.
5. Press Alt +
Q to return to Excel. The GBP function is now ready
to use.
Use the GBP function
in individual cells
1. Type the formula =GBP(A1)
into the cell where you want to display a written number, where A1 is the cell
containing the number you want to convert. You can also manually type the
value like =GBP(22.50).
2. Press Enter to
confirm the formula.
Save your GBP function
workbook
Excel cannot save a
workbook with macro functions in the standard macro-free workbook format
(.xlsx). If you click File > Save. A VB
project dialog box opens. Click No.
You can save your file
as an Excel Macro-Enabled Workbook (.xlsm) to keep your file
in its current format.
1. Click File > Save
As.
2. Click the Save
as type drop-down menu, and select Excel Macro-Enabled
Workbook.
3. Click Save.
0 Comments