G
Guest
I need to put an optional argument in one of my sub procedures. How do I do
that?
that?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
look in VBA Help and search for "Function". You will find a complete
description of how to do that.
description of how to do that.
G
Guest
if it were that easy, I would not have posted. I've searched using
"function", "UDF", "user defined function", "optional arguments", etc. and I
either get no results, or I get is a list of vba functions.
"function", "UDF", "user defined function", "optional arguments", etc. and I
either get no results, or I get is a list of vba functions.
G
Guest
it really is that easy, searching for "Function Statememt":
Function Statement
Declares the name, arguments, and code that form the body of a Function
procedure.
Syntax
[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
The Function statement syntax has these parts:
Part Description
Public Optional. Indicates that the Function procedure is accessible to all
other procedures in all modules. If used in a module that contains an Option
Private, the procedure is not available outside the project.
Private Optional. Indicates that the Function procedure is accessible only
to other procedures in the module where it is declared.
Friend Optional. Used only in a class module. Indicates that the Function
procedure is visible throughout the project, but not visible to a controller
of an instance of an object.
Static Optional. Indicates that the Function procedure's local variables are
preserved between calls. The Static attribute doesn't affect variables that
are declared outside the Function, even if they are used in the procedure.
name Required. Name of the Function; follows standard variable naming
conventions.
arglist Optional. List of variables representing arguments that are passed
to the Function procedure when it is called. Multiple variables are separated
by commas.
type Optional. Data type of the value returned by the Function procedure;
may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not
currently supported), Date, String, or (except fixed length), Object,
Variant, or any user-defined type.
statements Optional. Any group of statements to be executed within the
Function procedure.
expression Optional. Return value of the Function.
The arglist argument has the following syntax and parts:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [=
defaultvalue]
Part Description
Optional Optional. Indicates that an argument is not required. If used, all
subsequent arguments in arglist must also be optional and declared using the
Optional keyword. Optional can't be used for any argument if ParamArray is
used.
ByVal Optional. Indicates that the argument is passed by value.
ByRef Optional. Indicates that the argument is passed by reference. ByRef is
the default in Visual Basic.
ParamArray Optional. Used only as the last argument in arglist to indicate
that the final argument is an Optional array of Variant elements. The
ParamArray keyword allows you to provide an arbitrary number of arguments. It
may not be used with ByVal, ByRef, or Optional.
varname Required. Name of the variable representing the argument; follows
standard variable naming conventions.
type Optional. Data type of the argument passed to the procedure; may be
Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not
currently supported) Date, String (variable length only), Object, Variant, or
a specific object type. If the parameter is not Optional, a user-defined type
may also be specified.
defaultvalue Optional. Any constant or constant expression. Valid for
Optional parameters only. If the type is an Object, an explicit default value
can only be Nothing.
Remarks
If not explicitly specified using Public, Private, or Friend, Function
procedures are public by default. If Static isn't used, the value of local
variables is not preserved between calls. The Friend keyword can only be used
in class modules. However, Friend procedures can be accessed by procedures in
any module of a project. A Friend procedure does't appear in the type library
of its parent class, nor can a Friend procedure be late bound.
Caution Function procedures can be recursive; that is, they can call
themselves to perform a given task. However, recursion can lead to stack
overflow. The Static keyword usually isn't used with recursive Function
procedures.
All executable code must be in procedures. You can't define a Function
procedure inside another Function, Sub, or Property procedure.
The Exit Function statement causes an immediate exit from a Function
procedure. Program execution continues with the statement following the
statement that called the Function procedure. Any number of Exit Function
statements can appear anywhere in a Function procedure.
Like a Sub procedure, a Function procedure is a separate procedure that can
take arguments, perform a series of statements, and change the values of its
arguments. However, unlike a Sub procedure, you can use a Function procedure
on the right side of an expression in the same way you use any intrinsic
function, such as Sqr, Cos, or Chr, when you want to use the value returned
by the function.
You call a Function procedure using the function name, followed by the
argument list in parentheses, in an expression. See the Call statement for
specific information on how to call Function procedures.
To return a value from a function, assign the value to the function name.
Any number of such assignments can appear anywhere within the procedure. If
no value is assigned to name, the procedure returns a default value: a
numeric function returns 0, a string function returns a zero-length string
(""), and a Variant function returns Empty. A function that returns an object
reference returns Nothing if no object reference is assigned to name (using
Set) within the Function.
The following example shows how to assign a return value to a function named
BinarySearch. In this case, False is assigned to the name to indicate that
some value was not found.
Function BinarySearch(. . .) As Boolean
.. . .
' Value not found. Return a value of False.
If lower > upper Then
BinarySearch = False
Exit Function
End If
.. . .
End Function
Variables used in Function procedures fall into two categories: those that
are explicitly declared within the procedure and those that are not.
Variables that are explicitly declared in a procedure (using Dim or the
equivalent) are always local to the procedure. Variables that are used but
not explicitly declared in a procedure are also local unless they are
explicitly declared at some higher level outside the procedure.
Caution A procedure can use a variable that is not explicitly declared in
the procedure, but a naming conflict can occur if anything you defined at the
module level has the same name. If your procedure refers to an undeclared
variable that has the same name as another procedure, constant, or variable,
it is assumed that your procedure refers to that module-level name.
Explicitly declare variables to avoid this kind of conflict. You can use an
Option Explicit statement to force explicit declaration of variables.
Caution Visual Basic may rearrange arithmetic expressions to increase
internal efficiency. Avoid using a Function procedure in an arithmetic
expression when the function changes the value of variables in the same
expression.
Function Statement
Declares the name, arguments, and code that form the body of a Function
procedure.
Syntax
[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
[statements]
[name = expression]
[Exit Function]
[statements]
[name = expression]
End Function
The Function statement syntax has these parts:
Part Description
Public Optional. Indicates that the Function procedure is accessible to all
other procedures in all modules. If used in a module that contains an Option
Private, the procedure is not available outside the project.
Private Optional. Indicates that the Function procedure is accessible only
to other procedures in the module where it is declared.
Friend Optional. Used only in a class module. Indicates that the Function
procedure is visible throughout the project, but not visible to a controller
of an instance of an object.
Static Optional. Indicates that the Function procedure's local variables are
preserved between calls. The Static attribute doesn't affect variables that
are declared outside the Function, even if they are used in the procedure.
name Required. Name of the Function; follows standard variable naming
conventions.
arglist Optional. List of variables representing arguments that are passed
to the Function procedure when it is called. Multiple variables are separated
by commas.
type Optional. Data type of the value returned by the Function procedure;
may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not
currently supported), Date, String, or (except fixed length), Object,
Variant, or any user-defined type.
statements Optional. Any group of statements to be executed within the
Function procedure.
expression Optional. Return value of the Function.
The arglist argument has the following syntax and parts:
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [=
defaultvalue]
Part Description
Optional Optional. Indicates that an argument is not required. If used, all
subsequent arguments in arglist must also be optional and declared using the
Optional keyword. Optional can't be used for any argument if ParamArray is
used.
ByVal Optional. Indicates that the argument is passed by value.
ByRef Optional. Indicates that the argument is passed by reference. ByRef is
the default in Visual Basic.
ParamArray Optional. Used only as the last argument in arglist to indicate
that the final argument is an Optional array of Variant elements. The
ParamArray keyword allows you to provide an arbitrary number of arguments. It
may not be used with ByVal, ByRef, or Optional.
varname Required. Name of the variable representing the argument; follows
standard variable naming conventions.
type Optional. Data type of the argument passed to the procedure; may be
Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not
currently supported) Date, String (variable length only), Object, Variant, or
a specific object type. If the parameter is not Optional, a user-defined type
may also be specified.
defaultvalue Optional. Any constant or constant expression. Valid for
Optional parameters only. If the type is an Object, an explicit default value
can only be Nothing.
Remarks
If not explicitly specified using Public, Private, or Friend, Function
procedures are public by default. If Static isn't used, the value of local
variables is not preserved between calls. The Friend keyword can only be used
in class modules. However, Friend procedures can be accessed by procedures in
any module of a project. A Friend procedure does't appear in the type library
of its parent class, nor can a Friend procedure be late bound.
Caution Function procedures can be recursive; that is, they can call
themselves to perform a given task. However, recursion can lead to stack
overflow. The Static keyword usually isn't used with recursive Function
procedures.
All executable code must be in procedures. You can't define a Function
procedure inside another Function, Sub, or Property procedure.
The Exit Function statement causes an immediate exit from a Function
procedure. Program execution continues with the statement following the
statement that called the Function procedure. Any number of Exit Function
statements can appear anywhere in a Function procedure.
Like a Sub procedure, a Function procedure is a separate procedure that can
take arguments, perform a series of statements, and change the values of its
arguments. However, unlike a Sub procedure, you can use a Function procedure
on the right side of an expression in the same way you use any intrinsic
function, such as Sqr, Cos, or Chr, when you want to use the value returned
by the function.
You call a Function procedure using the function name, followed by the
argument list in parentheses, in an expression. See the Call statement for
specific information on how to call Function procedures.
To return a value from a function, assign the value to the function name.
Any number of such assignments can appear anywhere within the procedure. If
no value is assigned to name, the procedure returns a default value: a
numeric function returns 0, a string function returns a zero-length string
(""), and a Variant function returns Empty. A function that returns an object
reference returns Nothing if no object reference is assigned to name (using
Set) within the Function.
The following example shows how to assign a return value to a function named
BinarySearch. In this case, False is assigned to the name to indicate that
some value was not found.
Function BinarySearch(. . .) As Boolean
.. . .
' Value not found. Return a value of False.
If lower > upper Then
BinarySearch = False
Exit Function
End If
.. . .
End Function
Variables used in Function procedures fall into two categories: those that
are explicitly declared within the procedure and those that are not.
Variables that are explicitly declared in a procedure (using Dim or the
equivalent) are always local to the procedure. Variables that are used but
not explicitly declared in a procedure are also local unless they are
explicitly declared at some higher level outside the procedure.
Caution A procedure can use a variable that is not explicitly declared in
the procedure, but a naming conflict can occur if anything you defined at the
module level has the same name. If your procedure refers to an undeclared
variable that has the same name as another procedure, constant, or variable,
it is assumed that your procedure refers to that module-level name.
Explicitly declare variables to avoid this kind of conflict. You can use an
Option Explicit statement to force explicit declaration of variables.
Caution Visual Basic may rearrange arithmetic expressions to increase
internal efficiency. Avoid using a Function procedure in an arithmetic
expression when the function changes the value of variables in the same
expression.
B
Brendan Reynolds
Option Compare Database
Option Explicit
Public Function Optional1(ByVal RequiredArg As String, Optional ByVal
OptionalArg As String) As String
Debug.Print RequiredArg
If OptionalArg = vbNullString Then
Debug.Print "Either you didn't enter any value for the optional
argument, or you entered an empty string - I can't tell which"
Else
Debug.Print OptionalArg
End If
End Function
Public Function Optional2(ByVal RequiredArg As String, Optional ByVal
OptionalArg As Variant)
Debug.Print RequiredArg
If IsMissing(OptionalArg) Then
Debug.Print "You didn't enter any value for OptionalArg"
Else
Debug.Print OptionalArg
End If
End Function
On my system (Access 2003) the relevent help topic is titled 'Understanding
named and optional arguments'. Make sure you start your search in the VBA
editor, not in the main Access window.
Option Explicit
Public Function Optional1(ByVal RequiredArg As String, Optional ByVal
OptionalArg As String) As String
Debug.Print RequiredArg
If OptionalArg = vbNullString Then
Debug.Print "Either you didn't enter any value for the optional
argument, or you entered an empty string - I can't tell which"
Else
Debug.Print OptionalArg
End If
End Function
Public Function Optional2(ByVal RequiredArg As String, Optional ByVal
OptionalArg As Variant)
Debug.Print RequiredArg
If IsMissing(OptionalArg) Then
Debug.Print "You didn't enter any value for OptionalArg"
Else
Debug.Print OptionalArg
End If
End Function
On my system (Access 2003) the relevent help topic is titled 'Understanding
named and optional arguments'. Make sure you start your search in the VBA
editor, not in the main Access window.
G
Guest
thank you
Brendan Reynolds said:Option Compare Database
Option Explicit
Public Function Optional1(ByVal RequiredArg As String, Optional ByVal
OptionalArg As String) As String
Debug.Print RequiredArg
If OptionalArg = vbNullString Then
Debug.Print "Either you didn't enter any value for the optional
argument, or you entered an empty string - I can't tell which"
Else
Debug.Print OptionalArg
End If
End Function
Public Function Optional2(ByVal RequiredArg As String, Optional ByVal
OptionalArg As Variant)
Debug.Print RequiredArg
If IsMissing(OptionalArg) Then
Debug.Print "You didn't enter any value for OptionalArg"
Else
Debug.Print OptionalArg
End If
End Function
On my system (Access 2003) the relevent help topic is titled 'Understanding
named and optional arguments'. Make sure you start your search in the VBA
editor, not in the main Access window.