Worked example

This example function is intended to work with the weeklysales worksheet of the SALESMAN.XLS w orkbook. Its purpose is to find the highest w eekly sales in the week_sales named range. We have used a function for this purpose b ecause w e require a value to b e returned: i.e., the highest v alue. The function has been named getMaxSales, and searches through all the cells in the week_sales range to find the largest value, which will be an integer. The function therefore, will return an integer value. No arguments are passed into this function. The function definition is shown in Listing 9.6, and the screenshot in Figure 9.4 displays the result of 402 when the function is executed by calling it in cell G28.

Listing 9.6 Function getMaxSales() -

Function getMaxSales() As Integer Dim maxSales As Integer Dim Cell As Object maxSales = 0 'assume the lowest possible For Each Cell In Range("Week_sales") If Cell > maxSales Then maxSales = Cell 'we've found a bigger sales End If Next getMaxSales = maxSales End Function

In the function definition, there are no arguments associated with it - the parentheses for the name are empty. However, the return type is an integer (i.e. a maximum value of sales).

The function works by declaring a variable called maxSales that is used to contain the largest number in the range week_sales. The initial value assigned to maxSales is 0, which is the smallest value that the actual maximum can be, and therefore has to be used initially for comparison with all cell values in the range week_sales. Each cell in the For loop is examined to see if its current value is > maxSales. This comparison is implemented using the statement If Cell > maxSales Then maxSales = Cell. This means that if a cell value is greater than the current value of maxSales then assign the contents of maxSales to Cell, otherwise leave the maxSales value unchanged. When the loop is completed, the contents of the variable maxSales will contain the maximum sales value in the range. Notice that after the loop is completed, then maxSales is assigned to the return value, i.e., the function named getMaxSales. We can see this function running using the worksheet weeklysales.

Figure 9.4 Screenshot of Function getMaxSales() Called at cell G28
+1 0

Post a comment