User-defined Functions using Visual Basic Applications in Excel

This series of tutorials aims to increase the knowledge of ordinary and average Excel users so that they can create their own Excel function. This is commonly called a “user-defined function.”

With user-defined functions, an Excel application developer is able to develop limitless applications that can save a lot of office time when compared to using Excel's built-in functions.

Okay, let’s get started.

Basic Requirements

Microsoft Excel relies on the Visual Basic programming language. So in order to make your own Excel function, you need some knowledge of that programming language.

The very good thing is that Visual Basic is not all that difficult, and there are a lot of resources out there that can help you with the learning process. Below is a link to some good resources to start with when you are learning Visual Basic.

http://msdn.microsoft.com/en-us/vbasic/ms789086.aspx

A detailed tutorial on Microsoft Visual Basic is beyond the scope of this article; I recommend that you refer to the standard Microsoft documentation for further information. Also in this article, very simple Visual basic commands will be discussed in order to enhance the tutorial.

In order to maximize what you learn from this tutorial series, it is recommended that you have at least Microsoft Excel 2002 installed on your computer. If you have the latest version, Microsoft Excel version 2007, it is also fine and the concepts will still apply.

Understanding Excel Function Programming Steps

Microsoft hides the source code behind their existing built-in functions in Excel. This is normal; they do not belong to open source technology where developers can freely share the source code.

Though this makes developing applications difficult, Microsoft does provide us with sets of tools to develop our own user-defined functions.

One of the most important of these sets of tools available in Microsoft Excel is the Visual Basic Editor.

It is very hard or impossible to create user-defined functions without the Visual Basic Editor. You can access the Visual Basic Editor basically in two ways. Your first option is to go to Tools -> Macro -> Visual Basic Editor. The quickest way to access it, however, is by right clicking on any existing worksheet tab. See the screen shot below:

Programming a user-defined function requires you to take the following steps: 

• Open the Visual Basic Editor.
  
• Code the function in the Visual Basic Language. 
  
• Save it.
  
• Implement user-defined functions in your existing Excel applications.

Simple, isn’t it? This is why, when I encounter certain difficult, repetitive and time- consuming applications with the use of Excel built-in functions, I will resort to user-defined functions for more flexibility and speed. Of course, you have to write the code yourself.

Programming Syntax

As with any piece of programming code, user-defined functions in MS Excel also follow a set of rules when it comes to proper syntax.

Below is the standard syntax:

Function Functionname(Variable)

<Visual basic source code of your function here>

End Function

It should start with the syntax “Function” and then end with the “End Function.”

This tells Visual Basic that we are running an Excel function. Concepts will be very hard to understand without real-world examples.

We would like to make a user-defined Excel function that will automatically compute the monthly electricity bill (in dollars) given the power rating of the home appliance, the number of appliances involved (units) and the number of hours in operation.

In classic Microsoft Excel using built-in functions, the following is the Excel table using the lengthy computational processes:

Note that there are a lot of computations involved, and it takes up a lot of computational space. We will attempt to write a user-defined function in Visual Basic that will compute the cost automatically given the power rating (watts) of each home appliances.

Basic Code Example

First, just as in any programming language, we will define our variables.

In this exercise, we will be using the following variables (do not worry; at the end of this exercise, there is a free Excel workbook containing these examples for downloading).

Let:

bill = the function name

wattrating = the power consumption/rating of a certain electrical home appliance

units = the number of electrical appliance involve.

We will also be defining our constants for this example:

Let:

30 = the number of operating days per month

0.11 = the cost of electricity (in cents) per Kilowatt hour consumption.

Second, we will write the formulas using those defined variables and constants

By analyzing the relationships, we can come up with the final formula for computing the electrical bill:

bill = ((wattrating * units * operatinghours * 30 * 0.11) / 1000)

Take note that it is divided by 1000 to convert watts to kilowatts. You can do things like this, especially if you perform the conversion of units inside the user- defined function to make things faster.

Third and last, we will write the final Visual Basic programming function.

Now that we have successfully written the formula to define the electric “Bill,” we will be writing the Visual Basic code.

Okay, select whichever way you choose to go to Visual Basic editor (see instructions on the second page of this article). Once the Visual Basic editor has been opened, go to INSERT -> MODULE, and then complete the code as shown below:

Function bill(wattrating, operatinghours, units)

bill = ((wattrating * units * operatinghours * 30 * 0.11) / 1000)

End Function

Then click the “Save” button.

This is what it looks like in the Visual basic editor/module:

Once the function has been saved, we can start using it. In order to use it, you can click the fx symbol besides the formula bar, and then under “Or select a category,” navigate to “User Defined.”

Find the function “bill;” you should see it there. Click on it, and you can now start using it just like any Excel built-in function. Note that when you click on the function, it highlights the variables needed for it, which are:

=bill(wattrating,operatinghours,units)

We will use our previous example to compute the monthly electricity bill automatically without resorting to lots of formulas and columnar computations. See below for the output:

The table now looks cleaner, as all columnar computations are now being handled by a single Excel user-defined function, namely bill().

The example Excel workbook illustrated in this spreadsheet is available for downloading at: http://www.php-developer.org/user-defined-functions-sample-excel-workbook/

We will continue with the second and third part for more examples including the advanced application.