Writing Functions Manually

Without a doubt, using the formula extractor makes creating functions easy. But it only works if you have existing functions already written. For those times when a formula doesn’t already exist, you have to write the custom function from scratch. Luckily, if you are adept at writing formulas, then writing custom functions is not much different.

Writing functions manually has two additional requirements compared to writing formulas. The first is that you have to declare the function and the second is that you have to create the argument list. Declaring the function is pretty simple. In Basic syntax, use the Function keyword followed by the function name. Crystal syntax is the same except that it doesn’t require specifying the function name. Luckily for us, when you create a new function, Crystal Reports gives you a head start by creating a code template with the basic syntax already typed in.

Here is the code template for Crystal syntax:

Function()

All you need is the Function keyword and nothing else. You can write the programming logic just as you do with formulas.

Here is the code template using Basic syntax:

Function DaysToShip()
DaysToShip=
End Function

Basic syntax uses the Function keyword to start the code block and the End Function keyword to finish it. It also gives you the code to return the value back to the calling formula.

Declaring Function Arguments

Arguments are how you pass external data from a formula to the function. The arguments for a custom function are listed on the same line as the function declaration and they have two rules. The first is that they must be enclosed in a set of matching parentheses and the second is that multiple arguments are separated with commas.

In Crystal syntax the function declaration would look like this:

Function(DateTime BeginDate, DateTime EndDate)

In Basic syntax the function declaration would look like this:

Function DaysToShip(BeginDate As DateTime, EndDate As DateTime)

Function arguments have a unique feature that variables don’t have: they can be declared as optional. This means that when a formula calls a function, it doesn’t have to pass a value for every argument. When an optional argument isn’t passed a value, it uses a default value instead. This insures that every argument has a value even though it may not have come from the formula that called it.

To declare an argument as optional, use the Optional keyword before the argument declaration and specify the default value after the declaration.

In Basic syntax, declaring optional arguments looks like this:

Function ShowName(LastName As String, Optional FirstName As String=””)

In Crystal syntax, optional arguments look like this:

Function (StringVar LastName, Optional StringVar FirstName :=””)

After making the function declaration and defining the arguments, the final step is typing in the custom function’s code. As I mentioned before, this is no different than writing a formula. However, there is one exception for Basic syntax. When returning a value from a function, you have to assign the return value to the function’s name. For example, the following code listing assigns the result to the function name DaysToShip.

Function DaysToShip(BeginDate As DateTime, EndDate As DateTime)
DaysToShip=EndDate – BeginDate + 1
End Function