Optional Function Variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning (at least it's the morning here),

I think this is a simple one, I just have never had to do this.

I have a function with optional input variables. How can I determine if the
user passed along the variable or not?

Isnull?

Thank you,

Daniel P
 
hi Daniel,
Good morning (at least it's the morning here),
Click to expand...
Where is your here?
I have a function with optional input variables. How can I determine if the
user passed along the variable or not?
Isnull?
Click to expand...
No, this is not possible, but also not necessary as optional parameters
must have default values:

Public Function|Sub Foo(
Bar1 As [Type],
Optional Bar2 As [Type] = [DefaultValue]
)

If Bar2 <> [DefaultValue]
' Parameter was used
Else
' Parameter was not used or default value was specified
End If

End Function|Sub

The value for [DefaultValue] cannot be a constant, which makes coding a
little bit nasty as you have to be carfully using it twice in your code.


mfG
--> stefan <--
 
Function MyFunction(Variable1 As String, Optional Variable2 As Variant)

If IsMissing(Variable2) Then
' no value was passed for variable 2
End If

End Function

Note that the variable must be declared as Variant for the IsMissing
function to work.

Another option, of course, is to set a default value. Then it might not
matter:

Function MyFunction(Variable1 As String, Optional Variable2 As Long = -100)


End Function
 
If the optional argument A has no default value specified then use
IsMissing(A) to determine if it was not specified.

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Stefan,
You may want to look in VBA Help for the IsMissing function.
--
Dave Hargis, Microsoft Access MVP


Stefan Hoffmann said:
hi Daniel,
Good morning (at least it's the morning here),
Click to expand...
Where is your here?
I have a function with optional input variables. How can I determine if the
user passed along the variable or not?
Isnull?
Click to expand...
No, this is not possible, but also not necessary as optional parameters
must have default values:

Public Function|Sub Foo(
Bar1 As [Type],
Optional Bar2 As [Type] = [DefaultValue]
)

If Bar2 <> [DefaultValue]
' Parameter was used
Else
' Parameter was not used or default value was specified
End If

End Function|Sub

The value for [DefaultValue] cannot be a constant, which makes coding a
little bit nasty as you have to be carfully using it twice in your code.


mfG
--> stefan <--
Click to expand...
 
*If* optional argumentA is a variant, otherwise IsMissing won't return a
reliable result.
 
hi Klatuu,
You may want to look in VBA Help for the IsMissing function.
Click to expand...
Ah, thanks for the hint.

I never use it as my optional parameters are normaly not a variant.



mfG
--> stefan <--
 
Back
Top