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.