Using Conditionals with User-defined Functions in Visual Basic Applications in Excel
(Page 1 of 4 )
In the first part of this three-part series I introduced you to building user-defined functions for Excel with a very simple example. You can build a much more powerful and versatile application with Visual Basic, however, when you use conditional statements. I will be demonstrating such an application in this second part of the series.
Let’s continue our tutorial on making user-defined functions in MS Excel. In the first part, we discussed the programming steps required to make user-defined functions. We covered the programming syntax, and even provided some basic sample code illustrating a very simple user-defined functions application.
In this article, we will go in-depth by examining other important aspects of how to make user-defined functions in Excel. This comprises a little more advanced techniques, such as implementing conditional statements within the Visual Basic code.
Okay, I'm going to assume you have already read the first part. Let’s get started.
Developing conditional statements
Conditional statements are very useful for creating meaningful programs, just as they would be in any other programming language. They add intelligence to applications and make it easier to get the work done.
Since MS Excel user-defined functions rely on the Visual Basic language, developing conditional statements is easy.
Let’s make a payroll application that will compute the total salary of a certain employee based on a performance bonus. An additional $3000 bonus (to be added to the monthly salary) will be given if the performance rating is above 90 percent; otherwise, if the rating is below 90 percent, he will just received a $500 bonus.
To make this work, we need to to express it mathematically. the following is the formula for the employee's total monthly income (salary + bonus):
Total monthly income = Monthly basic salary + Bonus, where Bonus is equal to $3000 if performance rating is above or equal to 90%, otherwise it is only $500 if it is below 90%.
As we did in the first part, we will declare the variables to be used in our Visual basic program.
Let:
totsalary = total monthly income that the employee will received based on his performance rating.
basic = monthly basic salary of the employee.
rating = % performance rating of the employee.
So the Visual Basic program implementing the above variables and formulas will be:
Function totsalary(basic, rating)
If rating >= 0.9 Then totsalary = 3000 + basic Else totsalary = 500 + basic
End Function
Next: Building the Function >>
More BrainDump Articles
More By Codex-M
