Creating a UDF

Unlike manual operations, UDFs cannot be recorded—you have to write them from scratch using a standard module in the VBE. If necessary, you can insert a standard module by right-clicking in the Project Explorer window and choosing Insert O Module. A simple example of a UDF is shown here:

Function Fahrenheit(Centigrade)

Fahrenheit = Centigrade * 9 / 5 + 32 End Function

Here, a function called Fahrenheit() is created that converts degrees Centigrade to degrees Fahrenheit. In the worksheet, you could have column A containing degrees Centigrade and column B using the Fahrenheit() function to calculate the corresponding temperature in degrees Fahrenheit. You can see the formula in cell B2 by looking at the Formula bar in Figure 1-15.

tin

A

#

□ '

^JDTíífsffl -

Microsoft Evcel

LI 1

& Hsmê

irisen

Pôgc Laycut Formulas

Data k^viifoj

VreW DeVëcpér '-Ô' -

B2

v

f

=¡=ahrenhe¡t(A2}

S

A

G 1

c

D E

F G

H 1 J

K

1

Centigrade Fahrenheit

2

4

•.A

3

10

50

4

20

58

J

5

BO

86

5

40

104

7

50

122

3

60

140

9

70

155

10

B0

176

11

SO

194

12

100

211

11

«

H

H Sheetl

5ti»et2

5tieet3

I'M

Ki

Ready

m !

lain

raijMKi'-L-j fa

'■i> ¿K

Figure 1-15

Figure 1-15

The formula has been copied into cells B3:B12.

The key difference between a sub procedure and a function procedure is that a function procedure returns a value. Fahrenheit() calculates a numeric value, which is returned to the worksheet cell where Fahrenheit() is used. A function procedure indicates the value to be returned by setting its own name equal to the return value.

Function procedures normally have one or more input parameters. Fahrenheit() has one input parameter called Centigrade, which is used to calculate the return value. When you enter the formula, Fahrenheit(A2), the value in cell A2 is passed to Fahrenheit() through Centigrade. In the case where the value of Centigrade is 0, Fahrenheit() sets its own name equal to the calculated result, which is 32. The result is passed back to cell B2, as shown in Figure 1-15. The same process occurs in each cell that contains a reference to Fahrenheit().

A different example that shows how you can reduce the complexity of spreadsheet formulas for users is shown in Figure 1-16. The lookup table in cells A1:D5 gives the price of each product, the discount sales volume (above which a discount will be applied), and the percent discount for units above the discount volume. Using normal spreadsheet formulas, users would have to set up three lookup formulas together with some logical tests to calculate the invoice amount.

i tin'.

¡4

kj m ' - Mil - Mkrestft EiceJ

¡1)

m

Hclnd Irsen Pag£ LsycuT Formulas StVK-f,

VrgsV DEVEHJÉT 'Í

ï - ™ y

C9

T £ j =lnvoiceAmount(A9,B5r$A52:$D$5)

Price Discount Volume

10 100 30 50

S 100

12 100

Discount

Product

Apples Mangoes Oranges Pears

Price Discount Volume

10 100 30 50

S 100

12 100

Product

Apples Mangoes Oranges Pears

Product

Volume

Invoice Amount

Apples

5°l

500l

Pears

200

2340

Apples

150

1475

Mangoes

50

1500

Apples

20

Ready 5]

Figure 1-16

The InvoiceAmount() function has three input parameters: Product is the name of the product, Volume is the number of units sold, and Table is the lookup table. The formula in cell C9, in Figure 1-16, defines the ranges to be used for each input parameter:

Function InvoiceAmount(Product, Volume, Table) 'Find price in table

Price = WorksheetFunction.VLookup(Product, Table, 2) 'Find discount volume threshold

DiscountVolume = WorksheetFunction.VLookup(Product, Table, 3)

'Apply discount if volume above threshold If Volume > DiscountVolume Then 'Calculate invoice with discount

DiscountPct = WorksheetFunction.VLookup(Product, Table, 4) InvoiceAmount = Price * DiscountVolume + Price * _

(1 - DiscountPct) * (Volume - DiscountVolume)

Else

'Calculate invoice without discount InvoiceAmount = Price * Volume End If End Function

The range for the table is absolute so that the copies of the formula below cell C8 refer to the same range. The first calculation in the function uses the VLookup function to find the product in the lookup table and return the corresponding value from the second column of the lookup table, which it assigns to the variable Price.

If you want to use an Excel worksheet function in a VBA procedure, you need to tell VBA where to find it by preceding the function name with WorksheetFunction and a period. For compatibility with Excel 5 and 95, you can use Application instead of WorksheetFunction. Not all worksheet functions are available this way. In these cases, VBA has equivalent functions, or mathematical operators, to carry out the same calculations.

In the next line of the function, the discount volume is found in the lookup table and assigned to the variable DiscountVolume. The If test on the next line compares the sales volume in Volume with DiscountVolume. If Volume is greater than DiscountVolume, the calculations following it, down to the Else statement, are carried out. Otherwise, the calculation after the Else is carried out.

If Volume is greater than DiscountVolume, the percent discount rate is found in the lookup table and assigned to the variable DiscountPct. The invoice amount is then calculated by applying the full price to the units up to DiscountVolume plus the discounted price for units above DiscountVolume. Note the use of the underscore character, preceded by a blank space, to indicate the continuation of the code on the next line.

The result is assigned to the name of the function, invoiceAmount, so that the value will be returned to the worksheet cell. If Volume is not greater than DiscountVolume, the invoice amount is calculated by applying the price to the units sold, and the result is assigned to the name of the function.

0 0

Post a comment