VBA Case expression using Array

[kashms]

Hi

I need some help with the below sample code. Is it possible to use a variable as a case expression?

I have the following code that works.

For Each PivItem In _
    PT2.PivotFields("Brands").PivotItems
    Select Case PivItem.Name
        Case "Cola", "Pepsi", "7UP", "Fanta"
            PivItem.Visible = True
        Case Else
            PivItem.Visible = False
    End Select
Next PivItem

Instead of using actual names in the case expression I want to use a variable:

case BrandNames

instead of

Case "Cola", "Pepsi", "7UP", "Fanta"

I have been having no luck with an array variable. Is it at all possible?

Thanks!

 

[Sektor]

Something like this:

[COLOR="Blue"]Sub[/COLOR] Excerpt()
    
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
[COLOR="Green"]    ' Make sure array is always 0-based inspite of Option Base.[/COLOR]
    arr = VBA.Array("Cola", "Pepsi", "7UP", "Fanta")
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] PivItem [COLOR="Blue"]In[/COLOR] PT2.PivotFields("Brands").PivotItems
        [COLOR="Blue"]Select[/COLOR] [COLOR="Blue"]Case[/COLOR] PivItem.Name
            [COLOR="Blue"]Case[/COLOR] arr(0), arr(1), arr(2), arr(3): PivItem.Visible = [COLOR="Blue"]True[/COLOR]
            [COLOR="Blue"]Case Else[/COLOR]: PivItem.Visible = [COLOR="Blue"]False[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Select[/COLOR]
    [COLOR="Blue"]Next[/COLOR] PivItem
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[kashms]

Thanks for your quick reply!

I have more than 70 brandnames in the array and it is changing, so I would really like not to specify all options in the case expression. BTW all pivotitems in the array should be turned visible.

Is there a smart way (= short and concise) to reference all array elements in the case expression?

 

[Sektor]

It involves a pair lines of codes more, but result is still concise.
The arr is your array. I used Array function to generate simple array.

[COLOR="Blue"]Sub[/COLOR] Excerpt()
    
    [COLOR="Blue"]Dim[/COLOR] re [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]

[COLOR="Green"]    ' Create Regex object.[/COLOR]
    [COLOR="Blue"]Set[/COLOR] re = CreateObject("VBScript.RegExp")
    
[COLOR="green"]    ' Make sure array is always 0-based inspite of Option Base.[/COLOR]
    arr = VBA.Array("Cola", "Pepsi", "7UP", "Fanta")
    
[COLOR="green"]    ' Prepare pattern for Regex.[/COLOR]
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        s = s & arr(i) & "|"
    [COLOR="Blue"]Next[/COLOR]
    re.Pattern = "(" & Left(s, Len(s) - 1) & ")"

    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] PivItem [COLOR="Blue"]In[/COLOR] PT2.PivotFields("Brands").PivotItems
        PivItem.Visible = re.test(PivItem.Name)
    [COLOR="Blue"]Next[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[kashms]

Thanks again!

I will test it out.

 

[kashms]

SOLVED: VBA Case expression using Array

It worked!

Just some followup questions. I'm not sure I understand everything in your code, but:

1) what is meant by "Make sure array is always 0-based inspite of Option Base". Do I have to take special precautions?

2) in the last for each ... next statement how would I go about implementing a "nice" error msg if no PivItem is set to visible? Right now it is coming with error "Unable to set the Visible property of the PivotItem class".

Thanks again for your help!

 

[Sektor]

Re: SOLVED: VBA Case expression using Array

It worked!

1) what is meant by "Make sure array is always 0-based inspite of Option Base". Do I have to take special precautions?

2) in the last for each ... next statement how would I go about implementing a "nice" error msg if no PivItem is set to visible? Right now it is coming with error "Unable to set the Visible property of the PivotItem class".

1. I wrote a bit incorrectly. "Make sure" means that code is sure that array is 0-based. You can have special statement at the top of module "Option Base". By default, Option Base is 0. It means that lower bound of array is 0. You can overwrite this setting by explicitly stating that your array is 1-base this way:

Dim arr(1 To 10) As Variant

In this case arr's lower bound is 1. Since I don't know whether you have such statement or not, I make sure that it's always 0-based. The thing is if I would write arr = Array(...., then lower bound would be inherited from Option Base. If it's 1, then arr's lower bound is 1. If it's 0, arr's lower bound is 0.
arr = VBA.Array(... notion makes lower bound of array ALWAYS 0. I didn't see this notion in MS documentation.
Play with this. Try removing/adding "VBA.", and you will see that VBA.Array is always 0-based and Array inherits lower bound from Option Base.

[COLOR="Blue"]Option[/COLOR] [COLOR="Blue"]Explicit[/COLOR]
[B][COLOR="Blue"]Option[/COLOR] [COLOR="Blue"]Base[/COLOR] 1[/B]

[COLOR="Blue"]Sub[/COLOR] ArrayTest()

    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    arr = [COLOR="Red"][B]VBA.Array[/B][/COLOR]("1", "2", "3", "4")
    
    MsgBox "Lower bound: " & [COLOR="Blue"]LBound[/COLOR](arr) & vbNewLine & _
           "Upper bound: " & [COLOR="Blue"]UBound[/COLOR](arr)

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

2. You can use error handler like this:

[COLOR="Blue"]Sub[/COLOR] Excerpt()
    
    [COLOR="Blue"]Dim[/COLOR] re [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]

    ' Create Regex object.
    [COLOR="Blue"]Set[/COLOR] re = CreateObject("VBScript.RegExp")
    
    ' Make sure array is always 0-based inspite of [COLOR="Blue"]Option[/COLOR] Base.
    arr = VBA.Array("Cola", "Pepsi", "7UP", "Fanta")
    
    ' Prepare pattern for Regex.
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        s = s & arr(i) & "|"
    [COLOR="Blue"]Next[/COLOR]
    re.Pattern = "(" & Left(s, Len(s) - 1) & ")"

    [COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]GoTo[/COLOR] ERROR_HANDLER
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] PivItem [COLOR="Blue"]In[/COLOR] PT2.PivotFields("Brands").PivotItems
        PivItem.Visible = re.test(PivItem.Name)
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]

ERROR_HANDLER:
    MsgBox Err.Description, vbCritical, "Ooops, error!"

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]