Building Complex User-defined Functions with Visual Basic Applications in Excel

Just by taking advantage of the above concepts, one can make really useful user- defined functions in Excel suitable for academic, engineering, office and research applications. In this third part, we will show how we can make user-defined functions for developing more complex applications, such as those found in the engineering and technological sectors.

If you have not read the previous tutorials (parts one and two), it is highly recommended that you read those first, as this article assumes you know the basics already. If you are ready, let's get started.

Using Built-in Functions in Visual Basic User-defined Source Code

If you are programming your own user-defined functions, you may find it complex to create new lines of code to suit every application. If a Microsoft built-in function can do that job, then it would be probably easier on your part to create complex functions consisting of both customized functions that you have written and built-in functions from Excel.

In this document from Microsoft it is shown that it is possible to use Excel worksheet functions in Visual Basic, although this Microsoft documentation does not provide in-depth examples.

To incorporate built-in functions in your user-defined VB program, use this syntax:

Application.WorksheetFunction.BuiltinFunction(variables)

It tells VB that we are going to use built-in functions in the calculations. In order to understand this, consider a practical application. Say, for example, that we are going to calculate the %CV. This is the "coefficient of variation" in statistics which is a measurement of spread or variation. Mathematically, this is equivalent to:

%CV = Sigma / Mean x 100%

When Sigma is small as compared to the mean, the %CV is small also, and the process is said to be stable. "Sigma" is the standard deviation of the samples, while "mean" is the average of the samples. %CV is commonly used in the manufacturing/product engineering sector as a measurement of product parameter/process stability.

Building the Function

As we did in the first and second parts of this tutorial, we will first declare the variables:

Let:

CV = %coefficient of variation

Mean = average of the numerical samples

Standarddeviation = standard deviation of the samples

Numbers = the numerical data or the samples

By following the defined formula above, the resulting Visual Basic statement for this user-defined function is this:

Function CV(numbers As Range)

mean = Application.WorksheetFunction.Average(numbers)

standarddeviation = Application.WorksheetFunction.StDev(numbers)

CV = (standarddeviation / mean) * 100

End Function

It looks straightforward and simple. First we define the function "CV," which consists of "numbers" as Range. Range means that the input data is not a single cell, but goes over two or more cells in an Excel spreadsheet. Examples of range in MS Excel are A5:A8, B10:B12, and so forth. This is very useful if you have a lot of data to be analyzed.

Then, we make use of the Application.WorksheetFunction statement to compute the mean and standard deviation of this data.

Finally, once the standarddeviation and mean are computed, CV can also be computed using its mathematical definition. See the screen shot below for the sample implementation of the %CV user-define function:

Using Worksheet Cell Ranges inside Visual Basic Statement

One of the advanced uses of user-defined functions is to be able to grab data from an actual MS Worksheet and use that in the Visual Basic computation. This is highly useful if you are working with lots of data tables or databases, from which you need to extract some important information for further computation.

The Visual Basic command that we will be using is this:

Set Rangename = Worksheets("Sheetname").Range("xx:xx")

To explain how the command works, the ones in italics are the variables of the statement. For example you can select any name for "Rangename," the same as with "Sheetname." However, note that "xx:xx" is the actual table range in the Excel spreadsheet.

Sheetname is where the table or database is located. You can perform calculations in that sheet or in the other sheets.

Let's illustrate this using a classic example in academics. For example, you are a college professor in a certain university that uses a rating system of 1.0 (excellent) to 3 (passed). But when grades are computed as a whole, they need to conform to a scale of 70 to 100 in which 70 is "passing" and 100 is "excellent."

Although you can directly use VLOOKUP in this case, it can be troublesome to manage, especially if you have a different class using different spreadsheets but the same rating system. The more data you handle, the more your grade computations will be prone to error. So in this case, we will make use of user-defined functions to simplify the calculations.

We will let:

Equivalentscore = the name of the function that will convert the rating system in 1.0 to 3.0 to the 70 to 100 scale.

Rating = the variable input from the user, which is from the 1.0 to 3.0 rating scale.

Sheet1 = the name of the worksheet containing the database or Excel table.

a2:b6 = the cell range of the database or Excel data table.

Okay, the Visual Basic script that will capture the data table below and the grading requirement looks like this:

Function equivalentscore(rating)

Set DataRange = Worksheets("Sheet1").Range("a2:b6")

equivalentscore = Application.WorksheetFunction.VLookup(rating, DataRange, 2, True)

End Function

First, we define the function name, which is usually the first thing you need to do in any user-defined function. Then the function will define the table (in cell ranges) found in the Excel worksheet. Here, we make use of the Sheet name and the ranges (a2: b6); see the screen shot.

Finally this line:

equivalentscore = Application.WorksheetFunction.VLookup(rating, DataRange, 2, True)

computes the equivalent rating of 1.0 to 3.0 rating scale to 70 to 100. The part that says VLookup(rating, DataRange, 2, True) is a normal built-in Excel function which looks for "rating" data in cell "a2:b6," defined by datarange.

Supported Functions and Other Important Stuff

Not all built-in functions are supported in the Visual Basic programming environment to be used for user-defined functions. Microsoft provides a list of those functions that can be used in Visual Basic:

http://msdn.microsoft.com/en-us/library/aa272490%28office.10%29.aspx

Also, bear in mind that all examples illustrated from part one through part three of this tutorial use at least Excel 2002.

If you happen to run a user-defined function but get an error, Visual Basic will automatically switch to "debug" mode and will highlight the faulty syntax in red. If this happens, you cannot close or continue running the function.

The suggested solution is to click "Run" and then "Reset." This should be done in the MS Visual Basic editor. This should stop the "debug" mode. See the screen shot below ("reset" is highlighted):

Lastly, to make your user-defined function available to other workbooks, you need to save it as "Microsoft Excel Add - in." Then when you open a workbook not containing those functions, simply go to Tools -> Addins -> "Browse," find the add-in file, add it, and then press OK.

You can now start using those user-defined functions you have made before without creating them again in the new workbook.