Feb 19 2020 11:03 AM
I have been using the english number formula for a couple of years now and all of a sudden I am getting an inexplicable error. I am using code from this website:
https://www.techonthenet.com/excel/formulas/number_to_words.php
The code is as follows:
' Example created by techonthenet.com
Function EnglishNumber(ByVal N As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion
If (N = 0@) Then EnglishNumber = "zero": Exit Function
Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1
If (N >= Trillion) Then
Buf = Buf & EnglishNumberDigitGroup(Int(N / Trillion)) & " trillion"
N = N - Int(N / Trillion) * Trillion
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Billion) Then
Buf = Buf & EnglishNumberDigitGroup(Int(N / Billion)) & " billion"
N = N - Int(N / Billion) * Billion
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Million) Then
Buf = Buf & EnglishNumberDigitGroup(N \ Million) & " million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= Thousand) Then
Buf = Buf & EnglishNumberDigitGroup(N \ Thousand) & " thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If
If (N >= 1@) Then
Buf = Buf & EnglishNumberDigitGroup(N)
End If
EnglishNumber = Buf
End Function
Private Function EnglishNumberDigitGroup(ByVal N As Integer) As String
Const Hundred = " hundred"
Const One = "one"
Const Two = "two"
Const Three = "three"
Const Four = "four"
Const Five = "five"
Const Six = "six"
Const Seven = "seven"
Const Eight = "eight"
Const Nine = "nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False
Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select
If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishNumberDigitGroup = Buf
Exit Function
End If
Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select
If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & "-"
Else
EnglishNumberDigitGroup = Buf
Exit Function
End If
Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"
Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select
EnglishNumberDigitGroup = Buf
End Function
Normally this woks exactly as I want it to, converting numbers into text. But now it is giving me the #NAME error as if there is a typo, but there is none. See Screen shots:
Anyone else run into this issue? It is driving me crazy.
Feb 20 2020 12:47 AM
Hi @slider7420
Firstly make sure your macro is enable, i could be reason your macro is disabled the code is not working.
Have you tried using the spellnumber which is the same, maybe you can try this code.
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
Feb 20 2020 06:18 AM
@Faraz Shaikh I have tried SpellNumber with the same result. Macros are definitely enabled as I have been using this for several years before this issue started. Also I should mention that it works in other files I have been using for the same amount of time with literally the same code, however if I make any changes to those files - even if I undo those changes - English Number seems to stop working. If I create a new file, copy the code from a working file - the new file does not work. I thought maybe the issue was specific to my PC but trying elsewhere has the same result. I am at a loss.