Skip Navigation LinksHome > Categories > Code from a Category

User-defined Functions using Visual Basic Applications in Excel



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2095
Add Date: 10/04/2012
In this article we will learn User-defined Functions using Visual Basic Applications in Excel
Microsoft Excel has a lot of built-in functions aimed at increasing the productivity of any office worker. “Built-in 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 “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.

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.

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:

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.
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 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().

Happy Prrogramming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.