Direct Reference to Ranges

When you define a UDF, it is possible to directly refer to worksheet ranges rather than through the input parameters of the UDF. This is illustrated in the following version of the lnvoiceAmount() function:

Function InvoiceAmount2(Product, Volume)

'Create object variable referring to table in worksheet Set Table = ThisWorkbook.Worksheets("Sheet2").Range("A2:D5")

'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) InvoiceAmount2 = Price * DiscountVolume + Price * _

(1 - DiscountPct) * (Volume - DiscountVolume)


'Calculate invoice without discount InvoiceAmount2 = Price * Volume End If

End Function

Note that Table is no longer an input parameter. Instead, the Set statement defines Table with a direct reference to the worksheet range. Although this method still works, the return value of the function will not be recalculated if you change a value in the lookup table. Excel does not realize that it needs to recalculate the function when a lookup table value changes, because it does not see that the table is used by the function.

Excel only recalculates a UDF when it sees its input parameters change. If you want to remove the lookup table from the function parameters and still have the UDF recalculate automatically, you can declare the function to be volatile on the first line of the function, as shown here:

Function InvoiceAmount2(Product, Volume) Application.Volatile

Set Table = ThisWorkbook.Worksheets("Sheet2").Range("A2:D5")

However, you should be aware that this feature comes at a price. If a UDF is declared volatile, the UDF is recalculated every time any value changes in the worksheet. This can add a significant recalculation burden to the worksheet if the UDF is used in many cells.

0 0

Post a comment