Using an Array as argument in Case Select statement

Question

Based on certain information, my excel file is in a certain 'status'. For example we have the process of an service; (1)request, (2)process, (3)payment. After checking if all the information is provided a function returns a 1 or a 0. 1 is all the information is present, 0 if it is not.

Dim Status(1 to 3) as Integer

CheckFunction(Request)
Status(1)= CheckFunction 'Return 1 
CheckFuntion(Process)
Status(2)= CheckFunction 'Return 1
CheckFuntion(Payment)
Status(3)= CheckFunction 'Return 0

Now my array looks like this

Status(1, 1, 0)

This file should now be in the 'Process' status. I want to use this so determine the current status of the service, like;

Select Case Status()

Case (1, 0, 0)
  'Some code
Case (1, 1, 0)
  'Some code
Case (1, 1, 1)
  'Some code

End Select

But i cant get this to work properly. Iv got a total of 15 different statuses and this to me seems to most elegant way to do this.

Can someone help me out?

Vityata · Accepted Answer · 2018-07-02 14:56:48Z

4

Comparing arrays cannot be done explicitly - thus the below code gives Type Mismatch (Error 13):

Public Sub TestMe()            
    Debug.Print Array(1, 2, 3) = Array(1, 2, 3)            
End Sub

Thus arrays are compared by comparing every element of them or by some other business logic (e.g., one may consider that arrays are equal if the first 2 elements are the same or if the size is the same).

The simplest solution would be probably to transform the array to a string and to compare this string with another string. Then, this would work:

Public Sub TestMe()

    Dim status As Variant: status = Array(1, 1, 0)

    Select Case Join(status, "")
        Case "100"
            Debug.Print 100
        Case "110"
            Debug.Print 110
        Case "111"
            Debug.Print 111
    End Select

End Sub

edited Jul 2, 2018 at 14:56

answered Jul 2, 2018 at 14:54

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pᴇʜ Over a year ago

I recommend to compare strings as Case "100" to avoid implicit cast of values into strings and don't mess up with Case 001

Vityata Over a year ago

@Pᴇʜ - Case 001 is a good point. Indeed 010 would be the same.

JNevill · Accepted Answer · 2018-07-02 15:02:15Z

1

You could also just add up the values:

 For each elem in status
     statussum=statussum + elem
 Next

Then do your SELECT/CASE on that:

 SELECT CASE statussum
     Case 1
     Case 2
     Case 3
 End Select

Also, since it doesn't seem likely that you would have 001 or 101 or 011 you could check each element of the array in a SELECT:

 If Status(1) = 1 Then
     ...
 ElseIf Status(2) = 1 Then
     ...
 ElseIf Status(3) = 1 Then
     ...
 End If

Lastly, in a loop you could detect status. Which would be helpful if you do have instances of 001 or 011 or 101:

 Dim statusCount as Integer, maxStatus as Integer
 For each elem in Status
     statusCount = statusCount + 1
     if elem = 1 then maxStatus = statusCount
 Next elem
 Debug.print maxStatus

edited Jul 2, 2018 at 15:02

answered Jul 2, 2018 at 14:55

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

3 Comments

Pᴇʜ Over a year ago

The first example will fail because 001, 010 and 100 have the same sum. You would need to multiply the first item with 100 the second with 10 and the third with 1 and then sum them up.

JNevill Over a year ago

I get the impression that the condition with preceding 0 doesn't exist in OP's scenario. That being said I think using an array, in the first place, is probably not the best choice here (if that assumption holds true).

Pᴇʜ Over a year ago

This was not clear to me, but if that is the case I conclude with you. He could easily count the status up then from state 1 (request) to state 3 (payment) then without using an array.

Collectives™ on Stack Overflow

Using an Array as argument in Case Select statement

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related