VBA Function Optional parameters

Question

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

I know this is an old question now, but are you trying to pass an array? Then loop through x items in the array? — jcarroll, May 30 '16 at 18:06
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. — ProtoVB, May 31 '16 at 16:03

score 18 · Accepted Answer · answered Feb 27 '15 at 17:07

18

Change your sub and add ByVal

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

answered Feb 27 '15 at 17:07

Pankaj Jaju

5,371
2
25
41

7

At runtime, how do you tell whether the optional parameter was supplied? – Throw Away Account Nov 23 '16 at 14:25
@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`. – Pankaj Jaju Nov 23 '16 at 14:37
6

Since you did ignore his question, I will ask again ;) At runtime, how do you tell whether the optional parameter was supplied? – MushyPeas Feb 27 '17 at 16:51
1

@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 used`TheParameter` as a String... so what I guess is that it works with any type of Object. – Sreenikethan I Apr 27 '17 at 19:51
1

@ThrowawayAccount3Million See answer below excerpted from Chip Pearson. – Christopher Peisert Jan 11 '18 at 05:29
@MushyPeas See answer below excerpted from Chip Pearson. – Christopher Peisert Jan 11 '18 at 05:30

score 12 · Answer 2 · edited May 08 '18 at 21:49

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.

score 4 · Answer 3 · edited Nov 29 '17 at 15:52

4

Instead of CalculateMe(,strB) you can use

dblA = CalculateMe strB:="B"

edited Nov 29 '17 at 15:52

GhostCat

137,827
25
176
248

answered Nov 29 '17 at 15:33

user9026558

41
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/18101580) – Ankit Bajpai Nov 29 '17 at 19:16
3

@AnkitBajpai Again, this *is* an answer. – Rob Nov 30 '17 at 02:44

score 0 · Answer 4 · answered Feb 27 '15 at 18:18

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.

answered Feb 27 '15 at 18:18

David Zemens

53,033
11
81
130

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 – ProtoVB Feb 27 '15 at 18:30
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. – David Zemens Feb 27 '15 at 19:07

VBA Function Optional parameters

4 Answers4

Example

Linked