[Solved] SpellNumber in Calc Basic

Keyboard macros or custom scripts
LaMancha
Posts: 3
Joined: Thu May 12, 2016 3:25 am

[Solved] SpellNumber in Calc Basic

I got a VBA from Excel which works well in Excel. I want to put it in Calc macro. The function works except the case where dollar is zero and or cents is zero. Macro does not recognize the case when dollar or cent is "". Below is the macro. Hope that someone can help

Code: Select all

``````REM  *****  BASIC  *****

'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 = Str(Dollars)
Case "One "
Dollars = "One Dollar "
Case Else
Dollars = Dollars & " Dollars "
End Select
If Dollars = "" Then
Select Case Cents
Case ""
Cents = ""
Case "One "
Cents = "One Cent Only"
Case Else
Cents = Cents & "Cents Only"
End Select
Else
Select Case Cents
Case ""
Cents = "Only"
Case "One "
Cents = "and One Cent Only"
Case Else
Cents = "and " & Cents & "Cents Only"
End Select
End If
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``````
Last edited by LaMancha on Fri May 13, 2016 10:37 am, edited 2 times in total.
OpenOffice 4.1 on Windows XP
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area
Contact:

Re: SpellNumber in Calc Basic

Use a typed variable when you only need a specific type of data.
Here, an empty variant is not equal to an empty string.
Replace the first declaration by:

Code: Select all

``Dim Dollars As String, Cents As String, Temp As String``
Instead of this naive and inefficient code, use function MONEYTEXT, provided by extension Numbertext.
See this thread : How to format a number to its written form
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
LaMancha
Posts: 3
Joined: Thu May 12, 2016 3:25 am

Re: SpellNumber in Calc Basic

Thank for your reply. Moneytext did work but it appears the word U.S. dollar rather than dollar only. The problem is just as stated for the variant is not empty string.
OpenOffice 4.1 on Windows XP
Villeroy
Volunteer
Posts: 30968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SpellNumber in Calc Basic

=SUBSTITUTE(MONEYTEXT(A1;"USD";"en-US");"U.S. ";"")
=NUMBERTEXT(A2;"en-US")&" dollars"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
LaMancha
Posts: 3
Joined: Thu May 12, 2016 3:25 am

[Solve] Re: SpellNumber in Calc Basic

The MoneyText function is solved.
OpenOffice 4.1 on Windows XP
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

I want enter spellcurr macro in calc basic
and also share code for macro number to text
OpenOffice 3.1 on Windows Vista
RoryOF
Moderator
Posts: 33995
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] SpellNumber in Calc Basic

As far as I know spellcurr is an Excel function which is not available in OpenOffice

This extension may help
Numbertext

If the code needs tweaking, you should be able to extract and debug that with little difficulty.
Apache OpenOffice 4.1.13 on Xubuntu 22.04.1 LTS
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

How i can apply numbertext function please complete process required
OpenOffice 3.1 on Windows Vista
Villeroy
Volunteer
Posts: 30968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] SpellNumber in Calc Basic

Install extension
Restart office
Use it like an ordinary spreadsheet function
=NUMBERTEXT(A1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Is Extention a seprate soft ware which i have to installed?
Please share how can i install it
OpenOffice 3.1 on Windows Vista
Hagar Delest
Moderator
Posts: 31372
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] SpellNumber in Calc Basic

Open OpenOffice and go to Tools>Extension manager
LibreOffice 7.4 on Xubuntu 22.10 and 7.4 portable on Windows 10
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

OpenOffice 3.1 on Windows Vista
robleyd
Moderator
Posts: 4561
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] SpellNumber in Calc Basic

Link provided in the seventh post in this topic; the text for the link is

Numbertext

on a separate line. please scroll up to find it.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.13
LibreOffice 7.4.4.2; SlackBuild for 7.4.4 by Eric Hameleers