VBA fSpellNumber() FUNCTION
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.
Following items in this page:
- Only one module of 13 Kb
- Ready to be used on your project
1º) Num -
The cardinal number, with or without fraction, to spell out
2º) FracType - 1 to 8 for manners of spelling (writing out) the fraction (Optional - Default 3 or 1 if there is UnitNameSing):
Spells the fraction in cent/cents. 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
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), 2016 (32 and 64-bit), and 2019 (32 and 64-bit) in MSI-based and Click-To-Run installations of Office 365™ cloud-based services, and VB too.
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
See some examples
from a sheet: