| View previous topic :: View next topic |
| Author |
Message |
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
 Posted: Fri Oct 31, 2008 6:02 am Post subject: |
 |
|
| Quote: | | but how does it work in Excel, where this same function does ask for input? |
I really don't know as I have no experience with Excel macros.
| Quote: | | Could you rework it to ask for input, if it's not too much work? Come on Macro guru Bobban, I know you can do it!!!!!! LOL. As reward I'll thank you profusely and put in a good word for you to the good Lord above. Very Happy |
Well, if you think it would increase my chances of entry into heaven....ok! But probably check back tomorrow as it's late I might not get to it. 
I can assure you, I am no guru, but I will try. 
_________________
nil sine labore |
|
| Back to top |
|
 |
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
| Posted: Fri Oct 31, 2008 8:32 pm Post subject: |
|
|
Add this macro to the same module.
| Code: | Sub SpellNumberWithInputDialog
sInput = InputBox("Enter a sinlge cell adress to get the data from...", "", "")
cellAddress = sInput
oSheet = ThisComponent.CurrentController.ActiveSheet
ActiveCell = ThisComponent.getCurrentSelection()
oDataSourceCell = oSheet.getCellRangeByName(sInput)
ActiveCell.Formula = SpellNumber( oDataSourceCell.Value )
End Sub |
Bind this to a key to use as you did with Excel. It will crash with an error if you select more than a single cell for both input and output (ie. by selecting more than once cell than calling this will crash it). You can still use SpellNumber() as like any other formula too.
_________________
nil sine labore |
|
| Back to top |
|
|
radhika_24
General User
Joined: 10 Nov 2008
Posts: 19
|
| Posted: Mon Nov 10, 2008 11:18 pm Post subject: |
|
|
hey thanks like a dozen for that code!!!..
But please can anyone tell me a very basic thing...
I am sorry for askin this..
But i m rilly new to all this stuff...
Can u tell me where shud i save that numtowords OOobasic code?
Like all the python macros are stored under the file /usr/lib/openoffice/share/Scripts/python...
I am using CentOS
Can you please please tell me where do i save the code and then how do i invoke it ???
Thanks a lot ..
Radhika  |
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
|
| Back to top |
|
|
radhika_24
General User
Joined: 10 Nov 2008
Posts: 19
|
| Posted: Tue Nov 11, 2008 1:26 am Post subject: |
|
|
thanks thanks thanks a ton!! 
Radhika |
|
| Back to top |
|
|
radhika_24
General User
Joined: 10 Nov 2008
Posts: 19
|
| Posted: Wed Dec 03, 2008 2:04 am Post subject: @Danny |
|
|
It is not the fault of the code. Nor is it the fault of the OOo that the decimal places go hay wire. Your code does the perfect job of converting the number in figures to number in words cayse, thats what the result actually is.
To get rid of that however. Just declare nFrac in the same Sub as
| Code: | | Dim nFrac as Currency |
The rest would be handled!!
Thanks,
Enjoy,
Radhika |
|
| Back to top |
|
|
radhika_24
General User
Joined: 10 Nov 2008
Posts: 19
|
| Posted: Wed Dec 03, 2008 2:16 am Post subject: Indian Currency conversion |
|
|
Hey guys,
Only with the help of you all .. I have managed to develop a macro in OOBasic that converts the numbers in figures into numbers in words with respect to the Indian currency System....
Here is the entire code..
Thanks a zillion for the help !! 
| Code: |
Global oDocument
Global oSheet
Global oCell
Global bCell
Global oSelection
Global oRange
global r as integer
global c as integer
Global oListener
Global oRangeAddress as New com.sun.star.table.CellRangeAddress
Sub Main
oDocument = StarDesktop.ActiveFrame.Controller.Model
oSheet = oDocument.CurrentController.getActiveSheet() ' get actual sheet
oSelection = oDocument.CurrentController.getSelection
oRangeAddress = oSelection.getRangeAddress ' get actual range / obtiene el rango actual
r = oRangeAddress.StartRow 'current row / la fila actual
c = oRangeAddress.StartColumn 'current column / la columna actual
oCell = oSheet.getCellByPosition(c,r)
oCell = oSheet.getCellByPosition(c,r)
if c > 3 Then
bCell = oSheet.getCellByPosition(c-2,r+2)
else
bCell = oSheet.getCellByPosition(c,r+2)
EndIf
intanswer = NumToWords(oCell.getValue)
if intanswer = "Zero" Then
if bCell.getValue > 0 Then
bCell.setString(bCell.getValue)
else
bCell.setString("")
EndIf
Else
bCell.setString("Rs. "+intanswer)
EndIf
End Sub
Function NumToWords( ByVal nNumber As Double ) As String
cWords = ""
Dim nFrac As Currency
If nNumber < 0 Then
cWords = "Negative " + NumToWords( -1 * nNumber )
ElseIf nNumber <> Int( nNumber ) Then
cWords = NumToWords( Int( nNumber ) )
cWords = cWords + " and paise"
nFrac = nNumber - Int( nNumber )
nFrac = nFrac * 100
nFrac = Int(nFrac)
nFracWord = NumToWords(nFrac)
cWords = cWords + " " + nFracWord + " only"
ElseIf nNumber < 20 Then
Select Case nNumber
Case 0: cWords = "Zero"
Case 1: cWords = "One"
Case 2: cWords = "Two"
Case 3: cWords = "Three"
Case 4: cWords = "Four"
Case 5: cWords = "Five"
Case 6: cWords = "Six"
Case 7: cWords = "Seven"
Case 8: cWords = "Eight"
Case 9: cWords = "Nine"
Case 10: cWords = "Ten"
Case 11: cWords = "Eleven"
Case 12: cWords = "Twelve"
Case 13: cWords = "Thirteen"
Case 14: cWords = "Fourteen"
Case 15: cWords = "Fifteen"
Case 16: cWords = "Sixteen"
Case 17: cWords = "Seventeen"
Case 18: cWords = "Eighteen"
Case 19: cWords = "Ninteen"
End Select
ElseIf nNumber < 100 Then
nTensPlace = Int( nNumber / 10 )
nOnesPlace = nNumber Mod 10
Select Case nTensPlace * 10
Case 20: cWords = "Twenty"
Case 30: cWords = "Thirty"
Case 40: cWords = "Forty"
Case 50: cWords = "Fifty"
Case 60: cWords = "Sixty"
Case 70: cWords = "Seventy"
Case 80: cWords = "Eighty"
Case 90: cWords = "Ninety"
End Select
If nOnesPlace > 0 Then
cWords = cWords + " " + NumToWords( nOnesPlace )
EndIf
ElseIf nNumber < 1000 Then
nHundredsPlace = Int( nNumber / 100 )
nRest = nNumber Mod 100
cWords = NumToWords( nHundredsPlace )
cWords = cWords + " Hundred"
If nRest > 0 Then
cWords = cWords + " " + NumToWords( nRest )
EndIf
ElseIf nNumber < 100000 Then
nThousands = Int( nNumber / 1000 )
nRest = nNumber Mod 1000
cWords = NumToWords( nThousands )
cWords = cWords + " Thousand"
If nRest > 0 Then
cWords = cWords + " " + NumToWords( nRest )
EndIf
ElseIf nNumber < 10000000 Then
nLacs = Int( nNumber / 100000 )
nRest = Int( nNumber Mod 100000 )
cWords = NumToWords( nLacs )
cWords = cWords + " Lac"
If nRest > 0 Then
cWords = cWords + " " + NumToWords( nRest )
EndIf
ElseIf nNumber < 1000000000 Then
nCrores = Int( nNumber / 10000000 )
nRest = Int( nNumber Mod 10000000 )
cWords = NumToWords( nCrores )
cWords = cWords + " Crore"
If nRest > 0 Then
cWords = cWords + " " + NumToWords( nRest )
EndIf
' You can follow the pattern of the Lacs / Crores / HundredCrores
' if you need bigger numbers.
EndIf
NumToWords() = cWords
End Function |
Njoy,
Radhika |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Dec 05, 2008 11:54 am Post subject: |
|
|
There seems to be a floating point problem with spreadsheet formulas like
which yields One point One Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Nine Six Four Four Seven Two Eight Six Three Two One One Nine Nine Four Nine Nine
Quick fix:
| Code: |
Function NumToWords( ByVal nNumber As Double ) As String
Dim nFrac as Currency
[...]
|
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
zhijie46
General User
Joined: 06 Jun 2009
Posts: 13
|
| Posted: Mon Jun 29, 2009 8:58 pm Post subject: |
|
|
Hi, I notice that in your code, the decimals show Two Three.
I am no programmer to begin with, just very basic knowledge and after going through your code I found the reason for your method of getting the decimals
| Code: | If nNumber < 0 Then
cWords = "Negative " + NumToWords( -1 * nNumber )
ElseIf nNumber <> Int( nNumber ) Then
cWords = NumToWords( Int( nNumber ) )
cWords = cWords + " point"
nFrac = nNumber - Int( nNumber )
Do
nFrac = nFrac * 10
nDigit = Int( nFrac )
nFrac = nFrac - nDigit
If nDigit > 0 Then
cDigitWord = NumToWords( nDigit )
cWords = cWords + " " + cDigitWord
Else
Exit Do
EndIf
Loop |
Pardon my ignorance but is there a way to make this portion return Twenty Three rather than Two Three? Thanks |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Tue Jun 30, 2009 7:17 am Post subject: |
|
|
| Quote: | | is there a way to make this portion return Twenty Three rather than Two Three? |
Lightly tested. Try making the edits marked below which should produce point Zero One through Zero Nine then Ten, Eleven, etc. This will only handle two decimal points.
Also note that below this code the word "Nineteen" is spelled incorrectly as pointed out in this thread.
| Code: | Function NumToWords( ByVal nNumber As Double ) As String
cWords = ""
Dim nFrac as Currency 'EDIT
If nNumber < 0 Then
cWords = "Negative " + NumToWords( -1 * nNumber )
ElseIf nNumber <> Int( nNumber ) Then
cWords = NumToWords( Int( nNumber ) )
cWords = cWords + " point"
nFrac = nNumber - Int( nNumber )
If Int(nFrac*10) = 0 then 'EDIT
cWords = cWords + " " + "Zero" 'EDIT
EndIf 'EDIT
Do
nFrac = nFrac * 100 'EDIT
nDigit = Int( nFrac )
|
|
|
| Back to top |
|
|
zhijie46
General User
Joined: 06 Jun 2009
Posts: 13
|
| Posted: Tue Jun 30, 2009 7:19 am Post subject: |
|
|
Thanks JohnV
this should get me headed in the right direction now! |
|
| Back to top |
|
|
max_cn
General User
Joined: 26 Jun 2009
Posts: 19
|
| Posted: Fri Jul 03, 2009 12:49 am Post subject: |
|
|
I don't understand what kind of problems can be with such easy task.
Here didles tall me he can’t find solution for his troubles in this thread.
I’m not VB programmer, sorry, but I will show you how to resolve all your troubles with small example of C# code.
Note, my function numToWords only show how step by step to get every digit, I know you have your own for real translating, but my example have no troubles with negative and decimal numbers.
Hope it will be helpful :-\.
| Code: |
using System;
namespace Num2Words
{
class Program
{
//this function is only for showing how to get every digit
//use our own code instead!!!
static public string numToWords(int num)
{
if (num==0) return "Zero";
string sNum=num.ToString();
string word="";
for (int i=0;i<sNum.Length;i++) word=word+sNum[i];
return word;
}
//preparing parts of number for translating
static public string translateNum(double num)
{
string sign="";
if (num<0) sign="Negative ";
double absNum=Math.Abs(num);
int intNum=(int)(absNum);
//decimal part can have maximum 2 digits
int decNum=(int)Math.Round((absNum-intNum)*100);
string fullText=sign+numToWords(intNum)+" dollars";
if (decNum!=0) fullText=fullText+" and "+numToWords(decNum)+" cents";
return fullText;
}
//program entry point
static void Main(string[] args)
{
//testing number
double num=-123.45745;
Console.WriteLine("Result of translating is: "+translateNum(num));
}
}
}
|
|
|
| Back to top |
|
|
UniqueUserID
General User
Joined: 15 Sep 2009
Posts: 34
|
| Posted: Sat Sep 19, 2009 2:40 am Post subject: |
|
|
One more slightly nitpicky point:
In most English speaking countries, compound numbers in the range 21 to 99 should be hyphenated. Hence,
21 = Twenty-one not Twenty One
35 = Thirty-five not Thirty Five
Therefore this code at the end of the 3rd ElseIf clause:
| Code: |
ElseIf nNumber < 100 Then
.
.
If nOnesPlace > 0 Then
cWords = cWords + " " + NumToWords( nOnesPlace )
EndIf
.
.
|
should be changed to
| Code: |
ElseIf nNumber < 100 Then
.
.
If nOnesPlace > 0 Then
cWords = cWords + "-" + Lcase(NumToWords( nOnesPlace ) ) 'Edit
EndIf
.
.
|
|
|
| Back to top |
|
|
Mr.E
Newbie
Joined: 09 Nov 2010
Posts: 1
|
| Posted: Tue Nov 09, 2010 3:30 am Post subject: |
|
|
Hi,
I need this function, but I don't know anything about programming.
I would need it in French, and with euros and eurocents.
Of course I can provide the translations, and the specificities about how it works in French.
Can someone help me? |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Wed Nov 10, 2010 4:35 pm Post subject: |
|
|
Hello
Maybe this can help you?
Romke |
|
| Back to top |
|
|
|
