Populate VBA Array with list of values in native code

Question

Hoping there is a quick answer to this question....

I have an array and I want to populate it with a list of arguments.

Sub EasyArrayInput()
Dim myArr() as variant
myArr = ("string1", "string2", "string3")
End Sub

I am well aware of how to loop through and populate with a for/next or do/while, but it would be nice to be able to populate an array when the values wont change without using a hardcoded method.

Sub UsualMethodThatIDontWantToDo()
Dim myArr(1 to 3) as variant
myArr(1) =  "string1"
myArr(2) =  "string2"
myArr(3) =  "string3"
End Sub

Is there anyway to do it in a method similar to the first code snippet? I would prefer to do it that way. I apologize if this question has been asked/answered, but I'm not quite sure what the method I am asking about is called.

Thanks in advance!

Edit: Solution

The code snippet below (from the link that chancea sent) will create an array that is a variant and exaclty what I wanted.

Sub EasyArrayInput()
Dim myArr() as variant
myArr = Array("string1", "string2", "string3")
End Sub

The next code snippet looks to be useful for if you only have strings and don't want to initialize a variant:

Sub EasyArrayInput()
Dim myArr() as String
myArr = Split("String1,String2,String3", ",")
End Sub

This is a duplicate of stackoverflow.com/questions/19369132/… — chancea
– chancea, Commented Feb 19, 2015 at 16:04
Thanks chancea, I wasn't quite sure what I needed to look up but found the solution from your link. I have added the solution in my question. Really appreciate how quickly you were able to help! — JSS
– JSS, Commented Feb 19, 2015 at 16:17

basodre · Accepted Answer · 2015-02-19 16:15:12Z

10

How about?

Sub EasyArrayInput()
    Dim myArr() As Variant

    myArr = Array("string1", "string2", "string3")
End Sub

answered Feb 19, 2015 at 16:15

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rory · Accepted Answer · 2015-02-19 16:10:05Z

3

Assuming you have some sort of numeric sequence, you can do something like this:

Dim myArray()
myArray = [TRANSPOSE(INDEX("string"&ROW(1:10),))]

but frankly I think a loop is clearer.

answered Feb 19, 2015 at 16:10

Rory

34.2k5 gold badges40 silver badges42 bronze badges

2 Comments

JSS Over a year ago

That would work and I would use it, but the code that I am writing will be transferred from workbook to workbook with different scopes/sheets. I appreciate your help though!

Rory Over a year ago

I'm not sure I see the relevance, but OK.

Collectives™ on Stack Overflow

Populate VBA Array with list of values in native code

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related