PDA

View Full Version : Excel: convert number to equivalent word



Tinakarori
12-10-2004, 12:55 PM
Can anyone advise of a function or formula in Excel XP that will automatically provide the word equivalent of a number?

For example, if the number "1" is entered in the reference cell, the output cell displays "one"

I'm sure I read about a way to do this not long ago, in the context of an automatic cheque-writing process in Excel.

johnboy
12-10-2004, 01:10 PM
This is the ms page for it here (http://support.microsoft.com/default.aspx?scid=kb;en-us;213360)
hth

Tinakarori
12-10-2004, 02:14 PM
Many thanks Johnboy

I've printed the MS K_base article, and will now dive into implementing the VBA module in a sample spreadsheet.

B.M.
12-10-2004, 03:22 PM
I tried it and it worked great.

I wonder how one can save it as a function that will work on any spreadsheet?

parry
12-10-2004, 04:27 PM
> I tried it and it worked great.
>
> I wonder how one can save it as a function that will
> work on any spreadsheet?

Save it in your Personal.xls file. If you dont have one, you can create a file with this name and save in your XlStart folder (wherever Office is installed) or record a new macro (Tools|Macro|Record New macro) and in the drop down where it has 'Store Macro In' choose 'Personal Macro Workbook', click Ok then stop the macro. The Personal.xls file will be created if it didnt already exist.

Open the Personal.xls file (note it is a hidden file) in the xlstart folder then open the VBE (ALT+F11) then insert a new module (Insert|Module) and paste the function code in there. Save and close Personal.xls file.

hth

parry
12-10-2004, 04:30 PM
Forgot to say if a module doesnt already exist create one. Otherwise just paste underneath the rest of the code you have in there (if any).

hth

B.M.
12-10-2004, 08:35 PM
Thanks Parry, I'll give it a go tomorrow.

Cheers

Bob

B.M.
13-10-2004, 09:35 AM
Followed your instructions Parry and everything worked fine but only on that workbook.

Id like to be able to add it to the function list so it will work on existing workbooks.

Is that possible?

Cheers

Bob

parry
13-10-2004, 11:10 AM
Hi BM, my apologies this didnt work as I expected. You will need to preceed the function name with Personal.xls! for it to work. I didn't think you would have to reference this book.

eg...
=PERSONAL.XLS!spellnumber(123.45)

Just some background on this. When Excel starts it loads all add-ins and all workbooks located in the xlstart folder so any procedures/functions in these areas are available to all workbooks.

If the function was saved as an Add-in then you dont need to preceed the function with the name of the workbook in order for Excel to be able to locate the function, but it seems you do for xlstart files.

B.M.
13-10-2004, 11:33 AM
Sorry Parry, that returned #NAME?

Never mind, just thought it would be nice to add it to the dozens of other functions that already exist.

Cheers

Bob

parry
13-10-2004, 11:59 AM
If it returns name# it will mean you havent saved it the code in your Personal.xls file, its not located in xlstart or you havent used the correct syntax for the formula.

If you prefer to create an addin then -
1) open a new workbook
2) open the VBE (ALt + F11)
3) Insert a module (Insert | Module)
4) Paste the code in the right hand window
5) Close the VBE (Alt + Q)
6) Select File|Save As and in the drop down menu 'Save As Type' select the option Microsoft Excel Addin (its right at the bottom)
7) Name the file from Book1.xla to something more descriptive for you then click OK
8) Select Tools|Addins from the menu and put a check against the name you used in step 7 then click OK.

Close and re-open Excel and you should be able to use those functions.

hth

B.M.
13-10-2004, 02:16 PM
Still no go Parry.

The ADD-IN showed up a right and was ticked but =Spellamount(a1) still produced #NAME?

Dahhhhhh.

Works fine attached to one particular workbook.

Hmmmmmmmm?

parry
13-10-2004, 03:00 PM
Thats a head scratcher then. :-)

Did you change the function at all or just straight copy and paste? If the function has Private Function instead of Function (or Public Function) then this prevents viewing in other workbooks but I cant imagine you've done that.

Perhaps its a security setting. Try Tools|Options, Security tab then click the macro security button and under the Trusted Sources tab you have a tick against 'Trust All Installed Add-ins and Templates'. For good measure ensure the option under the Security Level tab is Medium.

B.M.
13-10-2004, 03:11 PM
Do you mean Tools-Macros-Security Parry?

That is set to low and all add-inns are trusted.

By the way I'm useing Excel 2000 ver 9.0.2720 not that probably makes much differance?

Yea, I'm scratching my head too.

parry
13-10-2004, 04:46 PM
Yes thats the same Bob, Im using xl 2002 so things may be slightly different. Im sorry but Ive run out of ideas why this wont work. As long as the codes correct it should be fine so open the addin book through Windows Explorer then open the VBE and with the module selected Debug|Compile VBA project just to make sure there is no coding errors. Note that Option Explicit should only be declared once and is the first line of code.

Perhaps have a read of this and follow the example and see what happens.

http://www.fontstuff.com/vba/vbatut03.htm

BlueSpade
20-05-2008, 09:05 PM
just save as the workbook as addins in the save menu..hope it be useful..

beeswax34
20-05-2008, 10:26 PM
After 4 years? Doubtful me thinks.

Auto-lock please?