Functions with no argument

Like Sub procedures, Function procedures need not have arguments. Excel, for example, has a few built-in functions that don't use arguments, including RAND, TODAY, and NOW. You can create similar functions.

This section contains examples of functions that don't use an argument.

CD- A workbook that contains these functions is available on the companion CD-ROM. The file ROM ¡s named '■■*■ no argument, xlsm.

Here's a simple example of a function that doesn't use an argument. The following function returns the userName property of the Application object. This name appears in the Options dialog box (General tab) and is stored in the Windows Registry.

Function User()

' Returns the name of the current user

User = Application.UserName End Function

When you enter the following formula, the cell returns the name of the current user (assuming that it's listed properly in the Registry):

Note When you use a function with no arguments in a worksheet formula, you must include a set of empty parentheses. This requirement is not necessary if you call the function in a VBA procedure, although including the empty parentheses does make it clear that you're calling a function.

To use this function in another procedure, you can assign it to a variable, use it in an expression, or use it as an argument for another function.

The following example calls the User function and uses the return value as an argument for the MsgBox statement. The concatenation operator (&) joins the literal string with the result of the User function.

Sub ShowUser()

MsgBox "Your name is " & User() End Sub

This example demonstrates how you can create a wrapper function that simply returns a property or the result of a VBA function. Following are three additional wrapper functions that take no argument.

Function ExcelDir() As String

' Returns the directory in which Excel is installed ExcelDir = Application.Path

End Function

Function SheetCount()

' Returns the number of sheets in the workbook

SheetCount = Application.Caller.Parent.Parent.Sheets.Count End Function

Function SheetName() ' Returns the name of the worksheet

SheetName = Application.Caller.Parent.Name End Function

Controlling Function Recalculation

When you use a custom function in a worksheet formula, when is it recalculated?

Custom functions behave like Excel's built-in worksheet functions. Normally, a custom function is recalculated only when it needs to be - which is only when any of the function's arguments are modified. You can, however, force functions to recalculate more frequently. Adding the following statement to a Function procedure makes the function recalculate whenever the sheet is recalculated. If you're using automatic calculation mode, a calculation occurs whenever any cell is changed.

Application.Volatile True

The Volatile method of the Application object has one argument (either True or False). Marking a Function procedure as volatile forces the function to be calculated whenever recalculation occurs for any cell in the worksheet.

For example, the custom StaticRand function can be changed to emulate Excel's RAND function using the Volatile method, as follows:

Function NonStaticRand() ' Returns a random number that ' changes with each calculation Application.Volatile True NonStaticRand = Rnd() End Function

Using the False argument of the Volatile method causes the function to be recalculated only when one or more of its arguments change as a result of a recalculation. (If a function has no arguments, this method has no effect.)

To force an entire recalculation, including nonvolatile custom functions, press Ctrl+Alt+F9. This key combination will, for example, generate new random numbers for the StaticRand function presented in this chapter._

Here's another example of a function that doesn't take an argument. I used to use Excel's RAND function to quickly fill a range of cells with values. But I didn't like the fact that the random numbers change whenever the worksheet is recalculated. So I remedied this by converting the formulas to values.

Then I realized that I could create a custom function that returned random numbers that didn't change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1. The custom function is as follows:

Function StaticRand()

' Returns a random number that doesn't ' change when recalculated

StaticRand = Rnd() End Function

If you want to generate a series of random integers between 0 and 1,000, you can use a formula such as this:

=INT(StaticRand()*1000)

The values produced by this formula never change when the worksheet is calculated normally. However, you can force the formula to recalculate by pressing Ctrl+Alt+F9

0 0

Post a comment