Optional parameters in function

  • Thread starter Thread starter Randy K.
  • Start date Start date
R

Randy K.

I have a function with 6 required parameter and several optional parameters
and am having problems with either getting: "invalid use of Null" errors or
Missing errors once in function.

For Example:
Function test(p1 as string, p2 as integer,etc, optional p7 as
string,optional p8 as currency, etc)

When I call the function I use:
call test1(p1,p2,p3,p4,p5,p6) if I don't need to set any optional values.
With this I get "invalid use of Null"

If I removed the type of variable (making them variant) in the function,
then it starts into the function, but I have if-thens setup to create a
string for a SQL statement and when it comes to the first if-then, it bombs
and if I hover over the parameter list, all the optionals show "missing"
instead of Null.

What is the ideal way to setup and use a function like this?

TIA,
Randy
 
Here some tips that may help:

1. Variables of simple data types (such String, Integer and Currency)
cannot be assigned the value Null, but Variant variables can.

2. The IsMissing function does not work on simple data types because,
unlike Variants, they don't have a provision for a "missing" flag bit.
Because of this, the syntax for typed optional arguments allows you to
specify a default value. If the argument is omitted when the procedure is
called, then the argument will have this default value. If you don't
specify a default value and the argument is omitted when the procedure is
called, the argument will have the value which variables of that type are
normally initialized (such as zero or an empty string).
 
When calling the function and passing the arguments, if you pass a variant
where a typed (string, integer, etc) variant is expected, you will get a
compile error "ByRef argument type mismatch". If you are passing variants,
you must explicitly cast them to the data type the function is expecting.

If you have an optional variant and don't assign a default for it and you do
not pass that optional argument, you will get a Run-time error 13: Type
mismatch. You may assign the default to an empty string (optional vp6 as
variant = "") or a number (optional vp6 as variant = 0) or whatever your
function is expecting to be passed.

The following test code, with results, might help:

--------------------------------------------------------
Public Function test1(strP1 As String, intP2 As Integer, varP3 As Variant, _
Optional strP4 As String, Optional intP5 As Integer, Optional varP6 As
Variant = "")
test1 = CStr(strP1 & ", " & intP2 & ", " & varP3 & ", " & strP4 & ", " &
intP5 & ", " & varP6)
End Function
--------------------------------------------------------
Public Sub getTest1()
Dim strTest1 As String
Dim vp1, vp2, vp3, vp4, vp5, vp6 ' all variant
Dim strP1 As String
Dim intP2 As Integer
Dim varP3 As Variant
Dim strP4 As String
Dim intP5 As Integer
Dim varP6 As Variant

vp1 = 1
vp2 = 2
vp3 = 3
vp4 = 4
vp5 = 5
vp6 = 6

'strTest1 = test1(vp1, CInt(vp2), vp3, CStr(vp4), CInt(vp5), vp6)
' Compile Error: ByRef argument type mismatch

strTest1 = test1(CStr(vp1), CInt(vp2), vp3, CStr(vp4), CInt(vp5), vp6)
Debug.Print strTest1
' 1, 2, 3, 4, 5, 6
strTest1 = test1(CStr(vp1), CInt(vp2), vp3, CStr(vp4), CInt(vp5))
Debug.Print strTest1
' 1, 2, 3, 4, 5,
strTest1 = test1(CStr(vp1), CInt(vp2), vp3, CStr(vp4))
Debug.Print strTest1
' 1, 2, 3, 4, 0,
strTest1 = test1(CStr(vp1), CInt(vp2), vp3)
Debug.Print strTest1
' 1, 2, 3, , 0,
strTest1 = test1(CStr(vp1), CInt(vp2), vp3, CStr(vp4), , vp6)
Debug.Print strTest1
' 1, 2, 3, 4, 0, 6
strTest1 = test1(CStr(vp1), CInt(vp2), vp3, , , vp6)
Debug.Print strTest1
' 1, 2, 3, , 0, 6

strP1 = "1"
intP2 = 2
varP3 = 3
strP4 = "4"
intP5 = 5
varP6 = "6"

strTest1 = test1(strP1, intP2, varP3, strP4, intP5, varP6)
Debug.Print strTest1
' 1, 2, 3, 4, 5, 6
strTest1 = test1(strP1, intP2, varP3, strP4, intP5)
Debug.Print strTest1
' 1, 2, 3, 4, 5,
strTest1 = test1(strP1, intP2, varP3, strP4)
Debug.Print strTest1
' 1, 2, 3, 4, 0,
strTest1 = test1(strP1, intP2, varP3)
Debug.Print strTest1
' 1, 2, 3, , 0,
strTest1 = test1(strP1, intP2, varP3, strP4, , varP6)
Debug.Print strTest1
' 1, 2, 3, 4, 0, 6
strTest1 = test1(strP1, intP2, varP3, , , varP6)
Debug.Print strTest1
' 1, 2, 3, , 0, 6
End Sub
 
Back
Top