Initializing Arrays with a single statement

cryosis

New Member
Joined
Jul 28, 2002
Messages
23
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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?
 
Upvote 0
Either:
Code:
Dim myArray As Variant
myArray = Array("string1", "string2")
or:
Code:
Dim myArray() As String
myArray = Split("string1,string2", ",")
 
Upvote 0
Either:
Code:
Dim myArray As Variant
myArray = Array("string1", "string2")
or:
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,224,084
Messages
6,176,270
Members
452,718
Latest member
Nyxs_Inquisitor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top