Access has a host of built in conversion functions. You can get a list by opening Help and searching on the string "Conversion Functions". They tend to be fairly straightforward-the usual suspects: inches to feet, pounds to kilograms, etc, so I won't cover them here. Rather what we'll do is build our own simple custom conversion function, and from there we'll branch out to make things increasingly complex.

The first thing we need to do is to create a new module. From the database window click on Modules and then click on New. This will bring you to a fresh module that will allow you to write code. Note that a module is a container for one or more functions and subroutines. Functions and subroutines are both VBA (Visual Basic for Applications) code. The major difference is that subroutines simply do work through the execution of code; functions also do work, however at the end of the process a function can return a value.

If you do not see "Option Explicit" at the top of your new module, type it in as the first line (without the quotes). This line of code sets a requirement that all variables (something that can change) must be declared. What this offers is some protection against mistyping, which will make debugging (finding and solving errors in your code) easier. As a matter of fact, if Option Explicit doesn't show up automatically, you should change your settings so it does. From the code window choose Tools on the menu at the top of the screen, then choose Options. In the dialog box, click on the editor tab then select Require Variable declarations.

Now that we’ve got the issue of declaring variables out of the way, let’s next type our simple conversion function as follows:

Public Function CubitsToInches(dblCubits As Double) As Double
    CubitsToInches = 30 * dblCubits
End Function

This is not a very powerful or complex function as it stands. The real purpose here is to demonstrate how to create a function and then use it.

Let's pull this function apart line by line:

Public Function CubitsToInches(dblCubits As Double) As Double

The first word public deals with something programmers call "scope." Scope is a concept that details from where a function or a subroutine may be called. There are two primary choices: public or private. Public indicates that the code can be called from anyplace within the application. A private function may only be accessed from within the form, report or module where it resides. Since we may be calling this cubit conversion function from any place within our application (who knows when we may want to convert from cubits to inches?), we declare this a public function.

The next word is function. This indicates that the procedure will do some work AND return some value to the calling routine.

CubitsToInches is the name of our function. There are a few things to note. First, the function name is all one word: no spaces, dashes, asterisks, exclamation points, piping characters, emoticons : ) or any other non-alphanumeric characters. Function (and subroutine) names may contain only letters and numbers. Also note that our name clearly represents what the function does – it changes cubits to inches.

You don't have to use mixed case, but I recommend that you do because it makes both function and subroutine names easier to read. You may think that using mixed case in a function or subroutine name opens the door for errors while typing in the code, but VBA has a special feature that eliminates that problem. In VBA, when you type a function or subroutine name in other lines of code, you may type it in all lower (or upper) case (your choice). If you’ve typed the string correctly, VBA will automatically change the case to reflect the capitalization of the function or subroutine you’ve declared when you hit the enter key at the end of the line. This is a great feature that helps you to write consistent, error-free code.

Next we have (dblCubits as double). The items within the parenthesis represent arguments (or pieces of data) that the function needs as input in order to do its work. The first item, dblCubits, is a variable name, and the second item, as double, defines the type of the input variable. In our case, the function expects to get input in the form of the number of cubits to be converted; this variable is called dblCubits. The as double represents the type of data we expect as the incoming cubits value. You’ll note that I included the type of data in the variable name by attaching the abbreviation dbl at the beginning of the variable name. Double represents a very precise floating point number that extends out many decimal places. As it is a very precise number, it requires a relatively large amount of memory to process, but memory tends to be cheap these days. As a rule, I tend to define data as double if I need a decimal number. We certainly didn't choose an integer (whole number) because we may want to process fractions, for example to find out how many inches are 2.5 cubits.

This line of code closes with as double. This code defines the precision of the value returned by the function, which in this case is double. The next line is the one that does the work: CubitsToInches = dblCubits * 30

In order for our function to return a value, we must define the operation: the name of our function, followed by an equal sign, followed by the calculation. The value of cubits to be converted is input and passed to the variable dblCubits, where it is stored. This value is then multiplied (the asterisk is VBA's representation of multiplication) by the conversion value of inches per cubit.

So if we input 2, we'll get back 60. If we input 2.41, we’ll get back 72.3.

Where did the 30 come from? I simply looked at my arm and guessed! Remember, this is only a basic demonstration. Don’t worry-we'll work with much more complicated (and practical!) functions in future issues.

We’ve created this wonderful function -- how do we use it? It’s easy to test the function in the code window where you’ve been creating the code; simply type control + g. By that I mean while holding down the control key, type the letter "g". Typing these keys will cause a new window, labeled “immediate” to open. Within that window, type the following:

? CubitsToInches(22)

Then hit return. With this text, we’re asking the function to convert 22 cubits to inches. If everything goes according to plan, you'll see 660 on the next line. Time to declare victory!

This demonstration of a trivial conversion function was a simple introduction to the power of functions. There are many ways to use them -- we'll investigate this as we move forward.

Next month, we'll develop a more powerful and utilitarian function. We'll change it so that rather than using the length of my arm, the user will have the option of selecting another standard length (e.g. Andre the Giant’s arm or Mildred the Midget's arm). As a side note, please don't sic the PC police on me; that's the stage name she uses – wouldn't it be silly for Mildred to call herself Mildred the Vertically- Challenged?

This month we called a function and passed in parameters -- pieces of data that the function needs when it gets called. When we passed in these parameters some people call them arguments) we put them within parenthesis. You can see this in the testing example where we typed: ? CubitsToInches (22)

Sub routines also take parameters, however when you pass in the parameters to a sub-routine you do not put them within parenthesis – another little inconsistency from Microsoft to keep us guessing.