Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Function Optional parameters

Tags:

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
like image 871
ProtoVB Avatar asked Feb 27 '15 17:02

ProtoVB


1 Answers

Change your sub and add ByVal

Public Sub CalculateMe(Optional ByVal strA As String, Optional ByVal strB As String)
like image 169
Pankaj Jaju Avatar answered Sep 20 '22 18:09

Pankaj Jaju