Microsoft Office Tutorials and References
In Depth Information
Using Custom Functions
To illustrate, we show you a function that converts numbers (like 123.45)
to words (like One Hundred Twenty Three and 45/100). It’s handy for
printing checks. This function also requires using a little bit of everything
that VBA has to offer — variables, arrays, loops, and decision-making — so
it works as an example of how programmers combine all aspects of
programming languages to come up with solutions to problems. Comments
throughout the function explain what’s going on, but it’s not important to
understand everything about how the function works. Rather, this function
is just an example of what a large custom function might look like.
To create a custom function that’s accessible to all objects in a database,
you have to put the function in a standard module. Listing 3-1 shows the
NumWord() custom function used to convert numbers to words as it would
appear in a module below the words Option Compare Database.
Listing 3-1: NumWord() Custom Function
‘Declare variables for NumWord to use
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, LoopCount As Integer
Dim StartVal As Integer, TensDone As Boolean
Dim EngNum(90) As String
‘NumWord converts a number to its words,
‘Useful for printing checks.
Function NumWord(AmountPassed As Currency) As String
‘Just bail out if no valid check amount passed.
If AmountPassed <= 0 Then
NumWord = “Void”
Exit Function
End If
‘Set up the array of words for numbers.
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”
Chapter 3
Search JabSto ::

Custom Search