A function 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.

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):

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 must 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

Was this article helpful?

0 0

Post a comment