B.5. Select Case Statement

 < Day Day Up > 

The Select Case statement is one of the most important in VBA, in my opinion. If you have ever had multiple If conditions that were difficult to write and even more difficult to read in code, it is likely that you could have benefited from a Select Case statement. This statement bascially evaluates a condition once, determines which cases meet the condition, and runs only that code assigned to the first case. Here is an example that takes input from a user and goes through two Select Case statements to give feedback to her.

 Dim x As String Dim y As Double x = InputBox("Enter characters in the box", "Input Required") Select Case IsNumeric(x)   Case True      y = CDbl(x)      Select Case y        Case 1 To 10          MsgBox "Your number was between 1 and 10", vbInformation        Case 20, 40, 60          MsgBox "Your number was 20, 40, or 60", vbInformation        Case Is > 10          MsgBox "Your number was greater than 10", vbInformation        Case Else          MsgBox "Your number was less than 1", vbInformation      End Select   Case False      MsgBox "You did not enter a number", vbInformation End Select 

When your run this code, notice that once a condition is met in a Case statement, it exits the statement. You can test this by typing in 20. You get only the message about the number being 20, 40, or 60. Even though that number is also greater than 10, you won't see that message. If you put the Case 20, 40, 60 line after the Case Is > 10 line, it would never run.

     < Day Day Up > 

    Integrating Excel and Access
    Integrating Excel and Access
    ISBN: 0596009739
    EAN: 2147483647
    Year: 2005
    Pages: 132

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net