I am calling a specific piece of code several times therefore I would like to use optional parameters. I can write something like:
Public Sub main()
strA = "A"
'Calling the function
CalculateMe (strA)
End Sub
Public Sub CalculateMe(strA As String)
Set rs = DB.OpenRecordset("tbl_A")
rs.MoveFirst
Do Until rs.EOF
If rs.Fields(0) = strA Then
dblA = rs.fields(2).Value
End If
rs.MoveNext
Loop
End Sub
How can I change the function to hold more than 1 optional parameters?
Something like:
Public Sub main()
strA = "A"
strB = "B"
'Calling the function
CalculateMe (strA, strB)
more code
End Sub
Public Sub CalculateMe(Optional strA As String, Optional strB as String)
Set rs = DB.OpenRecordset("tbl_A")
rs.MoveFirst
Do Until rs.EOF
If rs.Fields(0).Value = strA And rs.Fields(1).Value = strB Then
dblA = rs.Fields(2).Value
End If
rs.MoveNext
Loop
End Sub
Following Pankaj Jaju's advice, I have managed to have it run by changing it to:
Public Sub main()
strA = "A"
strB = "B"
'Calling the function
dblA = CalculateMe (strA, strB)
End Sub
Public Function CalculateMe(Optional ByVal strA As String, Optional ByVal strB as String)
Set rs = DB.OpenRecordset("tbl_A")
rs.MoveFirst
Do Until rs.EOF
If rs.Fields(0).Value = strA And rs.Fields(1).Value = strB Then
dblA = rs.Fields(2).Value
End If
rs.MoveNext
Loop
End Sub
Now, how can I clear the value of an optional parameter? I will need this for some of the calculations. Something like:
Set strA = Nothing
Change your sub and add
ByVal
Instead of
CalculateMe(,strB)
you can useExcerpts from Chip Pearson's excellent explanation:
Rules governing the use of optional parameters:
Optional
keyword must be present to make a parameter optional.Variant
data type.IsMissing
function will work only with parameters declared asVariant
. It will returnFalse
when used with any other data type.Example
Here, both L1 and L2 are required but P1 and P2 are optional. Since both are
Variant
types, we can useIsMissing
to determine whether the parameter was passed in.IsMissing
returnsTrue
if theVariant
parameter is omitted, orFalse
if theVariant
parameter is included. If the data type of the optional parameter is any data type other thanVariant
,IsMissing
will returnFalse
.I'm not sure you really mean "optional". In your example, you're listing the args as optional, but you're still passing both arguments to the function.
In any case, here you pass both arguments:
If you want to pass only one of the arguments, then do like:
Or:
Otherwise, if you are always passing both arguments, then it doesn't make sense to have them
Optional
.