VIEWS: 17 PAGES: 9

• pg 1
```									This article contains sample Microsoft Visual Basic for Applications functions that you can use to convert a numeric value in a Microsoft Excel
worksheet cell into its equivalent in English words.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to,
the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help
explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct
procedures to meet your specific requirements.

Back to the top

How to create the sample function Called SpellNumber
1.    Start Microsoft Excel.
2.    Press ALT+F11 to start the Visual Basic Editor.
3.    On the Insert menu, click Module.
4.    Type the following code into the module sheet.

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = ""           ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else                                 ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

How to use the SpellNumber sample function
To use the sample functions to change a number to written text, use one of the methods demonstrated in the following examples:

Back to the top

Method 1: Direct Entry
You can change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering the following formula into a cell:
=SpellNumber(32.50)

Back to the top

Method 2: Cell reference
You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell:
=SpellNumber(A1)

Back to the top

Method 3: Paste Function or Insert Function
To enter a custom function into a worksheet, you can use Paste Function in Excel 2000, or you can use Insert Function in Excel 2002 and in
Excel 2003.

Excel 2000
To use Paste Function, follow these steps:
1.   Select the cell that you want.
2.   Click Paste Function on the Standard toolbar.
3.   Under Function category, click User Defined.
4.   Under Function name, click SpellNumber, and then click OK.
5.   Enter the number or cell reference that you want, and then click OK.

Excel 2002 and Excel 2003
To use Insert Function, follow these steps:
1.    Select the cell that you want.
2.    Click Insert Function on the Standard toolbar.
3.    Under Or select a category, click User Defined.
4.    In the Select a function list, click SpellNumber, and then click OK.
5.    Enter the number or cell reference that you want, and then click OK.
Summary: Learn how to dynamically search table arrays in Microsoft Office Excel 2007 by using the
built-in VLOOKUP function.

Often cells in a worksheet have dependencies on other cells in the same or in a separate, but related, wo
most robust method of assigning values to these dependent rows is to dynamically locate and resolve the
Office Excel 2007 contains a number of search functions to address this need. The VLOOKUP function ena
search for a value in the first column of a table array, and if a match is found, then return a value from a
in this same row.

Code It
In this example, you create three calculated columns using the VLOOKUP function in a variety of
ways. It uses information about students at a hypothetical state university.

Open and Examine StudentInfo

In this example you will work with the file StudentInfo.xlsx, which you can download from Code
Gallery. Open this file in Office Excel 2007. It contains a workbook with three worksheets:

      Students contains key information about registered students. This is the worksheet that you
will be modifying.

      GPA lists the students' quarterly and averaged yearly grade point average, on a scale from 0
to 4.0.

      Grades contains a GPA-to-letter conversion table. (It also contains some UI at the bottom
that can be used in a procedure for calculating a grade from a GPA value. This is discussed in
the section Using VLOOKUP from VBA.)

This workbook represents a sample of an actual workbook that could potentially contain information
about thousands or tens of thousands of students.

The university pairs all incoming freshman with one another in a "buddy" system. Currently in
the Students worksheet, theClass Buddy column lists the ID of the corresponding student.
Although this information is available via a manual search in this same table, to enable easier access,
another column with the buddy's email will be added. To accomplish this, you will use
the VLOOKUP function to search in the same table using an exact match.

To create the buddy email column

1. On the Students worksheet, enter "CB Email" into cell I1. Increase the width of this column to about 180 pix

2. Enter the following formula into cell I2:

3. =VLOOKUP(H2, A2:H23, 7, FALSE)
Notice how the autocomplete and tooltip features in Office Excel 2007 assist in creating this formula. The pa
following meaning:

   H2   is the cell containing the value (391885) to lookup.

    A2:H23 is the table array to search through. Note the first, left-most column, here A (ID), represents
search for our lookup value.

   7  is the index of the column whose cell contains the returned value of the function, assuming a match
lookup value.

   FALSE   specifies that an exact search should be performed.

When you click Enter, I2 should display the calculated value of steventh@stateu.edu. Steven Thorpe is the a
Anne Hellung.

4. Edit the formula in I2 so that the table array uses absolute cell references:

5. =VLOOKUP(H2, \$A\$2:\$H\$23, 7, FALSE)

This should not change the calculated value in I2, but is required for the next step.

6. Copy the formula in cell I2 to cells I3 through I23. The easiest way to accomplish this is to autofill this colum
the lower-right handle on I2 down to I23. The rest of the calculated buddy email addresses should now displa
of these cells to examine the underlying LOOKUP formula.

Adding a Column to Confirm Buddy Compatibility

Buddies are usually required to be in the same graduation class. To help monitor this proscription, a
new column is requested to keep track of this relationship. Again you will use VLOOKUP to search in
the same table using an exact match, but the results will then be used in a further calculation.

To create the paired class validation column

1. On the Students worksheet, enter "CBSY?" into cell J1. This acronym represents "Class Buddies the Same Y

2. Enter the following formula into cell J2:

3. =EXACT(D2, VLOOKUP(H2, A:H, 4, FALSE))

This formula compares the graduation class of the current student with that of his buddy and returns the Bool
that this time that the table is described by a range of columns, A:H. This is more appropriate for tables in wh

4. Copy this formula to the rest of the cells in this column, cells J3 through J23.
Note that two paired students, Mr. White and Mr. Xie, are in different graduation classes.

Lastly, a request has been received to extend this worksheet with the current student average grade.
To accomplish this, you perform a nested lookup using tables on the other worksheets.

1. On the Students worksheet, enter "Grade" into cell K1.

2. Select the column and format the cells as a Number type with 2 Decimal places of precision.

3. Enter the following formula into cell K2:

4. =VLOOKUP(A2,GPA!A:F,6,FALSE)

This formula performs a lookup using the current student's ID on the table in the GPA worksheet. It returns t
values in column 6, which is the average GPA for the year.

5. Next convert this GPA into a grade by wrapping this function call inside of another call to VLOOKUP:

A lookup is performed using the result of the previous lookup, searching on the table in the GPA worksheet,
sixth column. Notice the following:

    The search type is approximate (FALSE) and therefore the first column (GPA) in the Grades workshe
ordered ascending.

7. Copy this formula to the rest of the cells in this column, cells K3 through K23.

The Resulting Students Worksheet

After you have added these three new columns, the resulting Students worksheet should appear as
in Figure 1 (except that in the figure, columns D-G have been hidden to conserve space).

Figure 1. Students worksheet after column additions.
]

The built-in VLOOKUP function in Office Excel 2007 is used to dynamically search for matching
values in a table arrays. Given a value, it performs a vertical search in the first column of the
specified table, and if it finds a match, it returns a value from the specified column in the same row.

This function can be used to find information in a single worksheet or, more commonly, to use a
known value in one worksheet to search for associated data in a row of another, related worksheet.

Office Excel 2007 contains a number of related search functions, including:

Function Description

LOOKUP     Searches for an exact matching value and, if successful, returns an associated value. The LOO
has two syntax forms: the vector form and the array form.

HLOOKUP This horizontal lookup function is the counterpart to VLOOKUP. It searches for a value in the
table array, and if a match is found, then it returns a value from the specified row in this same

MATCH      Returns the relative position of an item in an array that matches a specified value in a specifie
UseMATCH instead of one of the LOOKUP functions when you need the position of an item in
of the item itself.
In addition, it is common to use other built-in functions—such as INDEX, OFFSET, FIND, SEARCH,
and CHOOSE—to refine searches.

Syntax

VLOOKUP has the following syntax:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Where:

lookup_value      is the value to search for in the first column of the table array. This parameter can hold a value
or a reference.

Note:

     A text match is not case sensitive, but does consider whitespace, non-printing and special characters.
You can use the TRIM or CLEAN functions to remove these characters and EXACT to compare case.

      Exact text searches can use the question mark (?) and asterisk (*) wildcard characters to match a single
character or any sequence of characters, respectively.

      Exact matches on floating point values may not succeed because of rounding approximations. Instead,
perform an approximate match or use the TRUNC function.

Error Conditions

The following conditions will result in error values being returned:

      If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP
returns #N/A.

      If col_index_num is less than 1, then #VALUE! is returned; if greater than the number of columns
in table_array, then#REF! is returned.

Using VLOOKUP from VBA

The VLOOKUP function is exposed in the Office Excel 2007 object model as
the WorksheetFunction.VLookup method. For example, the following code could be added to
the Grades worksheet to perform a basic GPA-to-grade conversion. This code utilizes the existing UI
elements already found in this worksheet: the button named GradeCalc, and named

Note: VBA code will only function in macro-enabled spreadsheets and templates. If you want to add
and test this code, save the companion file StudentInfo.xlsx as a macro-enabled spreadsheet named
StudentInfo.xlsm.

' Event handler to calculate a grade from a GPA
Dim res As Variant
Dim errNum As Integer
'Traps and reports all application errors
On Error Resume Next
res = Application.WorksheetFunction.VLookup(Range("GpaInput"), _
Range("A2", "B12"), 2, True)
errNum = Err.Number
If errNum <> 0 Then
res = "Error: " & errNum
End If
End Sub

This code must be implemented as an event procedure for the GradeCalc button.

To create an event procedure for an existing form control

1. Right-click on the control (here the GradeCalc button) and choose the Assign Macro command from the co
The Assign Macro dialog box should display.

2. In the Macro Name text box, enter a name for the VBA event procedure (here GradeCalc_Click) and click

The dialog box closes and the Visual Basic Editor opens, displaying the VBA source code for the newly crea
procedure.

In addition to the standard runtime errors, such as out of memory, described in Core Visual Basic
Language Errors, theVLOOKUP method also issues the application-defined error (error number
1004) for the error conditions described in the previous section.

Explore It
VLOOKUP

WorksheetFunction.VLookup Method

Lookup and Reference Functions

Use HLookup and VLookup functions to find records in large worksheets

Dynamic searching using VLOOKUP, MATCH and INDEX

XL: How to Perform a Case-Sensitive Lookup

The VLOOKUP and HLOOKUP functions fail to find a number in a list in Excel