Switch Case statements

ermy78 · October 19th, 2006, 04:44 PM

Is there a way to do a switch case statement in VB like you can in C++ or do I need to write a bunch of If Else statements? I tried to do this line and it doesn't look like it's actually looking at the object that strDevice(iIndex) represents. I tried brackets around it and that doesn't work. I tried ['" & strDevice(iIndex) & "'] as well as without the brackets to no avail. strDevice(iIndex) is an array of CS, CS-A, CS-B, etc.

If (Not bLoop And (Not IsNull(strDevice(iIndex)))) Then...

If there's a way to do it in one line like I attempted here, that'd be awesome, but if not, I'd like to do it somewhat less efficiently in a switch case statement.
All help is appreciated! Thanks in advance.

Bob Bedell · October 19th, 2006, 05:28 PM

The VB analog for the 'switch' keyword (also used in C#) is the Select Case statement. From the VBA helpfile:

Code:

Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select

However, I don't see the problem with your code as written.

Code:

Sub Test()
     Dim n(0) As Integer
     Dim b As Boolean
     n(0) = 1
     b = False

     If (Not b And (Not IsNull(n(0)))) Then
        Debug.Print "Run"
     End If
End Sub

The above creates an array with a single element, initializes that element to an Integer, accesses the element at its index, evaluates for null, and the Debug statement runs. Seems like a perfectly valid line of code.

What do you mean by "it doesn't look like it's actually looking at the object that strDevice(iIndex) represents"?

HTH,

Bob

Bob Bedell · October 19th, 2006, 08:49 PM

Come to think of it, I don't think String arrays can store Null values in VBA anyway, so evaluating their elements for Null wouldn't make any sense. In VBA, I believe the only type of array elements you could assign Null values to would be elements of a Variant array.

Seems to me you would want to do your Null check elsewhere, i.e., when assigning values to the String array elements in the first place. Then if the value you are attempting to assign to the element is a Null, replace it with an empty string.

Code:

Sub Test()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim intIndex As Integer
    Dim astrArray(1) As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM Table1", dbOpenSnapshot)

    intIndex = 0

    With rst
        .MoveLast
        .MoveFirst

        ' If value of Field object is Null,
        ' place empty string in array element.
        For Each fld In rst.Fields
            If IsNull(fld.Value) Then
                astrArray(intIndex) = ""
            Else
                astrArray(intIndex) = fld.Value
                intIndex = intIndex + 1
            End If
        Next fld

    End With

    rst.Close: Set rst = Nothing
    dbs.Close: Set dbs = Nothing

End Sub

Then you could re-write your code stub as:

Code:

If (Not bLoop And Len(strDevice(iIndex)) > 0) Then...

Might want to check your data, but I would be extremely surprised if you are actually loading any Null values at all into your array (assuming its a string array, as your code suggests).

HTH,

Bob

ermy78 · October 20th, 2006, 08:58 AM

strDevice = Array("CS", "CS-A", "CS-B", "CS-C", "CS-D", "CS-E", "CS-F", "IO", "IO-A", "IO-B")

Each value of the array (CS, CS-A, etc.) is a field (combo box) in form A and also a column in table A. However, when they transfer to Form B (and table B), they will all be in the format CS?### or CS?###-? under one column titled devicename and the value found in the combo box will be under another column labeled type. I'm trying to verify that the type field (combo box) from Form A was null. I have a feeling the only way to do this is with the select case.
Thank you very much for your quick and helpful response Bob!