Conversion..

  • Thread starter Thread starter George Czarnonycz
  • Start date Start date
G

George Czarnonycz

OK

Here's a tricky one...

A dollar amount number is placed in a cell 1, 204.32
Is there a way to get it to display written out? "one thousand two hundred
four and thirty two cents"

Thanks... if anyone knows an answer to this - YOU are an excel genius!
 
First off - there are many genius' to the problem on the board!1

Second (question)- Is there a portion of Excel that controls this? I
included the "=SpellNumber()" command on a persons sheet to show them and it
did not work (#Name)... could she not have something installed?

It works great on mine and her boss', but not hers...

Thanx so much!
 
George

The error you see is due to the SpellNumber Function not being available to
that user.

It is a User Defined Function and must reside in the workbook, Personal.xls or
an add-in that is available to the user.

Either on a network or her own computer.

Gord Dibben XL2002
 
What "user defined Function or add-in" do we speak of?
I did nothing on my machine in order to make this work..
Any help?

Thanx!
George
 
SpellNumber is not a built-in XL function. You probably have an
add-in installed.

Check Tools/Add-ins... to bring up a dialog with a list of add-ins.
The one(s) with the checks in the checkboxes are installed.
 
Gordon is there anyway to place this code to come up and work with any
worksheet created?
She will using this function allot. I seem to recall a personal.xls or
something?

Thanx
 
I'm not Gord, but if you're going to share this function with others, you may
not want to take the time to incorporate it into each user's personal.xls file.

If you just create a new workbook (don't put anything in the worksheets) and add
the code to a general module, you can save it as an Addin.

Then give that workbook to each user and tell them to put it in their XLStart
folder.

Then each time excel opens, that addin will be opened, too.

And each user should only have to type:
=spellnumber(a1)
into a cell.

===
If you have a common folder that all can access, you can put this file in there.

Then tell them to do
tools|Addins|Browse to that folder
and load the addin.

Then you only have one copy of the original workbook to worry about.
 
George

1. Macros can be stored in a workbook so they are available only for that
workbook. These could be in general modules, worksheet modules or
Thisworkbook Module.

2. They can be stored in a Personal Macro Workbook that is placed in the
XLSTART folder so they are available for all open workbooks.

3. They can be stored in a workbook which has been saved as an Add-in with
the extension *.XLA

I prefer putting my macros/functions into a personal Add-in(*.XLA) and having
that loaded whenever Excel is opened.

The reason I favor the Add-in route is that you rarely have a problem with
Excel finding the macros/functions when referred to by name only, without
preceding with Personal.xls.!

One minor problem with an Add-in is that the macros don't show up in the
Tools>Macro>Macros list.

The Functions like SpellNumber will be available in the Function Wizard in
"User Defined" category.

If you has no Personal.xls one can be built easily.

On menu go to Tools>Macro>Record New Macro.

You will be presented with a dialog box with Macro1 as name of macro. Below
that is "Store macro in". Select Personal Macro Workbook.

Record something simple like copying a cell and pasting. Stop Recording.
Close Excel and you will be asked if you want to save changes to Personal.xls

Click Yes. Excel will save it to your XLSTART folder.

You now have a Personal.xls which will open each time you start
Excel. When it is open, you can go to Windows>Hide and make it hidden. Save
changes again. The macros you create will be available to work in all
workbooks.

At any time Personal.xls can be saved as an Add-in through File>Save
As>Type>Excel Add-in(*.xla) and stored in the Office\Library folder. It will
then show up in the list in Tools>Add-ins to be checked.

Enough already!!

Gord
 
Back
Top