Clear function arguments

[smk23]

I have some optional arguments in a function:

Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
As Long) As Long

This is called with the following line:

mlngPatientID = InsertRecord("usp_NewPatient_Insert")

The second optional argument is not passed. In the function, I check for a
value in the 2nd argument with:

If Not IsNull(brSecondaryKeyValue) Then

This is executing as if there is a value in brSecondaryKeyValue. How do I
clear this value? Do I set the Object (it's not an object), or make it equal
to zero or null?

Thanks again!!

 

[Duane Hookom]

What value do you get if you add this line immediately following the "Public
Function..."
MsgBox "brSecondaryKeyValue: " & brSecondaryKeyValue
I expect since you have declared it as Long, it will default to 0 unless you
provide a default in the function line. If you need to determine if a value
was passed in you could check for 0 or possibly set the default to an
unrealistic value like -999999 and then check for this value.

 

[Marshall Barton]

I have some optional arguments in a function:

Public Function InsertRecord(strSP As String, Optional brSecondaryKeyValue
As Long) As Long

This is called with the following line:

mlngPatientID = InsertRecord("usp_NewPatient_Insert")

The second optional argument is not passed. In the function, I check for a
value in the 2nd argument with:

If Not IsNull(brSecondaryKeyValue) Then

This is executing as if there is a value in brSecondaryKeyValue. How do I
clear this value? Do I set the Object (it's not an object), or make it equal
to zero or null?

In addition to Duane's explanation of using the arguments
default value, if you can live with declaring the optional
argument as Variant, then you could use the IsMissing
funtion.

 

[Klatuu]

Yes, the only really reliable way to use optional arguments is to declare
them as Variant. No other data type as a "missing" bit.

 

[John Nurick]

IME optional arguments always work reliably provided you remember that
when an optional argument is omitted from the call, it is still passed
to the called procedure but with a default value.

So if a procedure is declared as
Sub Foo(Optional X As Long)
and called as
Foo
it will receive the default value of a VBA Long, namely 0. For a
different default value, include it in the declaration:
Sub Foo(Optional X As Long = -1)

Variants work slightly differently. If you declare an optional argument
as Variant and don't explicitly set a default value, then and only then
you can use IsMissing() to detect whether or not the the calling code
passed a value.

 

[Klatuu]

John,

You are correct. That is why passing optional arguments with Variant is
more reliable. If you have a long identified in your function as an
argument, you don't know whether the 0 was passed or it is the default value.

 

[John Nurick]

I think we must have different criteria for reliability.<g>

John,

You are correct. That is why passing optional arguments with Variant is
more reliable. If you have a long identified in your function as an
argument, you don't know whether the 0 was passed or it is the default value.

 

[Klatuu]

define reliable

I think we must have different criteria for reliability.<g>