24

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
Improve this question
2
  • I know this is an old question now, but are you trying to pass an array? Then loop through x items in the array? Commented May 30, 2016 at 18:06
  • 1
    No, I was not; only 2 string variables. I was not familiar with the syntax at the time and Pankaj Jaju help solving that. Changing the function to a subroutine and adding ByVal enabled to perform what I was trying to do. Ignore any other posts/comments; they are only confusing. Commented May 31, 2016 at 16:03

4 Answers 4

Reset to default
22

Change your sub and add ByVal

Public Sub CalculateMe(Optional ByVal strA As String, Optional ByVal strB As String)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

At runtime, how do you tell whether the optional parameter was supplied?
@ThrowawayAccount3Million - When you call CalculateMe, you can choose to specify which argument to pass. For example, if you want to use only strB, then call CalculateMe(,"second"). Similarly, if you want to use just the first parameter, call CalculateMe("first"). If you do not wish to send any parameter, simply call CalculateMe.
Since you did ignore his question, I will ask again ;) At runtime, how do you tell whether the optional parameter was supplied?
@ThrowawayAccount3Million Use this statement which will compare your parameter with Nothing and do stuff based on that: If TheParameter Is Nothing Then [statements...] I did a test-run on it and I usedTheParameter as a String... so what I guess is that it works with any type of Object.
@ThrowawayAccount3Million See answer below excerpted from Chip Pearson.
|
14 
Public Sub CalculateMe(Optional varA As Variant, Optional varB as Variant)

Excerpts from Chip Pearson's excellent explanation:

Rules governing the use of optional parameters:

  • The Optional keyword must be present to make a parameter optional.
  • The data type should be (but need not be, see below) a Variant data type.
  • The optional parameter(s) must be at the end of the parameter list.
  • The IsMissing function will work only with parameters declared as Variant. It will return False when used with any other data type.
  • User defined 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

Here, both L1 and L2 are required but P1 and P2 are optional. Since both are Variant types, we can use IsMissing to determine whether the parameter was passed in. IsMissing returns True if the Variant parameter is omitted, or False if the Variant parameter is included. If the data type of the optional parameter is any data type other than Variant, IsMissing will return False.

Improve this answer

Comments

3

Instead of CalculateMe(,strB) you can use

dblA = CalculateMe strB:="B"
Improve this answer

2 Comments

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
@AnkitBajpai Again, this is an answer.
0

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:

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, then do like:

dblA = CalculateMe(, strB)

Or:

dblA = CalculateMe(strA)

Otherwise, if you are always passing both arguments, then it doesn't make sense to have them Optional.

Improve this answer

2 Comments

This is correct, I am passing bot arguments but then in another Subroutine calling the same function I will set one of the arguments (the string variable) to = vbNullString
why would you do that? You could simply pass a nullstring instead: Calculate("", strB) etc... Honestly it sounds like you are trying to be too fancy with your approach, without really understanding what you're doing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.