Microsoft Excel has a lot of builtin functions aimed at increasing the productivity of any office worker. “Builtin functions” means that these functions are shipped along with MS Excel installations, so by the time you have successfully installed the spreadsheet, these functions are readily available for use.
However, not all functions can satisfy or be customized to office needs, so an average Excel user cannot make a function to suit a customized application. That is what this tutorial is all about.
This article aim to increase the knowledge of ordinary and average Excel users so that they can create their own Excel function. This is commonly called a “userdefined function.”
With userdefined 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 builtin 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/enus/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.
Microsoft hides the source code behind their existing builtin 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 userdefined 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 userdefined 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 userdefined function requires you to take the following steps:
Open the Visual Basic Editor.
Code the function in the Visual Basic Language.
Save it.
Implement userdefined 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 builtin functions, I will resort to userdefined functions for more flexibility and speed. Of course, you have to write the code yourself.
As with any piece of programming code, userdefined functions in MS Excel also follow a set of rules when it comes to proper syntax.
Below is the standard syntax:
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 realworld examples.
We would like to make a userdefined 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 builtin 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 userdefined function in Visual Basic that will compute the cost automatically given the power rating (watts) of each home appliances.
First, just as in any programming language, we will define our variables.
In this exercise, we will be using the following variables
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:
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 builtin 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 userdefined function, namely bill().
Happy Prrogramming!!!
