Excel - Is spellnumber function no more available in MS office 2007?

Asked By LynnLim on 06-Jan-10 08:54 PM
I used to have VBA function spellnumber to convert number to text in MS Excel
2003.

However, in MS Office Excel 2007, this spellnumber function is no long valid
and it gives me #Name error on the cell.

How can I fix it???




Ms-Exl-Learner replied to LynnLim on 06-Jan-10 09:12 PM
Follow the below link and download the MoreFuction Addin from that and run
the setup.

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

After doing that open excel and goto

Tools>>Add-Ins>Check the Morefunc(add-in Function)>>Ok

Then go to

Insert>>Functions>>Select a Category>>Select Morefun>>Select a
Funcion:>>Select NBTEXT>>Ok

That's It!!!!

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------
Faraz A. Qureshi replied to LynnLim on 07-Jan-10 01:24 AM
Use the complete following code by copy & paste in the VBA Editor (Alt+F11)
and use the function =N2W to conver Numbers to Words:

Public Function N2W(ByVal dbMyNumber As Double, _
Optional ByVal sMainUnitPlural As String, _
Optional ByVal sMainUnitSingle As String, _
Optional ByVal sDecimalUnitPlural As String = "", _
Optional ByVal sDecimalUnitSingle As String = "")
Dim sMyNumber As String
Dim sCurrency As String
Dim sDecimalText As String
Dim sTemp As String
Dim iDecimalPlace As Integer
Dim iCount As Integer
Call Application.Volatile(True)
ReDim Place(9) As String
Application.Volatile (True)
Place(2) = "Thousand"
Place(3) = "Million"
Place(4) = "Billion"
Place(5) = "Trillion"
sMyNumber = Trim(CStr(dbMyNumber))
iDecimalPlace = InStr(dbMyNumber, ".")
If iDecimalPlace > 0 Then
sDecimalText = GetTens(Left(Mid(Round(sMyNumber, 2), iDecimalPlace +
1) & "00", 2))
If Len(sDecimalText) > 0 Then
sMyNumber = Trim(Left(sMyNumber, iDecimalPlace - 1))
Else
sMyNumber = ""
End If
End If
iCount = 1
Do While sMyNumber <> ""
sTemp = GetHundreds(sMyNumber, Right(sMyNumber, 3), iDecimalPlace)
If sTemp <> "" Then
If (iCount > 1) And (LCase(Left(Trim(sCurrency), 3)) <> "and") Then
sCurrency = " " & sCurrency
End If
sCurrency = sTemp & Place(iCount) & sCurrency
End If
If Len(sMyNumber) > 3 Then
sMyNumber = Left(sMyNumber, Len(sMyNumber) - 3)
Else
sMyNumber = ""
End If
iCount = iCount + 1
Loop
Select Case Trim(sCurrency)
Case "": sCurrency = "Zero " & sMainUnitPlural
Case "One": sCurrency = "One " & sMainUnitSingle
Case Else: sCurrency = sCurrency & sMainUnitPlural
End Select
If iDecimalPlace > 0 Then
'       If (Len(sDecimalUnitPlural) > 0 And Len(sDecimalUnitSingle) > 0) Then
sCurrency = sCurrency & " "
Select Case Trim(sDecimalText)
Case "": sDecimalText = "and Zero " & sDecimalUnitPlural
Case "One": sDecimalText = "and One " & sDecimalUnitSingle
Case Else: sDecimalText = "and " & sDecimalText &
sDecimalUnitPlural
End Select
'       Else
'       sCurrency = sCurrency & " and "
'       sDecimalText = Mid(Trim(Str(dbMyNumber)), iDecimalPlace + 1) & "/100"
'       End If
End If
N2W = Trim(sCurrency & sDecimalText)
End Function
Function GetHundreds(ByVal sMyNumber As String, _
ByVal sHundredNumber As String, _
ByVal iDecimal As Integer) As String
Dim sResult As String
If sHundredNumber = "0" Then Exit Function
sHundredNumber = Right("000" & sHundredNumber, 3)
If Mid(sHundredNumber, 1, 1) = "0" Then
sResult = GetDigit(Mid(sHundredNumber, 1, 1))
ElseIf Mid(sHundredNumber, 1, 1) <> "0" Then
sResult = GetDigit(Mid(sHundredNumber, 1, 1)) & "Hundred"
End If
If (sMyNumber > 1000) And (Mid(sHundredNumber, 3, 1) <> "0" Or _
Mid(sHundredNumber, 2, 1) <> "0") Or _
(Len(sResult) > 0) And (Mid(sHundredNumber, 3, 1) <> "0" Or _
Mid(sHundredNumber, 2, 1) <> "0") Then
sResult = sResult & " and "
End If
If Mid(sHundredNumber, 2, 1) <> "0" Then
sResult = sResult & GetTens(Mid(sHundredNumber, 2))
Else
If Mid(sHundredNumber, 3, 1) <> "0" Then
sResult = sResult & GetDigit(Mid(sHundredNumber, 3))
Else
If Mid(sHundredNumber, 1, 1) <> "0" Then
sResult = sResult & GetDigit(Mid(sHundredNumber, 3))
sResult = sResult & " "
End If
End If
End If
GetHundreds = sResult
End Function