[Solved] SpellNumber in Calc Basic

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
LaMancha
Posts: 3
Joined: Thu May 12, 2016 3:25 am

[Solved] SpellNumber in Calc Basic

Post by LaMancha »

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

Re: SpellNumber in Calc Basic

Post by B Marcelly »

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

Post by LaMancha »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SpellNumber in Calc Basic

Post by Villeroy »

:roll:

=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

Post by LaMancha »

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

Post by akhter28 »

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

Re: [Solved] SpellNumber in Calc Basic

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Post by akhter28 »

How i can apply numbertext function please complete process required
please email me at akhterali28@yahoo.com
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] SpellNumber in Calc Basic

Post by Villeroy »

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

Post by akhter28 »

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

Re: [Solved] SpellNumber in Calc Basic

Post by Hagar Delest »

Download the extension (see link provided above).
Open OpenOffice and go to Tools>Extension manager
Click the Add button
Select the extension you've downloaded.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
akhter28
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Post by akhter28 »

Link not provided
OpenOffice 3.1 on Windows Vista
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] SpellNumber in Calc Basic

Post by robleyd »

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.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply