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 rightclicking 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 115.
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'Lj fa 
'■i> ¿K 
Figure 115
Figure 115
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 115. 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 116. 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 StVKf, 
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

Post a comment