Hàm chuyển số thành chữ
Đã có rất nhiều hàm chuyển số thành chữ trên các diễn đàn, nhưng hôm nay, tôi
xin giới thiệu với các bạn hàm chuyển số thành chữ hoàn chỉnh nhất của
Paulsteigel trên diễn đàn Webketoan
Code:
Option Explicit
Function CountValue(ByVal Target As Range, ByVal Criteria As Long, ByVal
isGreater As Boolean) As Variant
Dim i As Long, j As Long
Dim k As Long
With Target
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If Not IsEmpty(.Cells(i, j)) Then
If isGreater Then
If Val(.Cells(i, j)) >= Criteria Then k = k + 1
Else
If Val(.Cells(i, j)) <= Criteria Then k = k + 1
End If
End If
Next
Next
End With
CountValue = k + 1
End Function
Public Function NumtoWordExl(ByVal Target As Range, Optional IsToUnicode
As Boolean = False) As String
Dim iStr As String, i As Long
Dim retVal As String
If isBigRange(Target) Then
NumtoWordExl = ""
GoTo tExitFunction
End If
' this is a trick to keep excel doesnt set the value to somewhat like 1.22e12+19
iStr = Format(Target.Value, "#000")
retVal = NumtoWord(iStr)
' Now we have to convert the result to unicode if neccessary
If retVal <> "" And IsToUnicode Then retVal = ToUnicode(retVal)
NumtoWordExl = retVal
tExitFunction:
End Function
Function NumtoWord(InTxt As String) As String
' Concert any length number to word
' The mentor is: break a number to 9 characters length and do the conversion
' for the rest .... increment the billion counter
' the main function for the conversion is at anywhere in the net and I took this one
from anonimity
' My onwed function work similarly - but i failed in searching for it - it dumbed...
' so take this one in replacement
Dim i As Integer, j As Integer
Dim OutString As String
Dim ProcArr() As String
ReDim ProcArr(10)
While Len(InTxt) > 9
' break the input string to group of 9 digit
ProcArr(i) = Right(InTxt, 9)
InTxt = Left(InTxt, Len(InTxt) - 9)
i = i + 1
Wend
ProcArr(i) = InTxt
ReDim Preserve ProcArr(i)
' Now convert the group to value
i = UBound(ProcArr)
While i > 0
' add with "w" as billion word...
OutString = OutString & IIf(Val(ProcArr(i)) > 0, ReadBilGroup(ProcArr(i)) &
String(i, "w"), "")
i = i - 1
Wend