# VBA fSpellNumber() FUNCTION

What's new

A user defined function (UDF) in Microsoft® Visual Basic® for Application (VBA) that spells in English (written out) any cardinal number with or without fraction.

The fraction part, if there is, can be written out in eight ways. See Features below.

The largest valid number is 1 trillion minus 0.01 (999,999,999,999.99) and the smallest sensitive fraction is 1 trillionth (0.000,000,000,001).

All in all, it has eight arguments turning it into very flexible and enough complete to be used by programmer of VB and VBA in the Excel, Word, Access, PowerPoint etc.

#### Features:

- Only one module of 13 Kb

- Arguments:

1º) Num - The cardinal number, with or without fraction, to spell out
The largest valid number: 1 trillion minus 0.01 (999,999,999,999.99)
The smallest sensitive fraction: 1 trillionth (0.000,000,000,001).

2º) FracType - 1 to 8 for manners of spelling (writing out) the fraction (Optional - Default 3 or 1 if there is UnitNameSing):

1 - Spells the fraction in cent/cents. Ideal for coins
2 - Spells decimal point, each zero and the rest of the number as an integer. Ideal for percents
3 - Spells the fraction in tenths to trillionths. Ideal for pure numbers
4 - Doesn't spell the fraction but writes it as a fraction with denominator of 100, 1000, 1000000... Ideal for coins when in fractions lesser than one cent, or even in cents, although option 1 occurs about ten times more frequently
5 - Idem adding 'Only' at the end. Ideal for coins
6 - Spells the fraction in penny/pennies. Ideal for coins
7 - Spells the fraction in penny/pence. Ideal for coins
8 - Spells the fraction in centavo/centavos only. Ideal for coins.

3º) UndNameSing - Unity name in the singular (Optional - The lack denote pure number)

4º) UndNamePlur - Unit name in the plural (Optional - In the lack the pluralization will be done automatically from UnitNameSing, thus this only is necessary when the desired plural diverges from the language default as defined in the code)

5º) UndMasc - Denote unity gender: True (masculine) or False (feminine) (Optional - Default True (masculine)) (Obs.: Although this isn't need in English, it stays here. Maybe it was useful to translate this for another language. The same for the number noun flexibility.)

6º) OneThousand - Options for spelling out 1000, either omitting 'one' or not: True (Yes, 'one thousand') or False (No, 'thousand') (Optional - Default True (Complete))

7º) ComBetThous - Denote Comma intercalation Between Thousands: True (Yes) or False (No) (Optional - Default False (Don't intercalate))

8º) LetterCase - Format letters in upper/lower case (Optional - Default 1):

1 - all lower case
2 - Sentence case (Only the 1º is upper)
3 - Title Case (1º Upper in Each Word)
4 - ALL UPPER CASE.

Tested in Access, Excel, PowerPoint, and Word for Windows, versions 97, 2000, 2002(XP), 2003, 2007, 2010 (32 and 64-bit), 2013 (32 and 64-bit), and 2016 (32 and 64-bit) in MSI-based and Click-To-Run installations of Office 365™ cloud-based services, and VB too.

#### Features:

The code of this function and the information showed here are to programmer of VB and VBA in the Access, Excel, PowerPoint, Word etc.

Not programmer can use too this function thru utilities already ready and free that install it automatically in the mentioned application: Popup SpellNumber for Access, for Excel, for PowerPoint, and for Word, and yet My Word Useful Tools.

The Popup Spell Number for Excel, ExcelSpellNumber.xla add-in, beyond makes available the fSpellNumber() sheet function to all workbooks opened in your Excel, it too enables to capture number in Excel, configure the respective spelled number thru a user interface and send to Windows Clipboard to be immediately pasted in any Excel part (cell, formula bar, text box, comment etc.) or in another application.

The ExcelSpellNumber.xla makes available the fSpellNumber() function only in the Excel in that it’s installed. The function does not work if your workbook is opened in another Excel without ExcelSpellNumber installed. To work in any Excel it is need to install the code in the VBA project of your workbook. See how in the follow topic.

#### Installing in Your Excel Workbook:

For this, you need to have some knowledge about VBA. But same being a layman in VBA, try this:

- Copy the Mod1SpellNumber module to your workbook
How:
1- Open YourWorkbook.xls or create a new
3- Press Alt+F11 to open the Microsoft Visual Basic window
4- Drag the Mod1SpellNumber module to over VBAProject(YourWorkbook.xls) object.
- Now you can use the fSpellNumber() function directly on your workbook cells; For example, if you had a number on A1 cell, you can put this formula on B1:
= fSpellNumber(A1, 1, "Dollar").

See some examples from a sheet:
205,02 \$ (two hundred five dollars and two cents)
205,02 \$ (two hundred five and 2/100 dollars)
205,02 \$ (two hundred five dollars and two pennies)
205,02 £ (two hundred five pounds and two pence)
205,02 € (two hundred five euros and two cents)
206,02 P (two hundred six pesos and two centavos only)
206,02 (two hundred six and 02/100 only)
205,02 % (two hundred five point zero two per cent)
205,02 (two hundred five, and two hundredths)
205,02 g (two hundred five gallons and two hundredths of gallon)
206,02 s (two hundred six sacks and two hundredths of sack)

Visitas acumuladas em todas as páginas:
Visitas hoje só nesta página: