Using Conditionals with User-defined Functions in Visual Basic Applications in Excel

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

Building the Function

This looks very simple. To bring it to life, open your Excel payroll workbook, and then go to Tools -> Macro -> Visual Basic Editor.

The next thing you will see is the Microsoft Excel Visual Basic Editor. Click on “Insert -> Module.” See the screen shot below:

You will then see a blank sheet into which you will have to copy and paste the Visual Basic script. Finally click “Save.”

To go back to the spreadsheet, click “File” and then click “Close and Return to Microsoft Excel.” This should take you back to your original spreadsheet, where you have your payroll table.

To save the worksheet, click “File” and then “Save.” We are now ready to implement our user-defined function that will automatically compute the total monthly income of employees.

Implementing the Function

To see our user-defined function:

a. Go to “Insert.”

b. Then click on “Function”

c. Under “Or select a category,” select “User defined.”

d. In there, we can find the user-defined function we have just written.

The definition of the function is this:

=totsalary(basic, rating)

See the screenshot below on the implementation of the user-defined function;  note that Column A is the employee name. Columns B and C are the function variables, where Column B is the Basic salary and Column C is the employee performance rating. Finally, Column D is the one in which the user-defined function will be placed, which will then output the total monthly income earned based on performance rating and basic salary.

For the sake of clarity for this tutorial, I have used column E to reflect the actual formula I have placed in Column D, so that you can try it in your Excel workbook.

Note that we need only a single function to compute the total monthly income of these employees, instead of using multiple built-in functions, which could be complicated to do in most cases.

But what if we need to edit the user-defined function? It is simple. In your spreadsheet, just click “Insert” -> “Macro” -> “Visual Basic Editor,” and then, under Project –VBA Project on the left pane, look for “Module 1.” Double click it; you should then see your code.

See screenshot below (highlighted section):

Note that when you close the workbook and then re-open it, there is a warning dialog such as “This workbook contains macros.” Just enable it; it will be fine because you are the one creating those Visual Basic Scripts.

Getting a URL's Filename Path

So far our examples have been simple and straightforward. We will go deeper in our tutorial by implementing a more complicated application. Let’s say we would like to extract the file name path of the hyperlinked cell address in an Excel spreadsheet.

Example:

http://www.php-developer.org/comments/feed/ ? /comments/feed

http://www.php-developer.org/contact/ ? /contact

Of course, you can open them one by one and take out the domain in the browser address bar, but this is a very time-consuming process (especially if we're dealing with moer than 1,000 URLs). Let’s make use of user-defined functions to do this automatically.

The following will be the procedure:

Step 1: Extract the URL address of the hyperlink.

Step 2: Remove any spaces in the URL.

Step 3: Use a Mid Function (like the built-in Excel function) to extract the file path.

Based on these programming steps, below is the Visual Basic code:

Function filepath(hyperlinkedaddress)

extracturl = hyperlinkedaddress.Hyperlinks(1).Address

cleaner = Trim(extracturl)

charcount = Len(cleaner)

filepath = Mid(cleaner, 29, charcount - 28)

End Function

Let me give you a detailed explanation of the above Visual Basic code. First, the name of the user-defined function is “filepath.” The name of the variable to be used on the Excel sheet is “hyperlinkedaddress;” this contains the hyperlinked information. Then “extracturl” is a variable that receives the URL address of the “hyperlinkedaddress.” The URL address is extracted using this Visual Basic command:

hyperlinkedaddress.Hyperlinks(1).Address

So if the URL address is: “http://www.php-developer.org/contact/,” extracturl variable has this value. We will then remove any unnecessary spaces in the URL by using the Trim command.

The clean URL (without any spaces) will be assigned to the cleaner variable. We will then count the number of characters in the URL and assign the results to the charcount variable. The VB command is this:

charcount = Len(cleaner)

Finally, the filepath of the URL is then extracted with this command:

filepath = Mid(cleaner, 29, charcount - 28)

The Mid function works the same with Visual Basic as a built-in Excel function. The syntax for this is:

Mid (text, start_num, num_char)

This means that we are going to extract text from the cleaner variable starting with character 29, and the number of characters to be extracted is charcount - 28

Manually, say the value of the cleaner variable is:

http://www.php-developer.org/contact/

The total number of characters in this URL is: 37

The number of characters to be extracted is: 37-28 = 9 characters.

We will start extracting at 29th character, which is “/”.

Below is the Excel spreadsheet implementation screenshot of this user-defined function: