Summa sõnadega

Alljärgnev makro aitab tabelarvutuse programmis (nt arvete peal) kirjutada numbrilisi väärtusi sõnadega. Viidatud autorite nimed ja e-postiaadressid ei pruugi toimida ent on siiski kirjas kui algsed viited autoritele.

Eesti keeles

rem **  Paigaldamiseks valida LibreOffice Calc (töötab ka Apache OpenOffice) tabelitöötluses 
rem **  'Tööriistad -> Makrod -> Makrode korraldamine -> LibreOffice Basic',
rem **  sealt valida kirje 'Minu makrod -> Standard' alapunkt 'Module1', 
rem **  'Module1' all parempoolsel paneelil valida 'Main' ja vajutada
rem **  nupul 'Redigeeri'. Avanevas toimetis tuleb kogu käesoleva faili 
rem **  sisu kopeerida teksti lõppu ja salvestada, sulgeda. Nüüd on funktsioon kasutatav kõigi 
rem **  arvutustabelite puhul. Kui soovitakse seda kasutada ainult aktiivses
rem **  tabelis, tuleb valida 'Standard' asemel dokumendi nimi, luua
rem **  nupu 'Uus' abil uus moodul ja kopeerida antud faili sisu sinna.
rem **  Kasutamiseks tuleb sisestada lahtrisse valem =sonadega(argument),
rem **  kus argument on kas numbritega määratud summa lahtri asukoht, näiteks
rem **  A1, B3, jne või konkreetne arvväärtus, näiteks 99,35 (kümnenderaldaja
rem **  vastavalt keelele, eesti keeles koma). Argument peab olema sulgudes.

rem *****  Algse Exceli funktsiooni autor on Urmas Leemet <exl@hot.ee>
rem *****  Funktsiooni kohandas OpenOffice.org jaoks Heno Ivanov <heno@aso.ee>
rem *****  Kaasajastas Edmund Laugasson <edmund@libreoffice.org>


Function Sonadega(Arv)
  Dim Mm As Variant
  Dim Kr As Variant
  Dim Jrk As Variant
  Dim So As String
  Dim Nr As String
  Dim Snt As String
  Dim F As Integer
  Dim Kro As String
  Dim Miin As Boolean
  Dim SONAD As String
  Dim SPIKKUS As Integer
  Dim EsiT As String
  On Error GoTo Viga
  
  
  Mm = Array("", "üks", "kaks", "kolm", "neli", "viis", "kuus", "seitse", "kaheksa", "üheksa", "")
  Jrk = Array("", " miljonit ", " tuhat ", "")
  Arv = Arv * 100
  If Arv < 0 Then Miin = True Arv = Int(Abs(Arv)) Snt = Right(Arv, 2) & " senti" Arv = Int(Arv / 100) Kr = Right("000000000" & Abs(Arv), 9) If Arv > 999999999.99 Then GoTo Viga
  If Kr = "000000000" Then So = "null eurot ": GoTo Lopp
  Kro = " eurot ": If Kr = "000000001" Then Kro = " euro "
  For F = 1 To 9
    Nr = Mid(Kr, F, 1)
    If (Nr <> 0) And (F = 1 Or F = 4 Or F = 7) Then So = So + Mm(Nr) & "sada "
    If (Nr <> 0) And (F = 2 Or F = 5 Or F = 8) Then
        If Nr = "1" Then
            If Mid(Kr, F + 1, 1) = "0" Then So = So + " kümme"
        Else
            So = So & Mm(Nr) & "kümmend "
        End If
    End If
    If (F = 3) And (Mid(Kr, 1, 3) <> "000") Then
        If Mid(Kr, 1, 3) = "001" Then
            So = So & Mm(Nr) & " miljon "
        Else
        
          If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
             So = So & Mm(Nr) & "teist" & Jrk(F / 3)
           Else
             So = So & Mm(Nr) & Jrk(F / 3)
           End If
        
        End If
    End If
    If (F = 6) And (Mid(Kr, 4, 3) <> "000" ) Then
      If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
        So = So & Mm(Nr) & "teist" & Jrk(F / 3)
      Else
        So = So & Mm(Nr) & Jrk(F / 3)
      End If 
    End If
    If (F = 9) Then 
      If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
        So = So & Mm(Nr) & "teist" & Jrk(F / 3)
      Else
        So = So & Mm(Nr) & Jrk(F / 3)
      End If    	
    End if
    
  Next F






Lopp:
If Miin = True Then So = "miinus " & So
SONAD = So & Kro & " ja " & Snt
if (Left(SONAD,1) = " ") then
  SONAD = Right(SONAD,Len(SONAD) - 1) 
End If
EsiT = UCase(Left(SONAD, 1))
SPIKKUS = Len(SONAD)
Sonadega = EsiT & Right(SONAD, SPIKKUS - 1)
  
 
  GoTo TheEnd
  
  Viga:
  Sonadega = "VIGA"
  TheEnd:
End Function

 

In English

rem **  For installation in spreadsheet of LibreOffice Calc (works also in Apache OpenOffice)
rem **  'Tools -> Macros -> Organize macros -> LibreOffice Basic',
rem **  choose 'My macros -> Standard' submodule 'Module1' from left 
rem **  under 'Module1' on right panel choose 'Main' and click 'Edit'
rem **  Copy all current text into opened editor and to the end of text, save and close.
rem **  Now you can use the function in all spreadsheets.
rem **  If you wish to use it only in active document then choose document name instead of 'Standard'
rem **  create new module using the button 'New' and copy current text there.
rem **  To use the function, enter the formula =withwords(argument) into appropriate cell
rem **  where 'argument' is the cell address where is sum written by numbers, e.g.
rem **  A1, B3 etc or exact number, e.g. 99,35 (separator according locale setting)
rem **  The argument should be between parentheses.

rem *****  The author of the original Excel function is Urmas Leemet <exl@hot.ee>
rem *****  Function has been adopted to Calc by Heno Ivanov <heno@aso.ee>
rem *****  Adopted to English and modernized by Edmund Laugasson <edmund@libreoffice.org>


Function Withwords(Arv)
  Dim Mm As Variant
  Dim Kr As Variant
  Dim Jrk As Variant
  Dim So As String
  Dim Nr As String
  Dim Snt As String
  Dim F As Integer
  Dim Kro As String
  Dim Miin As Boolean
  Dim WORDS As String
  Dim SPIKKUS As Integer
  Dim EsiT As String
  On Error GoTo ERROR
  
  
  Mm = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "")
  Jrk = Array("", " million ", " thousand ", "")
  Arv = Arv * 100
  If Arv < 0 Then Miin = True Arv = Int(Abs(Arv)) Snt = Right(Arv, 2) & " cents" Arv = Int(Arv / 100) Kr = Right("000000000" & Abs(Arv), 9) If Arv > 999999999.99 Then GoTo ERROR
  If Kr = "000000000" Then So = "zero euros ": GoTo Lopp
  Kro = " euros ": If Kr = "000000001" Then Kro = " euro "
  For F = 1 To 9
    Nr = Mid(Kr, F, 1)
    If (Nr <> 0) And (F = 1 Or F = 4 Or F = 7) Then So = So + Mm(Nr) & "hundred "
    If (Nr <> 0) And (F = 2 Or F = 5 Or F = 8) Then
        If Nr = "1" Then
            If Mid(Kr, F + 1, 1) = "0" Then So = So + " ten"
        Else
            So = So & Mm(Nr) & "kümmend "
        End If
    End If
    If (F = 3) And (Mid(Kr, 1, 3) <> "000") Then
        If Mid(Kr, 1, 3) = "001" Then
            So = So & Mm(Nr) & " miljon "
        Else
        
          If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
             So = So & Mm(Nr) & "teen" & Jrk(F / 3)
           Else
             So = So & Mm(Nr) & Jrk(F / 3)
           End If
        
        End If
    End If
    If (F = 6) And (Mid(Kr, 4, 3) <> "000" ) Then
      If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
        So = So & Mm(Nr) & "teen" & Jrk(F / 3)
      Else
        So = So & Mm(Nr) & Jrk(F / 3)
      End If 
    End If
    If (F = 9) Then 
      If Mid(Kr, F - 1, 1) = "1" And (Nr <> 0) Then
        So = So & Mm(Nr) & "teen" & Jrk(F / 3)
      Else
        So = So & Mm(Nr) & Jrk(F / 3)
      End If    	
    End if
    
  Next F






Lopp:
If Miin = True Then So = "miinus " & So
WORDS = So & Kro & " ja " & Snt
if (Left(WORDS,1) = " ") then
  WORDS = Right(WORDS,Len(WORDS) - 1) 
End If
EsiT = UCase(Left(WORDS, 1))
SPIKKUS = Len(WORDS)
Withwords = EsiT & Right(WORDS, SPIKKUS - 1)
  
 
  GoTo TheEnd
  
  ERROR:
  Withwords = "ERROR"
  TheEnd:
End Function

 

Eespool toodud tekst on kuvatud järgmise lahenduse abil:

<pre dir=”ltr” style=”border: 0px; margin: 0px; padding: 0px; overflow: auto; width: 700px; height: 280px; text-align: left;”>tekst</pre>