## [Solved] SpellNumber in Calc Basic

Keyboard macros or custom scripts

### [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   Expand viewCollapse view
`REM  *****  BASIC  *****'Main FunctionFunction 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 & CentsEnd Function      ' Converts a number from 100-999 into textFunction 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 = ResultEnd 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 = ResultEnd 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 SelectEnd Function`
Last edited by LaMancha on Fri May 13, 2016 10:37 am, edited 2 times in total.
OpenOffice 4.1 on Windows XP
LaMancha

Posts: 3
Joined: Thu May 12, 2016 3:25 am

### 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   Expand viewCollapse view
`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
B Marcelly
Volunteer

Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

### 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
LaMancha

Posts: 3
Joined: Thu May 12, 2016 3:25 am

### 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, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26974
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### [Solve] Re: SpellNumber in Calc Basic

The MoneyText function is solved.
OpenOffice 4.1 on Windows XP
LaMancha

Posts: 3
Joined: Thu May 12, 2016 3:25 am

### 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
akhter28

Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

### 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.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 29260
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: [Solved] SpellNumber in Calc Basic

How i can apply numbertext function please complete process required
OpenOffice 3.1 on Windows Vista
akhter28

Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

### 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, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26974
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### 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
akhter28

Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

### Re: [Solved] SpellNumber in Calc Basic

Open OpenOffice and go to Tools>Extension manager
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).

Off-line until Aug. 31.

Hagar Delest
Moderator

Posts: 28529
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: [Solved] SpellNumber in Calc Basic

OpenOffice 3.1 on Windows Vista
akhter28

Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

### 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
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2865
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia