Need help with the VBA - Digit to words

[success127]

I am very new to VBA and all the codes.


I need help with this VBA which I copied from the website or something else I should do.
The problem I face:


Example:
columnA1 - the formula =IF('2018-2019 Semester Data'!U81=0,"",'2018-2019 Semester Data'!U81)


The actual data from 2018-2019 Semester Data'!U81 is 83.8
and after the number is link to columnA1, I rounded the decimal and it gives me 84. The number is correct.


Here is the problem when use the VBA code
columnA2 - the formula =IF(A1="","",EnglishNumber(A1))


columnA2 against the columnA1 but the result came out to be "eighty-three", not on the rounded number (84 eighty-three).
I want it to be "eighty-four".




Can anyone help to edit the code to make every time the EnglishNumber VBA is used it will read the rounded number. Thanks


The following is the code.

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

 

[mole999]

if it always hs to be the higher value then wrap in ROUNDUP

 

[success127]

if it always hs to be the higher value then wrap in ROUNDUP

Sorry, I couldn't understand. Can you elaborate the ROUNDUP?

 

[MARK858]

I think mole999 means

Excel 2010
	A	B	C
1	83.8		eighty-four
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1="","",EnglishNumber(ROUNDUP(A1,0)))

If you mean to the nearest whole number rather than always the higher whole number use ROUND rather than ROUNDUP.