Using array with selec case statement

[nagure]

Hello to everyone!

I'm a newbie with vba so i would like to know is my idea is viable

I would like to allow my user to update a select case statament without having them to edit the code.
I just wrote values i want to use in select case in a range and built an array:

Dim BOX_INPS As Variant
BOX_INPS = Worksheets("CONFIGURAZIONE").Range("G2:G4")

Now i wonder if is possible to write a macro like this
select case
case <<array contents, values in Range("G2:G4") >>
do this
case else (or another array )
do this
end select

If is not possible i would to know if there is a way to split array into
string and have select case working with


Thank you in advance!

 

[Rick Rothstein]

Hello to everyone!

I'm a newbie with vba so i would like to know is my idea is viable

I would like to allow my user to update a select case statament without having them to edit the code.
I just wrote values i want to use in select case in a range and built an array:

Dim BOX_INPS As Variant
BOX_INPS = Worksheets("CONFIGURAZIONE").Range("G2:G4")

Now i wonder if is possible to write a macro like this
select case
case <<array contents, values in Range("G2:G4") >>
do this
case else (or another array )
do this
end select

If is not possible i would to know if there is a way to split array into
string and have select case working with

Why are you trying to use an array... is it because the number of cells in the range (G2:G4 in your example) can vary?

 

[nagure]

Why are you trying to use an array... is it because the number of cells in the range (G2:G4 in your example) can vary?

Exactly: I have certain codes (number) in the array but it could happen they may change so, in order to avoid to hard code the select case, i was wondering if i can built the array for the case on the fly.

 

[Rick Rothstein]

Exactly: I have certain codes (number) in the array but it could happen they may change so, in order to avoid to hard code the select case, i was wondering if i can built the array for the case on the fly.

I understood about the number themselves changing, what I was asking about is if the number of cells in the range could change. For example, could you want to use the numbers in G2:G4 one time and maybe G2:G7 another time?

 

[nagure]

I understood about the number themselves changing, what I was asking about is if the number of cells in the range could change. For example, could you want to use the numbers in G2:G4 one time and maybe G2:G7 another time?

For that i was thinking to dynamically build the range length calculating the last used row
Btw thank you for your time.