Looking at how NumWord() works
NumWord() is a fairly lengthy procedure mainly because the rules for converting numbers to words, in English, are a little complicated. But like any procedure, NumWord() is just a series of small decisions and steps needed to get the job done.
The first line of the procedure, as follows, defines the name of the procedure, NumWord(), and declares that it will accept a number Currency value (number) as an argument. Whatever number gets passed to the argument is referred to as AmountPassed in the rest of the procedure. The As String part at the end declares that NumWord() returns a string (text) to whatever called the function:
Public Function NumWord(AmountPassed As Currency) As String
The next lines declare some variables used for temporary storage by the procedure. Because there are lots of things to keep track of in this procedure, you need quite a few variables to store bits of information. In the following Dim statements, we're just declaring the names and data types of the variables. You can see how to put them to use later in the procedure:
'Declare some general working variables.
Dim English As String, strNum As String
Dim Chunk As String, Pennies As String
Dim Hundreds As Integer, Tens As Integer
Dim Ones As Integer, StartVal As Integer
Dim LoopCount As Integer, TensDone As Boolean
Next, the statement Dim EngNum(90) As String declares any array of variables, all containing text. The variables created by the statement are named EngNum(0), EngNum(1), EngNum(2), and so forth, up to EngNum(90). The Dim statement, as always, just sets aside space for those 90 variables. The variables don't contain any data at first:
Dim EngNum(90) As String
The next statements assign text to some of the variables that the Dim statement just declared. You don't need all 90 variables here - just enough of them to cover every possible unique number word. For example, you need ninety as a unique word, but you don't need ninety-one as a unique word because it can be built from two words: ninety and one.
The subscript for each variable matches the word that the variable contains. For example, EngNum(1) contains "One", EngNum(11) contains "Eleven", EngNum(70) contains "Seventy", and so forth. In a sense, you already solved part of the problem just by having the array subscript match the word that you need:
EngNum(0) = "" EngNum(1) = "One" EngNum(2) = "Two" EngNum(3) = "Three" EngNum(4) = "Four" EngNum(5) = "Five" EngNum(6) = "Six" EngNum(7) = "Seven" EngNum(8) = "Eight" EngNum(9) = "Nine" EngNum(10) = "Ten" EngNum(11) = "Eleven" EngNum(12) = "Twelve" EngNum(13) = "Thirteen" EngNum(14) = "Fourteen" EngNum(15) = "Fifteen" EngNum(16) = "Sixteen" EngNum(17) = "Seventeen" EngNum(18) = "Eighteen" EngNum(19) = "Nineteen" EngNum(20) = "Twenty" EngNum(30) = "Thirty" EngNum(40) = "Forty" EngNum(50) = "Fifty" EngNum(60) = "Sixty" EngNum(70) = "Seventy" EngNum(80) = "Eighty" EngNum(90) = "Ninety"
With all the needed variables declared, the procedure can get to work on translating whatever number was passed to it. The first If...End If block takes care of the problem of a zero or null value being passed to the function. The built-in Nz() (null-to-zero) converts a null value to a zero. Thus, the If statement Nz(AmountPassed) = 0 Then really says, "If the amount that's passed to me to work on is zero (or a null), then do the following lines up to End If. Otherwise, ignore those statements."
What happens if AmountPassed is a zero or null? The statement NumWord = "VOID" makes the return value for the function into the word VOID, and the Exit Function statement tells VBA to just bail out of the procedure now without doing anything else:
'** If Zero or Null passed, just return "VOID".
If Nz(AmountPassed) = 0 Then
NumWord = "VOID"
Exit Function
End If
Assuming that the amount passed to NumWord() is not a zero or null, execution then picks up at the following statement. This one is a little tricky. It uses the built-in Format function to make a string named strNum that exactly matches the amount passed. However, this string has exactly nine zeroes to the left of the decimal point and also two to the right. Suppose NumWord gets called with NumWord(7609511.98). By the time the following statement executes, the AmountPassed variable (a number) contains 7609511.98, and strNum contains (as a string) 007609511.98. Having those leading zeroes in place makes it easier to make decisions about how to handle the number later in the procedure:
'** strNum is the passed number converted to a string. strNum = Format(AmountPassed, "000000000.00")
Getting back to the NumWord(7609511.98) call, after the preceding statement executes, you have two copies of the amount that's passed to work with: the original AmountPassed (a number) and strNum, which is basically that same number with a fixed amount of leading zeroes:
AmountPassed = 7609511.98 strNum = "007609511.98"
Next, the following statement grabs the last two digits from StrNum and stores that value in a variable named Pennies:
'Pennies variable contains last two digits of strNum Pennies = Mid(strNum, 11, 2)
In this example, where we're using 7609511.98 as the number that's passed, the variable named Pennies contains the following line after the preceding statement executes:
Pennies = "98"
Now you need to get some starting values in some variables for the code to follow. The variable named English (which will eventually contain the entire number word) starts off as a zero-length string (""). LoopCount and StartVal each get values of 1. You can see how to use those variables in the code that follows the line 'Prep other variables for storage.
'Prep other variables for storage. English = "" LoopCount = 1 StartVal = 1
Next, start a loop that repeats until the LoopCount variable is greater than 3. Within that tool, the first thing you do is peel off chunks of the strNum variable and assign them to integer variables:
'** Now do each 3-digit section of number.
Do While LoopCount <= 3
Chunk = Mid(strNum, StartVal, 3) '3-digit chunk
Hundreds = Val(Mid(Chunk, 1, 1)) 'Hundreds portion
Tens = Val(Mid(Chunk, 2, 2)) 'Tens portion
Ones = Val(Mid(Chunk, 3, 1)) 'Ones portion
Getting back to the initial strNum number, 007609511.98, by the time the preceding statements execute, the following variables contain the corresponding values:
Chunk = "007" Hundreds = 0 Tens = 7 Ones = 7
The next statement says, "If the value of chunk (007, right now) is greater than 99, add EngNum(Hundreds) plus the word hundred to the string." In the current example, where Chunk is not greater than 99, nothing happens in this If...End If block:
'** Do the hundreds portion of 3-digit number
If Val(Chunk) > 99 Then
English = English & EngNum(Hundreds) & " Hundred "
End If
The next statements set the Boolean variable TensDone to False. Then the next statement says, "If the Tens portion is less than 10, add a blank space and EngNum(Ones) to the English variable, and change TensDone to True."
'** Do the tens & ones portion of 3-digit number
TensDone = False
'** Is it less than 10?
If Tens < 10 Then
English = English & " " & EngNum(Ones)
TensDone = True
End If
In this case, where Tens contains 7, the statement is true. By the time the preceding statements have executed (given the sample number), the following variables have the corresponding values:
English = " Seven" TensDone = True
The next If...End If statement deals with numbers in the range of 11-19. It says, "If the Tens number is between 11 and 19, add EngNum(Tens) to English and set TensDone to True." In this example, Tens is 7, which is not between 11 and 19, so this If block is skipped over. The contents and English and TensDone haven't changed:
'** Is it a teen?
If (Tens >= 11 And Tens <= 19) Then
English = English & EngNum(Tens)
TensDone = True
End If
The next block deals with Tens values that are evenly divisible by 10, such as 10, 20, 30, and so forth, up to 90. In this case, where Tens contains 7 (which is not evenly divisible by 10), nothing happens, so the English and TensDone variables hang on to their current values:
'** Is it evenly divisible by 10?
If (Tens / 10) = Int(Tens / 10) Then
English = English & EngNum(Tens)
TensDone = True
End If
The next If block kicks in only if the Tens portion of the number is still unresolved: that is, only if TensDone is still False. In this case, where TensDone got set to True already, the whole If...End If block is once again skipped over:
'** Or is it none of the above?
If Not TensDone Then
English = English & EngNum((Int(Tens / 10)) * 10)
English = English & "-" & EngNum(Ones)
End If
Next, look at adding the word million to the word. The If statement says, "If the amount that's passed is greater than 999,999.99 and the LoopCount variable equals one, add the word Million to English."
'** Add the word "Million" if necessary
If AmountPassed > 999999.99 And LoopCount = 1 Then
English = English & " Million "
End If
Using the running example, the number that's passed is greater than 999,999.99, and right now LoopCount equals 1. By the time the preceding If statement executes, the English variable has had the word Million tacked onto it, like this:
English = "Seven Million"
The next statement says that if the amount that's passed is greater than 999.99 and LoopCount equals 2, tack on the word Thousand. In the running example, where LoopCount now equals 1, this whole block of code is skipped over:
'** Add the word "Thousand" if necessary
If AmountPassed > 999.99 And LoopCount = 2 Then
English = English & " Thousand "
End If
The next statements increase the value of the LoopCount variable by 1 and increase the value of the StartVal variable by 3; then the Loop statement sends execution back up to the Do While LoopCount <= 3 statement for the next pass through the loop.
Converting the rest of the number involves more of the same. The next pass through the loop just has to work with the next three-digit chunk of strNum. In this example, where strNum contains 007609511.98, the next three digits after 007 are 609. By the time Chunk, Hundreds, Tens, and Ones have received their new values near the top of the loop, those variables contain these values:
Chunk = 609 Hundreds = 6 Tens = 9 Ones = 9
Looking through just the If...End If statements that prove true for this second pass through the loop, the statement Val(Chunk) > 99 is true this time. Thus, the statement English = English & EngNum(Hundreds) & " Hundred " executes, adding EngNum(6) plus the word "Hundred" to EngNum. By the time that statement has executed, the English variable has a new value:
English = "Seven Million Six Hundred"
The statement If Tens < 10 Then is also True on this second pass through the loop, so the statement English = English & " " & EngNum(Ones) adds a space and EngNum(9) to the English variable:
English = "Seven Million Six Hundred Nine"
No other If statement proves True here until If AmountPassed > 999.99 And LoopCount = 2 Then executes. Because it's true that AmountPassed is greater than 999.99 and LoopCount = 2 right now, the statement English = English & " Thousand " executes, and the English variable contains this line:
English = "Seven Million Six Hundred Nine Thousand"
Now you're at the bottom of the loop again, where LoopCount gets increased by 1 and StartVal gets increased by 3. By the time the Loop statement sends control back up to the Do While statement, those variables contain these values:
LoopCount = 3 StartVal = 9
At the top of the loop, the Chunk, Hundreds, Tens, and Ones variables all get new values, as follows, by peeling off the last three digits to the left of the decimal point:
Chunk = "511" Hundreds = 5 Tens = 11 Ones = 1
Once again, execution goes through all the statements, but only certain If...End If statements prove true. For example, the first True statement, If Val(Chunk) > 99, executes the statement English = English & EngNum(5) & " Hundred ". By the time that If...End If block has executed, the English variable contains this line:
English = "Seven Million Six Hundred Nine Thousand Five Hundred"
Going through the procedures that follow, the next If statement to prove True is If (Tens >= 11 And Tens <= 19) Then. So the statement English = English & EngNum(11) executes, making the English variable contain this line:
English = "Seven Million Six Hundred Nine Thousand Five Hundred Eleven"
No other If...End If statements execute. At the bottom of the loop where LoopCount = LoopCount + 1, the value of LoopCount increases to 4. The Do While loop repeats only while LoopCount is less than 4, so execution falls through the Loop statement, executing the statement NumWord = Trim(English) & " and " & Pennies & "/100". At that moment, NumWord (which is also the name of the function) gets " and ", the Pennies variable's value, and "/100" tacked on. The procedure then ends with an End Function statement. The value returned after calling NumWord(7609511.98) is
Seven Million Six Hundred Nine Thousand Five Hundred Eleven and 98/100
which, happily, is exactly right.
The procedure is designed to translate any number in the range of 0-999,999,999.99 where NumWord(999,999,999.99) returns
Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine and 99/100
If that's not big enough for you (because you print checks for a billion dollars or more), you could probably talk us into personally modifying the procedure to accommodate your needs.
To get away from the nitty-gritty details of how a complex procedure like NumWord() works, the most important concepts to remember are that you can create your own custom function in Access. To make the function freely available to all other objects in your database, you just have to put the custom function in a standard module. After you do that, you can treat your custom function as though it were any built-in function.
We admit that we got into some fairly intense code in this last example. If you're thinking that we just made up that procedure in our heads and jotted it down so that it worked the first time, you're way off base. Programming rarely works that way. It's all a matter of breaking down a large problem into small pieces. Then you attack one piece of the problem at a time, and get each little piece to work before moving on to the next one.
Along the way, you generally run into a whole lot of error messages because it's tough to write code that works right off the bat. You really need to create, test, and debug every little piece of code as you go along.