Microsoft Access Database Development With VBA

VBA Functions: Choose

   

Introduction

To provide an alternative to  check whether a condition is True or False and take an action, the Visual Basic language provides a function named Choose. Its syntax is:

Public Function Choose( _
   ByVal Index As Double, _ 
   ByVal ParamArray Choice() As Variant _
) As Variant

This function takes two required arguments. The fist argument is equivalent to the ConditionToCheck of our If...Then formula. For the Choose() function, this first argument must be a number. This is the value against which the second argument will be compared. Before calling the function, you must know the value of the first argument. To take care of this, you can first declare a variable and initialize it with the desired value. Here is an example:

Private Sub cmdFunction_Click()
    Dim Status As Byte, EmploymentStatus As String

    Status = 1

    EmploymentStatus = Choose(Status, ...)

    MsgBox ("Employment Status: " & EmploymentStatus)
End Sub

The second argument can be the Statement of our formula. Here is an example:

Choose(Status, "Full Time")

The second argument is actually a list of values and each value has a specific position referred to as its index. To use this function as an alternative to the If...Then...Else operation, you can pass two values for the second argument. To use the function in an If...Then...Else implementation, pass two values for the second argument. Here is an example:

Choose(Status, "Full Time", "Part Time")

The second argument to the function, which is the first value of the Choose argument, has an index of 1. The third argument to the function, which is the second value of the Choose argument, has an index of 2. 

When the Choose() function is called, if the first argument has a value of 1, then the second argument is validated. If the first argument has a value of 2, then the third argument is validated. As mentioned already, you can retrieve the returned value of the function and use it however you want. Here is an example:

Private Sub cmdFunction_Click()
    Dim Status As Integer, EmploymentStatus As String

    Status = 2

    EmploymentStatus = Choose(Status, "Full Time", "Part Time")

    MsgBox "Employment Status: " & EmploymentStatus
End Sub

This would produce:

Choose

To use the Choose function as an alternative to the If...Then...ElseIf...ElseIf condition, you can pass as many values as you judge necessary for the second argument. The index of the first member of the second argument would be 1. The index of the second member of the second argument would be 2, and so on. When the function is called, it would first get the value of the first argument, then it would check the indexes of the available members of the second argument. The member whose index matches the first argument would be executed. Here is an example:

Private Sub cmdFunction_Click()
    Dim Status As Byte, EmploymentStatus As String

    Status = 3

    EmploymentStatus = Choose(Status, _
                              "Full Time", _
                              "Part Time", _
                              "Contractor", _
                              "Seasonal")

    MsgBox "Employment Status: " & EmploymentStatus
End Sub

This would produce:

Choose

So far, we have used only strings for the values of the second argument of the Choose() function. In reality, the values of the second argument can be almost anything. One value can be a constant. Another value can be a string. Yet another value can come from calling a function. Here is an example:

Private Function ShowContractors$()
    ShowContractors$ = "=-= List of Contractors =-=" & vbCrLf & _
                       "Martin Samson" & vbCrLf & _
                       "Geneviève Lam" & vbCrLf & _
                       "Frank Viel" & vbCrLf & _
                       "Henry Rickson" & vbCrLf & _
                       "Samuel Lott"
End Function

Private Sub cmdFunction_Click()
    Dim Status As Byte, Result$

    Status = 3

    Result = Choose(Status, _
                    "Employment Status: Full Time", _
                    "Employment Status: Part Time", _
                    ShowContractors, _
                    "Seasonal Employment")
    MsgBox Result
End Sub

This would produce:

Choose

The values of the second argument can even be of different types.

 
 
     
 

Previous Copyright © 2013 FunctionX, Inc. Next