VBA Functions Advanced Options

Question

I call a specific piece of code several times, so 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 a function to store more than 1 optional parameters?

Sort of:

 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 the advice of Pankaj Jaju, I managed to run it 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 calculations. Sort of:

 Set strA = Nothing 

Answer 1

Change your sub and add ByVal

 Public Sub CalculateMe(Optional ByVal strA As String, Optional ByVal strB As String) 

Answer 2

I'm not sure what you really mean by "optional." In your example, you specify args as optional, but you pass both arguments to the function anyway.

In any case, here you pass both arguments:

 Public Sub main() strA = "A" strB = "B" 'Calling the function dblA = CalculateMe(strA, strB) more code End Sub 

If you want to pass only one of the arguments, do the following:

 dblA = CalculateMe(, strB) 

Or:

 dblA = CalculateMe(strA) 

Otherwise, if you always pass both arguments, then it makes no sense to have them Optional .

Answer 3

Instead of CalculateMe(,strB) you can use

 dblA = CalculateMe strB:="B" 

Answer 4

 Public Sub CalculateMe(Optional varA As Variant, Optional varB as Variant) 

Excerpts from Chip Pearson are a great explanation:

Rules governing the use of optional parameters:

• The Optional keyword must be present to make the parameter optional.
• The data type must be (but not required, see below) a Variant data type.
• The optional parameter must be at the end of the list parameter.
• The IsMissing function will only work with declared parameters like Variant . It will return False when used with any other data type.
• User types (UTDs) cannot be optional parameters.

Example

 Function Test(L1 As Long, L2 As Long, _ Optional P1 As Variant, Optional P2 As Variant) As String Dim S As String If IsMissing(P1) = True Then S = "P1 Is Missing." Else S = "P1 Is Present (P1 = " & CStr(P1) & ")" End If If IsMissing(P2) = True Then S = S & " " & "P2 Is Missing" Else S = S & " " & "P2 Is Present (P2 = " & CStr(P2) & ")" End If Test = S End Function 

Both L1 and L2 are required here, but P1 and P2 are optional. Since both are Variant types, we can use IsMissing to determine if a parameter has been passed. IsMissing returns True if the Variant parameter is omitted, or False if the Variant parameter is enabled. If the data type of the optional parameter is any data type other than Variant , IsMissing will return False .