[Solved] SpellNumber in Calc Basic

Keyboard macros or custom scripts

[Solved] SpellNumber in Calc Basic

Postby LaMancha » Thu May 12, 2016 3:31 am

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 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
LaMancha
 
Posts: 3
Joined: Thu May 12, 2016 3:25 am

Re: SpellNumber in Calc Basic

Postby B Marcelly » Thu May 12, 2016 9:32 am

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

Postby LaMancha » Thu May 12, 2016 10:06 am

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

Postby Villeroy » Thu May 12, 2016 10:44 am

: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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26626
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Solve] Re: SpellNumber in Calc Basic

Postby LaMancha » Fri May 13, 2016 10:18 am

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

Postby akhter28 » Thu Jan 18, 2018 7:31 pm

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
akhter28
 
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Postby RoryOF » Fri Jan 19, 2018 7:47 pm

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
User avatar
RoryOF
Moderator
 
Posts: 28742
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] SpellNumber in Calc Basic

Postby akhter28 » Sun Jan 21, 2018 4:20 pm

How i can apply numbertext function please complete process required
please email me at akhterali28@yahoo.com
OpenOffice 3.1 on Windows Vista
akhter28
 
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Postby Villeroy » Sun Jan 21, 2018 4:34 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26626
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] SpellNumber in Calc Basic

Postby akhter28 » Tue Jan 23, 2018 5:23 am

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

Postby Hagar Delest » Tue Jan 23, 2018 1:04 pm

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.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28458
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] SpellNumber in Calc Basic

Postby akhter28 » Thu Jan 25, 2018 4:53 am

Link not provided
OpenOffice 3.1 on Windows Vista
akhter28
 
Posts: 5
Joined: Thu Jan 18, 2018 7:03 pm

Re: [Solved] SpellNumber in Calc Basic

Postby robleyd » Thu Jan 25, 2018 5:06 am

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 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2630
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests