Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array as case statement? (97)

    Array as case statement?
    I was wondering could I use an array as the CASE in a CASE STATEMENT like below?

    <pre>Select case array(I,J)
    Case (1,1)
    Case (1,2)
    End select
    </pre>


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Array as case statement? (97)

    I've never tried it - but I think it might actually want to look at the value of the array element rather than the address of the element. But give it a try and see what happens.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array as case statement? (97)

    If I understand your question correctly, the answer is no. According to the VB documentation,
    <hr>Select Case testexpression
    ...

    The Select Case statement syntax has these parts:

    <table border=1><td>Part</td><td>Description</td><td>testexpression</td><td>Required. Any numeric expression or string expression</td><td>...</td><td>...</td></table><hr>
    So your expression *must* evaluate either to a number or to a string; you can't use arrays.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Array as case statement? (97)

    <P ID="edit" class=small>(Edited by MarkD on 07-Nov-02 06:38. Corrected error in second Select Case sequence.)</P>As noted, don't think you can do what you want to do using simple Select Case statement. You CAN test for value of an array, and sort of test for the address of the array's elements by concatenating the numerical values as a text string, but in either case the Select Case statement must be nested in a For... Next loop, as illustrated in sample code:

    Public Sub ArraySelectCase()

    Dim strArray(1 To 2, 1 To 2) As String
    Dim n As Integer
    Dim x As Integer
    Dim y As Integer

    For n = 1 To 2
    strArray(n, 1) = "ABC" & n
    strArray(n, 2) = "XYZ" & n
    Next n

    'Test for value of array:
    For x = LBound(strArray, 1) To UBound(strArray, 1)
    For y = LBound(strArray, 2) To UBound(strArray, 2)
    Select Case strArray(x, y)
    Case "ABC2"
    MsgBox "x = " & x & vbCrLf & "y = " & y, _
    vbInformation, "CASE ABC2"
    Case "XYZ1"
    MsgBox "x = " & x & vbCrLf & "y = " & y, _
    vbInformation, "CASE XYZ1"
    End Select
    Next y
    Next x

    'Test for address of array elements:
    For x = LBound(strArray, 1) To UBound(strArray, 1)
    For y = LBound(strArray, 2) To UBound(strArray, 2)
    Select Case x & "," & y
    Case "1,1"
    'Do something
    MsgBox "Array Value: " & strArray(x, y), vbInformation, "CASE 1,1"
    Case "1,2"
    'Do something
    MsgBox "Array Value: " & strArray(x, y), vbInformation, "CASE 1,2"
    Case "2,1"
    'Do something else
    MsgBox "Array Value: " & strArray(x, y), vbInformation, "CASE 2,1"
    Case "2,2"
    'Do something else
    MsgBox "Array Value: " & strArray(x, y), vbInformation, "CASE 2,2"
    End Select
    Next y
    Next x

    End Sub

    If you can explain the purpose for this exercise, perhaps a more useful reply can be provided.

    HTH

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    Well Mark your post was definitely informative & enlightening. Essentially what I

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    I post the following txt file to illustrate the route I
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array as case statement? (97)

    This looks overly convoluted:

    For j = 1 To 2
    Select Case i
    Case 1
    If j = 1 Then aryGL(i, j) = 1100
    Else
    aryGL(i, j) = 60614907700#
    End If

    The j-loop is making things more complicated than needed. You can accomplish the same with the somewhat simpler

    Select Case i
    Case 1
    aryGL(i, 1) = 1100
    aryGL(i, 2) = 60614907700#

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    tee hee! you're right!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Array as case statement? (97)

    In fact, it can be even shorter. It would be different if you could compute the values from the array indices, but that's not the case. Also, you'll have to declare aryGL as a dynamic array, you can't Dim an array of the same name several times in a procedure. Look up Dim and ReDim in the online help.

    Dim aryGL() As Double
    'array by dir...
    Select Case glblDirCode
    'national...
    Case 1100
    'national has 9 SAP GL codes...
    ReDim aryGL(1 To 9, 1 To 2)
    aryGL(1, 1) = 1100
    aryGL(1, 2) = 60614907700#
    aryGL(2, 1) = 1101
    aryGL(2, 2) = 60614907701#
    ' etc. up to aryGL(9, 2)
    Case 1200
    'west has 11 SAP GL codes...
    ReDim aryGL(1 To 11, 1 To 2)
    aryGL(1, 1) = 1200
    aryGL(1, 2) = 60614907710#
    aryGL(2, 1) = 1201
    aryGL(2, 2) = 60614907711#
    ' etc. up to aryGL(11, 2)
    Case ...
    ...
    End Select

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    thanks again!

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Array as case statement? (97)

    I got a chance to look at this, you are right that is pretty convoluted looking. I'd use one of the simpler solutions suggested. The only thing I have to add is that you can use Array function to populate an array if like me you are too lazy to type out each element one at a time. Example:

    Public Sub TestArrayFunction(glblDirCode As Double)

    Dim aryGL() As Variant
    Dim varTemp1 As Variant 'must use variant with array function
    Dim varTemp2 As Variant
    Dim intDim As Integer
    Dim x As Integer
    Dim y As Integer

    Select Case glblDirCode
    Case 1100 'national has 9 SAP GL codes:
    varTemp1 = Array(1100, 1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108)
    varTemp2 = Array(60614907700#, 60614907701#, 60614907702#, _
    60614907704#, 60614907703#, 60614907705#, _
    60614907707#, 60614907706#, 60614907707#)
    Case 1200 'west has 11 SAP GL codes:
    'do same thing
    Case 1300
    'do same thing
    Case 1400
    'do same thing
    Case 1500
    'do same thing
    End Select

    'Note: If using Array function Lower bound is 0 unless specified otherwise by Option Base statement
    intDim = UBound(varTemp1)
    ReDim aryGL(1 To intDim, 1 To 2)

    For x = 1 To intDim
    aryGL(x, 1) = varTemp1(x)
    aryGL(x, 2) = varTemp2(x)
    Next x

    'Test results:
    For x = 1 To intDim
    For y = 1 To 2
    Debug.Print "Array Value: (" & x & "," & y & "): " & aryGL(x, y)
    Next y
    Next x

    Erase varTemp1
    Erase varTemp2

    End Sub

    I don't know if this is any simpler than other suggestions. Note: You can also use nested Array functions to create multi-dimensional array, but the results are flaky; if you try to access array values in normal fashion you get a "Subscript out of range" error. Not recommended!! Also note in this example Option Base is set to 1 in Declarations section of code module, otherwise lower bound of array created by Array function is 0 by default. Base 1 is more intuitive in this case.

    Also, previous reply, I neglected to provide means of exiting loop if you don't want to continue testing possible array values after first true condition is met in Select Case statement. To exit after first true condition is met, use Exit For statement for each For... Next loop. Revised sample sub:

    Public Sub SelectCaseArrayVal()

    'Test for array value
    Dim strArray(1 To 2, 1 To 2) As String
    Dim n As Integer
    Dim x As Integer
    Dim y As Integer
    Dim bExit As Boolean

    For n = 1 To 2
    strArray(n, 1) = "ABC" & n
    strArray(n, 2) = "XYZ" & n
    Next n

    For x = LBound(strArray, 1) To UBound(strArray, 1)
    For y = LBound(strArray, 2) To UBound(strArray, 2)
    Select Case strArray(x, y)
    Case "ABC1"
    MsgBox "x = " & x & vbCrLf & "y = " & y, vbInformation, "CASE ABC1"
    bExit = True
    Exit For
    Case "XYZ2"
    MsgBox "x = " & x & vbCrLf & "y = " & y, vbInformation, "CASE XYZ2"
    bExit = True
    Exit For
    Case Else
    'do nothing
    End Select
    Next y
    If bExit = True Then Exit For
    Next x

    End Sub

    If you DO want to test all possible array values and execute instructions for any true conditions met in Select Case statement, then leave out the Exit For statements.

    HTH

  12. #12
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    Thanks for the new twist Mark very helpful but my question is that must the aryGL be defined as a variant?

    I mean in my previous code I defined it as a DOUBLE b/c that is the extent of the value needed to be held in the array. By defining it as a variant instead aren

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Array as case statement? (97)

    You define an array as a variant when you don't know for sure what kind of data it's going to hold. If you want numeric values, you don't use a string array, for example.
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array as case statement? (97)

    Mark, now correct me if I

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Array as case statement? (97)

    In response to first question, aryGL() can be declared as a Double, if you are sure that's the type of data it'll be containing. The two "temporary" arrays (varTemp1 & varTemp2) must be declared as variants to be able to assign them the values passed as arguments to the Array function. The Array function returns a dynamic array. The Erase statement at end of sub is used to empty the arrays listed; a fixed array will be re-initialized, while in case of dynamic array (applicable here), all memory allocated to the array is released. The aryGL() array should also be "erased" once you are thru with it. Example:
    <pre>Erase varTemp1, varTemp2, aryGL</pre>

    In response to 2nd question, in sample code the two varTemp arrays & in turn the aryGL array are redimensioned (resized) based on the number of elements passed as arguments to each Array function (each should be passed same number). In this example, there will be 9 elements (or "rows") each with 2 "columns" for a total of 18 discrete items of data stored in the array. The For... Next loop at end of sample sub prints out all values stored in the array based on the array's upper bound and the number of dimensions; in this case a total of 18. There will NOT be 1108 "rows" (elements) in array unless you pass 1108 arguments to each Array function.

    HTH

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •