Array as case statement? (97)

Topic

New Reply

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

Select case array(I,J)
	Case (1,1)
	Case (1,2)
End select

Reply | Quote

Replies

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.

Reply | Quote

If I understand your question correctly, the answer is no. According to the VB documentation,[indent]

---

Select Case testexpression
…

The Select Case statement syntax has these parts:

Part	Description
testexpression	Required. Any numeric expression or string expression
…	…

---

[/indent]So your expression *must* evaluate either to a number or to a string; you can’t use arrays.

Reply | Quote

(Edited by MarkD on 07-Nov-02 06:38. Corrected error in second Select Case sequence.)

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

Reply | Quote

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

Reply | Quote

I post the following txt file to illustrate the route I

Reply | Quote

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#

Reply | Quote

tee hee! you’re right!

Reply | Quote

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

Reply | Quote

thanks again!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Mark, now correct me if I

Reply | Quote

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:

Erase varTemp1, varTemp2, aryGL

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

Reply | Quote

To reply further, as alternative to mutlidimensional array you can define a simple User Defined Type (UDT). This example is similar to previous one, in that Array function is used to populate 2 “temporary” arrays which in turn populate a UDT array. Note that the Type statement that defines the UDT must be declared in the Declarations section of a module:

Option Compare Database
Option Explicit
Option Base 1

‘******* USER DEFINED TYPE **********
Public Type GL
GLCode As Integer
LedgerCode As Double
End Type
‘******* USER DEFINED TYPE **********

Public Sub PopulateGLCodes(glblDirCode As Integer)

Dim arrayGL() As GL
Dim varTemp1 As Variant
Dim varTemp2 As Variant
Dim intDim As Integer
Dim x As Integer
Dim strMsg As String

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 Else
strMsg = “Invalid code! Process terminated.”
MsgBox strMsg, vbExclamation, “PROCESS TERMINATED”
Exit Sub
End Select

intDim = UBound(varTemp1)
ReDim arrayGL(1 To intDim)

For x = 1 To intDim
arrayGL(x).GLCode = varTemp1(x)
arrayGL(x).LedgerCode = varTemp2(x)
Debug.Print “GL Code = ” & arrayGL(x).GLCode & ” – ” & _
“Ledger Code = ” & arrayGL(x).LedgerCode
Next x

Erase varTemp1, varTemp2

End Sub

Using a UDT may be more intuitive (and less confusing) than a multidimensional array, as you can refer to the UDT’s “fields” by name in code – the VBE “Intellisense” feature displays the element names, as illustrated. Note that it seems like the first code in each pair can be typed as Integer (1100, etc) rather than Double? I’d recommend using UDT in cases where the array has to store different data types, or where more than 2 dimensions are required.

HTH

Reply | Quote

What Mark says is quite correct !!
Here is an excerpt from A97 help.

Returns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.

Remarks

The notation used to refer to an element of an array consists of the variable name followed by parentheses containing an index number indicating the desired element. In the following example, the first statement creates a variable named A as a Variant. The second statement assigns an array to variable A. The last statement assigns the value contained in the second array element to another variable.

Dim A As Variant
A = Array(10,20,30)
B = A(2)

The lower bound of an array created using the Array function is always zero. Unlike other types of arrays, it is not affected by the lower bound specified with the Option Base statement.

Note A Variant that is not declared as an array can still contain an array. A Variant variable can contain an array of any type, except fixed-length strings and user-defined types. Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.

HTH
Pat

Reply | Quote