Initializing Arrays with a single statement

[cryosis]

I have need an array filled with constants to run a macro of mine. Is there any way to assign multiple values to an array in a single statement. It might look something like this :

Dim MyArray as String
MyArray = ["Value", "Value2", "Value3"]

I'm pretty sure you can do it in other languages but I'm not sure about VBA.

Thanks,
Rick

 

[boyshawn]

Hi Rick, really thank you for the helpful solution!

 

[teatea]

Try

Dim myarray As Variant

myarray = Array("Cat", "Dog", "Rabbit")

It might help to take a look at the Array function help entry in the VBA help files, too. HTH.

Hi, I have tried this code but it doesn't work for me! I have tried

Dim myArray() As Variant
ReDim myArray(1 To 2) As Variant
myArray = Array("string1", "string2")

I have also tried removing the parenthesis after Dim myArray, removing the redim statement, defining as string instead of variant and using the split function... None of it works! It seems instead that the result is a one dimensional array with the value "String 2".

Does anyone have any idea of why this might be?

 

[RoryA]

Either:

Dim myArray As Variant
myArray = Array("string1", "string2")

or:

Dim myArray() As String
myArray = Split("string1,string2", ",")

 

[teatea]

Either:

Dim myArray As Variant
myArray = Array("string1", "string2")

or:

Dim myArray() As String
myArray = Split("string1,string2", ",")

Hi Rory A,

I found out what was wrong, I tried accessing myArray(1) and myArray(2), but overlooked the fact that these arrays will start from zero. So what worked in the end was
myArray = Split(",string1,string2", ",")

Thank you!