VBA Code for Percentage value to English Word

Originally Posted by karedog

Maybe :

'Function F_convert() and F_mats() are taken from "http://www.snb-vba.eu/VBA_Getallen_naar_tekst_en.html"
Private Function F_convert(y)
  F_convert = "Invalid input"
  'If y = "" Or Val(y) = 0 Then Exit Function
  If y = "" Then Exit Function
  c00 = Format(Val(1 * y), String(3 * ((Len(Format(Val(1 * y))) - 1) \ 3 + 1), "0"))
  For j = 1 To Len(c00) \ 3
      x = Mid(c00, 3 * (j - 1) + 1, 3)
      sp = Array(F_mats(Left(x, 1)), F_mats(Val(Right(x, 2))), F_mats(Right(x, 1)), F_mats(Mid(x, 2, 1) & "0"), F_mats(Mid(x, 2, 1)))
      c01 = c01 & IIf(sp(0) = "", "", sp(0) & " Hundred ") & IIf(Right(x, 2) = "00", "", IIf(sp(1) <> "", sp(1), IIf(Mid(x, 2, 1) = "1", Trim(sp(2)) & "teen", IIf(sp(3) = "", sp(4) & "ty", sp(3)) & " " & sp(2)))) & Choose(Len(c00) \ 3 - j + 1, "", " Thousand ", " Million ", " Billion ")
  Next
  F_convert = IIf(c01 = "", "zero", Replace(c01, " ", " "))
End Function
Private Function F_mats(y)
  On Error Resume Next
  F_mats = Split(Split(" 0 1One 2Two 3Three 4Four 5Five 6Six 7Seven 8Eight 9Nine 10Ten 11Eleven 12Twelve 13Thirteen 15Fifteen 20Twenty 30Thirty 50Fifty 80Eighty ", y)(1))(0)
End Function
Private Function F_percent(y As String)
  If Len(y) Then
     v = Split(y, ".")
     F_percent = Application.Proper(F_convert(CLng(v(0))))
     If UBound(v) = 1 Then F_percent = F_percent & " point " & F_convert(CLng(v(1)))
     F_percent = F_percent & " Percent"
  End If
End Function


Sub Test()
  Debug.Print F_percent("")
  Debug.Print F_percent("0")
  Debug.Print F_percent("85")
  Debug.Print F_percent("85.51")
  Debug.Print F_percent("7.11")
End Sub

Thank you so much karedog.... But there are something wrong with .01 to .09, it's spell this way:

Example:
90.01 spell as "Ninety point One Percent" - instead "Ninety point Zero One Percent"
90.02 spell as "Ninety point Two Percent" - instead "Ninety point Zero Two Percent"
...and up to .09...

Hope you get my point...

Originally Posted by karedog

replace the f_percent() function above with this :

private function f_percent(y as string)
  if len(y) then
     v = split(y, ".")
     f_percent = application.proper(f_convert(clng(v(0))))
     if ubound(v) = 1 then
        v(1) = left$(v(1), 2)
        if left$(v(1), 1) = "0" then f_percent = application.proper(f_percent & " point zero " & f_convert(clng(right$(v(1), 1)))) else f_percent = application.proper(f_percent & " point " & f_convert(clng(v(1))))
     end if
     f_percent = f_percent & " percent"
  end if
end function

and don't forget to mark this thread as solved :
Select thread tools from the menu link above and mark this thread as solved.

thank so much karedog... It works!