Declare a Constant Array

[mdmackillop]

Hi,

Can I declare an array in a similar fashion to a constant so that the data can be used by separate elements of a userform and sent to a function for processing. I don't want to put them on the Userform itself, if I can avoid it.
Something like the following


Cols = Array("A", "D", "G", "H")

Sub Test1()
'Cols = Array("A", "D", "G", "H")
Range(Cols(0) & "3").Select
End Sub

Sub test2()
Dim Cols()
'Cols = Array("A", "D", "G", "H")
For Each gc In GetCols(Cols)
msg = msg & gc & ", "
Next
MsgBox msg
End Sub

Function GetCols(Cols() As Variant) As Variant
Dim MyCols()
ReDim MyCols(UBound(Cols))
For i = 0 To UBound(Cols)
MyCols(i) = Range(Cols(i) & ":" & Cols(i)).Column()
Next
GetCols = MyCols
End Function

[Richie(UK)]

Hi MD,

I don't believe that you can declare a Constant array.

Possible alternatives:

1. A Public array variable that you fill with a 'starter' routine.

2. Store the data in a worksheet.

[sixth sense]

How about a public function that returns the array. Function works just like any variables.

Try this.


Public Function Arr()
Arr = Array("a", "b", "c")
End Function

Sub try()
For Each a In Arr
MsgBox a
Next a
End Sub

[Zack Barresse]

That's pretty good Sixth Sense. :yes If that were the method, as I sometimes find myself lacking options, you can add different qualifying lines to your code so you don't have to keep writing multiple Public Functions. You can just call from the sub routine and assign them in your Function ...


Option Explicit
Public Function Arr(test)
Select Case test
Case "yes"
Arr = Array("a", "b", "c")
Case "no"
Arr = Array("d", "e", "f")
End Select
End Function

Sub try()
Dim a As Variant, test As String
test = "no"
For Each a In Arr(test)
MsgBox a
Next a
End Sub

Change the 'test = "no" ' to whatever you wanted, msgbox, returned value, whatever. Just thought I'd throw that out there, fwiw.

[mdmackillop]

Hi,
Thanks to all. I've decided to use the following, (which works on my test form at least!)
MD


Function Arr(MySet As String)
Select Case MySet
Case Is = "A"
Arr = Array("A", "B", "C", "D")
Case Is = "B"
Arr = Array("E", "F", "G", "H")
Case Is = "C"
Arr = Array("I", "J", "K", "L")
End Select

End Function
Private Sub UserForm_Initialize()
For Each A In Arr("B")
ListBox1.AddItem A
Next
End Sub