Sub Procedures versus Function Procedures Part-2

Sub Procedures versus Function Procedures Part-2

A function with one argument

This section describes a function for managers who need to calculate the accuracy in data entry by their Data Entry Executive. The calculations in this example are based on the following table:

Total Words	Error Range	Accuracy %
10000	0-99	100%
10000	100-199	99%
10000	200-299	98%
10000	300-399	97%
10000	400-499	96%
10000	500-599	95%

Note that the Accuracy Percentage is nonlinear and also depends on the daily Error. Employees who do less error are more accurate. You can calculate accuracy rate for various error number entered into a worksheet in several ways. If you’re not thinking too clearly, you can waste lots of time and come up with a lengthy formula such as this one:

                =IF(AND(A1>=0,A1<=99.99),100%,

IF(AND(A1>=100,A1<=199.99),99%,

IF(AND(A1>=200,A1<=299.99),98%,

IF(AND(A1>=300,A1<=399.99),97%,

IF(AND(A1>=400,A1<=499.99),96%,

IF(AND(A1>=500,A1<=599.99),95%,0%))))))

A better (non-VBA) approach is to use a lookup table function to compute the accuracy rate. For example, the following formula uses LOOKUP to retrieve the accuracy rate from a range named Table and multiplies that value by the value in cell B11.

Yet another approach (which eliminates the need to use a lookup table) is to create a custom function such as the following:

Function AccuracyPerc(Errors)

Const Tier1 = 1

Const Tier2 = 0.99

Const Tier3 = 0.98

Const Tier4 = 0.97

Const Tier5 = 0.96

Const Tier6 = 0.95

Const Tier7 = 0#

' Calculates Accuracy Percentage

Select Case Errors

Case 0 To 99.99: AccuracyPerc = Tier1

Case 100 To 199.99: AccuracyPerc = Tier2

Case 200 To 299.99: AccuracyPerc = Tier3

Case 300 To 399.99: AccuracyPerc = Tier4

Case 400 To 499.99: AccuracyPerc = Tier5

Case 500 To 599.99: AccuracyPerc = Tier6

Case Is >= 600: AccuracyPerc = Tier7

End Select

End Function

Use arguments, not cell references

All ranges that are used in a custom function should be passed as arguments. Consider the following function, which returns the value in A1, multiplied by 2:

Function DoubleCell()

DoubleCell = Range(“A1”) * 2

End Function

Although this function works, at times, it may return an incorrect result. Excel’s calculation engine can’t account for ranges in your code that aren’t passed as arguments. Therefore, in some cases, all precedents may not be calculated before the function’s value is returned. The DoubleCell function should be written as follows, with A1 passed as the argument:

Function DoubleCell(cell)

DoubleCell = cell * 2

End Function

A function with two arguments

In India there are many States and they collecting revenue from employee as Professional Tax. Each and Every State has different Tax Slab or Tax Range. Below is Tax Slab for Andhra Pradesh and Maharashtra. Now our goal is to create a custom function for both the state

Maharashtra				Karnataka
From	To	Amount		From	To	Amount
0	5000	0		0	10000	0
5000.01	10000	175		10000.01	15000	150
10000.01	99999999	200		15000.01	99999999	200

'' Prof Tax

Function PT(Salary As Integer, State As String)

   

If State = "Maharashtra" Then

    If Salary > 10000 Then

        PT = 200

            ElseIf Salary > 5000 Then

        PT = 175

            Else

        PT = 0

    End If

End If

If State = "Karnataka" Then

    If Salary > 15000 Then

        PT = 200

            ElseIf Salary > 10000 Then

        PT = 150

            Else

        PT = 0

    End If

End If

End Function

A function with an array argument

A Function procedure also can accept one or more arrays as arguments, process the array(s), and return a single value. The array can also consist of a range of cells. The following function accepts an array as its argument and returns the sum of its elements:

Function AddArray(List) As Double

Dim Item As Variant

AddArray = 0

For Each Item In List

If WorksheetFunction.IsNumber(Item) Then _

AddArray = AddArray + Item

Next Item

End Function

Excel’s ISNUMBER function checks to see whether each element is a number before adding it to the total. Adding this simple error-checking statement eliminates the type-mismatch error that occurs when you try to perform arithmetic with something other than a number.

A function with optional arguments

Many of Excel’s built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its syntax is

LEFT(text,num_chars)

The first argument is required, but the second is optional. If the optional argument is omitted, Excel assumes a value of 1. Therefore, the following two formulas return the same result:

=LEFT(A1,1)

=LEFT(A1)

The custom functions that you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument’s name with the keyword Optional. In the argument list, optional arguments must appear after any required arguments.

Following is a simple function example that returns the user’s name. The function’s argument is optional.

Function User(Optional UpperCase As Variant)

If IsMissing(UpperCase) Then UpperCase = False

User = Application.UserName

If UpperCase Then User = UCase(User)

End Function

If the argument is False or omitted, the user’s name is returned without any changes. If the argument is True, the user’s name is converted to uppercase (using the VBA UCase function).

A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax:

SUM(number1,number2...)

The first argument is required, but you can specify as many as 254 additional arguments.

Following is a function that can have any number of single-value arguments. (It doesn’t work with multicell range arguments.) It simply returns the sum of the arguments.

Function SimpleSum(ParamArray arglist() As Variant) As Double

For Each arg In arglist

SimpleSum = SimpleSum + arg

Next arg

End Function

To modify this function so that it works with multicell range arguments, you need to add another loop, which processes each cell in each of the arguments:

Function SimpleSum(ParamArray arglist() As Variant) As Double

Dim cell As Range

For Each arg In arglist

For Each cell In arg

SimpleSum = SimpleSum + cell

Next cell

Next arg

End Function