METBD 050Visual Basic – User Defined Function ExampleMETBD 050Visual Basic – User Defined Function ExampleYou can write your own functions to perform routine calculations. The following function performs the same tasks as the sub procedure above. Your function then works just like any other function in Excel. The function procedure begins with the word Function and ends with the line End Function. The final calculated value is saved to a variable that has the same name as the function. This is the value returned to the worksheet. The variables defined in parenthesis in the first line are the arguments for the function. The names of the variables can be used by the function.Problem Statement: A company pays hourly employees at a rate of 1.5 times their regular rate for all hours worked over 40 hours in a given week. Write a user-defined function computes the gross pay for a person based on the number of hours worked and the rate. Hours and Rate are arguments for the function.Flowchart:Function PayCalc(Hours, Rate)‘The following line allows for automatic recalculation of the‘each time the worksheet changes.Application.Volatile ‘Calculate pay If Hours > 40 Then Gross = Rate * 40 + 1.5 * Rate * (Hours - 40) Else Gross = Rate * Hours End If‘Print results PayCalc = Gross End FunctionHO-050-VBA-UserDefinedFunction-Pay.doc Page 1 of 211/14/04StartGet Hours and RateIs Hours>40?YesGross = 40 * Rate +1.5*Rate*(Hours-40)NoGross = Rate * HoursPrint GrossEndYou should get in the habit ofsketching out a flowchart beforebeginning to make your code.The spreadsheet below illustrates the use of the PayCalc function in a worksheet.HO-050-VBA-UserDefinedFunction-Pay.doc Page 2 of 211/14/04The order of the arguments is important and must match the argument list in the first line of the function procedure
View Full Document
Unlocking...